Question How to check if string entered in a text box matches a field in a data table

[Tinkerbell]

How to check if string entered in a text box matches a field in a data table


Hello

I have a form with two text boxes for users to enter their Username and Password. When they enter their details and click the 'Login' button I want their details to be

checked against data in a data table to see if they match or not. I am using an 'if...else' statement but can't work out the syntax to use. My code is as follows:

[

Imports System.Data.OleDb
Imports System

Public Class frmLogin

Dim OleDBConn As New OleDbConnection() 'The OleDB Connection
'Connstring = Server Name, Database Name, Windows Authentication

'Declare inside of class >
Dim SQLStr As String


Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click

'check Username & Password against records in tblLogin

Dim ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\K0201227Project.accdb;Persist Security Info=False;"


'Connection String
OleDBConn.ConnectionString = ConnString 'Set the Connection String

'SQL string
SQLStr = "SELECT * FROM tblLogin"

'New datatable
Dim ds As New DataTable

'New table adapter, passing the sql string and connection
Dim da As New OleDbDataAdapter(SQLStr, OleDBConn)

'fill the table ds with data selected from the sql string
da.Fill(ds)


''''this is the part i'm stuck on''''

If txtUsername.Text = ds????Username Then AND txtPassword.Text = ???Password Then

MessageBox.Show("Successfully logged in")

Else

MessageBox.Show("The Username or Password you entered is incorrect")
End If
End Sub
End Class
]

The fields I want to match the textbox strings against in my database table are called 'Username' and 'Password'.

Appreciate any help! Let me know if you need more info.

Thanks
Amy

 

[oliverg]

Try:

'SQL string
SQLStr = "SELECT Password FROM tblLogin WHERE Username = "(user entered username text)"

Then:

If da.fields(0).Value = (user entered password text) then
msgbox("Success")
Else
msgbox("Incorrect Passoword")
End If

 

[Tinkerbell]

Hi Oliver

I tried the code above but am getting an error for the word 'fields', saying its not a member of System.Data.OleDb.OleDbDataAdapter. (I do have the Imports statement included). Any suggestions?

Thanks
Amy

 

[oliverg]

Sorry,
Getting mixed up with adodb.

Try:

        Dim pWord As String
        Dim ds As New DataSet
        Dim da As New OleDb.OleDbDataAdapter(SQLStr, OleDBConn)

        da.Fill(ds)
        pWord = ds.Tables("tblLogin").Rows(0)("Password").ToString()

Then use the if statement but replace "da.fields(0).Value" with pWord.

I'm not too up on OleDb, but I think this might work.

 

[Tinkerbell]

Thanks Oliver. For the SQL string what should go in the brackets instead of 'user entered username text'? I changed the string to the following for now:

SQLStr = "SELECT Password FROM tblLogin"

Since i'm using a datatable instead of dataset i adapted:

pWord = ds.Tables("tblLogin").Rows(0)("Password").ToString()

to

pWord = ds.Rows(0)("Password").ToString()

It now checks the datatable password column and if it matches the first one it returns the 'Success' messagebox. However if I enter a password from another row in the table it returns 'Incorrect'. Any idea how to get it to check against all rows in the datatable until it finds a match?

Thank you
Amy

 

[oliverg]

Does it need to check all the rows?

When I'm making a user table, I prevent duplicate usernames from being saved.

This means the password that is retrieved from the database when querying a particular username will be the users only password.

But if you have more than one field returned from your query and you would like to check against them all, something like this should work:

        Dim match As Boolean = False

        For i = 0 To (ds.Rows.Count - 1)
            If ds.Rows(i)("Password").ToString() = (user entered password text) Then
                match = True
            End If
        Next

        If match Then
            MsgBox("password match")
        Else
            MsgBox("incorrect password")
        End If

 

[oliverg]

Does it need to check all the rows?

When I'm making a user table, I prevent duplicate usernames from being saved.

This means the password that is retrieved from the database when querying a particular username will be the users only password.

But if you have more than one field returned from your query and you would like to check against them all, something like this should work:

        Dim match As Boolean = False

        For i = 0 To (ds.Rows.Count - 1)
            If ds.Rows(i)("Password").ToString() = (user entered password text) Then
                match = True
            End If
        Next

        If match Then
            MsgBox("password match")
        Else
            MsgBox("incorrect password")
        End If

P.S.

The "user entered password text" should be your textbox.text value.

 

[Tinkerbell]

Its not that I want to check against each row, but when I run the program it only checks the first one. I think the problem is my SQL statement, as I haven't included the "WHERE Username = " part because I wasn't sure about the "user entered password text" bit. Where you've said to put the textbox.text value, how do I make the SQL string check against that? I tried a few things including the following but no luck:

 SQLStr = "SELECT Password FROM tblLogin WHERE Username = ('" & txtUsername.Text & "')"

Thank you!

 

[Tinkerbell]

Hi Oliver, not to worry...it is working now! thanks so much for all your help, I really appreciate it

 

[nokia6230ivn]

Use "For ... to " as above is good, i think.
But it take a lot of time if database has a lot of rows.
You should use "Do ... loop ... while..." is better.
Best regard.