VB.NET Tutorials

Welcome to Dream.In.Code
	Become an Expert! Join 307,124 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 2,007 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

Use VB.NET to connect to MySQL #2 - Displaying Data

Use VB.NET to connect to MySQL #2 - Displaying Data, A Continuation from Tutorial 1.

Options

PDUNZ

20 Aug, 2009 - 06:23 AM

Post #1

Welcome to Tutorial #2 on Using VB.NET to connect to MySQL

In my first tutorial I showed you how to make a connection to MySQL, in this tutorial
I will show you how to get records from MySQL and show it to a user.

Many of you are probably wondering what software I am using for the MySQL server,
I am using an all-in-one package called XAMPP, which is free to download - google the name.
This consists of Apache 2.2.11, PHP and MySQL server 5.0.51a.

Lets get to it
First thing you need to do is set up some data into a database. I am using phpMyAdmin to
manage the tables and data setup.

Im using 3 fields - ID, Firstname, Lastname. The ID is AutoIncrement.
I have also given the table the name "Contacts"

Creating the table

Defining the 3 fields

Table created

Some data entered

Now that our table has been setup, lets crack open Visual Studio and start a new project.

Add a reference to MySQL.Data.dll, and Imports MySql.Data.MySqlClient

Just a note: I am going to do something a bit different here. Instead of allowing the user to enter
the server, username and password for the database as shown in my first Tutorial - I will be hard-coding
it into the connection string.

Ok, now we will build up the connection (as shown in Tutorial 1).
Add Dim MysqlConn as MySQLConnection to the Public Class.

Under that, we add the following.

CODE

        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim myData As New DataTable
        Dim SQL As String

Now we will go back to the form and add 1 button to connect to the database, grab the data, and
then close the connection. We will also add a DataGridView to show the data.

Add MysqlConn = New MySqlConnection() to the Buttons On-Click Event, and lets also
define the connection string.

CODE

'Connection String
        MysqlConn.ConnectionString = "server=localhost;" _
        & "user id=root;" _
        & "password=;" _
        & "database=tutorial2"

Now, lets add a Try, Catch, and Finaly as well as the SQL for getting the data.
We will also define where to put the data - which is in this case the DataGridView in here.

We will start with this,

CODE

        ' Try, Catch, Finally
        Try
            MysqlConn.Open()

        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
            MysqlConn.Close()
            MysqlConn.Dispose()
        End Try

We will now add in the SQL statement, and bind the data to the Datagrid in the TRY statement, after the
connection has been opened. If the connection cannot be opened, then it will proceed to
Catch myerror As MySqlException which will show us an error, then Close and Dispose of the connection
in the FINALLY statement.

SQL statment I will be using is as follows,
SQL = "SELECT * FROM Contacts". Nice and simple.

And to connect the data to the Datagrid, I am going to use

CODE

    ContactsCommand.Connection = MysqlConn
    ContactsCommand.CommandText = SQL

    ContactsAdapter.SelectCommand = ContactsCommand
    ContactsAdapter.Fill(ContactsData)

    DataGridView1.DataSource = ContactsData

Let me explain the above,
ContactsCommand.Connection = MysqlConn and ContactsCommand.CommandText = SQL.
We assign the query text and tell the command object which connection object to use.

ContactsAdapter.SelectCommand = ContactsCommand.
Tells the MySqlDataAdaptor object which command object we want to use when querying the database.

ContactsAdapter.Fill(ContactsData)
We fill out ContactsData (our DataTable) from our ContactsAdaptor (MySqlDataAdapter).

Ok, now that I have gone through the required code, and if you managed to follow it this is what the code
should look like.

CODE

'Connecting VB.NET to MySQL.
'Tutorial #2 by PDUNZ
'For Dream.In.Code.Net

Imports MySql.Data.MySqlClient
Public Class Form1
    Dim MysqlConn As MySqlConnection
    Dim ContactsCommand As New MySqlCommand
    Dim ContactsAdapter As New MySqlDataAdapter
    Dim ContactsData As New DataTable
    Dim SQL As String

    Private Sub btnGrabData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGrabData.Click
        MysqlConn = New MySqlConnection()

        ' Define the SQL to grab data from table.
        SQL = "SELECT * FROM Contacts"

        'Connection String
        MysqlConn.ConnectionString = "server=localhost;" _
        & "user id=root;" _
        & "password=;" _
        & "database=tutorial2"

        ' Try, Catch, Finally
        Try
            MysqlConn.Open()

            ContactsCommand.Connection = MysqlConn
            ContactsCommand.CommandText = SQL

            ContactsAdapter.SelectCommand = ContactsCommand
            ContactsAdapter.Fill(ContactsData)

            DataGridView1.DataSource = ContactsData

        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
            MysqlConn.Close()
            MysqlConn.Dispose()
        End Try

    End Sub

End Class

And now the time has come, lets save the project and now run it.

Hmmm ok, nothing there. Oh, we forgot to press the button. Lets do that.

Success!!!

Thanks for reading my 2 Tutorials on using VB.NET with MySQL.

PS. A few people have asked why they cant log into a database on their website. The answer is that you have to make sure your hosting company has Remote Access enabled (which most dont) to allow outside connections. If your hosting company does not or will not enable it, I suggest trying a MySQL host (which has Remote Access Enabled) such as FreeMySQL.net.

Register to Make This Ad Go Away!

Chronicade

5 Sep, 2009 - 06:11 PM

Post #2

Thanks Mate. Just trying to learn how to make it leave out a few categories.

neatgadgets

8 Oct, 2009 - 09:15 PM

Post #3

Thankyou for putting together such a nice tutorial. I have given it a try, however upon pressing the button I get the following error:

"Guid should contain 32 digits with 4 dashes"

at line

ContactsAdapter.Fill(ContactsData)

I know the connection works as I have tried other code and the connection works fine. Any ideas?

Andrew