Syntax error in INSERT INTO statement.
-
hi there, Can someone help with this. Syntax error in INSERT INTO statement. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement. Source Error: Line 84: strsqlinsert = "INSERT INTO Transmittal(Action, Copies, Drw_Doc_Num, Kind_Attn, Project_Name, Referenced_To, Rev_Num, Title, Username)" + "VALUES('" + TextBox9.Text + "', '" + TextBox7.Text + "', '" + TextBox6.Text + "', '" + TextBox4.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + TextBox8.Text + "', '" + TextBox5.Text + "', '" + TextBox1.Text + "' )" Line 85: TransDataAdapter.InsertCommand.CommandText = (strsqlinsert) Line 86: TransDataAdapter.InsertCommand.ExecuteNonQuery() Line 87: TransConnection.Close() Line 88: End Sub Source File: C:\Inetpub\wwwroot\OM\trans1.aspx.vb Line: 86 Stack Trace: [OleDbException (0x80040e14): Syntax error in INSERT INTO statement.] System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41 System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +154 System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92 System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65 System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112 System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +54 OM.trans1.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\OM\trans1.aspx.vb:86 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1263
manik nath wrote: [OleDbException (0x80040e14): Syntax error in INSERT INTO statement.] insead of writing the long codes you can just work on insert statement. if you could post the value of strsqlinsert variable then we could suggest you some thing better and proper
-
manik nath wrote: [OleDbException (0x80040e14): Syntax error in INSERT INTO statement.] insead of writing the long codes you can just work on insert statement. if you could post the value of strsqlinsert variable then we could suggest you some thing better and proper
well rizwan, here's the value of strsqlinsert strinsert = "INSERT INTO Transmittal(ReferencedTo, Title, NumberofCopies, Username, ProjectName, KindAttn, RevisionNumber, Action, DrawingDocumentNumber)" + "VALUES ('" + TextBox3.Text + "', '" + TextBox5.Text + "', '" + TextBox9.Text + "', '" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox4.Text + "', '" + TextBox10.Text + "', '" + TextBox11.Text + "', '" + TextBox7.Text + "' )" it doesn't come in one line here. help me if you could.
-
well rizwan, here's the value of strsqlinsert strinsert = "INSERT INTO Transmittal(ReferencedTo, Title, NumberofCopies, Username, ProjectName, KindAttn, RevisionNumber, Action, DrawingDocumentNumber)" + "VALUES ('" + TextBox3.Text + "', '" + TextBox5.Text + "', '" + TextBox9.Text + "', '" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox4.Text + "', '" + TextBox10.Text + "', '" + TextBox11.Text + "', '" + TextBox7.Text + "' )" it doesn't come in one line here. help me if you could.
Two problems... The first is that you don't have a spcae just before the word "VALUES", so your SQL looks likes this:
...Action, DrawingDocumentNumber)VALUES ('...
which I believe is a syntax error in SQL... Second, drop this string concatenation garbage and make your life much easier and convert this to parameterized queries. You'll automatically eliminate about 75% of the problems you get, like the one your facing now!, by using string concatenation... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Two problems... The first is that you don't have a spcae just before the word "VALUES", so your SQL looks likes this:
...Action, DrawingDocumentNumber)VALUES ('...
which I believe is a syntax error in SQL... Second, drop this string concatenation garbage and make your life much easier and convert this to parameterized queries. You'll automatically eliminate about 75% of the problems you get, like the one your facing now!, by using string concatenation... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
hi dave, thanx for your sugesstion. but i did not eaxctly get you. could you just modify the string i sent and then give it you me.
-
hi dave, thanx for your sugesstion. but i did not eaxctly get you. could you just modify the string i sent and then give it you me.
strinsert = "INSERT INTO Transmittal(ReferencedTo, Title, NumberofCopies, Username, ProjectName, KindAttn, RevisionNumber, Action, DrawingDocumentNumber)" + **" VALUES ('"** + TextBox3.Text + "', '" + TextBox5.Text + "', '" + TextBox9.Text + "', '" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox4.Text + "', '" + TextBox10.Text + "', '" + TextBox11.Text + "', '" + TextBox7.Text + "' )"All you had to do is add a space before your "VALUES" keyword. This is one reason why you DO NOT USE string concatenation to build your SQL statements. How you're building your statement can be broken so easily, it's silly. All the user has to do is put a ' character in any one of those text boxes and your code breaks. Learn how to do it the correct way now, before this bad habit becomes your normal method. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome -
strinsert = "INSERT INTO Transmittal(ReferencedTo, Title, NumberofCopies, Username, ProjectName, KindAttn, RevisionNumber, Action, DrawingDocumentNumber)" + **" VALUES ('"** + TextBox3.Text + "', '" + TextBox5.Text + "', '" + TextBox9.Text + "', '" + TextBox1.Text + "', '" + TextBox2.Text + "', '" + TextBox4.Text + "', '" + TextBox10.Text + "', '" + TextBox11.Text + "', '" + TextBox7.Text + "' )"All you had to do is add a space before your "VALUES" keyword. This is one reason why you DO NOT USE string concatenation to build your SQL statements. How you're building your statement can be broken so easily, it's silly. All the user has to do is put a ' character in any one of those text boxes and your code breaks. Learn how to do it the correct way now, before this bad habit becomes your normal method. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnomethanx dave, the problem is solved by ur code. but the this method is working fine on one application. but on another it again gives the same syntax error. what to do?:( also what do you mean by parametrised query. or what is the better method can you tell me.
-
thanx dave, the problem is solved by ur code. but the this method is working fine on one application. but on another it again gives the same syntax error. what to do?:( also what do you mean by parametrised query. or what is the better method can you tell me.
-
but dave if that is the case then it should give error in both the cases. in one form the syntax is working fine and in another form it's giving error. i have also double checked everything.
-
but dave if that is the case then it should give error in both the cases. in one form the syntax is working fine and in another form it's giving error. i have also double checked everything.
All I can say is you're doing something wrong. If you're using the EXACT same statement in both forms, you've missed a character somewhere. Think about it! If the statement works on one form, it must work. So what's different about it on the second form? RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
All I can say is you're doing something wrong. If you're using the EXACT same statement in both forms, you've missed a character somewhere. Think about it! If the statement works on one form, it must work. So what's different about it on the second form? RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
ii checked everything dave twice and thrice. but the webform on which the code doesn't work and gives the error. also doesn't work with a code as simple as this. strsqlinsert = "INSERT INTO Chec (first)" + " VALUES (" + TextBox1.Text + ")" if there is something wrong with it i don't know about it.
-
ii checked everything dave twice and thrice. but the webform on which the code doesn't work and gives the error. also doesn't work with a code as simple as this. strsqlinsert = "INSERT INTO Chec (first)" + " VALUES (" + TextBox1.Text + ")" if there is something wrong with it i don't know about it.
If your getting a "syntax error", you might want to consider what the field "first" is defined as and what is in the textbox.Text. If your trying to put a string into a number field, obviously, it's not going to work. Seriously, pick up a book on SQL and VB.NET and save yourself the aggravation. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome