Syntax error in INSERT INTO statement.

Question

Hi I have a program to communicate with database. One of the functions is to INSERT data into the database which is giving me a syntax error! I can't figure out why... INSERT INTO Report ('Date' 'Reporter' 'Details') VALUES ('27/1/2010' 'Mark Smith 11260(S)' 'gfdsgsdfgsdf') I also tried it like this: INSERT INTO Report (Date Reporter Details) VALUES ('27/1/2010' 'Mark Smith 11260(S)' 'gfdsgsdfgsdf') Can someone please help? Andrew

theLizard · Answer

then what you have here should work. string command = INSERT INTO Report (Date Reporter Details) VALUES (' + DateText.Text + ' ' + ReporterText.Text + ' ' + DetailsText.Text + '); Questions? what database engine are you using. What are the values being parsed What is the value of command on a break point. If there are no problems with the values and you are not reserved words I am not sure if Date is if it is you may need to bracket like [Date] otherwise there is no reason why you would get an error from what I see.

Andrew · Answer

Hi theLizard I didn't use integers as ID since the ID includes (s) or an other letter. I didn't even use auto number since users use their real identification that is state ID which includes (s). (s) stands for which country you come from... Hope I didn't confuse you ;)

theLizard · Answer

Hi Andrew I do not use Access so I don't know for sure how access works with ID but I would have expected that id's are integers what does (S) represent! Is ID your identifier or an auto increment just curious. I may also learn something new today... :)

Andrew · Answer

Hi theLizard Actually I read it much more than one time lol No the ID is of type TEXT which explains the single quotes. It is not integer for the reason that our ID is not just '224536' BUT '224536(S)' for example... Andrew

theLizard · Answer

Hi Andrew Sometimes you need to read things more than once this is one of those times generally ID's are integers so I think where you say WHERE ID = ' + impId + '; is your problem can you see anything wrong? you have single quotes either side of your impId these indicate a string data type remove the single quotes and your statement should work.

Andrew · Answer

Hi theLizard I had already tried [Time] but didn't make difference. I also tried changing A/D to A_D and changing Time to time but for nothing. I totally can't figure out the problem! Exception Details: System.Data.OleDb.OleDbException: Syntax error in UPDATE statement. Still thanks for your help theLizard!

theLizard · Answer

Hi Andrew at first glance it looks ok but I think Time may also be one of those words I tend to begin my vars in lower case this normally overcomes these types of issues date is not the same as Date so sql server does not complain. string MyString = UPDATE Md SET Name=' + NText.Text + ' A/D ( not sure what / would do ) =' + DText.Text + ' [Time] =' + TText.Text +' WHERE ID=' + ImpId + ';; other examples of how I name vars is Customer Name -> customerName Supplier Name -> supplierName

Andrew · Answer

theLizard first of all thanks for your last comment regarding reserved words as when I did as you told me ie [Date] instead of Date it worked fine :) Thanks for that. Another problem I am facing is a syntax error in an UPDATE statement... I am suspecting it is something similar but I can't figure out what :| string MyString = UPDATE Md SET Name=' + NText.Text + 'A/D=' + DText.Text + 'Time=' + TText.Text +' WHERE ID=' + ImpId + ';; The strange thing is that another similar function below works perfectly... I can't see the difference string MyString = UPDATE Progress SET DN =' + Dct.Replace(' '') + 'WHERE ID =' + ImpId + ';; What database engine are you using. : Ms Access What are the values being parsed . : All values are of type TEXT Thanks in advance!

Andrew · Answer

Hi theLizard I tried it both ways you told me string command = INSERT INTO Report (Date Reporter Details) VALUES (' + DateText.Text + ' ' + ReporterText.Text + ' ' + DetailsText.Text + '); and it gave me the usual: OLEDBEXception was unhandled by user code: Syntax error in INSERT INTO statement. :(

theLizard · Answer

why are you doing this? string command = INSERT INTO Report  + (Date Reporter Details)  + VALUES (' + DateText.Text + ' ' + ReporterText.Text + ' ' DetailsText.Text.Replace(' '') + '); do this string command =  INSERT INTO Report (Date Reporter Details) VALUES ( '  + DateText.Text + ' ' + ReporterText.Text + ' ' you missed the + here (DetailsText.Text = DetailsText.Text.Replace(' '')) + '); this should be (DetailsText.Text = DetailsText.Text.Replace(' '')) //if this does not work in line try doing it before you build the command string and just do + DetailsText.Text..

Andrew · Answer

Hi thanks for the reply. You are right about asking these questions because what I pasted was the output given when debugging. The real command is : string command = INSERT INTO Report  + (Date Reporter Details)  + VALUES (' + DateText.Text + ' ' + ReporterText.Text + ' ' DetailsText.Text.Replace(' '') + '); Date is just Text like everything else. The strange thing is that in another form the same format for an Insert statement was used. Thanks Andrew

Vasanth · Answer

Andrew  is [date] column datatype is datetime or varchar. If varchar means your second query is right. But if [date] datatype is datetime means  please cast datetime as like sqlserver format. Please run the below query. INSERT INTO Report (Date Reporter Details) VALUES ('1/27/2010' 'Mark Smith 11260(S)' 'gfdsgsdfgsdf') Just i changed the date column value. If its not helps you please revert table schema also and error text. If it helps you  please mark as answer.

Amit Choudhary · Answer

hi friend your second one is right with syntax but i have dobut i your query INSERT INTO Report (Date Reporter Details) VALUES ('27/1/2010' 'Mark Smith  1260 ( S ) ' 'gfdsgsdfgsdf') use escape sequence before inserting special characters in your data using insert statement. use INSERT INTO Report (Date Reporter Details) VALUES ('27/1/2010' 'Mark Smith \  1260 \ ( S \ ) ' 'gfdsgsdfgsdf') {escape '\'} Please mark as answer if it helps.