How to fetch mysql data in vb.net using data grid view

In this tutorial, I’ll be showing you how you can list data from mysql database in a datagridview control in vb.net.

 

Requirements

  • Visual Studio 2008 or 2010
  • Wampserver

First thing that you’ll need to do is to create a new project in visual studio.

image

Then select the dataGridView control and drag it to your form

image

Then create a label and a textbox. I won’t use any buttons here because I’ll make use of the text_changed event. This will trigger an event that would cause our application to fetch data from the database whenever the text which is in the textbox changes.

image

What we have to do now is to create a class where we would store all the subroutines needed to connect to the database.

image

Name the class whatever you want as long as it ends with a .vb extension

image

We will be using odbc driver to connect this application to the mysql database. So if you don’t know how to do that, then check this out first.

I won’t include the usual routine of declaring connections and commands in this guide so you read this tutorial if you want to know how the usual routine is done.

Before you continue reading, make sure that you have already declared the connection string so that you can connect to the mysql database.

Now, you need to declare a subroutine which we will use to fetch data from the mysql database. Place these codes on the subroutine:

Try

	cmd.CommandText = "SELECT * FROM testdb WHERE name='" & name & "'"
	Me.dataAdapter = New OdbcDataAdapter(cmd.CommandText, con)

	Dim commandBuilder As New OdbcCommandBuilder(Me.dataAdapter)
	Dim table As New DataTable()

	table.Locale = System.Globalization.CultureInfo.InvariantCulture

	Me.dataAdapter.Fill(table)

	Form1.BindingSource1.DataSource = table

	Dim data As New DataSet()

	data.Locale = System.Globalization.CultureInfo.InvariantCulture

	Form1.DataGridView1.DataSource = Form1.BindingSource1

Catch

	MsgBox("Something went wrong..")
	
End Try

As you can see, we enclosed the codes in a try-catch so that it would not cause a yellow-ribbon if ever there’s an unexpected error.

There would be some errors and warnings that visual studio will throw at you when you paste the code above into the subroutine. Because there’s still some housekeeping that you need to do.

First, you need to  make sure that the variable name for the command is the same with the cmd in here. If the variable name for the command is different. Then make sure that they’re the same. The following codes will just point out what you need to change, you don’t need to copy them.

Dim cmd As OdbcCommand

cmd.CommandText = "SELECT * FROM testdb WHERE name='" & name & "'"

Then declare a data Adapter just after the declaration of the class. You don’t need to copy the class declaration, I only used it to point out that the declaration of the dataAdapter should come after the class.

public class parentClass
Private dataAdapter As New OdbcDataAdapter()

Then go to your form designer. The form where you have the datagridview and drag a binding source control.

image

Back to the parent class again. Make sure that every Form1 declaration that you see would be the change to the name of the form where the dataGridView is. Here are some that I have found, which needs to be change. If the name of the form where you have the dataGridView is form1 then there’s no need to change these lines of code.

Form1.BindingSource1.DataSource = table
Form1.DataGridView1.DataSource = Form1.BindingSource1

Now, here’s an optional code,which you might want to add. This will auto-resize the columns in the dataGridView so that it would fit the actual size of the dataGridView.

Form1.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.White
Form1.DataGridView1.AutoResizeColumns( _

DataGridViewAutoSizeColumnsMode.AllCells)

I think that’s all that you need to change.

This is what it will look like when you first run it

image

But when you type something on the textbox. It fires an event which causes the subroutine from the parent class to be executed.

image

 

Conclusion

That’s how you fetch data from the mysql database and throw it to the dataGridView form control in vb.net. Hope you learned something. If there’s something that you don’t understand then tell it on the comments. Thanks for reading.

12 thoughts on “How to fetch mysql data in vb.net using data grid view

  1. Pingback: vb.net database connection basics part 1 « Data Integrated Entity

  2. what codes are to be inserted at the .class ?

    Quote:
    ” public class parentClass

    Private dataAdapter As New OdbcDataAdapter() ”

    where is this?

    • That code is still in the form where you have your datagridview. As I have said in the post, I only used the parentClass declaration to demonstrate that you have to declare the instance of odbcDataAdapter after the class.

  3. Is given me below error at:
    Me.dataAdapter.Fill(table)
    is complaining of this below error:
    The ConnectionString property has not been initialized.

  4. Mr. Wern, i like to ask something different in here. But is still in the area DataGridView.
    i want to ask, how to manually populate data from database into datagridview ? But not using datasource in datagridview but using loop for each column in datagridview ?
    Please help me…thx

    • There’s a lot of ways in which you can do this. You can either create a new list object and then loop through all the records in the database(eg. MySQL) and then put the fetched records in that list. You can then specify the list to be the source of your dataGridView. Just do a quick Google search on this and you’ll surely find answers. Good luck!

  5. im using visual studio 2010 and wampserver as my database … what will be my codes if i want to link the data to be inserted in the datagridView to the tables in my database in the wampserver ??? help please !!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s