Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hai i got a probleam with my sql command for update. Please advice me how i able to correct my command .Here is may code



VB
Try
            Connect = New OleDbConnection("Provider=SQLOLEDB ;Data Source=Danawa;Initial Catalog=Store;Integrated Security=SSPI ")
            Connect.Open()
            cmd = New OleDbCommand  ("UPDATE ItemIn SET Itemquantity= Itemquantity - '" & ItemquantityTextBox.Text & "AND" & "'Total= Total -'" & total.Text & "'WHERE ItemName= '" & ItemNameComboBox.Text & "' ", Connect) / i got an error in this line
            ra = cmd.ExecuteNonQuery()
Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        MsgBox(" Purchase Successfull ", MsgBoxStyle.Information)
        Connect.Close()
Posted
Updated 12-Oct-11 18:48pm
v3
Comments
Simon_Whale 13-Oct-11 6:57am    
What is the actual error message cout by the try catch? As I have a feeling that it is to do with datatypes

Use the following query, this will help you:
SQL
"UPDATE ItemIn SET Itemquantity = Itemquantity - " & ItemquantityTextBox.Text & ", Total= Total - " & total.Text & " WHERE ItemName= '" & ItemNameComboBox.Text & "' "
 
Share this answer
 
v2
Looking at your sql command, I'm guessing that Itemquantity and Total are Numbers instead of VarChars. So, your solution, as well as the other suggested, won't work, since you're deducting a string (ItemquantityTextbox.Text and total.Text) from a number.
 
Share this answer
 
replace with this one once
cmd = New OleDbCommand  ("UPDATE ItemIn SET Itemquantity= Itemquantity - '" & ItemquantityTextBox.Text & "'," & "Total= Total -'" & total.Text & "' WHERE ItemName= '" & ItemNameComboBox.Text & "' ", Connect)
 
Share this answer
 
change AND to comma (,) in sql statement.
SQL
cmd = New OleDbCommand ("UPDATE ItemIn SET Itemquantity= Itemquantity - '" & ItemquantityTextBox.Text & "," & "'Total= Total -'" & total.Text & "'WHERE ItemName= '" & ItemNameComboBox.Text & "' ", Connect)
 
Share this answer
 
Comments
Pravin Patil, Mumbai 13-Oct-11 0:48am    
Good answer....
Bun Leap_kh 13-Oct-11 0:54am    
thanks...
Iswandi Abdul Rahman 13-Oct-11 1:15am    
Sorry bun it did not work, i got an error Incorrect syntax near Total. Please advice again
Bala Selvanayagam 13-Oct-11 2:34am    
It would not work.....

there is no blank space betwen single quote and Total and similarly there is no blank space between the single quote and where statement

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