Code does not work but has no errors

Question

I'm working on an information system and here's my syntax for update, it shows no errors, but it does not update my table. Anyone can help on this matter? By the way, I'm using VB.Net 2010 and MS Access 2007.

Try
        Dim conn As New OleDbConnection(gConnectionString)
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If
        Try
            Dim comm As New OleDbCommand( "UPDATE PropertiesPayors SET [PayorName]=@PayorName,[LotNumber]=@LotNumber,[LotArea]=@LotArea,[DateOfAward]=@DateOfAward,[DateDueForFullPayment]=@DateDueForFullPayment,[PurchasePrice]=@PurchasePrice,[ReAppraisedValue]=@ReAppraisedValue,[AmountDue]=@AmountDue,[TotalAmountPaid]=@TotalAmountPaid,[AmountUnpaid]=@AmountUnpaid,[PropertyRemarks]=@PropertyRemarks WHERE [PropertyID]=@PropertyPayorID ", conn)
            With comm
                With .Parameters
                    .AddWithValue("@PropertyPropertyID", Val(propertyPayorSessionID.ToString))
                    .AddWithValue("@PayorName", txtPayorName.Text)
                    .AddWithValue("@LotNumber", txtLotNumber.Text)
                    .AddWithValue("@LotArea", Val(txtLotArea.Text))
                    .AddWithValue("@DateOfAward", txtDateOfAward.Text.ToString)
                    .AddWithValue("@DateDueForFullPayment", txtDateOfFullPayment.Text.ToString)
                    .AddWithValue("@PurchasePrice", Val(txtPurchasePrice.Text))
                    .AddWithValue("@ReAppraisedValue", Val(txtReAppraisedValue.Text))
                    .AddWithValue("@AmountDue", Val(txtAmountDue.Text))
                    .AddWithValue("@TotalAmountPaid", Val(txtTotalAmountPaid.Text))
                    .AddWithValue("@AmountUnpaid", Val(txtAmountUnpaid.Text))
                    .AddWithValue("@PropertyRemarks", txtRemarks.Text)
                End With
                .ExecuteNonQuery()
            End With
            msg = MsgBox("Record Updated.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Update Payor")
        Catch myError As Exception
            MsgBox("Error: " & myError.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Query Error")
        End Try
    Catch myError As Exception
        MsgBox("Error: " & myError.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Connection Error")
    End Try

Answer 1

You just have a typo on your code

Replace

@PropertyPropertyID 

with

@PropertyPayorID

then arrange your parameter order same as your update statement.

And try this :

    Try
            Dim conn As New OleDbConnection(gConnectionString)
            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If
            Try
                  Dim comm As New OleDbCommand("UPDATE PropertiesPayors SET [PayorName]=@PayorName,[LotNumber]=@LotNumber,[LotArea]=@LotArea,[DateOfAward]=@DateOfAward,[DateDueForFullPayment]=@DateDueForFullPayment,[PurchasePrice]=@PurchasePrice,[ReAppraisedValue]=@ReAppraisedValue,[AmountDue]=@AmountDue,[TotalAmountPaid]=@TotalAmountPaid,[AmountUnpaid]=@AmountUnpaid,[PropertyRemarks]=@PropertyRemarks WHERE [PropertyID]=@PropertyPayorID ", conn)
            With comm
                With .Parameters
                    '.AddWithValue("@PropertyPayorID", Val(propertyPayorSessionID.ToString)) move this to the last part
                    .AddWithValue("@PayorName", txtPayorName.Text)
                    .AddWithValue("@LotNumber", txtLotNumber.Text)
                    .AddWithValue("@LotArea", Val(txtLotArea.Text))
                    .AddWithValue("@DateOfAward", txtDateOfAward.Text.ToString)
                    .AddWithValue("@DateDueForFullPayment", txtDateOfFullPayment.Text.ToString)
                    .AddWithValue("@PurchasePrice", Val(txtPurchasePrice.Text))
                    .AddWithValue("@ReAppraisedValue", Val(txtReAppraisedValue.Text))
                    .AddWithValue("@AmountDue", Val(txtAmountDue.Text))
                    .AddWithValue("@TotalAmountPaid", Val(txtTotalAmountPaid.Text))
                    .AddWithValue("@AmountUnpaid", Val(txtAmountUnpaid.Text))
                    .AddWithValue("@PropertyRemarks", txtRemarks.Text)
                    .AddWithValue("@PropertyPayorID", Val(propertyPayorSessionID.ToString))
                End With
                .ExecuteNonQuery()
            End With
                msg = MsgBox("Record Updated.", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "Update Payor")
            Catch myError As Exception
                MsgBox("Error: " & myError.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Query Error")
            End Try
        Catch myError As Exception
            MsgBox("Error: " & myError.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Connection Error")
        End Try

This will solve your problem.

see also: OleDbCommand parameters order and priority for reference.

Best Regards!

Answer 2

If I remember correctly, by default, there is no relation between the name of the place holders in the query and the name of the parameters. As BizApps has said, you should place your parameters in the same order as defined in your query; which means that PropertyPayorID should come last when you add it to your Parameters collection. The names for the Parameters collection are to be used only locally; like for changing some properties of the individual parameters.

Also, I don't remember if you can use named parameters in your query string as a place holder or if you must use a ? instead; something like Update PropertiesPayors SET [PayorName]=?, ...

Answer 3

The command statement .ExecuteNonQuery returns the number of rows which were affected.

This means if you used...

intRowsAffected = .ExecuteNonQuery()

And the value returned into the variable intRowsAffected was ZERO (0)

Then that means a record with the same value for your field PROPERTYID (meaning the value you passed into the parameters collection... PROPERTYPAYORSESSIONID) does not exist!

And thats why you are not recieving any errors and nor is your database being updated.

To double check this... Where your code statement .EXECUTENONQUERY() is...

You can replace it with the following...

intRowsAffected = .ExecuteNonQuery()
Messagebox(intRowsAffected & " Data rows were updated.")

If the messagebox shows zero rows were updated (no rows were updated) then your next step would be to MANUALLY check the database and see if the row ACTUALLY exists and if it has the SAME key value that you are using to identify the row - which I assume is the property-payor-session-id.

Also keep in mind that the session-id is apt to change with each session and not static all the time.