SQL Insert statement error

[SnoopFrogg]

I am attempting to write to an Access database table using this line of code:

[purple]insertIntoDB("INSERT INTO Mandrills(Time1, Animal, Loc, Eat, For, Soc, Agg, Ster, Act, View, Other, Location, ConMan, Indirect, Other2, Initials, Comments, NumObsAM, NumObsPM) VALUES('test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2', 'test2')");[/purple]

When I execute my program, I receive the following error:

[purple]System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
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 CopyFile.Form1.insertIntoDB(String sqlInsertStatement)[/purple]

I have run this query successfully in Access (I verified by seeing the added rows in my table).

I have executed the following similar query in Visual C# successfully (I verified the added rows, as well). The syntax for this query and the previous is similar so I'm not sure what I'm missing:

[purple]insertIntoDB("INSERT INTO test(test1, test2) VALUES('1', '2')");[/purple]

Any thoughts? The only cause I can think of is a possible length limitation for C# SQL statements.

 

[Coolstep]

SnoopFrogg,

(NumObsAM, NumObsPM)<--Are these varchars or numbers?

 

[SnoopFrogg]

They are text, along with every other field.

 

[NeilTrain]

make sure you escape for the single quote,

ie:

variable.Replace("'","''");

ots too bad Access is not returning a more descriptive error message. Try outputting yourt SQL text to the screen, then cutting and pasting it into access to see if you cant get a better error message.

 

[SnoopFrogg]

NeilTrain,

Thanks for the reply. I ran my query in Access and verified that it returned rows. When I execute the same query in Access it returns the above error. Also, when I run the following query,

[purple]insertIntoDB("INSERT INTO test(test1, test2) VALUES('3', '4')");[/purple]

it executes and adds a row to my table. So, I don't think it's a problem with my single quotes. If I'm missing something (that's probably smacking me in the face), fill me in.

Again, I appreciate the input.

 

[SnoopFrogg]

I found a solution!

I decided to go back to the drawing board (what I should have done when I first got this error but my brain was fried). Apparently C# doesn't like the two columns I have named 'Time' and 'For'. When I changed their names, C# executed my query and added a row to my table.

Thanks for getting my brain rolling!

 

[brd24gor]

Actually, you can still have those column names, but when you use them in an SQL query, you have to put square brackets around them. I have had the same problem naming a column 'Text'. For example:

INSERT INTO Test([Time], Place, [For]) VALUES('Now','There','Me');

 

[SnoopFrogg]

Thanks for the tip. Yet something else I should have thought of.