dcsimg
Results 1 to 3 of 3

Thread: [RESOLVED] [Access] SQL Insert Syntax Error

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    8

    Resolved [RESOLVED] [Access] SQL Insert Syntax Error

    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

    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.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,035

    Re: [Access] SQL Insert Syntax Error

    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)

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2011
    Posts
    8

    Re: [Access] SQL Insert Syntax Error

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width