"INSERT INTO" Command Help

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

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');

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...

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.

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...

I just tried that (without the memo field) and I still get the error. Bummer.

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?

try adding ; at the end... who knows...

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

I tried the ";" at the end. Still no luck.

Yes, I'm getting the error on the MyCommand.ExecuteNonQuery() statement.

like Date ;)

That was it. Thanks very much. I appreciate the help.