Why do I get "Syntax error in INSERT INTO statement"?

Question

Private Sub Save_Record()
    Dim conn As New OleDbConnection
    Dim cmd As New OleDbCommand
    Dim sSQL As String = String.Empty

    Try
        conn = New OleDbConnection(Get_Constring)
        conn.Open()
        cmd.Connection = conn
        cmd.CommandText = CommandType.Text

        If Me.txt_Forename.Tag = 0 Then
            sSQL = "INSERT INTO PlayerDatabase ( Age_Group, Surname, Forename, Rating, DOB, Address, Email, Position, Foot, Mins_Played, Goals, Assists, Yellow_Cards, Red_Cards)"
            sSQL = sSQL & "  VALUES(@Age_Group, @Surname, @Forename, @Rating, @DOB, @Address, @Email, @Position, @Foot, @Mins_Played, @Goals, @Assists, @Yellow_Cards, @Red_Cards)"

        Else
            sSQL = "UPDATE PlayerDatabase set Age_Group = @Age_Group, Surname = @Surname, Forename = @Forename, Rating = @Rating, DOB = @DOB, Address = @Address, Email = @Email, Position = @Position, Foot = @Foot, Mins_Played = @Mins_Played, Goals = @Goals, Assists = @Assists, Yellow_Cards = @Yellow_Cards, Red_Cards = @Red_Cards WHERE ID = @id"
            cmd.CommandText = sSQL
        End If


        cmd.Parameters.Add("@Surname", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txt_Surname.Text)) > 0, Me.txt_Surname.Text, DBNull.Value)
        cmd.Parameters.Add("@Forename", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txt_Forename.Text)) > 0, Me.txt_Forename.Text, DBNull.Value)

        cmd.Parameters.Add("@DOB", OleDbType.Date).Value = Me.dtp_DOB.Text
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = Me.txt_Address.Text
        cmd.Parameters.Add("@Age_Group", OleDbType.VarChar).Value = Me.cb_AgeGroup.Text
        cmd.Parameters.Add("@Rating", OleDbType.VarChar).Value = Me.cb_Rating.Text
        cmd.Parameters.Add("@Email", OleDbType.VarChar).Value = Me.txt_Email.Text
        cmd.Parameters.Add("@Position", OleDbType.VarChar).Value = Me.cb_Position.Text
        cmd.Parameters.Add("@Foot", OleDbType.VarChar).Value = Me.cb_Foot.Text
        cmd.Parameters.Add("@Mins_Played", OleDbType.VarChar).Value = Me.nup_MinsPlayed.Text
        cmd.Parameters.Add("@Goals", OleDbType.VarChar).Value = Me.nup_Goals.Text
        cmd.Parameters.Add("@Assists", OleDbType.VarChar).Value = Me.nup_Assists.Text
        cmd.Parameters.Add("@Yellow_Cards", OleDbType.VarChar).Value = Me.nup_YellowCards.Text
        cmd.Parameters.Add("@Red_Cards", OleDbType.VarChar).Value = Me.nup_RedCards.Text
        cmd.Parameters.Add("@ID", OleDbType.Numeric).Value = Me.txt_Forename.Tag
        cmd.ExecuteNonQuery()

        If Me.txt_Forename.Tag = 0 Then
            cmd.CommandText = "Select @@Identity"
            Me.txt_Forename.Tag = cmd.ExecuteScalar()
        End If
        MsgBox("Data has been saved.")

    Catch ex As Exception
        MsgBox(ErrorToString)
    Finally
        conn.Close()
    End Try
End Sub

Not sure what I'm doing wrong here. This is a adding to an Access database from various textboxes, comboboxes etc. This procedure runs when a button is pressed on a form that has the inputs. I'm a beginner at vb.net and programming in general so if it's something obvious I apologise.

Thanks

Answer 1

The word POSITION is reserved in MS-Access Jet Sql. This is the reason of the SYNTAX ERROR.
If you want to use it as a name for a column or for a table you need to put it between square brackets

sSQL = "INSERT INTO PlayerDatabase ( Age_Group, Surname, Forename, Rating, DOB, " & _ 
      "Address, Email, [Position], Foot, Mins_Played, Goals, Assists, Yellow_Cards, Red_Cards)"
....
sSQL = "UPDATE PlayerDatabase set Age_Group = @Age_Group, Surname = @Surname, " & _
       "Forename = @Forename, Rating = @Rating, DOB = @DOB, Address = @Address, " & _ 
       "Email = @Email, [Position] = @Position, Foot = @Foot, Mins_Played = @Mins_Played, " & _ 
       "Goals = @Goals, Assists = @Assists, Yellow_Cards = @Yellow_Cards, " & _ 
       "Red_Cards = @Red_Cards WHERE ID = @id"

A part from this you have another problem. OleDb doesn't recognize the parameters by their names. Usually you should use a question mark instead of a name, but Access allows this probably for some kind of portability toward its big cousin Sql Server. In any case you should add the parameters in the OleDbCommand collection in the same order in which the named placeholders appears in your query. So you need this order:

cmd.Parameters.Add("@Age_Group", OleDbType.VarChar).Value = Me.cb_AgeGroup.Text
cmd.Parameters.Add("@Surname", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txt_Surname.Text)) > 0, Me.txt_Surname.Text, DBNull.Value)
cmd.Parameters.Add("@Forename", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txt_Forename.Text)) > 0, Me.txt_Forename.Text, DBNull.Value)
cmd.Parameters.Add("@Rating", OleDbType.VarChar).Value = Me.cb_Rating.Text
cmd.Parameters.Add("@DOB", OleDbType.Date).Value = Me.dtp_DOB.Text
cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = Me.txt_Address.Text
cmd.Parameters.Add("@Email", OleDbType.VarChar).Value = Me.txt_Email.Text
cmd.Parameters.Add("@Position", OleDbType.VarChar).Value = Me.cb_Position.Text
cmd.Parameters.Add("@Foot", OleDbType.VarChar).Value = Me.cb_Foot.Text
cmd.Parameters.Add("@Mins_Played", OleDbType.VarChar).Value = Me.nup_MinsPlayed.Text
cmd.Parameters.Add("@Goals", OleDbType.VarChar).Value = Me.nup_Goals.Text
cmd.Parameters.Add("@Assists", OleDbType.VarChar).Value = Me.nup_Assists.Text
cmd.Parameters.Add("@Yellow_Cards", OleDbType.VarChar).Value = Me.nup_YellowCards.Text
cmd.Parameters.Add("@Red_Cards", OleDbType.VarChar).Value = Me.nup_RedCards.Text

and this last parameter should be added only if you have the UPDATE path not for the INSERT. (Assuming the ID column to be an AutoIncrement one)

If Me.txt_Forename.Tag <> 0 Then
   cmd.Parameters.Add("@ID", OleDbType.Numeric).Value = Me.txt_Forename.Tag
End If

Also another problem at the end when you try to read the @@IDENTITY value. Using the same command is fine, but you need to clear the parameters collection

   If Me.txt_Forename.Tag = 0 Then
        cmd.Parameters.Clear()
        cmd.CommandText = "Select @@Identity"
        Me.txt_Forename.Tag = cmd.ExecuteScalar()
    End If