What is wrong in this "Insert Into" syntax?

Question

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:

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

Answer 1

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.

Answer 2

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.