• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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.
0
NevSoFly
Asked:
NevSoFly
  • 9
  • 7
1 Solution
 
Priest04Commented:
What is with this line

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

Remove it (or just comment it)

Goran
0
 
NevSoFlyAuthor Commented:
Sorry I forgot to delete that line before I sent my question.
0
 
Priest04Commented:
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
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
NevSoFlyAuthor Commented:
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 (?,?,?,?,?,?,?,?)).
0
 
Priest04Commented:
Did you change field name to order_date?

Goran
0
 
NevSoFlyAuthor Commented:
yes I did.  I think that is what stopped the exception but still no update.
0
 
Priest04Commented:
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
0
 
NevSoFlyAuthor Commented:
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)
0
 
Priest04Commented:
Do you have a primary key set on table? if not, set it.

Goran
0
 
NevSoFlyAuthor Commented:
Yes I had it set.  You got it to work for you?
0
 
Priest04Commented:
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
0
 
NevSoFlyAuthor Commented:
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?
0
 
Priest04Commented:
Just to be sure that nothing is left, I will post the whole code. Since you have only one DataTable in DataSet, there is no need for DataSet, so I will remove it

Dim dtSaveOrder As New DataTable
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(dtSaveOrder)

Dim drSaveOrder As DataRow

For i As Integer = 0 To Me.DataGridView1.Rows.Count - 2
    drSaveOrder = dtSaveOrder.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

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

Next i

daSaveOrder.Update(dsSaveOrder)
dsSaveOrder.AcceptChanges

As far as the different way to do this, I am not quite sure what you do here. If you have unbound DataGridView and you want to insert records from it to database, then all you need is connection object and command object. Here is some example

dim cmdSaveOrder as new oledbcommand("", dbConnection)
cmdSaveOrder.CommandType = CommandType.Text

dim recordsAffected as integer

dbConnection.Open

For i As Integer = 0 To Me.DataGridView1.Rows.Count - 2
    cmdSaveOrder.CommandText = "INSERT INTO tblOrderedParts " _
        & "(Order_Date, Assembly, Description, SysID, Quanity, Priority, Approval, Comments) VALUES ('" _
        & txtDate.Text & "','" & Me.DataGridView1.Rows(i).Cells("colAssembly").Value & "','" _
        & Me.DataGridView1.Rows(i).Cells("colDescription").Value & "','" & Me.cboSySID.Text & "'," _
        & Me.DataGridView1.Rows(i).Cells("colQuanity").Value & ",'" & Me.cboPriority.Text & "','" _
        & Me.txtApproval.Text & "','" & Me.txtComments.Text & "')"

    recordsAffected = Global.Baza.Command.ExecuteNonQuery()

    if recordsAffected <> 1 then throw new exception ("error -  record not inserted")

Next i

dbConnection.Close

I most probably have some syntax erors in INSERT INTO query, since I dont know what fileds are of type text and what are numeric, but you get the picture. It is important to remember that text values are enclosed with single quotes, like 'some value', while numeric fields arent.

And I have just seem that one of your filds is called Quanity, just make sure it isnt a typing mistake.

Goran
0
 
Priest04Commented:
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
0
 
SanclerCommented:
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

http://www.experts-exchange.com/Programming/Languages/.NET/Visual_Basic.NET/Q_21896499.html

Roger
0
 
NevSoFlyAuthor Commented:
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
0
 
Priest04Commented:
You are welcome

Goran
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now