Life Without Wizards


In this section, we'll examine the code necessary to work with databases, but without using the Visual Basic .NET wizards. We'll also use some old-style ADO along with the new-style ADO.NET. Even though we could do everything with pure ADO.NET, I thought it would be interesting to show you how you can use ADO if needed (there is, after all, a lot of ADO legacy code out there).

Setting Up the Form

The program we're about to write is the SQL test program you saw in Chapter 24. Start a new project and name it SQLTester. You'll need to add the controls shown in Figure 25.22 to the form.

Figure 25.22. Controls for the SQLTester project.

graphics/25fig22.jpg

There are two list boxes near the top of the form named lstTables and lstFields . The buttons are named btnWhere , btnSubmit , btnClear , and btnExit . The multiline text box near the center is named txtQuery . The DataGrid control is named dtgGrid . You also need to add an OpenFileDialog control named OpenFileDialog1 (its default name) from the Windows Forms tab of the Toolbox.

Finally, drag copies of the OleDBConnection and OleDbDataAdapter from the Data tab of the Toolbox onto the form. The default names for each object are fine. When you drag the OleDbDataAdapter to the form, Visual Basic .NET automatically starts the Data Adapter Wizard. However, because we want to manage things ourselves now, click the Cancel button to dismiss the dialog box.

Because we'll be using ADO along with ADO.NET in this project, we need to inform Visual Basic .NET of that fact. To add the required references, use the Project, Add Reference menu sequence and click on the COM tab of the Add Reference dialog. Scroll down and click Microsoft ADO Ext. 2.7 for DDL And Security dynamic link library. Your screen should look similar to Figure 25.23. Now click the Select button to add the file to our project.

Figure 25.23. Controls for the SQLTester project.

graphics/25fig23.jpg

Before you dismiss the Add Reference dialog box, click on the .NET tab. Highlight the adodb component in the list (it should be near the top of the list) and click the Select button. Now you can click OK to dismiss the Add Reference dialog box. Visual Basic .NET now knows that we can use the ADO routines in our project.

Adding the Code

Listing 25.2 shows part of the code for the test program. The program begins by importing the necessary namespaces that are required to use ADO.NET. (I'll explain the purpose of the MAX_RECORDS constant later in the chapter.)

The first function in the project is GetFileName() . This function simply asks the user to select the Access database. Because we're working with the sample database that's distributed with Crystal Reports, I've hard coded that directory as the default startup directory (see the InitialDirectory property). Obviously, you should change this directory to reflect your own installation path for Visual Studio .NET. The remainder of the function's code should look familiar to you by now.

Listing 25.2 The GenFileName() and Form Load Event Code
 Imports System Imports System.Data Imports System.Data.OleDb Public Class frmMain  Inherits System.Windows.Forms.Form  Private Const MAX_RECORDS As Integer = 10000  Private strConnect As String ' Windows Form Designer generated code  Private Function GetFileName() As String   ' Purpose: This function attempts to open an Access database   '   ' Argument list:   '  none   '   ' Return value   '  string   the name of the file selected or an empty   '        string if cancelled.   '   Dim NewFileName As String   Dim NewFile As OpenFileDialog = New OpenFileDialog()   Dim MyChoice As DialogResult   With NewFile    .Filter = "Access Files (*.mdb)*.mdbAll Files (*.*)*.*"    .FilterIndex = 1           ' Assume text files    .DefaultExt = "mdb"          ' Ditto    .InitialDirectory = "D:\VSNET\Crystal Reports\Samples\Database\"    .CheckFileExists = False    .ReadOnlyChecked = True    .Title = "Open Access Data File"   End With   MyChoice = NewFile.ShowDialog      ' What did they do?   If MyChoice = DialogResult.Cancel Then ' Bail out?    NewFileName = ""   Else    NewFileName = NewFile.FileName   End If   If NewFileName.Length > 0 Then    Return NewFileName   End If  End Function  Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As_               System.EventArgs) Handles MyBase.Load   ' Purpose: This opens and reads in the table names from the database.   Dim FileName As String   Dim AdoConn As New ADODB.Connection()   Dim MyCat As New ADOX.Catalog()   Dim tbl As ADOX.Table   FileName = GetFileName()      ' Get the database file name   If FileName.Length = 0 Then    Exit Sub   End If   Me.Text = "SQL Tester: " & FileName ' Show the file name   ' This builds the connect string. It is used later as needed, too.   strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User _          ID=Admin;Data Source="   strConnect &= FileName   AdoConn.Open(strConnect)   MyCat.ActiveConnection = AdoConn   For Each tbl In MyCat.Tables    ' Show the field names    lstTables.Items.Add(tbl.Name.ToString)   Next   AdoConn.Close()  End Sub 

The Form Load event code sets things up by a call to GetFileName() . After the database filename is entered by the user, the code copies the filename and pathname to the form's title bar.

The Connection String

Now the code starts to get interesting. The first thing we need to do is build a connection string that provides the necessary details to Visual Basic .NET to form a connection to the selected database. Because we're connecting to an Access database, we can hard-code the first part of the connection string as

 strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User _         ID=Admin;Data Source=" strConnect &= FileName 

This tells Visual Basic .NET that we'll be using the Provider for Access databases, we aren't using a password, the User ID is Admin , and the Data Source is the filename just selected by the user. This connection string comprises the bare-bones essentials needed to form a connection to the database. It's probably obvious that this connection string will vary according to the type of database being used.

Programmer's Tip

graphics/tip_icon.gif

If you want to see a more complete list of the parameters that may be used with the connection string, reload the DBWizard project you created at the beginning of this chapter. Expand the Windows-generated code section of the project and look for the OleDbConnection1.ConnectionString statement. What you'll find is about eight lines of parameter information that may be used in the connection string. We're using only the essential parameters necessary to form a connection to an Access database (each parameter is delineated by a semicolon).

If you want to connect to a different type of database (for example, SQL Server or Oracle), follow the DBWizard directions, but respond to the wizard questions for the database you want to use. When you're done, examine the connection string in the Windows-generated code section to determine which parameters you need to include in your connection string. In most cases, the first four parameters are sufficient.


Using ADO

Some readers might question my use of the older ADO model in the program. After all, ADO is based upon Microsoft Component Object Model (COM) which, for the most part, is limited to Windows platforms; ADO.NET is not. Still, there are a number of reasons why I chose to include a little ADO code in the program. First, there are things that ADO can do for us that the present incarnation of ADO.NET cannot. For example, ADO.NET does not yet support a data definition language (DDL) for creating your own databases (this is forthcoming, however). Second, sticking your head in the sand doesn't make ADO go away. There is a ton of Visual Basic code out there that uses ADO, and you might learn something useful by studying it. Third, because we're simply using the ADO model to retrieve the table and field information, we aren't taking a significant performance hit. Finally, I wanted you to at least see how easy it is to use an ADO connection if you ever need to. 'nuff said.

After the connect string is formed , we use it to make an ADO connection to the database. The Catalog object enables us to retrieve a list of the tables that are associated with the database. We march through the Tables collection of the catalog, adding the table names to the lstTables list box.

We use the ADO connection in a similar fashion in the SelectedIndexChanged event for the lstTables list box. This code is presented in Listing 25.3. The same connection string, strConnect , is used again to reopen the ADO connection to read the field names for the table that the user selected.

Listing 25.3 The SelectedIndexChanged Event for the lstTables List Box
 Private Sub lstTables_SelectedIndexChanged(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles lstTables.SelectedIndexChanged  ' Purpose: Construct a list of database fields for the table that was  '      selected.  Dim AdoConn As New ADODB.Connection()  Dim i As Integer, buff As String  Dim MyCat As New ADOX.Catalog()  Dim tbl As ADOX.Table, col As ADOX.Column  txtQuery.Clear()     ' If this changes, clear the query  AdoConn.Open(strConnect)     ' Open a connection  MyCat.ActiveConnection = AdoConn  lstFields.Items.Clear()      ' Clear old field names  For Each tbl In MyCat.Tables   ' Go through the tables...   ' for the selected table...   If tbl.Name.ToString = lstTables.SelectedItem Then    lstFields.Items.Add("*")    For Each col In tbl.Columns  ' ...show the fields     lstFields.Items.Add(col.Name)    Next    Exit For   End If  Next  AdoConn.Close() End Sub 

The only real difference in the ADO code in Listing 25.3 is that we're also iterating through the Columns collection of the database and adding the columns to the lstFields list box. We add the wildcard field section (that is, the asterisk) at the top of the list box before adding the field names. As you'll recall from our SQL discussion in Chapter 24, the asterisk means that we want to retrieve all column values from the table for the SELECT statement.

As you can see from Listing 25.3, firing the SelectedIndexChanged event occurs when the user clicks on one of the database tables listed in the lstTables list box. The tables selection then causes the fields for that table to be displayed in the lstFields list box.

We're now ready to examine the rest of the program code. The remaining code is presented in Listing 25.4.

Selecting Field Names for the Query

The first subroutine in Listing 25.4 ( lstFields_SelectedIndexChanged ) processes the field choices made by the user. This event is fired any time the user clicks a choice in the lstFields list box. The code first checks to see whether the user elected to retrieve all fields by selecting the asterisk. If so, the query text "SELECT * " is moved into the txtQuery text box and we exit the subroutine.

A little more processing is necessary if the user clicks on a specific field name in the lstFields list box. First, we check the length of the txtQuery text box. If the length is 0, we're starting a new query and we must add the SELECT statement to the text box. If the txtQuery length is not 0, it means we're adding another field name to the query. Therefore, we do not need to copy the SELECT statement.

The code now must check to see whether the field name selected by the user has one or more words. This check is performed by the BracketMultiWords() function. We call BracketMultiWords() , passing in the field name that was just selected. If the field name contains two or more words (for example, "Contact First Name" as shown in Figure 25.21), we need to place brackets around the field name. The resulting name is then returned by the function with brackets added, if necessary.

Once that's done, we append the (adjusted) field name to the existing state of the query as held in txtQuery and then add a comma at the end of the string. The comma is appended in preparation for adding more field names (more about the comma later on).

Adding a WHERE Predicate

After the field names are added to the query, the user has two choices. First, he can click on the Submit button to submit the query for processing. Second, he can click on the Where button to add a WHERE predicate to the query. The code to add a WHERE predicate is quite simple.

First, we copy the content of the txtQuery text box into a temporary variable named temp . We do this so we can use the LastIndexOf() string method to see if a FROM keyword is already present in the query string. If there is no FROM present in the string, we use the Remove() string method to remove the trailing comma that was appended while the field names were being added to the query. The code then adds the FROM keyword followed by the table name being used in the query. (Once again, we call BracketMultiWords() in case the selected table name has two or more words in it.) The resulting string is then copied back into the txtQuery text box.

Next, we append the WHERE keyword and set the focus to the text box. Finally, we add a trailing blank space to txtQuery . Hmmm. Why not just add the space when the WHERE keyword is appended? If you use that approach, the Focus() method highlights the entire string in the text box. If the user would inadvertently type a keystroke at this point, the entire content of the text box that we have built would be immediately erased. Not good. By setting the focus and then adding the blank space, the content of the text box is not highlighted, but the cursor is politely waiting for the user to type in the WHERE predicate.

Listing 25.4 The Remaining Program Code
 Private Sub lstFields_SelectedIndexChanged(ByVal sender As Object, ByVal e _         As System.EventArgs) Handles lstFields.SelectedIndexChanged   ' Purpose: This selects the field names for the query.   If lstFields.SelectedItem = "*" Then   ' Did they chose a wildcard?    txtQuery.Text = "SELECT * "    Exit Sub                ' We're done...go home   End If   If txtQuery.TextLength = 0 Then    txtQuery.Text = "SELECT "   End If   txtQuery.Text &= BracketMultiWords(lstFields.SelectedItem) & ","  End Sub  Private Sub btnWhere_Click(ByVal sender As System.Object, ByVal e As _                System.EventArgs) Handles btnWhere.Click   ' Purpose: Add a WHERE predicate to the query. In preparation, we need   '      to remove the last comma from the field list.   Dim FromFound As Integer   Dim temp As String   temp = txtQuery.Text   FromFound = temp.LastIndexOf(" FROM ")   ' See if there is a FROM yet   If FromFound = -1 Then           ' If not...    temp = temp.Remove(temp.Length - 1, 1)  ' ...remove trailing comma    ' and add the FROM and table name    temp &= " FROM " & BracketMultiWords(lstTables.SelectedItem)    txtQuery.Text = temp   End If   txtQuery.Text &= " WHERE"   ' Now add the WHERE   txtQuery.Focus()       ' Set the focus so they can add the rest   txtQuery.AppendText(" ")   ' The order prevents highlighting the query  End Sub  Private Sub FixupTheQuery()   ' Purpose: This checks for an incomplete SQL query and supplies the   '      missing parts as needed   Dim FromFound As Integer   Dim buff, temp As String   Try    temp = txtQuery.Text            ' The current query state    FromFound = temp.LastIndexOf(" FROM ")   ' Does it have a FROM yet?    If FromFound = -1 Then           ' Nope, none there...     temp = temp.Remove(temp.Length - 1, 1)  ' Remove trailing comma     buff = BracketMultiWords(lstTables.SelectedItem)     temp &= " FROM " & buff          ' Add in the missing FROM     txtQuery.Text = temp           ' Copy it back    End If   Catch    MessageBox.Show("Make sure you have selected the table name")   End Try  End Sub  Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As _                System.EventArgs) Handles btnSubmit.Click   ' Purpose: This sets up the connection and objects and then submits   '      the query.   Dim i, j, RecordsRead, WhichField As Integer   Dim ReConnect As New OleDbConnection(strConnect) 'Reuse connect string...   ReConnect.Open()           ' ...and reopen the connection   FixupTheQuery()            ' Complete the query as needed   Dim MyAdapter As New OleDbDataAdapter(txtQuery.Text, ReConnect)   Dim MyDataSet As New DataSet()   Try    ' How many records were returned?    RecordsRead = MyAdapter.Fill(MyDataSet, 0, MAX_RECORDS, _                   lstTables.SelectedItem)    dtgGrid.CaptionText = "Records: " & CStr(RecordsRead) ' Tell how many    ReConnect.Close()                   ' Close it down    ' Tie everything to the data grid    dtgGrid.SetDataBinding(MyDataSet, lstTables.SelectedItem)    MyAdapter = Nothing      ' Free things up    MyDataSet = Nothing    ReConnect = Nothing   Catch    MessageBox.Show("Check the SQL query syntax for errors.")   End Try  End Sub  Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As _               System.EventArgs) Handles btnExit.Click   Me.Dispose()  End Sub  Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As _                System.EventArgs) Handles btnClear.Click   ' Purpose: Just clear out old query, the selected table, and the field   '      names in case they want to select another table.   txtQuery.Text = ""   lstFields.Items.Clear()   lstTables.ClearSelected()  End Sub  Private Function BracketMultiWords(ByVal Word As String) As String   ' Purpose: This function checks to see if a table or field name   '      contains multiple words. If so, it needs to have brackets   '      around it.   '   ' Argument list:   '  Word     a string with the name to check   '   ' Return value:   '  string    the string with the brackets added, if needed   Dim SpaceIndicator As Integer   Dim buff As String   SpaceIndicator = InStr(Word, " ")   ' See if field name is multi-word   If SpaceIndicator Then    buff = "[" & Word & "]"       ' Bracket if it is   Else    buff = Word   End If   Return buff              ' Send back the value  End Function End Class 

Submitting the Query

After a WHERE clause has been added, the user is ready to submit the query for processing. When the user clicks on the Submit button, the first thing we do is reestablish our connection to the database using the strConnect string. The call using the Open() method reconnects us to the database. However, we need to do a little checking on our query before we attempt to process it.

The call to FixupTheQuery() duplicates some of the checking we did with respect to the WHERE predicate. The primary check is to see whether the FROM keyword is present and add it into the query if it's missing. We surround the code in a Try - Catch block just in case the user hasn't selected a table yet. (We don't need to do this in the list boxes because the only way those events can fire is if the table has already been selected. Think about it.)

Next, we create an OleDbDataAdapter object named MyAdapter , initializing it with the ReConnect connection object and the SQL query that's now held in txtQuery . We also define a DataSet object named MyDataSet that will hold the results of the query.

A Try - Catch block is used in case something goes amiss. In most cases, an error at this point is caused by an improperly formed SQL statement. The Fill() method call

 RecordsRead = MyAdapter.Fill(MyDataSet, 0, MAX_RECORDS, _                 lstTables.SelectedItem) 

is overloaded, and we're using the version that takes four parameters. The first parameter, MyDataSet , tells the Fill() method where to store the results of the query. The second argument specifies the starting record, and the third argument determines the maximum records we want to return. We arbitrarily set this to 10,000 records in the belief that any more than that was probably an error in the SQL statement.

Programmer's Tip

graphics/tip_icon.gif

In a real application, you should probably make the maximum number of returned records from a query a member variable of the class that accesses the database. You could then let the user of the class set the maximum number of records returned.


The last argument of the Fill() method is the name of the database table being used in the query.

The return value from the Fill() method call is the number of records returned by processing the SQL query. We copy this value to the caption text of the data grid so that the user can see how many records were returned by the query.

At this juncture, MyDataSet is filled with the results from the SQL query. Therefore, we have what we need, so we immediately close the connection to the database using the Close() method of the connection object.

Finally, we bind the results stored in MyDataSet to the DataGrid control using the SetDataBinding() method call and free up the resources associated with the objects. The results of the query are then displayed in the grid control. A sample run is shown in Figure 25.24.

Figure 25.24. A sample run of the SQLTester program.

graphics/25fig24.jpg

Even though my machine is fairly slow by today's standards (it has a 750MHz clock), response time for the 3,000+ records is about a second using a WHERE predicate. Also notice that the field name in a WHERE predicate is not case-sensitive.

Finally, if you try a SELECT statement in which you need to expand the width of the column, move the cursor to the vertical line that separates the column headers. The cursor will change to a double-arrow cursor. At this point, you could drag the bar to the right to expand the column width, as pointed out in Chapter 24. However, if you double-click when the double-arrow cursor is showing, the column automatically expands to a width that shows the widest entry in the column. Cool stuff.

Navigating a Database

You often need to work with a database in which you're very familiar with its contents. You know all the table names and the field names, and there's really no reason for the user to need knowledge of either. In situations like this, all you might need is a way to navigate through the database and, perhaps, edit or update the content of an individual record. In this section, we'll write a simple program that's designed to navigate through a known database.

Start by creating a new project named DBTextboxes. Place the controls shown in Figure 25.25 on the form.

Figure 25.25. The form layout for the DBTextboxes program.

graphics/25fig25.jpg

By now, you should be able to look at the code and know what the names of the various controls need to be. Therefore, we won't denude any more of the nation's forests by stating the obvious. The complete code for the program appears in Listing 25.5.

A good portion of the code was copied from the previous project, so we'll concentrate on the new elements of the program.

Listing 25.5 Source Code for the DBTextboxes Project
 Imports System Imports System.Data Imports System.Data.OleDb Public Class frmMain  Inherits System.Windows.Forms.Form  Private Const MAX_RECORDS As Integer = 10000  Private RecordsRead As Long  Private strConnect As String  Private MyTable As String  Private MyDataSet As New DataSet()  ' Windows Form Designer generated code  Private Function GetFileName() As String   ' Purpose: This function attempts to open an Access database   '   ' Argument list:   '  none   '   ' Return value   '  string   the name of the file selected or an empty   '        string if cancelled.   '   Dim NewFileName As String   Dim NewFile As OpenFileDialog = New OpenFileDialog()   Dim MyChoice As DialogResult   With NewFile    .Filter = "Access Files (*.mdb)*.mdbAll Files (*.*)*.*"    .FilterIndex = 1           ' Assume text files    .DefaultExt = "mdb"          ' Ditto    .InitialDirectory = "D:\VSNET\Crystal Reports\Samples\Database\"    .CheckFileExists = False    .ReadOnlyChecked = True    .Title = "Open Access Data File"   End With   MyChoice = NewFile.ShowDialog      ' What did they do?   If MyChoice = DialogResult.Cancel Then ' Bail out?    NewFileName = ""   Else    NewFileName = NewFile.FileName   End If   If NewFileName.Length > 0 Then    Return NewFileName   End If  End Function  Private Sub frmMain_Load(ByVal sender As System.Object, ByVal e As _               System.EventArgs) Handles MyBase.Load   ' Purpose: This opens and reads in the table names from the database.   Dim FileName, SQL As String   FileName = GetFileName()      ' Get the database file name   If FileName.Length = 0 Then    Exit Sub   End If   Me.Text = "SQL Tester: " & FileName ' Show the file name   ' This builds the connect string. It is used later as needed, too.   strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User _          ID=Admin;Data Source="   strConnect &= FileName   MyTable = "Customer"   Dim Connect As New OleDbConnection(strConnect) ' Reuse connect string...   Connect.Open()              ' ...and reopen the connection   SQL = "SELECT * FROM Customer"   Dim MyAdapter As New OleDbDataAdapter(SQL, Connect)   Try    ' How many records were returned?    RecordsRead = MyAdapter.Fill(MyDataSet, 0, MAX_RECORDS, MyTable)    Connect.Close()    SetControlBindings()    MyAdapter = Nothing      ' Free things up    Connect = Nothing   Catch    MessageBox.Show("Something wrong during connecting.")   End Try   WhichRecord()  End Sub  Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As _               System.EventArgs) Handles btnExit.Click   Me.Dispose()  End Sub  Private Sub SetControlBindings()   txtCompany.DataBindings.Add("Text", MyDataSet, "Customer.Customer Name")   txtFirstName.DataBindings.Add("Text",MyDataSet, "Customer.Contact First _                   Name")   txtLastName.DataBindings.Add("Text", MyDataSet, "Customer.Contact Last _                   Name")   txtAddress.DataBindings.Add("Text", MyDataSet, "Customer.Address1")   txtCity.DataBindings.Add("Text", MyDataSet, "Customer.City")   txtState.DataBindings.Add("Text", MyDataSet, "Customer.Region")   txtZip.DataBindings.Add("Text", MyDataSet, "Customer.Postal Code")  End Sub  Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As _                System.EventArgs) Handles btnFirst.Click   Me.BindingContext(MyDataSet, MyTable).Position = 0   WhichRecord()  End Sub  Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As _               System.EventArgs) Handles btnNext.Click   Me.BindingContext(MyDataSet, MyTable).Position += 1   WhichRecord()  End Sub  Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As _                 System.EventArgs) Handles btnPrevious.Click   Me.BindingContext(MyDataSet, MyTable).Position -= 1   WhichRecord()  End Sub  Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As _               System.EventArgs) Handles btnLast.Click   Me.BindingContext(MyDataSet, MyTable).Position = RecordsRead _- 1   WhichRecord()  End Sub  Private Sub WhichRecord()   ' Purpose: This finds out which record we are looking at and updates   '      the groupbox text property with the record number.   Dim where As Long   where = Me.BindingContext(MyDataSet, MyTable).Position + 1   gbxDBNavigation.Text = "Navigate Database: Record # " & CStr(where)  End Sub End Class 

Because we know the name of the database table we want to use as well as the field names we are interested in, we can push all the real work into the Form Load event. We hard-code the query into variable SQL and use that string in the OleDbDataAdapter() initialization call:

 SQL = "SELECT * FROM Customer"  Dim MyAdapter As New OleDbDataAdapter(SQL, Connect) 

The Fill() method of the data adapter transfers the data to the MyDataSet object. We also fill in RecordsRead with the number of records that were returned from the database. We make use of this value later in the program.

The subroutine SetControlBindings() does most of the new work in the program. Each text box is tied to a field in the MyDataSet object according to the parameters in the call. For example, the statement

 txtCompany.DataBindings.Add("Text", MyDataSet, "Customer.Customer Name") 

ties the txtCompany text box to the Customer Name field of the Customer table. The first parameter ( "Text" ) of the Add() method tells which property of the control is being bound. In all cases, we want to bind to the Text property of the text boxes. The other calls are similar, changing only the text box name and the field name. (You could also use the Properties window to bind the text boxes, but I wanted to show you how to do it in code.)

If all goes well, you should see the first record of the Customer table displayed in the text boxes. You can navigate through the records by clicking on the buttons in the group box. The code within each button is almost identical, so we just discuss the btnFirst button.

The statement

 Me.BindingContext(MyDataSet, MyTable).Position = 0 

sets the record pointer for the data set to the first record in the MyDataSet object. Because the records are zero-based , the first record is at position 0. Every form has a BindingContext() method associated with it when there are bound controls on the form. Because we do bind each text box to the MyDataSet object, we can use the Me keyword for the base object of the BindingContext() method.

With the record pointer set to the desired position, we call WhichRecord() to update the record location text for the group box. The remaining buttons work in the same fashion, either incrementing or decrementing the position in the dataset. Note that we use RecordsRead to set the position when the Last button is clicked. You could also use the Count property, too:

 Me.BindingContext(MyDataSet, MyTable).Position = _              Me.BindingContext(MyDataSet, MyTable).Count - 1 

However, because RecordsRead already holds the record count, you might as well use it. A sample run of the program is shown in Figure 25.26.

Figure 25.26. Sample run of the DBTextboxes program.

graphics/25fig26.jpg

That's all there is to it! The key is to bind the text boxes to a DataSet object. Once the dataset is built, the rest is very easy.

You really should experiment with this program. For example, you might try to modify the program so that it would work with other tables. How would you cope with the labels for the text boxes in this situation? Would parts of the SQLTester project be useful? Trying to figure out how to accomplish these tasks is a great way to learn how to manipulate a database.



Visual Basic .NET. Primer Plus
Visual Basic .NET Primer Plus
ISBN: 0672324857
EAN: 2147483647
Year: 2003
Pages: 238
Authors: Jack Purdum

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net