In this tutorial, I’ll be showing you how you can list data from mysql database in a datagridview control in vb.net.
- Visual Studio 2008 or 2010
First thing that you’ll need to do is to create a new project in visual studio.
Then select the dataGridView control and drag it to your form
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.
What we have to do now is to create a class where we would store all the subroutines needed to connect to the database.
Name the class whatever you want as long as it ends with a .vb extension
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.
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
But when you type something on the textbox. It fires an event which causes the subroutine from the parent class to be executed.
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.