Insert Command Syntax Error

[Ed Pisa]

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.

ElseIf NEWMODE = True Then

            Dim oAdapter As OleDb.OleDbDataAdapter
            Dim cb As OleDb.OleDbCommandBuilder
            Dim dr As DataRow
            Dim ds As DataSet
            Dim strSQL As String = "SELECT * FROM [Private Criminal Complaints 2]"

            ds = New DataSet()
            oAdapter = New OleDb.OleDbDataAdapter(strSQL, Con)
            oAdapter.Fill(ds) 'Execute the Query and grab results

            Try
                dr = ds.Tables(0).NewRow()

                dr.BeginEdit()

                dr.Item("CompLastName") = txtCLname.Text
                dr.Item("CompFirstName") = txtCFname.Text
                dr.Item("DefLastName") = txtDLname.Text
                dr.Item("DefFirstName") = txtDFname.Text
                dr.Item("NameADA") = txtADA.Text
                dr.Item("Detective") = txtDet.Text
                dr.Item("Disposition") = txtDisposition.Text
                dr.Item("ReasonFiled") = txtreason.Text
                dr.Item("DateFiled") = txtDate.Text


                dr.EndEdit()

                ds.Tables(0).Rows.Add(dr)
                cb = New OleDb.OleDbCommandBuilder(oAdapter)
                oAdapter.InsertCommand = cb.GetInsertCommand()
                oAdapter.Update(ds)
                ds.AcceptChanges()

                MessageBox.Show("Insert Successful")
                NEWMODE = False 'Add success, end NEWMODE
                ClearTextBoxes() 'Add Success, clean up textboxes
            Catch ex As Exception
                MessageBox.Show(ex.Message)

            Finally
                Con.Close()
            End Try

        Else 'Its not Editmode or Newmode
            'Do nothing
        End If

        RefreshLV() 'Regardless of what happens, might as well refresh the data in the Listview
    End Sub

[NeoPa]

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.

[Ed Pisa]

Thanks for the reply. I am using the Oledbcommandbui lder. 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?

[mshmyob]

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,

[NeoPa]

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.

[ADezii]

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.

'Code intentionally omitted...
oAdapter.Fill (ds)

cb = New OleDb.OleDbCommandBuilder(oAdapter)
  
dr = ds.Tables(0).NewRow()
  
dr("CompLastName") = txtCLname.Text
dr("CompFirstName") = txtCFname.Text
dr("DefLastName") = txtDLname.Text
dr("DefFirstName") = txtDFname.Text
dr("NameADA") = txtADA.Text
dr("Detective") = txtDet.Text
dr("Disposition") = txtDisposition.Text
dr("ReasonFiled") = txtreason.Text
dr("DateFiled") = txtDate.Text
  
ds.Tables(0).Rows.Add (dr)

oAdapter.Update (ds.GetChanges())

ds.AcceptChanges()
  
MessageBox.Show ("Insert Successful")
'Code intentionally omitted...

[Ed Pisa]

Thanks for the advice I will have to try it first thing tomorrow since it is on my desktop at work.

[Ed Pisa]

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.

[NeoPa]

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.

[Ed Pisa]

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

I needed to add the following code

ds.Tables(0).Rows.Add(dr)
                cb = New OleDb.OleDbCommandBuilder(oAdapter)

                [B][I]cb.QuotePrefix = "["
                cb.QuoteSuffix = "]"[/I][/B]

                oAdapter.InsertCommand = cb.GetInsertCommand()

                oAdapter.Update(ds)
                ds.AcceptChanges()

Hopefully this will help people in the future. thank you again for all of your responses.