Update Oledb Command Object not working

[speedy gonzalos]

Hi

I am trying to update more than 1 field in an Access table. I am developing a windows application using an Access database in vb.net.

This is the code that i have for my Update button, which works fine if i want to update the field 'CategoryName' however when i add in the 'Description' field into the sql update statement it doesnt work at all....

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        'open(connection)
        con.Open()

        cmd.Connection = con
        Try
cmd.CommandText = "UPDATE LK_Categories SET CategoryName = mCategoryName, Description = mDescription WHERE PK_CategoryID = mCatID"
       
            Dim param As New OleDb.OleDbParameter
            Dim param2 As New OleDb.OleDbParameter
            Dim param3 As New OleDb.OleDbParameter

            'add content txtProdName as parameter
            param.ParameterName = "mCategoryName"
            param.Value = CategoryNameTextBox.Text()
            cmd.Parameters.Add(param)

            param2.ParameterName = "mCatID"
            param2.Value = PK_CategoryIDTextBox.Text
            cmd.Parameters.Add(param2)

            Try
                param3.ParameterName = "mDescription"
                param3.Value = DescriptionTextBox.Text()
                cmd.Parameters.Add(param3)

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
           

            cmd.ExecuteNonQuery()

            MessageBox.Show("customer data successfully updated")

        Catch ex As Exception

            MessageBox.Show("cust data not updated because " _
           & ex.Message)

        Finally
            'close connection if it is open
            If (con.State = ConnectionState.Open) Then
                con.Close()
            End If
        End Try
        con.Close()

    End Sub

[sknake]

What do you mean by it doesnt work? Looking at your code you are handling an exception when creating the parameter and adding it to the command's collection. Are you raising an exception there, when you fire the .ExecuteNonQuery() , or is there no error it just doesn't update the data?

You should also be formating your queries like this:

Update Table Set aColumn = ? Where PK = ?

Then add parameters to the collection in the same order as they appear in the query.

[speedy gonzalos]

The data does not get updated at all. But i will try replacing the parameter names with '?' and order them as they appear in the query.

[speedy gonzalos]

can you show me a sample code as i am getting a "syntax error" error message, that will really help me understand where i am going wrong.

[sknake]

Dim ConnString As String = Utils.GetConnString()
Dim SqlString As String = "Update Contacts Set FirstName = ?, LastName = ?"
Using conn As New OleDbConnection(ConnString)
  Using cmd As New OleDbCommand(SqlString, conn)
    cmd.CommandType = CommandType.Text
    cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text)
    cmd.Parameters.AddWithValue("LastName", txtLastName.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
  End Using
End Using

Taken from:
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

[speedy gonzalos]

I got my original code working. The Update statement was fine, i just had to put my parameters in the order they were used in the update statement. In other works i moved param2 to come after param3 as follows:

param.ParameterName = "mCategoryName"
            param.Value = CategoryNameTextBox.Text()
            cmd.Parameters.Add(param)

            param3.ParameterName = "mDescription"
            param3.Value = DescriptionTextBox.Text()
            cmd.Parameters.Add(param3)

            param2.ParameterName = "mCatID"
            param2.Value = PK_CategoryIDTextBox.Text
            cmd.Parameters.Add(param2)

Thanks for your help anyways.

[sknake]

I'm glad you found resolution for your issue.

Please mark this thread as solved (since you answered your own question! :P) and good luck