MySql - An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Question

I am using MYSQL database for that...I get this following Error...Please anyone can help me?

Imports System.Data
Imports MySql.Data Imports MySql.Data.MySqlClient
Public Class formLogin
  Dim connStr As String = "server=localhost;user=root;database=ssknet;port=3306;password=;"
  Dim connection As New MySqlConnection(connStr)

Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
    connection.Open()
    Dim cmd As New MySqlCommand("SELECT * FROM user WHERE username=@username and password=@password", connection)


    cmd.Parameters.Add("@username", MySqlDbType.VarChar).Value = txtUsername.Text
    cmd.Parameters.Add("@password", MySqlDbType.VarChar).Value = txtPassword.Text

    Dim adapter As New MySqlDataAdapter
    Dim table As New DataTable

    adapter.Fill(table)

    If table.Rows.Count <= 0 Then
        MessageBox.Show("Invalid Username or Password")
    Else
        MessageBox.Show("Login Success!")
    End If

    'cmd.ExecuteNonQuery()
    connection.Close()
End Sub

End Class

I want to access database right now. Quick frnds

Steve · Accepted Answer · 2018-01-13T11:24:57.277

You are missing a critical step. You are not assigning any command to your adapter. It doesn't know how to query anything without a command

Dim adapter As New MySqlDataAdapter(cmd)

Next, according to the MySql Reserved KeyWords, user and password are reserved and to use them in a query as field names you should put backticks (ALT+096) around them. So the query should be written as

Dim cmd As New MySqlCommand("SELECT * FROM `user` 
                            WHERE username=@username 
                            AND `password`=@password", connection)

A part from this your code is good enough albeit there are a couple of thing to consider.

First, connection object should not be kept as global objects. This leads to many problems with the resources kept on the server and with closing/opening the connection when there is an error. Just create and discard the connection inside a using statement

Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
Using connection = New MySqlConnection(connStr)
   connection.Open()

   .... all of your code except the close connection

End Using ' this close the connection also in case of exceptions

Second, you don't need an SqlDataAdapter and a datatable if you just want to check if the user/password exists

Dim reader = cmd.ExecuteReader()
if reader.HasRows then
    MessageBox.Show("Login Success!")
Else
    MessageBox.Show("Invalid Username or Password")
End If

Third, it is a great security risk to keep password in plain text inside your database and then using queries to retrieve it. You should use Salt and Hashing methods to store and retrieve password

It is already on. Look at the parameter passed to the MySqlDataAdapter constructor — Steve, Jan 13 '18 at 11:02
When you receive the exception, can you look at the value of InnerException? Probably there is another clearer message there — Steve, Jan 13 '18 at 11:10
Also according to the [MySql 9.3 Keywords and Reserved Words](https://dev.mysql.com/doc/refman/5.7/en/keywords.html) page, user and password are reserved keywords. In MySql you need to put them between backticks. Updating the answer — Steve, Jan 13 '18 at 11:16
Thanks a lot frnd...It works very fine..Thank you...You taught me more...great job u did — ssk developer, Jan 13 '18 at 11:17

MySql - An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

1 Answers1