[SOLVED] Problem with Update Statement VB.net

Question

0.00/5 (No votes)

See more:

Hi All

Please i'm having problems with my update statement in a program in currently coding for small car shop. Its a car management system and as a rookie I'm stuck.
In my OLE database i have a couple of tables but the ones concerned with my problem is:
tlbCar - This table holds all the cars registered with their corresponding stock.
tlbSales - This holds sales record in the company.
What i intend doing in this particular form is that when a customer comes to buy a car and the operator selects the model of any car (i've got a textbox which becomes automatically populated by the corresponding field in the tlbCar table showing the current stock of that car) and clicks the save button. I want two things to happen. One to actually save the record for the current transaction and to Update tlbCar (by subtracting amount the customer wants to buy from the amount in the database) with the result of the subtraction. The error is get is that on firing the order form i notice that there wasn't and deduction from the database.
Thanks

VB

Private Sub Create_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Create.Click
        UpdateStock()
        AddRec()
    End Sub
Public Sub AddRec()
        Dim cmdtext As String = "Insert Into tlbSales" & _
                                "(Ord_ID, Ord_Date, Cus_Name, Model, Qty, Price, Total, Mode)" & _
                                "Values (@OrdID,@OrdDate,@CusName,@CarModel,@OrdQty,@OrdPrice,@OrdTotal,@PayMode)"
        Dim cmd As New OleDbCommand(cmdtext, con)
        With cmd.Parameters
            .Add(New OleDbParameter("@OrdID", OrdID.Text))
            .Add(New OleDbParameter("@OrdDate", CDate(OrdDate.Text)))
            .Add(New OleDbParameter("@CusName", CusName.Text))
            .Add(New OleDbParameter("@CarModel", CarModel.Text))
            .Add(New OleDbParameter("@OrdQty", OrdQty.Text))
            .Add(New OleDbParameter("@OrdPrice", CarPrice.Text))
            .Add(New OleDbParameter("@OrdTotal", OrdTotal.Text))
            .Add(New OleDbParameter("@PayMode", PayMode.Text))
        End With
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        Try
            cmd.ExecuteNonQuery()
            SalesDetails()
            Clear.PerformClick()
            MsgBox("Order Transaction Completed" & " " & OrdID.Text, MsgBoxStyle.Information, "Sucessful Operation")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Close()
    End Sub

Public Sub UpdateStock()
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        Dim cmdtext As String = "UPDATE tlbCar SET Car_Quantity = @CQty, Date_Modified = @DMod WHERE Car_ID = @CID"
        Dim cmd As New OleDbCommand(cmdtext, con)
        With cmd.Parameters
            .Add(New OleDbParameter("@CID", CarID.Text))
            .Add(New OleDbParameter("@CQty", Stock.Text))
            .Add(New OleDbParameter("@DMod", CDate(OrdDate.Text)))
        End With
        Try
            cmd.ExecuteNonQuery()
            MsgBox("Stock Updated" & " " & OrdID.Text, MsgBoxStyle.Information, "Sucessful Operation")
        Catch ex As Exception
        End Try
        con.Close()
    End Sub

Posted 21-Aug-14 5:09am

Jesse Ebosionu

Updated 21-Aug-14 9:51am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PhilLenoir · Accepted Answer · 2014-08-21T06:47:00

Solution 1

Jesse,

You don't show where you are updating Stock.Text. If this is the original amount of the stock, you are updating the database with its existing value. I think that you want to reduce Stock.Text by OrdQty.Text or "SET Car_Quantity = Car_Quantity - @OrdQty".

I can see a few other potential issues here:
1) what happens if the insert succeeds but the update doesn't? Myself, I'd suggest putting both database write operations into a stored procedure and surrounding them by a transaction.
2) Concurrency: What happens if two separate connections attempt to update the stock quantity or it is updated from out-of-date state on the client side. For this reason, I'd always use the more dynamic update of reducing the stock amount. Placing this in a transaction (which could be client side instead using a stored proc) makes the operation "atomic" and removes any concurrency issue. If you need the current amount of stock in the application, you could return the amount of stock from the stored proc.
3)Another concurrency issue here is that another process might have sold all stock, so a subsequent competing process puts stock into a negative amount.

Maybe you had already considered these issues and are dealing with them a single connection or some other way. I'd urge you to think about these and incorporate the appropriate safeguards!

Posted 21-Aug-14 6:47am

PhilLenoir

Comments

Jesse Ebosionu 21-Aug-14 13:23pm

Stock.Text = Val(Stock.Text) - Val(OrdQty.Text)
1. The insert statement works without fuss because i dont get any errors whatsoever and the statement implements.
I initially had the two operations on one sub and it works with the update statement not using parameters. But when i opted to use parameters it stops implementing.
2. Stock is updated from the tlbCar table and binded to a text box after you select the model from a combobox (populated also from the same table)
This is how get my stock to the stock textbox
Public Sub RefreshProductID()
Dim da As New OleDbDataAdapter
Dim dt As New DataTable
Try
If con.State = ConnectionState.Open Then
con.Close()
End If
con.Open()
da.SelectCommand = New OleDbCommand("SELECT * FROM tlbCar", con)
da.Fill(dt)
CarModel.DataSource = dt
CarModel.DisplayMember = "Car_Model"
CarModel.ValueMember = "Car_Model"
Stock.DataBindings.Add("Text", dt, "Car_Quantity")
CarPrice.DataBindings.Add("Text", dt, "Car_Price")
CarID.DataBindings.Add("Text", dt, "Car_ID")
Catch ex As Exception
MsgBox(ex.Message)
Finally
con.Close()
End Try
End Sub

PhilLenoir 21-Aug-14 14:47pm

Jesse, thanks for the update. The most logical explanations are the key you are using is not finding the record or you are updating with the same value. Any update with a WHERE clause that finds no records is not an error. Have you inspected CarID.Text and Stock.Text at run time? How about the modified date after the update ran, has it changed? That would be a useful diagnostic. What back-end are you using?

Please note that although refreshing the quantity after the model is selected does not remove the atomicity and concurrency issues, although it may reduce the window a little. You don't know how long an end user will take to commit the final order.

PhilLenoir 21-Aug-14 14:49pm

... what data type is your key? If there are trailing spaces in either the where clause value or the table, this will throw the record seek.

Jesse Ebosionu 21-Aug-14 14:59pm

Im using Access database.
Please can you possibly give me a code snippet that would perform both the insert and update concurrently.
My date didn't change after clicking the button.
If it's the primary key you are referring to its an integer.

PhilLenoir 21-Aug-14 15:06pm

In Access you would have to use a transaction and commit or rollback the transaction appropriately. Have you thought about using SQL Express, then you would be able to use stored procedures? See http://msdn.microsoft.com/en-us/library/93ehy0z8.aspx for transactions with OleDB.

PhilLenoir 21-Aug-14 15:08pm

... but that only helps the concurrency/atomicity issues it won't solve your update problem. Did you inspect those text boxes in debug?

PhilLenoir 21-Aug-14 15:13pm

Jesse, I just noticed that you don't deal with an exception, there's nothing in your Catch section. Are you sure that there are no errors? I'd grab any exception message and log it, or at least look at it in debug.

Jesse Ebosionu 21-Aug-14 15:22pm

And Msgbox (ex.message) inside the catch doesn't say anything so no errors (runtime or syntax) in the code.
Im going to try that link you gave me and see if im going to come out with anything tangible.

PhilLenoir 21-Aug-14 15:38pm

Jesse, I've had another look at the code. What does SalesDetails() do? You run the update code before the insert code, could this be the problem? Also, check your text boxes in debug just before you use them in the command params. This will hopefully stop us chasing ghosts!

One more thing, the transaction code will not help you with conflict on stock quantity if you update from the stock text box, use OrdQty. Explanation: user 1 selects Car ID 3 and there's 4 in stock. Then user 2 selects Car ID 3, there are still 4 in stock. Each user orders 1 car; both processes update the stock left to 3 when the real answer is 2 (stock text box is 4-1). If you reduce the stock by OrdQty you will get the right answer as the Update is atomic.

Jesse Ebosionu · Accepted Answer · 2014-08-21T09:50:00

This snippet solves it which i know it would just that i don't know why with parameters its us chasing ghosts.

VB

Public Sub UpdateStock()
        If con.State = ConnectionState.Open Then
            con.Close()
        End If
        con.Open()
        Dim cmdtext As String = "UPDATE tlbCar SET Car_Quantity =" & Stock.Text & "" & _
                                ", Date_Modified = '" & CDate(OrdDate.Text) & "'" & " WHERE Car_ID = " & CarID.Text & ""
        Dim cmd As New OleDbCommand(cmdtext, con)
        Try
            cmd.ExecuteNonQuery()
            MsgBox("Stock Updated" & " " & OrdID.Text, MsgBoxStyle.Information, "Sucessful Operation")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        con.Close()
    End Sub

Thanks for ya time