XTreme Logo

If you appreciate these tips, please consider a small donation:

Database Tip - by a guest contributor

Closing, Reopening and Rebinding with DataEnvironment
Tested with VB6 SP1
This VB Tip is provided courtesy of our friend Aslan.  When you're done here, be sure to check out her VBDelight web site for more delightful info!

The following is a simple example of how to close the Connection and Command Objects of a DataEnvironment, re-open them, and then re-bind the DataGrid to the DataEnvironment at runtime.

The steps and code below will open a Recordset against the Authors table in the pubs sample database, which comes with SQL Server, using the DataEnvironment Connection and Command Objects. It will then close the Recordset and the Connection Objects, and re-open them against the Employee table in the same database. (You should be able to modify the following example to do the same thing against the scott schema that comes with Oracle).

1. Create a Standard EXE project in Visual Basic. Form1 is created by default.

2. Add a DataEnvironment to the project. Connection1 is created by default.

3. Set Connection1 to use the OLEDB Provider for SQL Server to connect to the pubs sample database on your server/local machine.

4. Add a command (Command1) to Connection1 based on the following query:
"SELECT * FROM dbo.authors"

5. Right-click and drag the command onto Form1 and choose "Data Grid."
(FYI: this is a short-cut, that you should not use on a regular basis; it can cause the bindings to get lost later.)

6. Add a CommandButton to Form1.

7. Paste the following code in the General Declarations section of your code window on Form1.

Option Explicit

Private Sub Command1_Click()
With DataEnvironment1
' Close connections and recordsets
         ' This is the important step, it disconnects the grid
         ' from the database.

Set DataGrid1.DataSource = Nothing
' Re-connect to database, then re-open recordset
         ' You will need to change the Data Source to
         ' one on your system.

         .Connection1.CursorLocation = adUseClient
         .Connection1.ConnectionString = "Provider=SQLOLEDB.1;" & _
                      "Persist Security Info=False;User ID=sa;" & _
                      "Initial Catalog=pubs;Data Source=VBRESOURCE"
         ' Set the source for the Command to the new table.
         .rsCommand1.Source = "Select * from dbo.Employee"
         .rsCommand1.ActiveConnection = .Connection1
End With
' Re-bind the DataGrid to the DataEnvironment.
Set DataGrid1.DataSource = DataEnvironment1
' Display a record count to show it is reconnected.
    MsgBox "RecordCount = " & DataEnvironment1.rsCommand1.RecordCount
End Sub

Private Sub Form_Load()
    Command1.Caption = "Disconnect and Reconnect"
    ' Display a record count before disconnecting.
    MsgBox "RecordCount = " & DataEnvironment1.rsCommand1.RecordCount
End Sub

[ Back To The Top ]

Contact: web@xtremecomp.com
All contents copyright XTreme Computing unless noted otherwise.