[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update not working

Posted on 2007-08-02
17
Medium Priority
?
194 Views
Last Modified: 2010-04-23
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
Comment
Question by:NevSoFly
  • 9
  • 7
17 Comments
 
LVL 18

Expert Comment

by:Priest04
ID: 19619635
What is with this line

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

Remove it (or just comment it)

Goran
0
 

Author Comment

by:NevSoFly
ID: 19619816
Sorry I forgot to delete that line before I sent my question.
0
 
LVL 18

Expert Comment

by:Priest04
ID: 19620121
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NevSoFly
ID: 19620436
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
 
LVL 18

Expert Comment

by:Priest04
ID: 19620529
Did you change field name to order_date?

Goran
0
 

Author Comment

by:NevSoFly
ID: 19620615
yes I did.  I think that is what stopped the exception but still no update.
0
 
LVL 18

Expert Comment

by:Priest04
ID: 19620689
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
 

Author Comment

by:NevSoFly
ID: 19620751
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
 
LVL 18

Expert Comment

by:Priest04
ID: 19620815
Do you have a primary key set on table? if not, set it.

Goran
0
 

Author Comment

by:NevSoFly
ID: 19620923
Yes I had it set.  You got it to work for you?
0
 
LVL 18

Expert Comment

by:Priest04
ID: 19621016
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
 

Author Comment

by:NevSoFly
ID: 19621610
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
 
LVL 18

Accepted Solution

by:
Priest04 earned 1200 total points
ID: 19621795
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
 
LVL 18

Expert Comment

by:Priest04
ID: 19621804
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
 
LVL 34

Expert Comment

by:Sancler
ID: 19623275
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
 

Author Comment

by:NevSoFly
ID: 19624885
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
 
LVL 18

Expert Comment

by:Priest04
ID: 19625522
You are welcome

Goran
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question