Report Designer Component - Changing Data Source At Run-Time 
Author Message
 Report Designer Component - Changing Data Source At Run-Time

I have spent an unhealthy amount of time trying to make it possible to
change the datasource of a report (shown through the CRViewer) at run-time.
I have initially created a report using ADO with the OLEDB driver and the
report runs fine in my Visual Basic test program.  I then, at run-time
change the SELECT statement to refine my selection and hey presto the report
reflects those changes.  The problem is that whilst the program is running I
then again choose to change the SELECT criteria for the recordset created
and the new changes are not reflected in the CRViewer - I have attempted
such things as the refresh method but with no joy.

The code that I initially used came form Seagatesoftware.Com  - Knowledge
Base and the article number was
c2003176 (below is a direct copy of their solution)

I have put this code behind a command button and changed the QSQL statement
and added a variable obtained from a text box eg "SELECT * FROM customers
WHERE customerID = " & clng(text1.text)

As I have mentioned before it will work for the first value passed by the
textbox but if I pass another appropriate value and press the command button
the report does not reflect this change.

Help - it is affecting my sleeping patterns

__________________________________________________
Synopsis

A report was created in the Report Designer Component (RDC) version 7.0,
using the Active Data driver and OLEDB/ADO as the data source. The location
of the data source was to be changed at runtime.

Solution

To change the location of the data source, at runtime, the following code
needs to be used:

'In the General Declaration Section add the following:

Dim adocn As New ADODB.Connection
Dim adors As ADODB.Recordset
Dim CPw1 As ADODB.Command
Dim Conn As String
Dim QSQL As String

'Add the following in your code:

Set adors = CreateObject("ADODB.recordset")
adors.CursorType = adOpenKeyset
adors.LockType = adLockBatchOptimistic

' The following line changes the ds to the new location
Conn = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\test\xtreme.mdb"
With adocn
      .ConnectionString = Conn
      .CursorLocation = adUseClient
      .Open
End With

QSQL = "select * from customer"

Set CPw1 = New ADODB.Command
With CPw1
     .ActiveConnection = adocn
     .CommandText = QSQL
     .CommandType = adCmdText
End With

Call adors.Open(QSQL, adocn)



Thu, 06 Jun 2002 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Changing a formula @ Run Time - Report Designer Component

2. Parameters of the Data Source of Report Designer Component

3. How to change the ConnectionString in the VB6 Data Designer object at run time

4. Data Report Designer - Creating Hierarchy at Run Time

5. Data Environment / Data Report / Changing database file at run time

6. .( Changing data source at run time ).

7. Changing the data source at run-time

8. changing data source in crystal report @ runtime

9. Changing Groups Using Report Designer Component

10. Changing SQL statement in Report Designer Component

11. Runtime File Requirements for the Report Designer Component (RDC) 8

12. Setting Data Definition file data source at run time

 

 
Powered by phpBB® Forum Software