What is wrong in this "Insert Into" syntax?

VKSB 176 Reputation points
2024-03-13T13:23:11.25+00:00

Hi all,

I am trying to enter and save values in my Database but I am getting the Error message "Syntax Error in INSERT INTO statement."

I created Textboxes for entering the "values" & a Button to "Insert" values.

My Database is shown here:database

Here is my code:

 Private Sub SaveDetails_Btn_Click(sender As Object, e As EventArgs) Handles Boy_SaveDetails_Btn.Click
        pro = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\My Program Projects MS VS 2013\DataBase Project\Cities of World.accdb"
        
connstring = pro
        myconnection.ConnectionString = connstring
        myconnection.Open()
        Command = " Insert into Boy City Coordinates ([City],[State / District],[Country],[Time Zone],[Latitude],[Longitude]) Value ('" & Boy_CityNew_TxtBox.Text & "','" & Boy_StateDistrictNew_TxtBox.Text & "','" & Boy_CountryNew_TxtBox.Text & "','" & Boy_TimeZoneNew_TxtBox.Text & "','" & Boy_LatitudeNew_TxtBox.Text & "','" & Boy_LongitudeNew_TxtBox.Text & "')"

        Dim cmd As OleDbCommand = New OleDbCommand(Command, myconnection)

        'cmd.Parameters.Add(New OleDbParameter("ID", CStr(ID_TxtBox.Text)))
        cmd.Parameters.Add(New OleDbParameter("City", CStr(Boy_CityNew_TxtBox.Text)))
        cmd.Parameters.Add(New OleDbParameter("State / District", CStr(Boy_StateDistrictNew_TxtBox.Text)))
        cmd.Parameters.Add(New OleDbParameter("Country", CStr(Boy_CountryNew_TxtBox.Text)))
        cmd.Parameters.Add(New OleDbParameter("Time Zone", CDbl(Boy_TimeZoneNew_TxtBox.Text)))
        cmd.Parameters.Add(New OleDbParameter("Latitude", CDbl(Boy_LatitudeNew_TxtBox.Text)))
        cmd.Parameters.Add(New OleDbParameter("Longitude", CDbl(Boy_LongitudeNew_TxtBox.Text)))

        MsgBox("Record Saved")

        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myconnection.Close()
            Boy_CityNew_TxtBox.Clear()
            Boy_StateDistrictNew_TxtBox.Clear()
            Boy_CountryNew_TxtBox.Clear()
            Boy_TimeZoneNew_TxtBox.Clear()
            Boy_LatitudeNew_TxtBox.Clear()
            Boy_LongitudeNew_TxtBox.Clear()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

Can you please let me know what is wrong in the "Insert Into" syntax?

I couldn't spot it; tried removing the "Space" between the name such as "State / District" & used "State/District" & did the same to "TimeZone" ("Time Zone); but still failed.

Thanks

VKSB

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,562 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Jiachen Li-MSFT 26,191 Reputation points Microsoft Vendor
    2024-03-14T01:21:22.89+00:00

    Hi @VKSB ,

    Please refer to the code below.

            Command = "INSERT INTO [Boy City Coordinates] ([City], [State / District], [Country], [Time Zone], [Latitude], [Longitude]) VALUES (@City, @StateDistrict, @Country, @TimeZone, @Latitude, @Longitude)"
    
            Dim cmd As OleDbCommand = New OleDbCommand(Command, myconnection)
    
            cmd.Parameters.AddWithValue("@City", Boy_CityNew_TxtBox.Text)
            cmd.Parameters.AddWithValue("@StateDistrict", Boy_StateDistrictNew_TxtBox.Text)
            cmd.Parameters.AddWithValue("@Country", Boy_CountryNew_TxtBox.Text)
            cmd.Parameters.AddWithValue("@TimeZone", Boy_TimeZoneNew_TxtBox.Text)
            cmd.Parameters.AddWithValue("@Latitude", Boy_LatitudeNew_TxtBox.Text)
            cmd.Parameters.AddWithValue("@Longitude", Boy_LongitudeNew_TxtBox.Text)
    
    

    Best Regards.

    Jiachen Li


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments No comments

  2. Olaf Helper 40,576 Reputation points
    2024-03-13T13:27:16.36+00:00
    Command = " Insert into Boy City Coordinates ([City],[State / District]
    ,[Country],[Time Zone],[Latitude],[Longitude])  Value ('" & 
    Boy_CityNew_TxtBox.Text & "','" & Boy_StateDistrictNew_TxtBox.Text 
    & "','" & Boy_CountryNew_TxtBox.Text & "','" & 
     Boy_TimeZoneNew_TxtBox.Text & "','" & Boy_LatitudeNew_TxtBox.Text & "','" & Boy_LongitudeNew_TxtBox.Text & "')"
    

    First you generate the SQL statement not with parameter, but with dynamically added value (bad pratice) and later you assign parameter, which don't exists in SQL ???

    One wrong sign in the text field(s) and the SQL fails.