Yes Yes Y'All
Please im having problems with my update statement in a program in currently coding for a Poly student. Its a car management system and as a rookie im 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. And booom there is some sort of logical error that i just cant figure out. This is a code snipet. Someone should please be of help.
Thanks
...................................................................................................
Private Sub Create_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Create.Click
AddRec()
UpdateStock()
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 and Stock Updated" & " " & 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
cmd.ExecuteNonQuery()
con.Close()
End Sub
..................................................
PS: This stuff works when using none parameterized query but im just trying to go by industry standards (lol) by using parameters but its not been funny.

Hi,

I am not really into MS SQL but I think all u nid to do is read about accepting and storing the prices/amount of the cars in the same datatype as in ur database (interger/decimal lika said am not really an MS SQL guy. I use MySQL but its the same concept anyways). Then when retrieving the amount convert it to the appropriate type in ur code.. Somethin like CType() before performing the math operations that's should do it..


Addition: I think u shoud create a sub routine that handles ur connection states( closed/open) instead of u having to re-write them later, u would only need to call the subs..


Hope this helps...

fretfingers: Hi,

I am not really into MS SQL but I think all u nid to do is read about accepting and storing the prices/amount of the cars in the same datatype as in ur database (interger/decimal lika said am not really an MS SQL guy. I use MySQL but its the same concept anyways). Then when retrieving the amount convert it to the appropriate type in ur code.. Somethin like CType() before performing the math operations that's should do it..


Addition: I think u shoud create a sub routine that handles ur connection states( closed/open) instead of u having to re-write them later, u would only need to call the subs..


Hope this helps...

I totally dig you bro but its a parameterized query so the issue of data type mismatch is handled because i already validated my form controls to behave properly (i.e the textbox that receives data for price only accepts numerals same goes to quantity).
The issue is that if i don't make the query parameterized every works smoothly but i just want to iron out the issue so that future project wont suffer such.
Maybe I'll have to run to stackoverflow or daniweb to search for answers that's if I'm eventually the last poster in this thread.

Addition: I think u should create a sub routine that handles ur connection states( closed/open) instead of u having to re-write them later, u would only need to call the subs..

Please show me how to go about this. The current way isn't giving problems though but would like to improve and lessen the codes.

Cheers

If the textboxes are accepting numerals, try performing math operations on the numerals first... Create n Test wit a new project specifically for this cause... Twud be better to solve that separately before adding it to the main project kk..


'To connect/open database for transactions or anything its called...
Private Sub openDB()
Try
If con.state = connectionState.closed con.Open()
Catch ex As MySQLException
'U can display msg box here to tell u why the connection trows an exception
Catch ex As Exception
End try

End Sub

'To close DB
Private Sub closeDB()
con.close()
End Sub


Then weneva u nid to open the DB just call each sub routines identifier e.g

Wen u want to open
OpenDB()

That's opens the DB

To close

CloseDB()


Hope this helps...

Your inputs are appreciated.
Thanks a Mill

guente02: Your inputs are appreciated.
Thanks a Mill

Yw bro...

@guente02,

Do you get any specific error message when you run it? Can you share it?

tomilay: @guente02,

Do you get any specific error message when you run it? Can you share it?

It wasn't showing error just dat it wasn't implementing. Its fixed now sha. I had to "deparameterize" the update statement

guente02:
It wasn't showing error just dat it wasn't implementing. Its fixed now sha. I had to "deparameterize" the update statement

I see. Ok. Params is the right way to go. But for your purposes, probably not a big deal.

You might ultimately want to take a peek at model view controller and model view presenter patterns to help separate concerns.