Other Views

ADONET FAQ - Creating "Hello, ADO.Net" App in VB.Net

How do I make my first “Hello, ADO.Net” Application in VB.Net?

Let's now create a demonstration application for accessing data. First create a windows form application and make the layout shown the following snapshot.

http://www.programmersheaven.com/articles/images/faq/image007.gif We have set the Name property of the text boxes (from top to bottom) as txtArticleID, txtArticleTitle, txtArticleTopic, txtAuthorId, txtAuthorName, txtNumOfLines and txtDateOfPublishing. Also we have set the ReadOnly property of all the text boxes to true as don't want the user to change the text. The names of the buttons (from top to bottom) are btnLoadTable, btnNext and btnPrevious. Initially we have disabled the Next and Previous buttons (by setting their Enabled property to false). We have also defined three variables in the Form class:

 
Public Class ADOForm    
Inherits System.Windows.Forms.Form    ' Private global members to be used in various methods 
Private dataTable As dataTable
Private currRec As Integer = 0 
Private totalRec As Integer = 0	    ...

The dataTable object will be used to reference the table returned as a result of the query. The currRec and totalRec integer variables are used to keep track of the current record and total number of records in the table. Loading table For the LoadTable button, we have written the following event handler

 
 
Private Sub btnLoadTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoadTable.Click
        ' for Sql Server
        Dim connectionString As String = "server=P-III; database=programmersheaven;" + _
                                         "uid=sa; pwd=;"
        ' for MS Access
        'Dim connectionString As String = "provider=Microsoft.Jet.OLEDB.4.0;" +
        '                            "data source = c:\\programmersheaven.mdb"
        ' for Sql Server
        Dim conn As New SqlConnection(connectionString)
        ' for MS Access
        'Dim conn As New OleDbConnection(connectionString)
        Dim commandString As String = "SELECT " + _
                               "artId, title, topic, " + _
                               "article.authorId as authorId, " + _
                               "name, lines, dateOfPublishing " + _
                               "FROM " + _
                               "article, author " + _
                               "WHERE " + _
                               "author.authorId = article.authorId"
        ' for Sql Server
        Dim dataAdapter As New SqlDataAdapter(commandString, conn)
        ' for MS Access
        'Dim dataAdapter As New OleDbDataAdapter(commandString, conn)
        Dim ds As New DataSet()
        dataAdapter.Fill(ds, "prog")
        dataTable = ds.Tables("prog")
        currRec = 0
        totalRec = dataTable.Rows.Count
        FillControls()
        btnNext.Enabled = True
        btnPrevious.Enabled = True
    End Sub

First we created the connection, data adapter and filled the dataset object, all of which we have discussed earlier. It should be noted that we have commented out the code for the OleDb provider (MS-Access) and are using SQL Server specific code. If you would like to use an Access databases, you can simply comment the SQL server code out and de-comment the Access code. Next, we have assigned the data table resulting from the query to the dataTable object which we declared at the class level, assigned zero to currRec variable and assigned the number of rows in the dataTable to the totalRec variable:

 
dataTable=ds.Tables("prog")currRec=0totalRec=dataTable.Rows.Count

Then we called the FillControls() method, which fills the controls (text boxes) on the form with the current record of the table "prog". Finally we enabled the Next and Previous Buttons.

Filling the controls on the Form

The FillControls() method in our program fills the controls on the form with the current record of the data table. The method is defined as follows:

Private Sub FillControls()
txtArticleId.Text=dataTable.Rows(currRec)("artId").ToString()
txtArticleTitle.Text=dataTable.Rows(currRec)("title").ToString()
txtArticleTopic.Text=dataTable.Rows(currRec)("topic").ToString()
txtAuthorId.Text=dataTable.Rows(currRec)("authorId").ToString()
txtAuthorName.Text=dataTable.Rows(currRec)("name").ToString()
txtNumOfLines.Text=dataTable.Rows(currRec)("lines").ToString()
txtDateOfPublishing.Text=dataTable.Rows(currRec)("dateOfPublishing").ToString()
End Sub

Here we have set the Text property of the text boxes to the string values of the corresponding fields of the current record. We have used the Rows collection of the dataTable and using its indexer we have got the DataRow representing the current record. We have then accessed the indexer property of this DataRow using the column name to get the data in the respective field. If this explanation looks weird to you, you can simplify the above statements to:-

Dim row As DataRow = dataTable.Rows(currRec) ' getting current row
Dim data As Object = row("artId")            ' getting data in the artId field
Dim strData As String = data.ToString()      ' converting to string
txtArticleId.Text = strData                  ' display in the text box

which is equivalent to

 
txtArticleId.Text = dataTable.Rows(currRec)("artId").ToString()

Hence when you start the application and press the LoadTable button, you will see the following output:

http://www.programmersheaven.com/articles/images/faq/image008.gif

Back