Previous
Page Next
Page
A Demonstration Application
Let's now create a demonstration
application for accessing data. First create a windows form application
and make the layout shown the following snapshot.
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 we 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 properties 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=phuser; pwd=nicecoding;"
' 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 SubWe created the connection, the 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 uncomment
the Access code.
Next, we have assigned the Data Table resulting from the query to the
dataTable object which we declared at class level. We assigned zero to the
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 SubHere 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 boxwhich
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:
Previous
Page Next
Page
|
