Using the Data Menu to Add/Create a New Data Source

Using the Data Menu to add/create a new data source.

Adding a data connection for the application to connect to the database.

Browsing to find the HealthClub database file.

Resulting Data Connection

Select NO when this appears.

Objects to include in the dataset

Resulting Design Screen after the data connection and dataset are created.

Beginning to configure the data source and data set.

Viewing dataset objects and objects on the GUI

Binding the MemberNumber in the dataset to an object on the form. Once the first object is bound, 3 items appear in the component tray.

:  Please bind the following fields to the following controls:

§  The MemberNumber field to the MemberNumber label.

§  The LastName field to the LastName label.

§  The FirstName field to the FirstName label.

§  The Gender field to the Gender label.

§  The Address field to the Address textbox.

§  The City field to the City textbox.

§  The State field to the State textbox.

After the first binding of field object and control happens, the data set, binding source and table adapter appear in the component tray at the bottom of your screen. At the same time that this appears, the form load event procedure appears in the code window, containing the following code:

Me.TblMembershipTableAdapter.Fill (Me.HealthClubDataSet. tblMembership)

Application displaying the first record

Beginning to code Private Sub DisplayStatus

Module-level vs local variables

Code for DisplayStatus

Code for PreviousButton

Code for NextButton

Adding the “call” to the form load event procedure

Selecting the table adapter in order to modify the query.

Edit Queries in DataSet Designer

DataSet Designer showing table object, included fields, the table adapter and its query.

Right-click Fill, GetData( ), and then Configure

Existing SQL statement used to fill the Data Table

Modified SQL (after adding WHERE statement)

Newly modified SQL statement

After the SQL query is modified, the form load event displays a syntax error

Code for FindButton and description of what that code does

The line “Dim State As String” creates a procedure-level variable called State, which will be used to store the state value that you wish to search for. The string data type indicates that the data stored in this variable will be composed of letters and/or numbers not used in a calculation.

Textboxes can be used as a means to input data into a computer program as it is running, and the assignment statement of “State = StateTextBox.Text”says to store the current value of the textbox’s text property (what is contained in the textbox) in the State variable.

Just a short time ago, you modified the SQL used by this form’s query so that it now includes the line WHERE (State=?). This created a filter that would reduce the number of records that were displayed, but the State value was not specified. The line Me.TblMembershipTableAdapter.Fill(Me.StateQueryDataSet.tblMembership, State) indicated that the value stored in the State variable (which came from the textbox) will be used in place of the question mark (?) in the WHERE portion of the SQL Select statement, thus filtering the records.

TESTING THE RESULTING APPLICATION

Enter each of the following values into the state textbox and then click the Find button to see your query results (Valid state abbreviations for this database include CA, AZ, KS, OR, FL, and OK).

After seeing your results with a valid state abbreviation, type in an invalid state abbreviation and the Record Status label should display (No Records).

The remainder of this handout deals with some of the underlying concepts used to create a program that displays data from related tables. The user selects a course and the application displays the instructor of the course (in a label) and the enrolled students (in a listbox)