Putting a table on a form, filled with data from one database file

Start the application and work with the Wizard
1. Start a new Windows application
2. Toolbox – Data tab – OleDbDataAdapter.
3. Wizard begins. Click NEXT
4. Make a note of the server name that pops up in the data connection box—the part before the \ backslash (mine says GALLETTA\netSDK.pubs.dbo so it’s just GALLETTA). Once you copy this, you can then do multi-table extracts manually (next page). For the textbook’s examples, click the down arrow and choose the server named pubs. Click NEXT
5. Use SQL statements. NEXT
6. In the “what data” box click QUERY BUILDER.
7. Choose the table(s) you need. ADD each one then CLOSE.
8. Choose the fields then OK. Click NEXT. You’ll see the SQL generated by the choices.
9. Click FINISH. You’ll be taken back to your form. Nothing seems to have happened. Worry not. You can see that, just under your form, you have new icons labeled OleDbDataAdapter1 and OleDbConnection1.
Generate the data set
10. Click on the form.
11. Click on Data - Generate Dataset
12. Make sure default options are chosen: (1) New dataset – (2) chosen tables – (3) add this to the designer. Rename it to something more meaningful; mine was called DataSet11.
Place the table grid on the form
13. Toolbox – Windows forms – Datagrid
14. Drag on form to suitable size. Be generous to make sure there’s enough room!
15. Set the DataSource property to the one without the table name following the dot.
16. Set the DataMember property to the table name. In the last step, if you inadvertently chose the DataSource that has the table name, you’ll have nothing to choose here, so you should correct the problem and return to the previous step.
Populate the table
17. Double click the form (to create a form1_load event)
18. Enter the following code (the Sub and End Sub are already provided)
Private Sub Form1_Load(ByVal sender As System.Obj…
DataSet11.clear()
OleDbDataAdapter1.fill(Dataset11)
End Sub
19. Click the > key to run! Instant table!

Putting a table on a form, filled with data from multiple tables

Start the application, cancel the Wizard, and set up manually
1. Start a new Windows application
2. Toolbox – Data tab – OleDbDataAdapter. The Wizard begins but click CANCEL.
3. For manual setup, locate the Properties of the OleDbDataAdapter1. Expand the SelectCommand property by clicking on the + sign.
4. You can rename the OleDbDataAdapter1 if you wish. I named mine odaAuthorTitles like the book did.
5. Then click on the SelectCommand plus sign to define a connection. Choose NEW on the Connection property.
6. A familiar form will come up. The note you made of the server name (without the pubs.dbo) from page 1 now comes in handy. (mine said GALLETTA\netSDK). Choose the Windows NT Integrated Security option
7. Click the down arrow and cross your fingers. The system will search for the databases on the server. If it takes some time something is wrong on the previous step—recheck your server name. If a list comes up, choose “pubs” for continuing the textbook’s example. You can click the button “Test Connection” to make sure things are working ok. Then click OK.
8. Click on the “Command Text” property. The query builder will load. For the first multi-table textbook example, we chose Authors, TitleAuthor, and Titles.
9. Choose the fields you want on the report and click OK.
10. You’ll be taken back to your form. As before, nothing seems to have happened. Worry not. You can see that, just under your form, you have new icons labeled OleDbDataAdapter1 (or the one with the new name) and OleDbConnection1.
Generate the data set
11. Click on the form.
12. Click on Data - Generate Dataset
13. Make sure default options are chosen: (1) New dataset – (2) chosen tables – (3) add this to the designer. Rename it to something more meaningful; mine was called DataSet11.
Place the table grid on the form
14. Toolbox – Windows forms – Datagrid
15. Drag on form to suitable size. Be generous to make sure there’s enough room!
16. Set the DataSource property to the one without the table name following the dot.
17. Set the DataMember property to the table name. In the last step, if you inadvertently chose the DataSource that has the table name, you’ll have nothing to choose here, so you should correct the problem and return to the previous step.
Populate the table
18. Double click the form (to create a form1_load event)
19. Enter the following code. Note that you might not have renamed the OleDbDataAdapter1 as OdaAuthorTitles so you would need to use the old name.
Private Sub Form1_Load(ByVal sender As System.Obj…
DataSet11.clear()
OdaAuthorTitles.fill(Dataset11)
End Sub
20. Click the > key to run.

Updating Data on a Form (using the data grid) from a Live Data Source

1. Put the form’s table together as in the previous two pages. One difference: place all fields on the form.
2. Using a save button:
a. Add a button that will have a caption such as “save the changes” or something like that.
b. Double click the button and enter the following code. Note that you might not have renamed the OleDbDataAdapter1 as OdaAuthorTitles so you would need to use the old name.
Private Sub btnClear_Click(ByVal sender As…
OdaAuthorTitles.update(Dataset11)
MsgBox("Thanks for saving", MsgBoxStyle.Information, "DataUpdated")
End Sub
3. Using an exit button
a. Add a button that says “exit” or something like that.
b. New code would use a msgbox so that when the user clicks the exit button it asks if changes should be saved or not. If yes, then the update command would be done. If no, just end. An appropriate message box informs you of what was done.
Private Sub btnEnd_Click(ByVal sender As…
Dim response As Integer
response = MsgBox("Do you wish to save all changes?", MsgBoxStyle.YesNoCancel, "End")
If response = vbYes Then
OleDbDataAdapter1.Update(DataSet11)
MsgBox("Thanks for saving! Goodbye!", MsgBoxStyle.Information, "DataUpdated")
End
End If
If response = vbNo Then
MsgBox("Not saved. Changes are lost forever!", MsgBoxStyle.Information, "Goodbye")
End
End If
End Sub

Tying Data to Text Boxes and Filtering Data

1. Start a new Windows application
2. Toolbox – Data tab – OleDbDataAdapter.
3. Wizard begins. Click NEXT
4. Make a note of the server name that pops up in the data connection box—the part before the \ backslash (mine says GALLETTA\netSDK.pubs.dbo so it’s just GALLETTA). Once you copy this, you can then do multi-table extracts manually (next page). For the textbook’s examples, click the down arrow and choose the server named pubs. Click NEXT
5. Use SQL statements. NEXT
6. In the “what data” box click QUERY BUILDER.
7. Choose the table(s) you need. ADD each one then CLOSE.
8. Choose the fields then OK. Click NEXT.
9. Choose the field you want to use as a search criterion. In the textbook’s example, click on the field you want (STATE in the example), which inserts it on the table below the SQL code. Insert “=?” in the criterion column. Then click “off” the field if you don’t want it to show.
10. You’ll see the SQL generated by the choices, which will show WHERE (field=?) as the last line. Then after NEXT you will see the results of the wizard.
11. You’ll be taken back to your form. As before, nothing seems to have happened, except that under your form, you have new icons labeled OleDbDataAdapter1 (or the one with the new name) and OleDbConnection1.
12. Click on the form and generate the data set as before.
13. Create the text boxes which will contain the data in the database one record at a time.
14. In the properties sheet for each text box, find the DataBindings category (at the top). Open it (click the + sign) and select Text. Click the down arrow in the property of Text, and select the database, and click the + sign to the left and select the field that is to be displayed in that box.
15. Place a button on the form to do the “find,” and provide code to cover the selectcommand.parameter that sets the value to the field you’re using for the search. In the text example with STATE, that’s the code
Dim state As String = TextBox5.Text
OleDbDataAdapter1.SelectCommand.Parameters("state").Value = state
dataset11.Clear()
OleDbDataAdapter1.Fill(dataset11)
showposition()


Note that the variable must be taken from the text box; you can use a temporary variable to do that.
16. A new subroutine called showposition() can provide the current location. Code in the text is used:
Private Sub showposition()
Dim tot, curpos As Short
tot = Me.BindingContext(dataset11, "authors").Count
If tot = 0 Then
Lblrecstatus.Text = "(No records)"
Else
curpos = Me.BindingContext(dataset11, "authors").Position + 1

Lblrecstatus.Text = curpos & " of " & tot
End If
End Sub

17. Note that position is +1 because numbering starts at zero.
18. “Previous” and “next” buttons now need definition. Code behind the buttons is shown:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e…
Me.BindingContext(dataset11, "authors").Position = _
Me.BindingContext(dataset11, "authors").Position - 1
showposition()
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e…
Me.BindingContext(dataset11, "authors").Position = _
Me.BindingContext(dataset11, "authors").Position + 1
showposition()
End Sub
19. That’s it! You can browse through the data.