Friday, February 4, 2011

Is there anything wrong with this query?

INSERT INTO tblExcel (ename, position, phone, email) VALUES ('Burton, Andrew', 'Web Developer / Network Assistant', '876-9259', 'aburton@wccs.edu')

I've got an Access table that has five fields: id, ename, position, phone, and email...each one is plain text field with 50 characters, save for position which is 255 and id which is an autoincrement field. I'm using a VB.NET to read data from an Excel table, which gets pushed into a simple class that's used to fill out that query. I do the same thing with two other tables, whose data are pulled from a DB2 table and a MySQL table through. The other two work, but this simple INSERT loop keeps failing, so I don't think it's my "InsertNoExe" function that handles all the OleDb stuff.

So, um, does that query, any of the field titles, etc. look bogus? I can post other bits of code if anyone wants to see it.

EDIT: Fixed. I wasn't sure if the wide image counted as a Stack Overflow bug or not, which is why I left it.

EDIT 2: I'm dense. I use a try...catch to see the bogus query, and don't even check the ex.messsage. Gah.

INSERT INTO tblExcel (ename, position, phone, email) VALUES ('Burton, Andrew', 'Web Developer / Network Assistant', '876-9259', 'aburton@wccs.edu')

   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
   at EmployeeList.EmployeeDatabase.ExeNonQuery(String sql) in C:\andy\html\code\vb\EmployeeList\EmployeeDatabase.vb:line 263

Syntax error in INSERT INTO statement.

EDIT 3: Thank you, Chris.

  • Could you edit you post and post the text of the query as code?

    From EBGreen
  • The spacing of "Web Developer / Network Assistant" looks a little wonky, maybe there is a hidden character in there (carriage return?)

    I'd try taking the slash out, and see if the insert works, if not try taking all punctuation out. Then add it back and maybe you will be able to identify the bug.

  • For future reference, you can use Ctrl+Shift+Ins to copy text from a message box.

  • What error do you see?

    From Jay Mooney
  • I beleive "position" is a reserved word.

    Try...

    INSERT into tblExcel (ename, [position], phone, email) VALUES (...

    Reserved Words

    From Chris

0 comments:

Post a Comment