By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Insert Command Syntax Error

P: 6
Hello,

I have been working with this problem now for several days. I can delete and Update my data but I can not get it to insert a new record. I receive the syntax error insert into. I am not using any reserved words and I have not been able to find my answer googling like crazy. Any help would be greatly appreciated. below is the code that seems to give me the problem.

Expand|Select|Wrap|Line Numbers
  1.  
  2. ElseIf NEWMODE = True Then
  3.  
  4.             Dim oAdapter As OleDb.OleDbDataAdapter
  5.             Dim cb As OleDb.OleDbCommandBuilder
  6.             Dim dr As DataRow
  7.             Dim ds As DataSet
  8.             Dim strSQL As String = "SELECT * FROM [Private Criminal Complaints 2]"
  9.  
  10.             ds = New DataSet()
  11.             oAdapter = New OleDb.OleDbDataAdapter(strSQL, Con)
  12.             oAdapter.Fill(ds) 'Execute the Query and grab results
  13.  
  14.             Try
  15.                 dr = ds.Tables(0).NewRow()
  16.  
  17.                 dr.BeginEdit()
  18.  
  19.                 dr.Item("CompLastName") = txtCLname.Text
  20.                 dr.Item("CompFirstName") = txtCFname.Text
  21.                 dr.Item("DefLastName") = txtDLname.Text
  22.                 dr.Item("DefFirstName") = txtDFname.Text
  23.                 dr.Item("NameADA") = txtADA.Text
  24.                 dr.Item("Detective") = txtDet.Text
  25.                 dr.Item("Disposition") = txtDisposition.Text
  26.                 dr.Item("ReasonFiled") = txtreason.Text
  27.                 dr.Item("DateFiled") = txtDate.Text
  28.  
  29.  
  30.                 dr.EndEdit()
  31.  
  32.                 ds.Tables(0).Rows.Add(dr)
  33.                 cb = New OleDb.OleDbCommandBuilder(oAdapter)
  34.                 oAdapter.InsertCommand = cb.GetInsertCommand()
  35.                 oAdapter.Update(ds)
  36.                 ds.AcceptChanges()
  37.  
  38.                 MessageBox.Show("Insert Successful")
  39.                 NEWMODE = False 'Add success, end NEWMODE
  40.                 ClearTextBoxes() 'Add Success, clean up textboxes
  41.             Catch ex As Exception
  42.                 MessageBox.Show(ex.Message)
  43.  
  44.             Finally
  45.                 Con.Close()
  46.             End Try
  47.  
  48.         Else 'Its not Editmode or Newmode
  49.             'Do nothing
  50.         End If
  51.  
  52.         RefreshLV() 'Regardless of what happens, might as well refresh the data in the Listview
  53.     End Sub
  54.  
Dec 6 '10 #1

✓ answered by Ed Pisa

Thank you everyone for your help I have figured out the problem.

I needed to add the following code

Expand|Select|Wrap|Line Numbers
  1. ds.Tables(0).Rows.Add(dr)
  2.                 cb = New OleDb.OleDbCommandBuilder(oAdapter)
  3.  
  4.                 cb.QuotePrefix = "["
  5.                 cb.QuoteSuffix = "]"
  6.  
  7.                 oAdapter.InsertCommand = cb.GetInsertCommand()
  8.  
  9.                 oAdapter.Update(ds)
  10.                 ds.AcceptChanges()
  11.  
Hopefully this will help people in the future. thank you again for all of your responses.

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,307
Your title implies this is related to the SQL INSERT INTO command, but I see no sign of that. My guess is you're not hooking the right experts.
Dec 6 '10 #2

P: 6
Thanks for the reply. I am using the Oledbcommandbuilder. I think I am just incredibly lost at this point. I am very new to all of this and I have been following several different guides to accomplish my task. I am so close to finishing as it does everything but insert a new record. Do you have any suggestions on a better way to insert a new record that would get rid of this syntax error?
Dec 6 '10 #3

mshmyob
Expert 100+
P: 903
This looks more like vb or vb.net not vba. Is this an Access problem or have you posted in the wrong forum by mistake.

cheers,
Dec 6 '10 #4

NeoPa
Expert Mod 15k+
P: 31,307
Ed Pisa:
Do you have any suggestions on a better way to insert a new record that would get rid of this syntax error?
That depends on what you're really after. generally, in Access, you would simply use a form to manage updating of your data for you. There are various wizards, but the simplest handles record management including all of the above. If you're new to Access it's perfectly possible you're trying to code your way to what Access already does for you. You wouldn't be the first to follow that path.
Dec 6 '10 #5

ADezii
Expert 5K+
P: 8,616
If I am not mistaken, this is ADO.NET, which should easily be converted to standard ADO. If you can obtain a Reference to the ActiveX Data Objects X.X Object Library, and you feel as though this functionality can be incorporated into your existing environment, then we can try the conversion and see what happens. The other option would be to Post this in the .NET Forum and see what, if any, response you obtain there.

P.S. - I just took a 15 minute Crash Course in ADO.NET. Try this revised Syntax, it comes with no guarantee whatsoever, since I have no way of testing it.
Expand|Select|Wrap|Line Numbers
  1. 'Code intentionally omitted...
  2. oAdapter.Fill (ds)
  3.  
  4. cb = New OleDb.OleDbCommandBuilder(oAdapter)
  5.  
  6. dr = ds.Tables(0).NewRow()
  7.  
  8. dr("CompLastName") = txtCLname.Text
  9. dr("CompFirstName") = txtCFname.Text
  10. dr("DefLastName") = txtDLname.Text
  11. dr("DefFirstName") = txtDFname.Text
  12. dr("NameADA") = txtADA.Text
  13. dr("Detective") = txtDet.Text
  14. dr("Disposition") = txtDisposition.Text
  15. dr("ReasonFiled") = txtreason.Text
  16. dr("DateFiled") = txtDate.Text
  17.  
  18. ds.Tables(0).Rows.Add (dr)
  19.  
  20. oAdapter.Update (ds.GetChanges())
  21.  
  22. ds.AcceptChanges()
  23.  
  24. MessageBox.Show ("Insert Successful")
  25. 'Code intentionally omitted...
Dec 6 '10 #6

P: 6
Thanks for the advice I will have to try it first thing tomorrow since it is on my desktop at work.
Dec 7 '10 #7

P: 6
I have a form in access but the problem is the staff that uses it is not the most technically inclined. Through their clicking it was managed to delete an entire column and lost alot of information. I know that you can sent permissions and what not but I thought by creating an easy to use interface through visual studio 2010 it would help the end user. Thanks for your reply I am just going to have to try try again.
Dec 7 '10 #8

NeoPa
Expert Mod 15k+
P: 31,307
The question here Ed, is how comfortable are you (relatively) working in .NET and Access.

Unless you are comfortable in .NET and completely lost in Access, the most promising approach would be to develop the form further in Access and VBA. There is a great deal that can be done to control access to the data this way, and much of it can be done simply by configuring the properties of the form and its controls, though on top of that is a whole area of control using VBA code in the various event procedures.
Dec 7 '10 #9

P: 6
Thank you everyone for your help I have figured out the problem.

I needed to add the following code

Expand|Select|Wrap|Line Numbers
  1. ds.Tables(0).Rows.Add(dr)
  2.                 cb = New OleDb.OleDbCommandBuilder(oAdapter)
  3.  
  4.                 cb.QuotePrefix = "["
  5.                 cb.QuoteSuffix = "]"
  6.  
  7.                 oAdapter.InsertCommand = cb.GetInsertCommand()
  8.  
  9.                 oAdapter.Update(ds)
  10.                 ds.AcceptChanges()
  11.  
Hopefully this will help people in the future. thank you again for all of your responses.
Dec 8 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.