User Login DataReader? 1

[swk003]

I am having problems writing some user login DataReader code. I'm not entirely sure how the DataReader object works and have come a cropper when trying to authenticate the txtPassword data. Do I need to initialise 2 select queries (1) username (initials)(2) password (Login_ID), can i use a DataReader index?? Do I need to open a second connection? Can anyone help with the syntax? Here's context:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click

'Open the Users table
Dim myQuery As String = "SELECT Initials,Login_ID FROM Users WHERE Initials = '" & Me.txtUserName.Text & "'"
'Dim myQuery1 As String = "SELECT Login_ID FROM Users WHERE Login_ID = '" & Me.txtPassword.Text & "'"

Dim myConnect As New OleDbConnection("Data Source=C:\HELSdb1.mdb;Provider=Microsoft.Jet.OLEDB.4.0")
Dim myCommand As OleDbCommand = New OleDbCommand(myQuery, myConnect)

myConnect.Open()
Dim myReader As OleDbDataReader = myCommand.ExecuteReader

If myReader.Read = False Then
MsgBox("Invalid login, no such user!", vbOKOnly + vbCritical)
txtUserName.Focus()
End If
If myReader.Read = True Then
'problems here as don't know how to test for the txtPassword value
If myReader.Read = True Then
MsgBox("Valid login", vbOKOnly + vbInformation)
Dim getUser As String
getUser = Me.txtUserName.Text
Me.Visible = False
dlg = New Form1
dlg.Show()
Else
MsgBox("Invalid password, try again or Quit Application!", vbOKOnly + vbCritical)
txtPassword.Focus()
End If
End If
myReader.Close()
myConnect.Close()

 

[mansii]

This is what I did:

Dim myConnect As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = C:\HELSdb1.mdb;Jet OLEDB:Database Password=myPWD")

Dim cmd As OleDbCommand = New OleDbCommand("Select count(UserName) from Users where Initials='" & Me.txtUserName.Text & "' and Login_ID  = '" & Me.txtPassword.Text & "'", myConnect )

Dim rTest
myConnect.Open()
rTest = cmd.ExecuteScalar
cmd.Dispose()
myConnect.Close()
If rTest > 0 then
   MsgBox("OK")
Else
   MsgBox("Not OK")
End If

Regards.

 

[swk003]

Hi Mansii

Just the ticket. Does exactly what I need it to do and very simple. I had seen references to the ExecuteScalar method previously but wasn't sure how to use it. Thanks again!

 

[mansii]

My pleasure, and thank's for the star.