Help in Login form with sql Statement dr = cmd.ExecuteReader

[alreakemz]

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click
Dim sqlSearch, User, Pass, Level As String

open_con()
sqlSearch = "SELECT * FROM tblSecurity WHERE Username = '" & txtUsername.Text & "' AND Userpass = '" & txtUserpass.Text & "'"
cmd = New OleDbCommand(sqlSearch, con)
dr = cmd.ExecuteReader
If dr.Read() Then
User = dr("Username")
Pass = dr("Userpass")
Level = dr("Userlevel")
'Else
'MsgBox("Failed to Enter", MsgBoxStyle.Information, "Access Denied!")
End If
dr.Close()
dr = Nothing
con.Close()
If txtUsername.Text <> "" And txtUserpass.Text <> "" Then
If txtUsername.Text = User And txtUserpass.Text = Pass Then
If Level = "Administrator" Then
MsgBox("Welcome Administrator", MsgBoxStyle.Information, "Information")
Me.Hide()
x.Show()

Dim a, b As String

a = TimeOfDay
b = Today
'------------
Dim Newrec As Integer = 400000
Dim Authid As Integer
Dim copy As Integer
Dim logcode As String

'Search records in database

cmd = New OleDbCommand("SELECT LogCode FROM tblLogs", con)
dr = cmd.ExecuteReader

While dr.Read
Newrec = +1
Authid = dr("LogCode")
End While
dr.Close()

logcode = Authid + 1
copy = logcode
If Authid = Newrec Then
logcode += 1
copy = logcode
End If
If Authid = 0 Then
'newrec + =1
logcode = Newrec
copy = logcode
End If

cmd = Nothing
Refresh()

sqlInsert = "INSERT INTO tblLogs VALUES('" & logcode & "', '" & txtUsername.Text & "','" & a & "','" & b & "',' active ')"

cmd = New OleDbCommand(sqlInsert, con)
Try
cmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Entry failed to save in database", MsgBoxStyle.Exclamation, "Information")
End Try

cmd = Nothing

'----------------------------------------------------


x.StatAdmAns.Text = "Administrator"
x.StatUserAns.Text = User
x.StatLoginAns.Text = TimeOfDay
x.StatDateAns.Text = x.MonthCalendar1.TodayDate


End If
Else
MessageBox.Show("You Have " & 4 - forgot & " attempt!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Warning)
forgot += 1
If forgot And ax > 1 Then
MsgBox("Invalid account", MsgBoxStyle.Critical, "Warning")
txtUsername.Text = ""
txtUserpass.Text = ""
txtUsername.Focus()
Refresh()

Else
MsgBox("Sorry, you are not registered user", MsgBoxStyle.Exclamation, "Message")
txtUsername.Enabled = False
txtUserpass.Enabled = False
cmdLogin.Enabled = False
Timer1.Enabled = False
Timer1.Start()
b -= 1
If b = 1 Then
MsgBox("Program has been terminated", MsgBoxStyle.Exclamation, "Message")
End
End If
End If
End If
Else
MessageBox.Show("You have " & ax - forgot & "attempt!", "Information", MessageBoxButtons.OK, MessageBoxIcon.Warning)
forgot += 1
If forgot < 4 And ax > 1 Then
If txtUsername.Text <> "" And txtUserpass.Text = "" Then
MsgBox("Please enter correct password", MsgBoxStyle.Critical, "Warning")
txtUserpass.Focus()
If txtUserpass.Text <> "" And txtUsername.Text = "" Then
MsgBox("Please enter correct username", MsgBoxStyle.Critical, "Warning")
txtUsername.Focus()
Else
MsgBox("Please complete all fields", MsgBoxStyle.Exclamation, "Confirmation")
End If
Else
MsgBox("Sorry, you are not a registered user", MsgBoxStyle.Exclamation, "Message")
txtUsername.Enabled = False
txtUserpass.Enabled = False
cmdLogin.Enabled = False
Timer1.Enabled = False
Timer1.Start()
b -= 1
MsgBox("Program has been terminated", MsgBoxStyle.Exclamation, "Message")
End
End If
End If
End If
con.Close()
End Sub


my problem is:
cmd = New OleDbCommand("SELECT LogCode FROM tblLogs", con)
dr = cmd.ExecuteReader

While dr.Read
Newrec = +1
Authid = dr("LogCode")
End While
dr.Close()

Error Here:
An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: ExecuteReader requires an open and available Connection. The connection's current state is Closed.


any suggestion plz... i need to correct this problem... sorry for my bad english..

 

[jmcilhinney]

As the error message says, your connection is closed. Open it.

 

[BlackByte]

you should also try making your code more cleaner by separating it into function / sub procedures and also google 'n-tier architecture'.