Alright, here is my issue.
Working on a Inventory Control program, and got it mostly done, when a wild bug appears. The system will check out a item, but will not check it back in, even though it throws all the proper messages that it does check the item in.
What's worse, is that the SQL statement is encapsulated in a try-catch class and acts as if nothing is wrong, and does not throw an exception.
And this is just a functional build, not a streamlined one, so it looks a little rough.
The statement in question is:
Dim OleCheckIn As New OleDbCommand("UPDATE Assets SET [Checked Out]='Checked In' WHERE [ID Number]=" + sBarcode + "", OleDbConn)
I am sure it is something very very obvious, but I have been rebuilding and staring at it for so long, I am likely glossing over a glaring hole in it.
Option Strict On
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
Public EmpIDFlag As Boolean
Public ItemBCode As Boolean
Public CheckFlag As Boolean
Public dEmpID As Double
Public sEmpID As String
Public dbEmpID As Double
Public dBarcode As Double
Public sBarcode As String
Public sFirstName As String
Public sLastName As String
Public sFullName As String
Public sItem As String
Public sCheckedOut As String
Public sCheckedOutBy As String
Public OleDbConn As OleDb.OleDbConnection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source = C:\Users\rcassel\Documents\Visual Studio 2012\Projects\Inventory Control\Inventory Control\Inventory Control2.accdb;")
Private Sub TextBox1_LostFocus(sender As Object, e As EventArgs) Handles TextBox1.LostFocus
dEmpID = (Val(TextBox1.Text))
'Checks to see if someone entered a Badge
If dEmpID = Nothing Then
MsgBox("You must scan your Badge!", MsgBoxStyle.OkOnly)
TextBox1.Focus()
Else
sEmpID = dEmpID.ToString
'Fire Query into Database
Try
OleDbConn.Open()
Dim OleEmp As New OleDbCommand("SELECT [First Name],[Last Name],[Employee ID] FROM Contacts WHERE [Employee ID]=" + sEmpID + "", OleDbConn)
Dim r1 As OleDbDataReader = OleEmp.ExecuteReader()
While r1.Read()
sFirstName = CStr(r1("First Name"))
sLastName = CStr(r1("Last Name"))
dbEmpID = CInt(r1("Employee ID"))
End While
r1.Close()
Catch ex As Exception
'MsgBox("Cannot Pull Data." & vbCrLf & ex.Message)
End Try
If dbEmpID = Nothing Then
MsgBox("You are not Authorised to use this device. This activity has been logged.", MsgBoxStyle.OkOnly)
Else
Me.ListBox1.Items.Add(sFirstName)
Me.ListBox1.Items.Add(sLastName)
Me.ListBox1.Items.Add(sEmpID)
TextBox2.Focus()
End If
OleDbConn.Close()
End If
End Sub
'Item Barcode
'Private Sub TextBox2_LostFocus(sender As Object, e As EventArgs) Handles TextBox2.LostFocus
Private Sub Textbox2_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles TextBox2.KeyPress
dBarcode = (Val(TextBox2.Text))
If e.KeyChar = Microsoft.VisualBasic.ChrW(Keys.Enter) Then
sBarcode = dBarcode.ToString()
OleDbConn.Open()
Try
Dim OleItem As New OleDbCommand("SELECT [Item],[Checked Out],[Checked out Last by] FROM Assets WHERE [ID Number]=" + sBarcode + "", OleDbConn)
Dim r2 As OleDbDataReader = OleItem.ExecuteReader()
While r2.Read()
sItem = CStr(r2("Item"))
sCheckedOut = CStr(r2("Checked Out"))
sCheckedOutBy = CStr(r2("Checked out Last by"))
End While
ItemBCode = True
'Set Checkout Flag, this will be called later by the Check In/Check Out button
If sCheckedOut = "Checked Out" Then
CheckFlag = True
End If
r2.Close()
Catch ex As Exception
MsgBox("Barcode Invalid." & vbCrLf & ex.Message)
ItemBCode = False
End Try
If ItemBCode = True Then
Me.ListBox2.Items.Add(sItem)
Me.ListBox2.Items.Add(sCheckedOut)
Me.ListBox2.Items.Add(sCheckedOutBy)
End If
OleDbConn.Close()
End If
End Sub
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
TextBox1.Focus()
End Sub
'This is the "Check In" button
Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
If ItemBCode = False Then
MsgBox("You must have a Valid Item Barcode!", MsgBoxStyle.OkOnly)
TextBox2.Focus()
Else
If CheckFlag Then
Try
OleDbConn.Open()
Dim OleCheckIn As New OleDbCommand("UPDATE Assets SET [Checked Out]='Checked In' WHERE [ID Number]=" + sBarcode + "", OleDbConn)
MsgBox("This Item has been Checked in!", MsgBoxStyle.OkOnly)
Catch ex As Exception
MsgBox("Barcode Invalid." & vbCrLf & ex.Message)
ItemBCode = False
End Try
Else
MsgBox("This Item is already Checked in!", MsgBoxStyle.OkOnly)
TextBox2.Focus()
End If
End If
OleDbConn.Close()
End Sub
'This is the "Check Out" button
Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
If ItemBCode = False Then
MsgBox("You must have a Valid Item Barcode!", MsgBoxStyle.OkOnly)
TextBox2.Focus()
Else
If CheckFlag = False Then
Try
sFullName = String.Format("{0} {1}", sFirstName, sLastName)
OleDbConn.Open()
Dim OleCheckOut As New OleDbCommand("UPDATE Assets SET [Checked Out]='Checked Out',[Checked out Last by] ='" + sFullName + "' WHERE [ID Number]=" + sBarcode + "", OleDbConn)
MsgBox("This Item has been Checked Out!", MsgBoxStyle.OkOnly)
Catch ex As Exception
MsgBox("Barcode Invalid." & vbCrLf & ex.Message)
ItemBCode = False
End Try
Else
MsgBox("This Item is already Checked Out!", MsgBoxStyle.OkOnly)
TextBox2.Focus()
End If
End If
OleDbConn.Close()
End Sub
End Class
You never execute your update commands:
OleCheckIn.ExecuteNonQuery()
OleCheckOut.ExecuteNonQuery()
Also, use parameters. You are exposing your system to SQL injection.