I'm running into an interesting situation that I can't seem to solve. I have a set of (unbound) controls with various values provided by the user. When they click a button on the form, I am wanting to place these values into a Access table. The button executes the following code: (note: I have omitted the connection string - I have varified it is not the problem)

---------------
Dim ReportDatabase As New System.Data.OleDb.OleDbConnection(strConn)
Dim sqlSQL As New OleDb.OleDbCommand

ReportDatabase.Open()
sqlSQL.Connection = ReportDatabase
sqlSQL.CommandText = "INSERT INTO tblRequests (User, DateRequested, ReportName, TargetDate, Priority, Format, Status, Instructions) " & "VALUES ('" & strUserId & "', #" & Now.Date & "#, '" & txtReportName.Text & "', #" & dtpTargetDate.Text & "#, '" & cboPriority.Text & "', '" & cboFormat.Text & "', '" & cboStatus.Text & "', '" & txtInstructions.Text & "')"

sqlSQL.ExecuteNonQuery()
ReportDatabase.Close()
----------------

This code throws an OledbException: "Syntax error in INSERT INTO statement." However, I do not think there is a syntax error. Without the variables, the sqlSQL.CommandText expression is this:

-----------------
INSERT INTO tblRequests (User, DateRequested, ReportName, TargetDate, Priority, Format, Status, Instructions)
VALUES ('falsep', #4/6/2007#, 'Test', #4/12/2007#, 'High', 'Crystal Report', 'Unacknowledged', 'This is a test.')
-----------------

I have executed this SQL statement directly with the Access database in question, and things work just fine. There is nothing wrong with the SQL as far as I can tell - this leads me to believe that something is wrong with my code.

Would any VB database regulars be able to tell me if you see anything out of the ordinary? Any help would be greatly appreciated.

EDIT:
I attempted this same action again, this time using the syntax recommended by Microsoft, and simplifying the INSERT statement to something so simple that it couldn't be screwed up.

------------------
Dim sqlSQL = New OleDbCommand("INSERT INTO tblRequests (User) VALUES ('Crap')", ReportDatabase)

ReportDatabase.Open()
sqlSQL.ExecuteNonQuery()
ReportDatabase.Close()
------------------

Again, I verified that all spelling was correct, and that the database I'm connecting to isn't corrupted. I can connect to the database fine (as I pull values from other tables for this same form). However, even with this completely simplified code, I still get the "Syntax error in INSERT INTO statement". I can run the SQL directly against the database myself and get the expected result.

I'm really at a loss here. Am I crazy? Have I forgotten something important?

__________________
<hr noshade size=\"1\">\"We do things my way, or the Hemingway...\"

hi there.. I don't see anything wrong there... the only weird thing is that you are getting data from combos and you are storing the string instead of the code (that could be a bad use of database, but not an error)...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========

Thanks for the reply. I had thought that this could have possibly been it, so I (as read in the edit I just made) eliminated all of that and tried a very simple INSERT INTO statement that doesn't pull any data from my form at all. No dice here either, it seems.

Is user a text data type???

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========

Indeed it is. I had checked that before (sorry I didn't mention that), but checked again when I read your reply just to make sure.

well.. the only idea i have is that you are not connecting to the database that you are thinking.. I know you said that the connection string is ok.. but maybe you are going elsewhere????

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========

Well, I didn't consider that possibility because I'm using the same connection string that is pulling data from tables that are in the same databse as this table. I figured I would test this anyway, so I threw a DataGridView control on the form and used the same connection string to get data FROM the tblRequests table. This worked fine.

In order to rule out something funny with my project file or mdb file, I'm going to build a new project and mdb file and test this same code. If you (or anyone) may have an idea in the meantime, feel free to post it - I'm willing to try anything.

Thanks for all your suggestions.

UPDATE
I built an entirely new project using an entirely new database and received the same error. In frustration, I tried to execute another non-query SQL expression. I replaced the contents of the INSERT statement with a DELETE statement:

-----------------
Dim sqlSQL = New OleDbCommand("DELETE FROM tblRequests WHERE User='Crap'", ReportDatabase)

ReportDatabase.Open()
sqlSQL.ExecuteNonQuery()
ReportDatabase.Close()
------------------

To my surprize, this worked. I changed it back to my INSERT statement, and got the error again. For fun, I also tried an update query - this also worked. It's almost as if VS "thinks" that there is something wrong with a perfectly good INSERT expression.

UPDATE 2
While staring at my table hoping the answer would just materialize, I discovered that the word "User" is a VB reserved phrase. Changing the name of the field resolved the issue. I cannot explain why it still worked for the delete and update queries.

Well, DELETE statements ignore field names, because they are irrelevant.
But as for the update... Did you include the USER column in that query?

I sure did. This is pretty much the most bizarre problem I've ever encountered.