?
Solved

"INSERT INTO" Command Help

Posted on 2009-12-27
12
Medium Priority
?
203 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:FOS-Bret
  • 7
  • 5
12 Comments
 

Author Comment

by:FOS-Bret
ID: 26129165
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26129170
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26129181
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:FOS-Bret
ID: 26129189
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26129199
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
 

Author Comment

by:FOS-Bret
ID: 26129203
I just tried that (without the memo field) and I still get the error. Bummer.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26129237
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 26129253
try adding ; at the end... who knows...

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

Author Comment

by:FOS-Bret
ID: 26129260
I tried the ";" at the end. Still no luck.

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

Accepted Solution

by:
HainKurt earned 500 total points
ID: 26129262
or use this

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

maybe you are using some reserved words ;)
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26129263
like Date ;)
0
 

Author Closing Comment

by:FOS-Bret
ID: 31670279
That was it. Thanks very much. I appreciate the help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question