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

May be *this* piece of code or *method/function* is not called & executed. — KV Prajapati, Dec 26 '12 at 03:07
Thanks for the reply but I'am pretty sure I called it. I'm really having a hard time because of this. — Kosmo, Dec 26 '12 at 03:09
Remove all try..catch statements and see what happen? Please post the *exception/error* description. Please include *event* handler definition (button_click or something like that). — KV Prajapati, Dec 26 '12 at 03:11
I did try that, but there were no errors or exceptions in the error list. — Kosmo, Dec 26 '12 at 03:13
Could you try my method and see just HOW MANY rows are affected? I've a feeling that if your code does not raise errors but gets to the EXECUTENONQUERY method then its simply a case of the KEY FIELD in the database records not being matched... IE.. the record does not exist — Zeddy, Jan 03 '13 at 07:42

score 1 · Answer 1 · edited May 23 '17 at 12:27

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!

thanks for the answer, but it was really a typo from the text editor here in the forums. I'm actually new here so placing the code is hard. But then again, I'm still stuck. — Kosmo, Dec 26 '12 at 04:24
yes, i saw the comment at the first part of the parameters, I've tried it, but still, it won't work. — Kosmo, Dec 26 '12 at 05:36

score 0 · Answer 2 · answered Dec 26 '12 at 05:17

0

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]=?, ...

answered Dec 26 '12 at 05:17

SylvainL

3,926
3
20
24

thanks for the reply @SylvainL, i tried your method, but it didn't work. And about the `?` as a place holder, I'm not actually familiar with it. What should I put in the parameter name? – Kosmo Dec 26 '12 at 05:31
For the parameter name, you write anything; it's just a local reminder in case you want to change later a property of a parameter element: it's useful locally but without significance remotely. I know that there is somewhere an option for ODBC (COM) to change this behavior but for .NET OleDbCommand, I have no idea as I'm not familiar with it. – SylvainL Dec 26 '12 at 06:29

score 0 · Answer 3 · answered Dec 28 '12 at 15:29

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.

Code does not work but has no errors

3 Answers3