asked on

Update not working

I am using VB.net 2005 and I have the below code to update a database by inserting a new row. Everything works until the update command is executed.

I receive an OleDbException "Syntax error in INSERT INTO statement." When I displayed the insert into statement it read as "INSERT INTO tblOrderedParts (Date, Assembly, Description, SysID, Quanity, Priority, Approval, Comments) VALUES (?,?,?,?,?,?,?,?)".

Could someone please tell me what is going on.

Dim dsSaveOrder As New DataSet
Dim sqlSaveOrder As String = "SELECT DATE, Assembly, Description, SysID, Quanity, Priority, Approval, Comments FROM tblOrderedParts WHERE Received <> 'True'"
Dim daSaveOrder As New OleDbDataAdapter(sqlSaveOrder, dbConnection)
Dim cbSaveOrder As New OleDbCommandBuilder(daSaveOrder)

daSaveOrder.Fill(dsSaveOrder)

For i As Integer = 0 To Me.DataGridView1.Rows.Count - 2
Dim drSaveOrder As DataRow

drSaveOrder = dsSaveOrder.Tables(0).NewRow()
drSaveOrder("Date") = Me.txtDate.Text
drSaveOrder("Assembly") = Me.DataGridView1.Rows(i).Cells("colAssembly").Value
drSaveOrder("Description") = Me.DataGridView1.Rows(i).Cells("colDescription").Value
drSaveOrder("SysID") = Me.cboSySID.Text
drSaveOrder("Quanity") = Me.DataGridView1.Rows(i).Cells("colQuanity").Value
drSaveOrder("Priority") = Me.cboPriority.Text
drSaveOrder("Approval") = Me.txtApproval.Text
drSaveOrder("Comments") = Me.txtComments.Text

dsSaveOrder.Tables(0).Rows.Add(drSaveOrder)

Next i
cbSaveOrder.GetInsertCommand.CommandText = "Insert () into tblOrderedParts values()"
daSaveOrder.Update(dsSaveOrder)

Thanks for any and all help.

Priest04

What is with this line

cbSaveOrder.GetInsertCommand.CommandText = "Insert () into tblOrderedParts values()"

Remove it (or just comment it)

Goran

NevSoFly

ASKER

Sorry I forgot to delete that line before I sent my question.

Priest04

Everything works fine. Most probably the error is caused by using reserved word DATE as field name. Try enclosing it in brackets [] like

[DATE]

or even better, rename it to order_date, both in access and code.

Goran

NevSoFly

ASKER

I don't get an the OleDbException anymore but my Access database is still not updating. I checked the insert statement again and it is still the same (INSERT INTO tblOrderedParts (Date, Assembly, Description, SysID, Quanity, Priority, Approval, Comments) VALUES (?,?,?,?,?,?,?,?)).

Priest04

Did you change field name to order_date?

Goran

NevSoFly

ASKER

yes I did. I think that is what stopped the exception but still no update.

Priest04

And you have changed this lines too

Dim sqlSaveOrder As String = "SELECT order_date, Assembly, Description, SysID, Quanity, Priority, Approval, Comments FROM tblOrderedParts WHERE Received <> 'True'"

and

drSaveOrder("order_date") = Me.txtDate.Text

Goran

NevSoFly

ASKER

Yes here is the new code:

Dim dsSaveOrder As New DataSet
Dim sqlSaveOrder As String = "SELECT Order_Date, Assembly, Description, SysID, Quanity, Priority, Approval, Comments FROM tblOrderedParts WHERE Received <> 'True'"
Dim daSaveOrder As New OleDbDataAdapter(sqlSaveOrder, dbConnection)
Dim cbSaveOrder As New OleDbCommandBuilder(daSaveOrder)

daSaveOrder.Fill(dsSaveOrder)

For i As Integer = 0 To Me.DataGridView1.Rows.Count - 2
Dim drSaveOrder As DataRow

drSaveOrder = dsSaveOrder.Tables(0).NewRow()
drSaveOrder("Order_Date") = Me.txtDate.Text
drSaveOrder("Assembly") = Me.DataGridView1.Rows(i).Cells("colAssembly").Value
drSaveOrder("Description") = Me.DataGridView1.Rows(i).Cells("colDescription").Value
drSaveOrder("SysID") = Me.cboSySID.Text
drSaveOrder("Quanity") = Me.DataGridView1.Rows(i).Cells("colQuanity").Value
drSaveOrder("Priority") = Me.cboPriority.Text
drSaveOrder("Approval") = Me.txtApproval.Text
drSaveOrder("Comments") = Me.txtComments.Text
Next i

daSaveOrder.Update(dsSaveOrder)

Priest04

Do you have a primary key set on table? if not, set it.

Goran

NevSoFly

ASKER

Yes I had it set. You got it to work for you?

Priest04

There is no reason why it shouldnt work. The sample you have provided is the most basic one, and I see no reason why it should work.

I see now that you have left this line from new code

dsSaveOrder.Tables(0).Rows.Add(drSaveOrder)

Goran

NevSoFly

ASKER

I put
dsSaveOrder.Tables(0).Rows.Add(drSaveOrder)
back in the code.
There must be something very simple that I'm missing. is there a different way I can do this?

ASKER CERTIFIED SOLUTION

Priest04

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Priest04

Of course, you can also involve Transaction object here, since most probably you would want either to insert all rows, or, if some insert fails, none to be inserted.

Goran

Sancler

Can I ask how you KNOW it isn't working? Certainly, Goran's alternative approach should work - but, as he says, so should the final code for your original approach.

One thing you might try is changing this line

daSaveOrder.Update(dsSaveOrder)

in the ORIGINAL code to

MsgBox(daSaveOrder.Update(dsSaveOrder))

That will report how many records the dataadapter THINKS it has updated. It it reports 0, then the problem lies somewhare before that. If it reports the number that you think should be updated, the problem lies elsewhere. If it's the latter, then have a look at this PAQ

https://www.experts-exchange.com/questions/21896499/My-OLEdb-Update-isn't-working.html

Roger

NevSoFly

ASKER

Master Priest04 (Goran),

Thank you for your patience. After I changed a couple of dsSaveOrder lines to dtSaveOrder and
dsSaveOrder.Tables(0).Rows.Add(drSaveOrder)
to
dtSaveOrder.Rows.Add(drSaveOrder)
your code worked, Just for my knowledge I'm going to printout both old and new codes to see what was different.

again I thank you.

Sancier,
thank you for your comments. I knew the original code wasn't working because I could see the data in the dataset get updated but the database info stay the same. Thank you for the link and the tip of using msgbox to report what the adapter thinks is happening. i will use it in the future.

Robert

Priest04

You are welcome

Goran