I am trying to use a global variable within my asp.net page, but the value goes to zero when a new subroutine is run. I am looking for another set of eyes to debug since I have been staring at this code a a while. The variable that is not holding its value for both subroutines is 'userID'.
Code:
<SCRIPT runat="server">
Dim DBConnection As OleDbConnection
Dim DBCommand As OleDbCommand
Dim DBReader As OleDbDataReader
Dim SQLString As String
Dim SQLString1 As String
Dim uID
Dim userID
'View certain items from database
'
Sub Page_Load
If Not Page.IsPostBack Then
If session("id") <> "" Then
userID = session("id")
lblEmployee.Text = userID
DBConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Inetpub\wwwroot\support\database\EMCMasterDatabase.mdb")
DBConnection.Open()
SQLString = "SELECT * FROM Client WHERE Active = Yes ORDER BY Client"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
ddClient.DataSource = DBReader
ddClient.DataTextField = "Client"
ddClient.DataValueField = "ClientID"
ddClient.DataBind()
DBReader.Close()
SQLString = "SELECT * FROM InvItems ORDER BY Item"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
ddItem.DataSource = DBReader
ddItem.DataTextField = "Item"
ddItem.DataValueField = "InvItemID"
ddItem.DataBind()
DBReader.Close()
DBConnection.Close()
Else
response.redirect("error.htm")
End If
End If
End Sub
'Add new record to database
'
Sub AddRecord (Src As Object, Args As EventArgs)
Dim ValidRecord As Boolean = True
If ValidRecord = True Then
Try
DBConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Inetpub\wwwroot\support\database\EMCMasterDatabase.mdb")
DBConnection.Open()
SQLString = "SELECT UserID FROM Users WHERE Username = " & userID
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
while DBReader.Read()
uID = DBReader("UserID")
End While
DBReader.Close()
SQLString = "INSERT INTO InvList " & _
"(IvItemID, Quantity, ClientID, UserID) " & _
"VALUES (" & _
"'" & ddItem.SelectedItem.Value & "', " & _
"'" & tbQuantity.Text & "', " & _
"'" & ddClient.SelectedItem.Value & "', " & _
"'" & uID & "')"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBCommand.ExecuteNonQuery
SQLString = "UPDATE InvItems SET QuantityRemaining = QuantityRemaining + tbQuantity.Text WHERE " & _
" InvItems.InvItemID = InvList.InvItemID"
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBCommand.ExecuteNonQuery
DBConnection.Close()
AddRecordMessage.Text = "Record added"
Catch
AddRecordMessage.Text = "Update problem. Record not added. " & SQLString
End Try
End If
End Sub
Sub Get_Items (Src As Object, Args As EventArgs)
noRecords.Text = ""
Try
DBConnection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Inetpub\wwwroot\support\database\EMCMasterDatabase.mdb")
DBConnection.Open()
If searchItem.Text = "all" Then
SQLString = "SELECT * FROM InvItems"
SQLString1 = "SELECT Count(*) FROM InvItems"
Else
If Src.id = "Button1" Then
SQLString = "SELECT * FROM InvItems WHERE Item LIKE '%" & searchItem.Text & "%'"
SQLString1 = "SELECT Count(*) FROM InvItems WHERE Item LIKE '%" & searchItem.Text & "%'"
End If
If Src.id = "Button2" Then
SQLString = "SELECT * FROM InvItems WHERE Description LIKE '%" & searchItem.Text & "%'"
SQLString1 = "SELECT Count(*) FROM InvItems WHERE Description LIKE '%" & searchItem.Text & "%'"
End If
End If
DBCommand = New OleDbCommand(SQLString1, DBConnection)
If DBCommand.ExecuteScalar() <> 0 Then
DBCommand = New OleDbCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
RepeaterDisplay.DataSource = DBReader
RepeaterDisplay.DataBind()
DBReader.Close()
Else
noRecords.Text = "No records match selection"
End If
DBConnection.Close()
searchItem.Text = ""
Button1.Checked = "False"
Button2.Checked = "False"
Catch
End Try
End Sub
</SCRIPT>