Link to home
Start Free TrialLog in
Avatar of pavel_dolgov
pavel_dolgov

asked on

Binding DataReport (in VB6) to custom recordset

I nave designed a report using DataReport designer in VB6 (NOT Crystal). This report is bound to some DataEnvironment in design time. I want to bind my report in runtime to my custom ADODB.Recordset (having just the same structure) How to do it?
Avatar of eelpark
eelpark

Basically you have to cycle through each report section and reset the datamember/source. This is from article is from

http://support.microsoft.com/support/kb/articles/q190/4/11.asp

I hope this helps.

Here is the offical answer with example:
-----------------------------------------------------------------------------------
HOWTO: Bind a DataReport To an  ADO Recordset at Run Time

  The information in this article applies to:

       Microsoft Visual Basic Professional and Enterprise Editions for Windows, version 6.0

  SUMMARY

  The DataReport is a powerful tool and it's easy to build complex reports by dragging and
  dropping fields out of the DataEnvironment window. However, there are times when you
  may want to bind the DataReport directly to an ActiveX Data Objects (ADO) recordset
  rather than to the DataEnvironment. For example, you may have built a hierarchical query
  with ADO, or you may have an n-tier application that receives a recordset from a business
  object.

  This article helps you understand how to bind a DataReport directly to an ADO recordset.

  MORE INFORMATION

  First, build a hierarchical query with the DataEnvironment. Next, create a simple DataReport
  that is based on your query and bound to the DataEnvironment.

  Use the DataEnvironment to connect to the Northwind database (NWind.mdb) that is
  included with Visual Basic by following these steps:

     1.Create a new Standard EXE project in Visual Basic.

     2.Add a DataEnvironment to that project and rename it deCustomerOrders.

     3.Rename the initial connection to cnNWind

     4.Set the connection to use the Microsoft.Jet.OLEDB.3.51 OLE DB provider.

     5.Locate the Northwind database on your machine.

     6.Add a command to the connection and rename it Customers.

     7.Set the Customers command to query the Customers table.

     8.Add a child command to the Customers command and rename it Orders.

     9.Set the Orders command to query the Orders table.

    10.Relate the two commands on the CustomerID field on the Relation tab.

    11.Add a DataReport to the project and rename it rptCustomerOrders.

    12.Set the DataSource property of the DataReport to deCustomerOrders.

    13.Set the DataMember property of the DataReport to Customers.

    14.Right-click on the DataReport and clear "Show Report Header/Footer".

    15.Right-click on the DataReport and clear "Show Page Header/Footer".

    16.Right-click on the DataReport and select "Insert Group Header/Footer".

    17.Drag the CustomerID and CompanyName fields from the Customers command in the
       DataEnvironment onto the Group Header section.

    18.Drag the OrderID and OrderDate fields from the Orders command in the
       DataEnvironment onto the Detail section.

    19.Add a CommandButton to your form.

    20.Add the following code to your form:

          Private Sub Command1_Click()
              rptCustomerOrders.Show
          End Sub


    21.Run the project, click on the CommandButton and you should see the report with the
       customer and order information.

    22.To bind the DataReport directly to the hierarchical recordset generated by the
       DataEnvironment, add the following code:

          Private Sub Form_Load()
              Dim intCtrl As Integer
              With rptCustomerOrders
                  Set .DataSource = Nothing
                  .DataMember = ""
                  Set .DataSource = deCustomerOrders.rsCustomers
                  With .Sections("Section2").Controls
                      For intCtrl = 1 To .Count
                          If TypeOf .Item(intCtrl) Is RptTextBox Or _
                             TypeOf .Item(intCtrl) Is RptFunction Then
                              .Item(intCtrl).DataMember = ""
                          End If
                      Next intCtrl
                  End With
                  .Show
              End With
          End Sub


  Note: If you omit steps 13 and 14, you need to change "Section2" to "Section6" in the
  preceding code.

     1.Run the project, and you should see the report with the customer and order
       information.

  The DataReport uses the DataSource and DataMember properties to find the top-level
  command on which the report is based. For example, if you have a hierarchical query in the
  DataEnvironment containing Customers, Orders, and Order Details information but you only
  want to show the Orders and Order Details information, then you should set the DataSource
  property to be the DataEnvironment, and the DataMember property to be the Orders
  command.

  Each field on the DataReport has two properties that allow the DataEnvironment to
  determine what information to show on the report:

  - DataMember - DataField.

  Use the DataMember property to select the level of the hierarchy that contains the
  information you want to display. Use the DataField property to select the field you want to
  display.

  For example, the CustomerID field is in both the Customers and the Orders table. If you
  want to show the CustomerID field with the rest of the customer information, set
  DataMember to Customers. If you want to show the CustomerID with the rest of the Order
  information, set DataMember to Orders.

  When you bind directly to a recordset object as shown in step 21, the DataSource property
  of the DataReport should be set to the recordset object and the DataMember property
  should be set to an empty string. For the fields on the report, the DataMember property of
  the top-level recordset information (customer information in this case) should be set to an
  empty string. For information other than that which is in the top-level recordset (Order
  information in this case), the DataMember property of the report TextBoxes should be set
  to the name of the command (Orders in this case).



Avatar of pavel_dolgov

ASKER

Maybe it works but it isn't what I need. I want to use MY OWN recordset having no relation to DataEnvironment at all. I know that I should use hierarhical recordset but how can I create it ?
The point is pavel_dolgov you have to use the dataenvironment to set up the report in the reportdesigner. Then you can reset the recordsource/datamember at runtime to another recordset whether that recordset is in the dataenvironment or is a standalone recordset.

This example just happens to be for a hierarchial dataset. It doesn't have to be.

You had it correct in that it has to be the same structure.

For the example above:

  Set .DataSource = deCustomerOrders.rsCustomers

replace that with

  Set .DataSource = rsMyVeryOwnRecordsetObjectICreatedMyself

That example just happened to be as bad as it gets.

If you can't get that to work I will e-mail you a smaller example.

Hope this helps.

(You don't actually have to use the Data Environment to create a report - Just start adding fields to a report and type in the field name rather than selecting from the drop-down list)

This is a simple sample for creating your own unbound recordset, populating it with data and setting it to be the datareport's datasource.

For this code to run as-is you would need a datareport named MyReportsName with no group sections and four fields in the detail section (MyField1, MyField2, MyField3, MyField4)

Dim Rst As Recordset
Dim myRpt As datareport

Set Rst = New Recordset
Set myRpt = New MyReportsName

Rst.Fields.Append "MyField1", adChar, 50
Rst.Fields.Append "MyField2", adChar, 50
Rst.Fields.Append "MyField3", adChar, 50
Rst.Fields.Append "MyField4", adChar, 50

Rst.Open

Rst.AddNew

Rst!MyField1 = "Some Text"
Rst!MyField2 = "Some Text"
Rst!MyField3 = "Some Text"
Rst!MyField4 = "Some Text"

Rst.Update

Rst.AddNew

Rst!MyField1 = "Some More Text"
Rst!MyField2 = "Some More Text"
Rst!MyField3 = "Some More Text"
Rst!MyField4 = "Some More Text"

Rst.Update

Set myRpt.Source = Rst

myRpt.Show

That is correct you do not have to use the dataenvironment. It is just easier (for me anyway) to do it that way to test the report especially if you have a lot of grouping levels.

The question was "I want to bind my report in runtime to my custom ADODB.Recordset (having just the same structure) How to do it?"

12 ways to skin a cat. ;-)
ASKER CERTIFIED SOLUTION
Avatar of vbWhiz
vbWhiz

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Way #2:

'The purist method'

Step 1:

Create your commands in the data environment.

Step 2:

Create a DataReport based on these commands.

Step 3:

Find out what the entire SQL statement looks like for your commands (In ADO child command's SQL is embedded into the parent's SQL using the SHAPE command)

Dim myCmd as Command

For each myCmd in DataEnvironment1.Commands
   If myCmd.Name = "[Insert the name of your parent command here]" Then
      Debug.Print myCmd.CommandText
      Exit For
   End IF
Next myCmd

Step 4:

Determine what it is you want to change about this command or sub-commands and locate those spots in the command text.

Step 5:

Write a report-printing routine that creates a recordset rather than depends on the DataEnvironment

That routine might look something like this....

SAMPLE CODE:

Public Sub PrintThatReport(Optional Byval CompanyID as String)

   Dim myDBConn as ADODB.Connection
   Dim myCompRst as Recordset
   Dim myRpt as DataReport

   Set myDBConn = New ADODB.Connection
   myDBConn.Open "Database Connection String Goes Here!"
   If CompanyID <> "" Then
      Set myRpt = New CompanyReport

      myCompRst.Open "SELECT tblCompany.* FROM tblCompany WHERE tblCompany.fldCompanyID = '" & CompanyID & "'", myDBConn, adOpenStatic, adLockReadOnly
      Set myRpt.DataSource = myCompRst
      myRpt.Refresh
      myRpt.Show
   Else
      myCompRst.Open "SELECT tblCompany.* FROM tblCompany", myDBConn, adOpenStatic, adLockReadOnly
      Set myRpt.DataSource = myCompRst
      myRpt.Refresh
      myRpt.Show
   End If

End Sub



Step 6:

Remove the reference to the DataEnvironment's command as the report datasource in the designer and remove that stinkin' DataEnvironment from the project! (You can still keep it around if you want to modify the report but it isn't necessary to be in the compiled version)
Way #3:

'The way of no bounds'

Step 1:

Create your commands in the data environment.

Step 2:

Create a DataReport based on these commands.

Step 3:

Examine the command structure especially the resulting field lists. Create a routine that builds an unbound recordset containing that same field structure.

Dim Rst As Recordset

Set Rst = New Recordset

Rst.Fields.Append "fldCompanyID", adChar, 10
Rst.Fields.Append "fldCompanyName", adChar, 50
Rst.Fields.Append "fldCompanyAddress1", adChar, 50
Rst.Fields.Append "fldCompanyAddress2", adChar, 50
Rst.Fields.Append "fldCompanyCity", adChar, 50
Rst.Fields.Append "fldCompanyState", adChar, 2
Rst.Fields.Append "fldCompanyZip", adChar, 10

Rst.Open


Step 4:

Load the report data into this unbound recordset.

[-NOTE- The advantage of the 'way of no bounds' is that the report data doesn't actually have to come from a database, or be retrieved with one recordset]

Dim Rst As Recordset

Set Rst = New Recordset

Rst.Fields.Append "fldCompanyID", adChar, 10
Rst.Fields.Append "fldCompanyName", adChar, 50
Rst.Fields.Append "fldCompanyAddress1", adChar, 50
Rst.Fields.Append "fldCompanyAddress2", adChar, 50
Rst.Fields.Append "fldCompanyCity", adChar, 50
Rst.Fields.Append "fldCompanyState", adChar, 2
Rst.Fields.Append "fldCompanyZip", adChar, 10

Rst.Open

Rst.AddNew

Rst!fldCompanyID = "1001"
Rst!fldCompanyName = "Boundless Company"
Rst!fldCompanyAddress1 = "12324 Willow Lane"
Rst!fldCompanyAddress2 = ""
Rst!fldCompanyCity = "New York"
Rst!fldCompanyState = "NY"
Rst!fldCompanyZip = "12345"

Rst.Update

Step 5:

Write a report-printing routine that creates an unbound recordset, fills it with data, and then generates the report

That routine might look something like this....

SAMPLE CODE:

Public Sub PrintThatReport()

   Dim myRpt as DataReport
   Dim Rst As Recordset

   Set Rst = New Recordset

   Rst.Fields.Append "fldCompanyID", adChar, 10
   Rst.Fields.Append "fldCompanyName", adChar, 50
   Rst.Fields.Append "fldCompanyAddress1", adChar, 50
   Rst.Fields.Append "fldCompanyAddress2", adChar, 50
   Rst.Fields.Append "fldCompanyCity", adChar, 50
   Rst.Fields.Append "fldCompanyState", adChar, 2
   Rst.Fields.Append "fldCompanyZip", adChar, 10

   Rst.Open

   Rst.AddNew

   Rst!fldCompanyID = "1001"
   Rst!fldCompanyName = "Boundless Company"
   Rst!fldCompanyAddress1 = "12324 Willow Lane"
   Rst!fldCompanyAddress2 = ""
   Rst!fldCompanyCity = "New York"
   Rst!fldCompanyState = "NY"
   Rst!fldCompanyZip = "12345"

   Rst.Update
   Set myRpt = New CompanyReport
   Set myRpt.DataSource = Rst
   myRpt.Refresh
   myRpt.Show

End Sub


Step 6:

Remove the reference to the DataEnvironment's command as the report datasource in the designer and remove that stinkin' DataEnvironment from the project! (You can still keep it around if you want to modify the report but it isn't necessary to be in the compiled version)