"INSERT INTO" Command Help

I give up. I know this is something dumb, but I just can't figure it out.

I'm executing the following "INSERT INTO" statement on my (Jet) database.

INSERT INTO tblGrandLodgeCommunications (MemberID, BackupName, Date, Communication) VALUES ('204', John H. Doe', '40174.8401551157', 'blah');

I've verified the name of the table and the names of the four fields within the table. The simple table schematic is in the image below.

I keep getting "Syntax error in INSERT INTO statement."

If it helps, I've also attached the output from clicking "Copy exception detail to clipboard."

Any help will be much appreciated.

Thanks,
Bret

tblGrandLodgeCommunications.jpg
ExceptionDetail.txt
FOS-BretAsked:
Who is Participating?
 
HainKurtConnect With a Mentor Sr. System AnalystCommented:
or use this

SQL = "INSERT INTO tblGrandLodgeCommunications ([MemberID], [BackupName], [Date], [Communication]) VALUES ('" & MyMemberID & "', '" & MyBackupName & "', '" & MyDateString & "', '" & MyCommunication & "')"

maybe you are using some reserved words ;)
0
 
FOS-BretAuthor Commented:
Oops. Sorry. Here's the code:

    Private Sub SaveCommunication(ByVal WhichCommunication As String)
        Dim SQL As String
        Dim MyConnection As OleDbConnection
        Dim MyConnectionString As String
        Dim MyCommand As New OleDbCommand
        Dim FullName As String

        Dim MyDateString As String
        Dim MyMemberID As String
        Dim MyBackupName As String
        Dim MyCommunication As String

        MyConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataFile & ";"

        FullName = frmMain.grid1.Item("MasonicPrefix", frmMain.grid1.CurrentRow.Index).Value() & " " & frmMain.grid1.Item("FirstName", frmMain.grid1.CurrentRow.Index).Value() & " "
        If Trim(frmMain.grid1.Item("MiddleName", frmMain.grid1.CurrentRow.Index).Value()) <> "" Then
            FullName &= Trim(frmMain.grid1.Item("MiddleName", frmMain.grid1.CurrentRow.Index).Value()) & " "
        End If
        FullName &= frmMain.grid1.Item("LastName", frmMain.grid1.CurrentRow.Index).Value() & " " & frmMain.grid1.Item("Suffix", frmMain.grid1.CurrentRow.Index).Value()
        FullName = Trim(FullName)

        MyMemberID = frmMain.grid1.Item("ID", frmMain.grid1.CurrentRow.Index).Value()
        MyBackupName = FullName
        MyDateString = DateToDouble(DateTime.Now).ToString
        MyCommunication = WhichCommunication

        SQL = "INSERT INTO tblGrandLodgeCommunications (MemberID, BackupName, Date, Communication) VALUES ('" & MyMemberID & "', '" & MyBackupName & "', '" & MyDateString & "', '" & MyCommunication & "')"
        Debug.Print(SQL)
        MyConnection = New OleDbConnection(MyConnectionString)
        MyCommand = New OleDbCommand
        MyCommand.Connection = MyConnection
        MyCommand.CommandText = SQL
        MyCommand.Connection.Open()
        MyCommand.ExecuteNonQuery()
        MyCommand.Connection.Close()

    End Sub
0
 
HainKurtSr. System AnalystCommented:
INSERT INTO tblGrandLodgeCommunications (MemberID, BackupName, Date, Communication) VALUES ('204', John H. Doe', '40174.8401551157', 'blah');

-->

INSERT INTO tblGrandLodgeCommunications (MemberID, BackupName, Date, Communication) VALUES ('204', 'John H. Doe', '40174.8401551157', 'blah');

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
HainKurtSr. System AnalystCommented:
if you try this, maybe it works...

SQL = "INSERT INTO tblGrandLodgeCommunications (MemberID, BackupName, Date) VALUES ('" & MyMemberID & "', '" & MyBackupName & "', '" & MyDateString & "')"

if it works the problem is in memo field...
0
 
FOS-BretAuthor Commented:
HainKurt,

Thanks for the ultra quick reply. Unfortunately that was a typo on my part. I changed the name for the post and inadvertently deleted the tick mark before the name. The SQL being generated does include that tick mark.
0
 
HainKurtSr. System AnalystCommented:
I thougt it is a type too, I checked the code and it looks ok... maybe the problem is on memo field, thats why, if you try above (do not include 4th column) and try to insert record we will be sure...
0
 
FOS-BretAuthor Commented:
I just tried that (without the memo field) and I still get the error. Bummer.
0
 
HainKurtSr. System AnalystCommented:
I just tried a query similar to yours on a Access db

insert into users (Company, LastName, FirstName, EMail) values ('North Star', 'Kurt', 'Hain', 'hainkuer@ee.com')

and it has an ID as autonumber like your table... no issues here...

and you are getting the error here :

MyCommand.ExecuteNonQuery()

right?
0
 
HainKurtSr. System AnalystCommented:
try adding ; at the end... who knows...

SQL = "INSERT INTO tblGrandLodgeCommunications (MemberID, BackupName, Date, Communication) VALUES ('" & MyMemberID & "', '" & MyBackupName & "', '" & MyDateString & "', '" & MyCommunication & "');"
0
 
FOS-BretAuthor Commented:
I tried the ";" at the end. Still no luck.

Yes, I'm getting the error on the MyCommand.ExecuteNonQuery() statement.
0
 
HainKurtSr. System AnalystCommented:
like Date ;)
0
 
FOS-BretAuthor Commented:
That was it. Thanks very much. I appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.