locked
Syntax error in INSERT INTO statement...Please Help RRS feed

  • Question

  • User-415937349 posted

     I'm reading reading names from a text file and saving them in an Access database.  Everything works fine until I get to a name the doesn't have a middle name listed.  If I remove the middle name from my insert statement it works wonderfully.  If even tried making F2 = " " (one space) and F2 = "" (no spaces) and nothing works.  I have checked my table and all 3 fields have the "allow zero length" property set to "Yes" so I have no idea what is causing the error.  Can anyone out there help me out with this problem?  All and any help would be greatly appreciated.  My code is as follows:

    '--Entry in text file:
    '--"JOHN        ","             ","DOUGH        "

    F1 = arInfo(0).ToString().Replace(vbTab & "  ", "")
    F1 = Right(F1, Len(F1) - 1) : F1 = Left(F1, Len(F1) - 1)
    F1 = Replace(F1, vbTab, "")
    '--The above code returns: F1 = "JOHN"

    F2 = arInfo(1).ToString().Replace(vbTab & "  ", "")
    F2 = Right(F2, Len(F2) - 1) : F2 = Left(F2, Len(F2) - 1)
    F2 = Replace(F2, vbTab, "")
    '--The above code returns: F2 = "   "

    F3 = arInfo(2).ToString().Replace(vbTab & "  ", "")
    F3 = Right(F3, Len(F3) - 1) : F3 = Left(F3, Len(F3) - 1)
    F3 = Replace(F3, vbTab, "")
    '--The above code returns: F3 = "DOUGH"

    sqlInsert = "INSERT INTO Table(FirstName, MiddleName, LastName) " & _
                     "VALUES ('" & F1 & "', '" & F2 & "', '" & F3 & "')"

    Tuesday, December 16, 2008 2:46 PM

Answers

  • User-1199946673 posted

    Try setting the Required Property of the field to false...

    Also, read this

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 16, 2008 8:03 PM

All replies

  • User-1199946673 posted

    Try setting the Required Property of the field to false...

    Also, read this

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 16, 2008 8:03 PM
  • User-415937349 posted

     That's exactly what it was hans_v.  I thought I've already set the required property to false, but I guess I was mistaken.  Your reply made me go back and double-check myself.  Oh, and thanks alot for the link as I'm sure it will proove very useful to me in the future.  Thanks again for your time and help.  I greatly appreciate it.

    Wednesday, December 17, 2008 9:13 AM