Updating a MS Access table row

aschech

New member
Joined
Jun 7, 2004
Messages
2
Programming Experience
10+
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

Member
Joined
Jun 3, 2004
Messages
19
Programming Experience
3-5
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

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
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.

VB.NET:
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.
 
Last edited:
Top Bottom