Solved

Changing Datasource in VB not working

Posted on 2004-04-26
16
1,562 Views
Last Modified: 2007-11-27
I am trying to change the Datasource dynamically without success:

With crTable
      Select Case UCase(.DllName)
         Case UCase("crdb_odbc.dll")
               .ConnectionProperties.DeleteAll
               .ConnectionProperties.Add "Connection String", gConnStrMaster
               '.ConnectionProperties.Add "DSN", gDSN
               .ConnectionProperties.Add "Database", gDBName
               '.ConnectionProperties.Add "Trusted_Connection", IIf(gTrusted, 1, 0)
               'If Not gTrusted Then
               '   .ConnectionProperties.Add "User ID", gServerUID
               '   .ConnectionProperties.Add "Password", gServerPwd
               'End If
      end select
end with

and I run this on each table, and the sub-reports.

The report was built using a DSN = "EDI_Desktop", and Database name = "EDI_Desktop".
I'm running a VB app in development, and the report is supposed to run from DSN = "Development" and DB name "Development".

With the above code, I'm still getting an error:
42502 [ODBC SQL Server Driver] Invalid Object name 'EDI_Desktop.dbo.Order Header'

I have followed the examples provided on the KB, to no avail.

G

0
Comment
Question by:gdbjohnson
  • 9
  • 6
16 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 10922217
Which version of CR?  Which edition?

How are you calling the reports?  RDC? OCX?

Have you tried the code without using the CASE statement?  Since that is the only option why not just change the connections?

Are there any subreports?

mlmcc
0
 
LVL 2

Author Comment

by:gdbjohnson
ID: 10922611
CR 9 ver: Service Pak 3, 9.2.3.970

I thought that with VB, you could only call the reports through RDC?  I declare it thus:
Dim crReport As CRAXDDRT.Report

The select case holds "crdb_ado.dll" as well, but I haven't developed any reports with this connection method, so only the one applies here, and I omitted the code handling the other.

In code, I am able to see that once the connection properties change for one table, all tables for the same connection are changed as well, and the ConnectionProperties collection seems to hold the correct information.

I then view the report, using the viewer OCX:
crViewer.ReportSource = crReport
crViewer.ViewReport

Once the viewer displays, and the report tries to load/display, I get the above error.

I looked at the database properties in the CR9 App GUI, and it displays table properties that are not visible from the CRAXDDRT.Report interface.  In the table properties are Qualifier1, and Qualifier2 for the table, being: "EDI_Desktop", and "dbo" respectively.  Is it possible that the connection properties are being updated properly in code, but that CR is still using the old qualifiers for building the SQL queries?

G
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 10924767
>>I thought that with VB, you could only call the reports through RDC?  
The OCX was for previous versions of CR.

Do you really need CRAXDDRT or can you use CRAXDRT?
CRAXDDRT is only needed if the user is going to manipulate the report in a design mode.

mlmcc
0
 
LVL 2

Author Comment

by:gdbjohnson
ID: 10928203
oh, really?  I didn't know that.  haha.  The pitfalls of learning on your own.
yeah, maybe I should change that.
I will try using the other object, and see if that changes it.

G
0
 
LVL 2

Author Comment

by:gdbjohnson
ID: 10928236
no change... still the same problem.

G
0
 
LVL 2

Author Comment

by:gdbjohnson
ID: 10928251
I guess this problem isn't obvious, would it be better if I include every line of code, A-Z?
0
 
LVL 2

Author Comment

by:gdbjohnson
ID: 10933060
I've added more points to this question.  I need a solution this week.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 10935307
It might help to see all the code.

You are using the RDC.  That is what CRAXDRT & CRAXDDRT are.  The main reason for using CRAXDRT is CRAXDDRT can require royalty payments if certain calls are used.  CRAXDRT has most of the same capabilities but doesn't require a royalty.  The only thing you really lose is the ability for users to edit and manipulate the report.

I didn't expect that change to solve the problem.

mlmcc
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Author Comment

by:gdbjohnson
ID: 10939092
good to know.  I haven't coded anything for users to edit reports, so I think I'm safe.  ;)
Here's the full code:

I call FixReportDataSource on the created report to switch all the tables.


'*****************************************
Private Sub OpenReport(X as string)
  strReportPath = X
  Set crApp = New CRAXDRT.Application
  Set crReport = crApp.OpenReport(strReportPath, 1)
  FixReportDataSource crReport
  crViewer.ReportSource = crReport
  crViewer.ViewReport
end sub

Public Sub FixReportDataSource(ByRef crReport As CRAXDRT.Report)        
         Dim Y As Integer
         Dim X As Long
         Dim oSection As CRAXDRT.Section
         Dim oObject As Object
         Dim oSubReportObject As CRAXDRT.SubreportObject
         Dim oSubReport As CRAXDRT.Report
         
         For Y = 1 To crReport.Database.Tables.Count
            SetTableConnection crReport.Database.Tables(Y)
         Next Y
         
10006         For Each oSection In crReport.Sections
10007            For Each oObject In oSection.ReportObjects
10008               If oObject.Kind = CRAXDRT.crSubreportObject Then
10009                  Set oSubReportObject = oObject
10010                  Set oSubReport = oSubReportObject.OpenSubreport
10011                  For Y = 1 To oSubReport.Database.Tables.Count
10012                     SetTableConnection oSubReport.Database.Tables(Y)
10013                  Next Y
10014               End If
10015            Next oObject
10016         Next oSection
10020    Exit Sub      
End Sub


' This sub has gotten a bit hacky while I've been debugging it...  
'My reports run from two databases: EDI_Desktop holds data, and EDI_Desktop_Master holds Meta-data
' so I must check to which DB the table belongs.
Private Sub SetTableConnection(crTable As CRAXDRT.DatabaseTable)  
   Dim arr
   Dim X As Integer
   Dim bMaster As Boolean
   arr = crTable.ConnectionProperties.NameIDs

   With crTable
            For X = 0 To UBound(arr)
               If arr(X) = "Connection String" Then
                  If .ConnectionProperties("Connection String") = gConnStr Then
                     bMaster = False
                  Else
                     bMaster = True
                  End If
                  Exit For
               ElseIf arr(X) = "DSN" Then
                  If .ConnectionProperties("DSN") = "EDI_Desktop" Then
                     bMaster = False
                  Else
                     bMaster = True
                  End If
               End If
               Exit For
            Next X
            If bMaster Then
               .ConnectionProperties.DeleteAll
               .ConnectionProperties.Add "Connection String", gConnStrMaster
               .ConnectionProperties.Add "Database", gDBName
            Else
               .ConnectionProperties.DeleteAll
               .ConnectionProperties.Add "Connection String", gConnStr
               .ConnectionProperties.Add "Database", oLogin.DBName
            End If
   End With
End Sub
'*****************************************

This is all the code I call (slightly edited).  If I set a breakpoint in FixReportDataSource after line 10020, I can open the ConnectionProperties and they are set correctly for the tables, but if I call the Verify() function, it fails because it is trying to verify against the original DB saved in the report.  

Likewise if I retreive the SQLQueryString property, the tables are all qualified with the original db name, instead of the new database name I'm setting in code.

Why aren't the changes propagating to the tables?  What am I missing?


G

0
 
LVL 100

Expert Comment

by:mlmcc
ID: 10944528
Should you be passing the table ByRef as in
Private Sub SetTableConnection(ByRef crTable As CRAXDRT.DatabaseTable)  

mlmcc
0
 
LVL 2

Author Comment

by:gdbjohnson
ID: 10948594
if the byRef is omitted, then it is implied, so it is no different.  
I added it just to highlight the fact that the object is being passed by reference.

I'm surprised that my question hasn't generated more discussion...  you seem to be the only one interested in helping me, mlmcc.
Have you ever tried to switch the datasources programmatically?
0
 
LVL 2

Author Comment

by:gdbjohnson
ID: 10950303
I decided to build a test project specifically to play with the reports, and I solved it.
It seems the changes do not actually become applied until you set the Location property... not very intuitive, but it works.

I added the following line to the end of the SetTableConnection function:
  crTable.Location = crTable.Location

Setting the Location to itself causes the changes to actually propagate throughout the report.

Looks like you're getting a freebee, mlmcc.  I was thinking of asking for a refund for the question, EE didn't really help me much on this one, but you gave an effort, so a refund doesn't seem fair either.  So, enjoy.

cheers,
G
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 400 total points
ID: 10955282
No problem either way.  I apparently am lucky in that we build our development server to match our production server and since it is on its own network we gave it the same name and used the same directory structure.  In that way when I move a report there are no code changes and no need to change locations.

Thanks for the tip on set location.

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 10965304
Thanks.
Glad I could help

mlmcc
0
 
LVL 3

Expert Comment

by:philipleighs
ID: 11029888
Hi G,

I thought I'd let you know that your solution has worked for me too (I'm using version 10).

This morning I received the same error when running my report on the test server for the first time. Until then I'd only been running reports on a development database.

Adding this line did the trick.

oTable.Location = oTable.Location;

It's no surprise that such a peculiar solution should be required for this, the 10th revision of a heaving pile of dung cobbled together over the ages. Will it ever be a polished product? I'm yet to be pleasantly surprised by this software. Sorry if you're a Crystal afficionado and don't share my view (rant over).

Thanks for describing the solution,
P.
0
 
LVL 2

Author Comment

by:gdbjohnson
ID: 11030530
>10th revision of a heaving pile of dung

Wait... so are you then saying you do or don't like the software?  haha...

When I first started using it, I noticed that some features/solutions really require a different way of thinking -you just need to see the trick.  Some techniques maybe could even be described as a little bit hacky...  

but, you have to admit that the software is pretty powerful at being able to develop reports that look pretty good, quickly.  Any software that complicated will have areas of improvement.

Anyways, I'm glad I could help.  Maybe I should email Business Objects so it gets added to the KB.

G

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now