1,105,534 Community Members

Error in login codes using mySQL database

Member Avatar
monching
Junior Poster in Training
68 posts since Oct 2010
Reputation Points: 3 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Code:
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

    Try
        conn.Open()

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

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

            conn.Close()

            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
                mainForm.Show()
            End If
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
            conn.Dispose()
            PasswordTextBox.Clear()
        End Try

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

Error:
System.InvalidOperationException was unhandled
Message="Connection must be valid and open."
Source="MySql.Data"

Member Avatar
Teme64
Veteran Poster
1,039 posts since Aug 2008
Reputation Points: 189 [?]
Q&As Helped to Solve: 208 [?]
Skill Endorsements: 9 [?]
 
0
 

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.

HTH

Member Avatar
Reverend Jim
Noli mentula
5,444 posts since Aug 2010
Reputation Points: 746 [?]
Q&As Helped to Solve: 653 [?]
Skill Endorsements: 51 [?]
Moderator
Featured
 
0
 

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
Member Avatar
monching
Junior Poster in Training
68 posts since Oct 2010
Reputation Points: 3 [?]
Q&As Helped to Solve: 1 [?]
Skill Endorsements: 0 [?]
 
0
 

Teme64,
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 vb.net 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 1 Year Ago by Teme64 and Reverend Jim
You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article