Binding a Database to a VB.Net Formby Floyd Winters revised 04/05/2005Troubleshooting

Click and select the Data toolbar, and drag anOleDbDataAdapter to yourexisting form and click [Next] when the Data Adapter Configuration Wizard opens. The Adaptorwill populate a dataset from available tables.

In the Data Form Wizardshown below (Choose Your Data Connection) dialog box, click the [New Connection] button; the Data Link Properties dialog box appears.

In the Data Link Properties dialog box, click the Provider tab and select Microsoft Jet 4.0 OLE DB Provider and click the [Next] button.

In the Connection tab, click the ellipsis […] button for the Select or enter a database name box, then browse to your desired database. (It is a wise idea to first copy or move your database to the Bin folder of your project folder; that allows you to easily find it and keep track of it.) Select your desired database. If it was placed in the Bin folder of your current project you shoulddelete the automatically inserted path, and just leave the Database file name in the Select or enter a database name box. This creates a relative path that will work on any machine. If you forget to do this you can edit the OleDbConnection1ConnectionString property or open the Windows Form Designer code, search for C: and delete the path portion. Leave “” in.

Click the [Test Connection] button to verify the connection. Click [OK]button; click [OK] again.

You return to the Data Adapter Configuration Wizard dialog box. For the Which data connection should the data adapter use box, select the new connection that you just made and click the [Next] button.

In the Choose a Query Type dialog box, select the Use SQL statementsoption and click [Next].

Click the [Query Builder] button and then add the desired Table(s) to the Query Builder Wizard.

Check in the desired ordereach of the desired Fields columns that you want toinclude in the SQL statements.(You can add a formula to the automatically generated SQL statement, such as dbHours * dbRate AS dbPay.)And you can choose to Sort by any field desired. Click [OK],and [Next]when done.

POSSIBLE ERROR NOTE: (You may get a ‘Could not determine which columns uniquely identify the rows for "tblName"’ if there is not a Primary key defined in the table.Consequently, you get a yellowwarning error message for the Insert and Update options. If that is the case, click [Cancel], add a unique field or Primary key to your database .mdb file and start again. Example: You can make EmployeeID a primary key by clicking the Key icon.) Click [Finish].VB.Net will prompt you for a password. Click the [Don’t include password] button. The OleDbConnection Object is automatically added to the Component tray.

Click the Generate Dataset link found on the bottom of the right pane or right-click the OleDbDataAdapterobject and select Generate Datasetand click [OK]. Click the New option button, and accept the default name of Dataset1 and click [OK].

The Dataset object is added to the Component Tray and is also shown in the Solution Explorer window.

The Dataset is actually a Snapshot of the table. It becomes permanent when the OleDbDataAdapter1.Update(DataSet11) method is applied by the Update button.

To Bind the textboxes to the dataset: click on a textbox, expand[+] its Data Bindings property and set the Textproperty to the appropriate field in the dataset table. Do the same for each bound control.For a CheckBox use the Checked property under the Data Bindings section.

POSSIBLE ERROR NOTE: (It will not work if you click the Tag property instead.)

To Load the dataset - In the Form Load type: OleDbDataAdapter1.Fill(DataSet11, "tblWeeklyHours")
(Use the name of your actual table)

Also on form load you should probably Call your Calculations. (Do not save totals to the database.)

You may include routines such as cboState.Text = cboState.Text.ToUpper() in the Calculations.

POSSIBLE ERROR NOTE:If you copy your program to a different drive, you may have to right-click on the OleDbDataAdapter1 in the component tray, choose Configure Data Adapter, and use the Wizard to point to the new Path. Fortunately, it will remember your SQL code.

Add command buttons for Back, Next, Add, Update, and Delete and add the following code:

(This is just the minimal code. For more detailed code that includes better error checking, Choose Project, Add Windows Form and choose the Data Form Wizard. Be sure to give the Wizard DataSet a different name.)

PrivateSub btnBack_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)... ' Add code from automatically generated Data Wizard

Me.BindingContext(DataSet11, "tblWeeklyHours").Position -= 1

' tblWeeklyHours is the table name that is used by the Database

EndSub

PrivateSub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)...

Me.BindingContext(DataSet11, "tblWeeklyHours").Position += 1

EndSub

PrivateSub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)...

' Most of this code was copied from the automatically generated code from the Data Wizard

Try

'Clear out the current edits

Me.BindingContext(DataSet11, "tblWeeklyHours").EndCurrentEdit()

Me.BindingContext(DataSet11, "tblWeeklyHours").AddNew()

Catch eEndEdit As System.Exception

System.Windows.Forms.MessageBox.Show(eEndEdit.Message)

EndTry

Call DataSet1_PositionChanged()

EndSub

PrivateSub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)..

' Add code for automatically generated Data Wizard

Me.BindingContext(DataSet11, "tblWeeklyHours").EndCurrentEdit()

OleDbDataAdapter1.Update(DataSet11)

EndSub

PrivateSub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)..

' Add code for automatically generated Data Wizard

Me.BindingContext(DataSet11, "tblWeeklyHours").RemoveAt(Me.BindingContext(DataSet11, "tblWeeklyHours").Position)

EndSub

Troubleshooting: Try Right-Clicking on the OLEDataAdapter and Reconfigure the Adapter. When Done, Right-Click on the OLEDataAdapter and Regenerate the Dataset.