Tom's Subject Directory & City Building Fan Site

Visual Basic 2008 & Mysql Version 5

An Article and Tutorial:  Connecting to MySql

Part : III Coding the Login Form

Connection, Command and DataReader are the elements of the MySql Connector/NET covered in this article. The Connection creates a connection to a specific data source. The Command object executes an SQL statement against a data source. The DataReader reads streams of data from a data source.

With the connect code tested, we can write code for the login part of the form. I elected to place this code in a block named "Public Sub authenticate()". I chose authenticate but you can use any name you like. The first thing we do is set up the query string to find the index of the row with the user name and password present. In this case we are looking for an id of 4. I am using the table shown below;

The first thing we do is set up the query string to find the user id (index) of the row with the user name and password present. This string is almost identical to a query string made for php or MySql Admin.

• Dim query As String = "SELECT id FROM user WHERE name = " & "'" & TxtUsername.Text & "'" & " AND password = " & "'" & txtPassword.Text & "'"

Followed by a new MySQLConnection object (as declared in Part II). The ConnectionString property can be set only when the connection is closed. A new instance of the SqlConnection class along with the connection string is needed to establish the initial connection.;

• Dim Connection As New MySqlConnection(connStr)

Next we declare the variable cmd as a new MySqlCommand (This initializes a new instance of the MySqlCommand class.) along with the variables query and Connection from above.

Finally we declare reader MySqlDataReader class. To create a MySQLDataReader, you must call the MySqlCommand.ExecuteReader method of the MySqlCommand object, rather than directly using a constructor which we do directly after opening the connection.

reader.read() opens a forward-only stream of data rows from a data source (in this case MySQL Server Database).

     Public Sub authenticate()
        '   MySql SELECT Syntax for retrieving user id with correct login
        Dim query As String = "SELECT id FROM user WHERE name = " & "'" & TxtUsername.Text & "'" & " AND password = " & "'" & txtPassword.Text & "'"
        Dim Connection As New MySqlConnection(connStr)
        Dim cmd As New MySqlCommand(query, Connection)
        Dim reader As MySqlDataReader
        Try
            Connection.Open()
            reader = cmd.ExecuteReader()
            reader.Read()
            MsgBox("User ID: " & reader.GetString(0))
            reader.Close()
            Connection.Close()
            svrStatus.Text = connClosed
        Catch ex As MySqlException
            MsgBox(ex.Number & " - " & ex.Message)
	Finally
            Connection.Close()  
        End Try
    End Sub

Our MsgBox with reader.GetString(0) gets our users id number provided the name and password are together in our database. Screen shots below are my debug run and the database I queried.

These screen shots are from phpMyAdmin.MouseOver will show the sql query made and results.               

    Part II   
Complete Source Code