[Resolved] [2005] Error using datareader with mysql

[moviejunkie]

I am making a program which requires the user to login in, it is a simple form with a username and password text boxes and a login buttion, all the user information is stored on a MySqlDatabase. I can connect and read from the database, the trouble I am having is when the user types a username that is not in the database it give me an error of "Invalid attempt to access a field before calling Read()"

HELP. Here is my code for the login button

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        conn.ConnectionString = sql
 
        Try
            conn.Open()
            SqlCommand.Connection = conn
            SqlCommand.CommandText = "SELECT * from users where username = ?UserName"
            SqlCommand.Parameters.Add("?UserName", txtUserName.Text)
            Dim reader As MySqlDataReader = SqlCommand.ExecuteReader
            reader.Read()
            username = reader.Item("username").ToString
            password = (DecryptText(reader.Item("password").ToString, "aaaa"))
            If txtPassword.Text = password Then
                MsgBox(username)
                CheckInAllowed = reader.Item("CheckInAllowed")
                LookAtRecord = reader.Item("LookAtRecord")
                ChangeRecord = reader.Item("ChangeRecord")
                EditUsers = reader.Item("EditUsers")
                allowexit = reader.Item("allowexit")
            Else
                MsgBox("error")
            End If
            conn.Close()
        Catch ex As MySqlException
            MessageBox.Show("Error Connecting to Database: " & ex.Message)
            Debug.Write(ex.Message)
        Finally
            conn.Dispose()
        End Try
    End Sub

[jmcilhinney]

If the user enters an invalid user name then the reader will not contain any rows, so you can't read any rows. If the reader contains no rows then Read will return False, so when you call Read you have to test its return value.

Also, you're going about your password validation all wrong. You shouldn't be getting the record that corresponds to a user name and then decrypting the password it contains and then comparing that to the password provided. You should be encrypting the password provided and then querying the database for a record that has the provided user name AND that password. Normally you would use a one-way encryption scheme too, so that there is no way someone can get hold of your encryption algorithm and use it to decrypt all the passwords in the database.

[moviejunkie]

Ok I am still having problems... Help..... ok now that is out of my sysytem here is my updated code:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        conn.ConnectionString = sql
        Sentpassword = (EncryptText(txtPassword.Text, "aaaa"))
        Try
            conn.Open()
            SqlCommand.Connection = conn
            SqlCommand.CommandText = "SELECT * from users where username = ?UserName AND password = ?Password"
            SqlCommand.Parameters.Add("?UserName", txtUserName.Text)
            SqlCommand.Parameters.Add("?Password", Sentpassword)
            Dim reader As MySqlDataReader = SqlCommand.ExecuteReader
            If reader.Read = True Then
                username = reader.Item("username").ToString
                MsgBox(username)
                CheckInAllowed = reader.Item("CheckInAllowed")
                LookAtRecord = reader.Item("LookAtRecord")
                ChangeRecord = reader.Item("ChangeRecord")
                EditUsers = reader.Item("EditUsers")
                allowexit = reader.Item("allowexit")
            Else
                MsgBox("error")
            End If
            conn.Close()
        Catch ex As MySqlException
            MessageBox.Show("Error Connecting to Database: " & ex.Message)
            Debug.Write(ex.Message)
        Finally
            conn.Dispose()
        End Try
    End Sub

I am still perplexed on how I can use the ".reader" to tell if the user can login in or not.

Oh and thanks for helping. I love this site

[jmcilhinney]

I am still perplexed on how I can use the ".reader" to tell if the user can login in or not.

If Read returns False that does NOT mean an error has occurred. As I said before, it means that your query has not matched any rows, which means there is no user with those credentials. If Read returns True then that means you have matched a row, so there is a user with those credentials so the login is successful.

[moviejunkie]

Thank you for your help I got it to work....well kind of....if the user does not have a password it does not work if you leave the pass text box blank. Oh well I guess I will have to have the people that do not want a password not be able to log in. (I think it has something with the encryption)

Well here is my working code:

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        conn.ConnectionString = sql
        Try
            conn.Open()
            SqlCommand.Connection = conn
            SqlCommand.CommandText = "SELECT * from users where username = ?UserName AND password = ?Password"
            SqlCommand.Parameters.Add("?UserName", txtUserName.Text)
            SqlCommand.Parameters.Add("?Password", (EncryptText(txtPassword.Text, "aaaa")))
            Dim reader As MySqlDataReader = SqlCommand.ExecuteReader
            If reader.Read Then
                username = reader.Item("username").ToString
                MsgBox(username)
                CheckInAllowed = reader.Item("CheckInAllowed")
                LookAtRecord = reader.Item("LookAtRecord")
                ChangeRecord = reader.Item("ChangeRecord")
                EditUsers = reader.Item("EditUsers")
                allowexit = reader.Item("allowexit")
            Else
                MsgBox("Either your user name or password was wrong, try again", MsgBoxStyle.OkOnly, "Error")
            End If
            conn.Close()
        Catch ex As MySqlException
            MessageBox.Show("Error Connecting to Database: " & ex.Message)
            Debug.Write(ex.Message)
        Finally
            conn.Dispose()
        End Try
    End Sub

[jmcilhinney]

Why don't you rewrite EncryptText to simply return an empty string if it's passed an empty string, and the same for DecryptText?

[moviejunkie]

could do that....man your smart thanks for all the help, I am new at this db programming stuff...

[jmcilhinney]

Don't mistake experience for intelligence.