Matching the records

[reycons]

Option Explicit On
Imports MySql.Data.MySqlClient

Public Class LOGIN
Dim conn As MySqlConnection

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click

'Setting of Variables...
Dim mycommand As MySqlCommand
conn = New MySqlConnection

'Connection string...
conn.ConnectionString="server=localhost;
database=GeneticDatabase;user=root;password=root;"

mycommand = New MySqlCommand
mycommand.Connection = conn

'Finding the record...
mycommand.CommandText = "SELECT Username,Password FROM Login " & "WHERE Username = '" & txtUsername.Text & "'
Password = '" & txtPassword.Text & "'"

End Sub

My problem is this how to put a condition using if Else Statement next to select statement. When the user type his/her username on txtUsername and password in the txtPassword and when it matches the records in the table Login it generates messagebox Record Found and if not it generates a message Record Not found...

Thanks in advance...

 

[assimang]

dim LoginCmd as SqlCommand
dim SqlLogin as string
dim LoginRdr as sqlDataReader

SqlLogin="SELECT Username,Password FROM Login " & "WHERE Username = '" & txtUsername.Text & "' and Password = '" & txtPassword.Text & "'"

LoginCmd=new SqlCommand (SqlLogin,conn)
If Conn.State = ConnectionState.Closed Then Conn.Open()
LoginRdr=LoginCmd.ExecuteReader

Do While LoginRdr.Read
If txtUserName.text=LoginRdr("Username") and txtPassword.text=LoginRdr("Password") Then
msgbox ("User found", msgboxstyle.Information+msgboxstyle.Okonly,"Login")
else
msgbox ("User not found", msgboxstyle.Information+msgboxstyle.Okonly,"Login")
End If
Loop

The LoginRdr provides a steady one-way stream of data pouring from the database straight into your program but doesn’t provide disconnected access or any ability to change or update the original data source.

Hope it helps.

 

[reycons]

Public Class LOGIN
Dim conn As MySqlConnection

Private Sub cmdLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdLogin.Click

'Setting of Variables...
Dim LoginCmd As MySqlCommand
Dim SqlLogin As String
Dim LoginRdr As MySqlDataReader
conn = New MySqlConnection

'Connection string...
conn.ConnectionString = "server=localhost;user=root;password=root;database=geneticdatabase;"

SqlLogin = "SELECT Username,Password FROM Login " & "WHERE Username = '" & txtUsername.Text & "' and Password = '" & txtPassword.Text & "'"

LoginCmd = New MySqlCommand(SqlLogin, conn)
If conn.State = ConnectionState.Closed Then conn.Open()
LoginRdr = LoginCmd.ExecuteReader

Do While LoginRdr.Read
If txtUsername.Text = LoginRdr("Username") And txtPassword.Text = LoginRdr("Password") Then
MessageBox.Show("User found", "Login")
Else
MessageBox.Show("User not found", "Login")
End If
Loop

End Sub

It displays no messagebox like User Found or User not found..It is a logical error..

 

[reycons]

Sorry...

The Messagebox User found is working. The only problem is the Else statement because the User not found mesagebos does not display...

 

[assimang]

I can't find something wrong with this, I don't know why the else statement doesn't work. Are you getting an error?

Well try this:
Do While LoginRdr.Read
If txtUsername.Text = LoginRdr("Username") And txtPassword.Text = LoginRdr("Password") Then
MessageBox.Show("User found", "Login")
ElseIf txtUsername.Text <> LoginRdr("Username") And txtPassword.Text <> LoginRdr("Password") Then
MessageBox.Show("User not found", "Login")
End If
Loop
LoginRdr.Close
conn.close
End Sub

Something else: When you type the username and password in textboxes and you click on cmdLogin, it's expected that if username and password are match with a record in the Login table then you will get the messagebox that the user found.
Differrently, if you type a username and a password that don't exist in your login table in your database it's expected that it will display the message box user not found.

Another way, if that doesn't work is to hold the values of textboxes into variables

Public TempUserName as string
Public TempPassword as string
TempUserName=txtUsername.text
TempPassword=txtPassword.text

Do While LoginRdr.Read
If TempUserName = LoginRdr("Username") And TempPassword = LoginRdr("Password") Then
MessageBox.Show("User found", "Login")
ElseIf TempUserName <> LoginRdr("Username") And TempPassword <> LoginRdr("Password") Then
MessageBox.Show("User not found", "Login")
End If
Loop
LoginRdr.Close
conn.close

Hope it helps.

 

[gmmastros]

I think the problem here is the query. You have a where clause on the query to match the user name and password. So, if there is no match, you wouldn't receive any records. So the Do/While loop never gets entered.

If this were SQL Server, I would have written the query like this...

If Exists(SELECT * FROM Login WHERE Username = 'UserNameToTest' and Password = 'PasswordToTest')
  Select 1 As LoginSuccessful
Else
  Select 0 As LoginSuccessful

With the query like this, you will always receive 1 row with 1 column (Named LoginSuccessful). Then, from the VB side, just check the value of the column and present your message box based on it's value.

I should mention that the code I show above removed your text box values, so you'll need to put that in. I removed it because it makes the code easier to understand from a logical perspective.

I should also mention that this code will work with Microsoft SQL Server. Therefore, it may not work with MySQL. I don't know about that part. However, I feel relatively confident that if this exact syntax does not work, there is probably a MySQL alternative that effectively returns the same info, but with a slightly different syntax.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jebenson]

The problem is this line:

ElseIf TempUserName <> LoginRdr("Username") And TempPassword <> LoginRdr("Password") Then

Usin the "And" condition means that the "User not found" message will only appear if both the username and password are incorrect. If the username is correct but password is incorrect (and vice versa), this condition will not be met. Change it to this:

ElseIf TempUserName <> LoginRdr("Username") Or TempPassword <> LoginRdr("Password") Then

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[earthandfire]

jebenson, you are only partially correct there.

The entire ElseIf clause is superfluous, since If correctly tests using And, Else, as used in reycons' post of 19 Apr 08 23:47 is all that is needed.

 

[gmmastros]

But....

        Do While LoginRdr.Read
            If txtUsername.Text = LoginRdr("Username") And txtPassword.Text = LoginRdr("Password") Then
                MessageBox.Show("User found", "Login")
            Else
                MessageBox.Show("User not found", "Login")
            End If
        Loop

If no records are returned from the query, would the IF line even execute? I think the DO loop would be skipped entirely, therefore no messagebox at all. This, clearly, is a vb.net thingy, which I am just now starting to learn, so it's possible that I'm wrong here. (please be gentle).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[earthandfire]

Good point, George.

There should in any case be a maximum of 1 record returned, so, assuming the SQL query is correct, then something like:


not tested
[tt]Dim LogInOK as Boolean = rdr.HasRecords[/tt]


should do what is needed.

 

[earthandfire]

... unless using the SQL statement that you (George) suggested, then there should be only 1 record returned. In which case, again no need for the Do Loop, just the simple

If Then
Else
End If


Hope this helps.

 

[assimang]

Except of all these reycons said : "The Messagebox User found is working. The only problem is the Else statement because the User not found mesagebos does not display... "

In my opinion it must work right because I consider that if the messagebox user found is working, must be working too and the messagebox user not found. That means, as e&f told too, that if the sql statement doesn't return 1 record the if else endif statement will be executed at all. But, if the if statement executed, it's expectable that else statement will be executed too. But every statement will be executed in the right case.

 

[earthandfire]

assimang, as George pointed out, if 0 records are returned neither the If nor the Else will be executed because the Do Loop will not be entered.

Since a maximum of 1 record will be returned (whichever version of the query is used), there is no need for the Do Loop (which would only be needed to iterate through multiple records).


Hope this helps.

 

[reycons]

Well... Thanks guys for your assistance I think that help surely guide me in searching a record.. the logical problem is only a slight problem in vb.net...

Thanks to:
assimang (Programmer)
gmmastros (Programmer)
jebenson (TechnicalUser)
earthandfire (Programmer)

Thanks to you guys...
I've apply the things you said but i think that error is within vb.net.

 

[assimang]

You are right e&f I suggested the do loop because I didn't know if Reycons had set the fields as primary keys. He could have allowed dublicated recors which would be a very bad problem.

 

[adrianfan]

Hi reycons,

I am new to VB Coding. However,hope this help.

Thanks to the above experts I made minor adjustment to the if else conditions

'Checks whether username is entered
If txtUsername.Text = "" Then
MsgBox("Please enter a username")

'Checks whether password is entered
ElseIf txtPassword.Text = "" Then
MsgBox("Please enter a password")

'Checks whether conenction is open
ElseIf conn.State = ConnectionState.Closed Then
conn.Open()
loginreader = login.ExecuteReader
Do While loginreader.Read
If txtUsername.Text = loginreader("username") And txtPassword.Text = loginreader("password") Then
frmMain.Show()
Me.Hide()
Me.Close()
Else
MsgBox("Wrong username or password")
End If
Loop

End If