Error in insert statement

[007iceman]

hi pple i have the following code to insert values into a table but it gives me a error on the line "cmdinsert.ExecuteNonQuery()" .I'm using a dropdown n sending its value,when i remove that particular filed from the insert it works jus fine

Error:


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 44: Dim sinsert = "insert into Questionbank(Question,OptionA,OptionB,OptionC,OptionD,Key) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "'," & DropDownList1.SelectedValue & ")"
Line 45: Dim cmdinsert As New OleDb.OleDbCommand(sinsert, cnAccess)
Line 46: cmdinsert.ExecuteNonQuery()



Code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cnAccess As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\ADO.NET\Testdb.mdb")
cnAccess.Open()
Dim sinsert = "insert into Questionbank(Question,OptionA,OptionB,OptionC,OptionD,Key) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "'," & DropDownList1.SelectedValue & ")"
Dim cmdinsert As New OleDb.OleDbCommand(sinsert, cnAccess)
cmdinsert.ExecuteNonQuery()
cnAccess.Close()


End Sub

Please help me guyz

[Shuja Ali]

Probably, the one of the values in Text Boxes contains a Special Character. IT is always better to use the Prepared Queries rather than the queries that are written on the Fly. Take a look at your MSDN on how to use OleDBCommand with Parameters

[007iceman]

Probably, the one of the values in Text Boxes contains a Special Character. IT is always better to use the Prepared Queries rather than the queries that are written on the Fly. Take a look at your MSDN on how to use OleDBCommand with Parameters

nah ders no special character in any of the text boxes the error DOESNOT occur when i remove "Dropdownlist1.selectedvaue" frm the insert statement.In that case all the values form the text box gets inserted into the db properly.What cuc possibly be wrong with the dropdown??

[Shuja Ali]

It would be better if you try to check what the query is in the debug mode. Just write this statement before you execute the query Dim sinsert = "insert into Questionbank(Question,OptionA,OptionB,OptionC,OptionD,Key) values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "'," & DropDownList1.SelectedValue & ")"
System.Diagnostics.Debug.Writeline(sinsert)This will print the query in the Output window and you can run the query from the Access database itself to see what is wrong with the query.

[007iceman]

Hi dude thanx for the info I figured out what was the error "Key" is a reserved word in Jet and I was using it as a column name hence the error.

One more thing in Access is there any way to generate an auto number column in which if i delete certain rows the rows get re-numbered automatically

eg: I have 10 rows numbered 1-10(auto numbered); I delete the 5th row so Access does not re-number the rows 1-9,it remains 1-4 and 6-10 ; is there any way to acheive this ?? thanx again

[Shuja Ali]

eg: I have 10 rows numbered 1-10(auto numbered); I delete the 5th row so Access does not re-number the rows 1-9,it remains 1-4 and 6-10 ; is there any way to acheive this ?? thanx againI don't think Access does the renumbering of the AutoID fields when you delete something. I have never tried it myself but i remeber reading it somewhere that the autoid numbers do not get reset. It will be better if you post this question in the database forum.