[RESOLVED] [Access] SQL Insert Syntax Error

[tcbuist]

Hello all,

I'm trying to take various values from text boxes and combo boxes on my form and insert them into a database, but I keep getting a syntax error.

The specific message is "Syntax error in INSERT INTO Statement."

I'm up to my eyes in quote marks here and I cannot figure out what's wrong.

Here's the code

Private Sub CmdRC_Click()
Dim SqlStr As String
Dim cno As Integer
Dim lco As Long
If cmbCall.Value <> "" Then
    cno = cmbCall.Value
Else
    MsgBox "You did not select a call number, selecting most recent.", vbOKOnly, "Error!"
    Me.cmbCall.SetFocus
    lco = cmbCall.ListCount - 1
    Me.cmbCall = cmbCall.ItemData(lco)
    cno = cmbCall.Value
End If
If CmbCallRes.Value <> "" Then
Else
    MsgBox "Error! No Result Selected!", vbCritical
    Exit Sub
End If
If txtEName.Value = "" Then
    MsgBox "Error! No Student Selected!", vbCritical
    Exit Sub
End If

SqlStr = " INSERT INTO [CallRecords] (CallNo, Student_EName, Student_KName, Student_Class, CDate, Time, Result) VALUES (" & cno & "," & "" & txtEName.Value & "" & "," & "" & txtKName.Value & "" & "," & "" & txtClass.Value & "" & "," & "" & txtCDate.Value & "" & "," & "" & txtCTime.Value & "" & "," & "" & CmbCallRes.Value & "" & ");"
'MsgBox SqlStr
DoCmd.RunSQL (SqlStr)

End Sub

I dunno what I'm doing wrong... sigh.

[si_the_geek]

Your commented-out code 'MsgBox SqlStr is good, but Debug.Print SqlStr is better - it copies the text to the Immediate window, which you can compare to your code more easily, and can also copy-and-paste if apt (to here for example).


There are a few things that aren't right...

First of all there is no point in having & "" & , as it is just a longer way to write & by itself. If you are trying to put quotes around string values, you should be using single quotes (eg: & "'" & ), and merging things to simplify it (eg: & "'" & "," & "'" & becomes & "','" & ).

A big problem (and enough to cause your current error by itself) is your bad field names... CDate and Time (plus possibly Result and others) are reserved words, and as such should not be used as a field/table name (it confuses the query parser within the database system). If you change the names (in the table design and your code), it is likely to work correctly.

For more information (including lists of Reserved words), see the article What names should I NOT use for tables/fields/views/stored procedures/...? from our Database Development FAQs/Tutorials (at the top of the Database Development forum)

[tcbuist]

Hello Si,

I worked out the kinks in my string on my own, but thanks for the advice on simplifying it and on using the Debug.Print command, much more useful than the message box.

In regards to the field names, I never even though about it while I was coding away but you're right, of course. A novice mistake from a novice, I suppose. I'll set about fixing this using the list you provided.

Thanks very much.