Click here to Skip to main content
15,843,623 members
Please Sign up or sign in to vote.
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
Updated 21-Aug-14 10:51am
v3

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!
 
Share this answer
 
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.
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
 
Share this answer
 
v2

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900