Updating a MS Access table row

[aschech]

Hi,
I try to update a MS Access table row using OleDbDataAdapter. When I execute the code no error message is displayed but the column I update is not changed.
Following is the code I used:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sel_sql As String = "SELECT customer,service,amount from mem_tran where customer=cust and service = serv"

Dim upd_sql As String = "UPDATE mem_tran set amount=0 where customer=cst and service=sv"

Dim da As New OleDbDataAdapter

Dim ds As New DataSet

Dim x As Integer

Dim cs As OleDbCommand = New OleDbCommand(sel_sql, OleDbConnection1)

cs.Parameters.Add("cust", OleDbType.WChar, 8).Value = "ADEN"

cs.Parameters.Add("serv", OleDbType.Integer).Value = 40500000

da.SelectCommand = cs

OleDbConnection1.Open()

Try

x = da.Fill(ds, "mem_tran")

Catch ex As Exception

MsgBox(ex.ToString)

End Try

da.SelectCommand.Parameters.Clear()

Dim cu As OleDbCommand = New OleDbCommand(upd_sql, OleDbConnection1)

da.UpdateCommand = cu

cu.Parameters.Add("amnt", OleDbType.Double).Value = 110

cu.Parameters.Add("cst", OleDbType.WChar, 8).Value = "ADEN"

cu.Parameters.Add("sv", OleDbType.Integer).Value = 40500000



Try

x = cu.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The table has only three columns : Customer,Service and Amount
Please anybody can help me to figure out the problem!!!!!!!
Thank you
Chaim

 

[tlkerns]

Try looking up parameters in the Visual Studio help and read about placeholders (?) and named parameters (@param_name).

The help system is where you should go first for basic questions like this. If that doesn't work, then try a Google search.

Tony

 

[Paszt]

Or you could go to vbdotnetforums.com, the place to ask any VB.NET questions.

Here is some code showing how to use an update statement with parameters.

Dim upd_sql As String = "UPDATE mem_tran set amount=[@amnt] " & _
                        "WHERE customer=[@cst] and service=[@sv]"
Dim da As New OleDbDataAdapter()
Dim ds As New DataSet()
Dim x As Integer
Dim cu As OleDbCommand = New OleDbCommand(upd_sql, oleDbConnection1)
'don't need the next statement, it's set above
'da.UpdateCommand = cu
cu.Parameters.Add("@amnt", OleDbType.Double).Value = 110.0
cu.Parameters.Add("@cst", OleDbType.WChar, 8).Value = "ADEN"
cu.Parameters.Add("@sv", OleDbType.Integer).Value = 40500000
Try
    oleDbConnection1.Open()
    x = cu.ExecuteNonQuery()
Catch ex As Exception
    MsgBox(ex.ToString)
Finally
    'put close in try/catch block in case
    'the connection wasn't opened
    Try
        oleDbConnection1.Close()
    Catch : End Try
End Try

The ampersands (@) are common notation for parameters in an update statement. They aren't necessary, but are a good convention to use as are the brackets (for readability).

There's no need to use a SELECT statement if all you are doing is updating info. The select statement does as it sounds, it selects data based on the criteria set.