Code:
'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
Comment