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?
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.rsCustome rs
replace that with
Set .DataSource = rsMyVeryOwnRecordsetObject ICreatedMy self
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.
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.rsCustome
replace that with
Set .DataSource = rsMyVeryOwnRecordsetObject
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. ;-)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
'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)
'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)
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.rsCustome
With .Sections("Section2").Cont
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).