Update query not working in vb.net 2008 access?

[gagandeepsngh]

'Code for selecting a particular data from database
'***** START *****
 
sSQL = "SELECT name FROM trans"
cmd = New OleDbCommand(sSQL, con)
Dim reader As OleDbDataReader = cmd.ExecuteReader()
While reader.Read()
Dim tempitem As String
tempitem = reader.GetString(0)
 
sSQL = "SELECT itemname = '" + tempitem + "' FROM itemcount"
cmd = New OleDbCommand(sSQL, con)
Dim itemFound As Integer
itemFound = cmd.ExecuteNonQuery()
MsgBox("Temp Item : '" + tempitem + "' ")
 
If (itemFound = 0) Then
'INSERTION QUERY
MsgBox(" In the insertion area ")
Dim sSQL2 As String
 
'PROBLEM AREA STARTS
sSQL2 = "INSERT INTO itemcount(itemname, itemcnt) VALUES ('" + tempitem + "',1)"
cmd = New OleDbCommand(sSQL, con)
Dim chek As Integer = cmd.ExecuteNonQuery()
If (chek > 0) Then
MsgBox("Sucess")
Else
MsgBox("Failure")
End If
 
'PROBLEM AREA ENDS
'OUTPUT IS ALWAYS (FAILURE) HENCE IT NEVER GOES IN THE ELSE CONDITION
Else
'UPDATE QUERY
MsgBox(" In the Update area ")
Dim sSQL2 As String
sSQL2 = "SELECT qty FROM trans WHERE name = '" + tempitem + "'"
cmd = New OleDbCommand(sSQL2, con)
Dim val1 As OleDbDataReader = cmd.ExecuteReader()
Dim val2 As Integer
val2 = 1
 
Dim sSQL3 As String
sSQL3 = "UPDATE itemcount SET itemcnt = '" + val2 + "' where itemname = '" + tempitem + "'"
cmd = New OleDbCommand(sSQL3, con)
cmd.ExecuteNonQuery()
MsgBox(cmd)
 
End If
 
End While
'***** END *****
 
reader.Close()
con.Close()
MsgBox("The Connection to the Database is now Closed")
End Sub
End Class

Help me out guys

[MrMancunian]

sSQL = "SELECT itemname = '" + tempitem + "' FROM itemcount"
cmd = New OleDbCommand(sSQL, con)
Dim itemFound As Integer
itemFound = cmd.ExecuteNonQuery()
MsgBox("Temp Item : '" + tempitem + "' ")
 
If (itemFound = 0) Then
'INSERTION QUERY
MsgBox(" In the insertion area ")
Dim sSQL2 As String
 
'PROBLEM AREA STARTS
sSQL2 = "INSERT INTO itemcount(itemname, itemcnt) VALUES ('" + tempitem + "',1)"
cmd = New OleDbCommand(sSQL, con)
Dim chek As Integer = cmd.ExecuteNonQuery()
If (chek > 0) Then
MsgBox("Sucess")
Else
MsgBox("Failure")
End If 

Ok, there are a few things I noticed. Concatenation in VB.NET is done with an ampersand (&), instead of plus (+). If your If...Then-statement doesn't go to the else, it means that your query ("SELECT itemname = '" + tempitem + "' FROM itemcount") doesn't affect any row. The ExecuteNonQuery returns the number of affected rows. You need to check if your query is correct (mind the concatenation). You can put a breakpoint on your query and see how it looks in runtime. If you're not sure it works, copy and paste it in a query editor in your SQL-environment.

Steven

[gagandeepsngh]

Thanks a lot that really helped me and i did solved my problem