More on Databases June 30, 2008
Posted by tuse in : Databases, Tips and Tricks, VB.NET , add a commentIn the previous post, we were introduced to the usage of MySQL Databases in VB.NET.
Today we focus on binding data from databases into controls.
What we will try to do is populate a ListBox Control with data from a table.
So in a new form, drag a ListBox Control from the ToolBox.
We will use a Data Reader (there are other methods too which will be explained soon) to bind the data to the ListBox. The Data Reader reads data from the table specified as directed by the Connection String and the SQL Command.
A simple while loop is set up to add the available data to a ListBox as long as we are through reading all the records. The population of the ListBox is done in the ‘Form_Load’ event.
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim cn As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=mydb; User=root;Password=;")
Dim cmd As New Odbc.OdbcCommand("Select * from mytable", cn)
cn.Open()
' Use a Data Reader Object to read Records from the table
Dim dr As Odbc.OdbcDataReader
dr = cmd.ExecuteReader
While dr.Read 'While there are records
Me.ListBox1.Items.Add(dr.Item(1))
' Add the 2nd Field of the table into the ListBox. To add the n th field, give dr.Item(n-1)
End While
cn.Close()
End Sub
End Class
Connecting to Databases in VB.NET June 29, 2008
Posted by prasu in : Databases, VB.NET , 6 commentsIn most of the practical applications made using VB.NET we need to access a database to retrieve or manipulate the data. To access the data stored in a database from VB.NET we need the following objects-
- Connection: The connection object tells VB.NET how to get the data (i.e the database and the driver to connect to it) and also the database user credentials. This initialization is made in the Connection String
- Container: We need a object to locally hold the data we retrieve from the database. This object is known as the DataSet
- Bridge:We need a bridge between the Connection and the DataSet. This object is known as the DataAdapter.
- Command: The SQL Command which determines the kind of data we are trying to retrieve from the database is given in the Command Object.
A Connection string has to be specified so that the program can connect to the required database.
The data is stored in the DataSet via DataAdapters. This is done through a method called Fill, which takes two arguments The DataSet object and the Table name
To bind the data present in the DataSet to a DataGridView (a control where the data will be echoed on the form), the DataSet and the tables are to be specified.
Now to code a simple program to access the data from a table to DataGridView
- Add a windows form.
- Drag a DataGridView control from the toolbox to the form.
- Double click on the form. you will go to the Form_Load method. Now type the code as given.
Public Class Form2
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'cn is the Connection Object with the Connection String to establish a database connection using the connection string in the constructor
Dim cn As New Odbc.OdbcConnection("Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=mydb; User=me;Password=xxx;")
cn.Open() 'Open the connection in the form load event
Dim cmd As New Odbc.OdbcCommand 'Command Object
Dim adp As Odbc.OdbcDataAdapter 'Data Adapter
Dim ds As New DataSet 'Data Set
cmd.Connection = cn 'Associate the 'cn' connection with the Command Object
cmd.CommandText = "Select * from mytable" 'The SQL Command
adp = New Odbc.OdbcDataAdapter(cmd) 'Data Adapter- Associate it with the Command
adp.Fill(ds, "mytable") 'The fill method. This stores data from the DB into the dataset
Me.DataGridView1.DataSource = ds 'The source of data for the grid view control is the dataset
Me.DataGridView1.DataMember = "mytable" 'Specify data from which table is needed
cn.Close() 'Close the connection after data access is done
End Sub
End Class
As you can by now figure out from the Connection String (ps-read earlier posts), we have used a MySQL database connected to the VB.NET Application using the ODBC Connector.
This is how your output should look like-
For those of you looking to develop applications dealing with databases, VB.NET is the tool to create an elegant front-end.
