Error in login codes using mySQL database


Hi there,
I have been trying solve my error at this codes, it takes me too long.

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

    Dim conn As New MySqlConnection
    Dim myCommand As New MySqlCommand
    Dim comm As String
    Dim myconnstring As String
    Dim UserID As Integer
    Dim myReader As MySqlDataReader
    Dim pword As String = PasswordTextBox.Text

    myconnstring = "datasource=localhost;username=****;password=********;database=newscoring;"
    conn = New MySqlConnection(myconnstring)
    comm = "SELECT a.`uname`, a.`pword` FROM admin a where `pword` = " + pword


            myCommand.Connection = conn
            myCommand = New MySqlCommand(comm)

            UserID = myCommand.ExecuteScalar '**my program stops here.** 
                                             'Error:Connection must be valid and open.
            myReader = myCommand.ExecuteReader


            While myReader.Read
                TextBox1.Text = myReader.GetValue(myReader.GetOrdinal("pword"))
            End While

            If TextBox1.Text = PasswordTextBox.Text Then
                Dim mainForm As New Form1
                mainForm.UserID = UserID
                mainForm.connectionString = myconnstring
            End If
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        End Try

    Catch myerror As MySqlException
        MessageBox.Show("Error Connecting to Database: " & myerror.Message)
    End Try
End Sub

System.InvalidOperationException was unhandled
Message="Connection must be valid and open."


In the line 17 you set a connection object to a command object: myCommand.Connection = conn
In the line 18 you create a new command object which destroys previous command instance: myCommand = New MySqlCommand(comm)
and this new command object doesn't have an open connection.

Solution: swap lines 17 and 18 i.e. first create a command object and then set it's properties.



Since you already created the MySqlCommand object at line 2 you don't have to create it again. You can just replace line 18 with

myCommand.CommandText = comm

You made the same mistake with conn. You created a new instance when you declared it and again when you initialized it. You should be aware of the difference between the two statements

Dim conn As MySqlConnection
Dim conn As New MySqlConnection

The first statement creates a variable that can be used to refer to a MySqlConnection object but does not create the object. The second one declares the reference and creates the object. The second statement is equivalent to

Dim conn As MySqlConnection
conn = New MySqlConnection

Reverend Jim,

Thanks a lot, I will change my errors. It is hard for me to trace my error because its my first time to use with mysql, also i have no internet connection at home where i can compare my codes. But still, i'm trying

Question Answered as of 2 Years Ago by Teme64 and Reverend Jim
This question has already been solved: Start a new discussion instead
Start New Discussion
Tags Related to this Article