Hi, I created access database and let people checkin and checkout. I created four buttons, but every time it seems that when people clcik the second one, it gives web server is busy message. It takes quite a while to retrive the page and then check in. It seems that not many people check in at that time. Would any expert look at my code? I really appreciate it and thanks!!!!
Sub Check_Test(sender as Object, e as EventArgs)
Dim currEmpNumber As String = employeeID.Text
currEmpNumber = checkUserInput(currEmpNumber.Trim())
strSQL="Select * from Employee WHERE IDEmployee= '" & currEmpNumber & "'"
call openDB()
Dim objDR as OleDbDataReader
Dim Cmd as New OleDbCommand(strSQL, connTemp)
objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
While objDR.Read()
currName=ValidateInputForDB(objDR("Name"))
End While
call CloseDB()
Call OpenDB()
strSQL="Select IDEmployee from Employee WHERE IDEmployee= '" & currEmpNumber & "'"
Dim myCommand1 As New OleDbCommand(strSQL, connTemp)
currEmpID = myCommand1.ExecuteScalar()
If currEmpID = 0 Then
labelMessage.text = "<blockquote><font size=6 color='blue'><b>* Wrong No! Please try again!</font>"
labelMessage1.visible=false
Else
Dim myCountEmployee as Int32 = 0
strSQL="Select count(*) from Clock WHERE IDEmployee= '" & currEmpNumber & "' and serverDate = '" & currServerDate & "'"
Dim myCommand3 As New OledbCommand(strSQL, connTemp)
myCountEmployee = myCommand3.ExecuteScalar()
If myCountEmployee = 0 Then
labelMessage.text = "<blockquote><font size=6 color='blue'><b>* Sorry! </font>"
labelMessage1.visible=false
else
If myCountEmployee > 0 Then
strSQL="UPDATE Clock SET OutWork1 = '" & currServerTime & "' WHERE IDEmployee= '" & currEmpNumber & "' and serverDate = '" & currServerDate & "' and Outwork1 = '' "
call InsertUpdate(strSQL)
call CloseDB()
End IF
end if
end if
labelMessage1.text ="Bye, " & cstr(currName) &"!" & " "
End Sub
hi,
i'm not an expert, i'm neither familiar with VB. I'm coding in c#, but it looks to me that you don't close the db connection every time.
If myCountEmployee > 0 Then
strSQL="UPDATE Clock SET OutWork1 = '" & currServerTime &"' WHERE IDEmployee= '" & currEmpNumber & "' and serverDate= '" & currServerDate & "' and Outwork1 = '' "
call InsertUpdate(strSQL)
call CloseDB()
End IF
it think the connection is closed only when there are any employees ?
And it is opened before the if, so it should be closed not in the if clause.
If my assumption is right, it is possible to run out of sql connections very soon, which may causes your problem.
If i'm wrong sorry for puzzling you more :)
Cheers,
Yani
