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
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