Z-Y The VB Guy Z-Y The VB Guy -4 years ago 180
Vb.net Question

VB Update Command w/ Access Database

Function UpdateTableRow()
sqlLink.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\will\Documents\Computing\ComputingProjectDatabase.accdb';"

Try
Dim sqlOrder As New OleDbCommand
If sqlLink.State = ConnectionState.Closed Then
sqlLink.Open()
End If

' Creating the command and its parameter here before entering the loop to avoid a continue'
' create and destroy pattern for the OleDbCommand'

sqlOrder.CommandText = "UPDATE StockSystem SET [Stock Price] =?, [Stock Size] =?, [Stock Quantity] =?, [Stock Category] =?, WHERE [Stock ID] =?"
sqlOrder.Connection = sqlLink

sqlOrder.Parameters.AddWithValue("@StockCategory", 0)
sqlOrder.Parameters.AddWithValue("@StockQuantity", 0)
sqlOrder.Parameters.AddWithValue("@StockSize", 0)
sqlOrder.Parameters.AddWithValue("@StockPrice", 0)
sqlOrder.Parameters.AddWithValue("@row", 0)

Dim rows = DataGridView1.Rows

For Each row In rows

sqlOrder.Parameters("@row").Value = row.Cells(0).Value
sqlOrder.Parameters("@StockPrice").Value = row.Cells(1).Value
sqlOrder.Parameters("@StockSize").Value = row.Cells(2).Value
sqlOrder.Parameters("@StockQuantity").Value = row.Cells(3).Value
sqlOrder.Parameters("@StockCategory").Value = row.Cells(4).Value

sqlOrder.ExecuteNonQuery()

Next

MsgBox("Data Updated.")

'Problem with the update parameters, works for DELETE FUNCTION, needs to be changed to fit an UPDATE FUNCTION

Catch ex As Exception
MsgBox(ex.Message)
Finally
sqlLink.Close()

End Try
Return DataGridView1.SelectedRows
DataGridView1.Refresh()
End Function


Hey, so as you can see I have a nice bit of code here. Only it doesn't 100% work. I keep getting told
Syntax Error in UPDATE statement
. Now I'm pretty sure the update statement is correct, as without the parameters if doesn't have an issue with the code (admittedly without the parameters it doesn't work at all). I've been stuck on this for about a week so any help would be great >.<

The second error I get is, because I'm using Access, there is a "blank" row at the bottom of the table. I was thinking something like (row - 1) would work, only it VB 2010 doesn't like that at all.

As I said, any help would be awesome.

Answer Source

You have added a , before WHERE . This is the cause of syntax error in UPDATE statement

Try this

sqlOrder.CommandText = "UPDATE StockSystem SET [Stock Price] =?, [Stock Size] =?, [Stock Quantity] =?, [Stock Category] =? WHERE [Stock ID] =?"

On the other hand I'm not sure what you mean. This blank row?

enter image description here

It's just there to allow user to insert a new row. It is not an empty row, so you don't need to reference the rows with rowindex - 1

UPDATE: Trying to correct your code. I would do somethink like this:

Try

    If sqlLink.State = ConnectionState.Closed Then
        sqlLink.Open()
    End If

    Dim rows = DataGridView1.Rows

    For Each row In rows

        Dim sqlOrder As New OleDbCommand("UPDATE StockSystem SET [Stock Price] =?, [Stock Size] =?, [Stock Quantity] =?, [Stock Category] =? WHERE [Stock ID] =?", sqlLink)            

        sqlOrder.Parameters.AddWithValue("@StockPrice", row.Cells(1).Value)
        sqlOrder.Parameters.AddWithValue("@StockSize", row.Cells(2).Value)
        sqlOrder.Parameters.AddWithValue("@StockQuantity", row.Cells(3).Value)
        sqlOrder.Parameters.AddWithValue("@StockCategory", row.Cells(4).Value)
        sqlOrder.Parameters.AddWithValue("@row", row.Cells(0).Value)

        sqlOrder.ExecuteNonQuery()

    Next

    MsgBox("Data Updated.")

Catch ex As Exception
    MsgBox(ex.Message)
Finally
    sqlLink.Close()
End Try
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download