VB6 Crystal10 DSR ADO stored procedure issue

[adale]

I am trying to connect to a a stored procedure, then attached it to a Crystal Report already built, to update the data. This code almost gets there, but gives me a popup "Crystal Report Viewer"..."This field name is not known." and the report is blank. I can't see the data in the locals, but the command code seems to be recognised.
In fact I would settle for using Crystal's own popup that ask users for the stored procedure parameters, but using code would be more manageable.

This is the code in the DSR that should attach the report to the data.

Any ideas?


Dim adoRst As New ADODB.Recordset
Dim adoCmd As ADODB.Command
Dim adoCon As New ADODB.Connection

Set adoRst = New ADODB.Recordset
Set adoCmd = New ADODB.Command

adoRst.CursorType = adOpenStatic
adoRst.LockType = adLockOptimistic

With adoCmd
.ActiveConnection = strConnection
.CommandType = adCmdStoredProc
.CommandText = "rptBlotter"
.Parameters("@BusinessDate").Value = CDate(2005 - 9 - 1)
.Parameters("@IsCashYorN").Value = "Y"
.Parameters("@IsLoanYorN").Value = "N"
End With

Set adoRst = adoCmd.Execute
Database.SetDataSource adoRst
[/color blue]

 

[DrJavaJoe]

Some where in your report it is referring to a fieldname that is not being returned by this stored procedure. Check all of the fields on the report against the field names in adoRst also check your formula fields for the same.


Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[adale]

The raw rpt file works fine, and is unchanged from the one imported into VB6. When I look at the locals window, I cannot see any dat coming back from the recordset. Does the code look correct?

 

[DrJavaJoe]

Your date needs to be in quotes.

CDate(2005 - 9 - 1)

should be

CDate("2005-9-1")

I do not think that is going to solve the unknown field name error but one thing at a time, right?




Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[adale]

You're right, I caught the date issue and the recordset runs. I checked that by sending some of the output to the immediate window. The problem now is that although the recordset runs though, the report keeps looking at the development database it was designed on.
The above code, by the way, is the Report_Initialize section.
Now I'm investigating ways of just changing where the report is looking. But everything seems to be failing as I can't find a decently clean example.

Thanks for the help so far though.

 

[DrJavaJoe]

I would move this out of the Report_Initialize and put it in a form event such as a command button's click event and try the following and let me know what you get.

Private Sub Command1_Click()
Dim adoRst As New ADODB.Recordset
Dim adoCmd As ADODB.Command
Dim adoCon As New ADODB.Connection

Set adoRst = New ADODB.Recordset
Set adoCmd = New ADODB.Command

adoRst.CursorType = adOpenStatic
adoRst.LockType = adLockOptimistic
With adoCmd
.ACTIVECONNECTION = strConnection
.CommandType = adCmdStoredProc
.CommandText = "rptBlotter"
.Parameters("@BusinessDate").Value = CDate("2005-9-1")
.Parameters("@IsCashYorN").Value = "Y"
.Parameters("@IsLoanYorN").Value = "N"
End With
Set adoRst = adoCmd.Execute
crYourCrystalReport.Database.SetDataSource adoRst
crYourCrystalReport.DiscardSavedData
CRViewer1.ReportSource = crYourCrystalReport
CRViewer1.ViewReport
end sub


Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[BobRodes]

adale, in your code as written creates unnecessary overhead with your Connection object. See faq222-6008 for more detail. Note that every implicit call to the connection object has the overhead described as well as every explicit one.

Bob

 

[adale]

But in that code therer is no mention of the DSR that the viewier must get the report format from.

What about the logonserver code? I can see the database and server name I dont want in the locals window when the report is opened.

 

[adale]

I ran the code in the form as you suggested and it did what it's been doing all along.
It runs the code, opens & executes the recordset fine. Then it opens the report against the default server/database asks for the parameters and shows me development data.

This code cant be the right road to be going down.

 

[DrJavaJoe]

But in that code therer is no mention of the DSR that the viewier must get the report format from.

substitute crYourCrystalReport with the name of your DSR.

Also let's add this line after the DiscardSavedData line.

crYourCrystalReport.RecordSelectionFormula = "{rptBlotter.BusinessDate} = #" & CDate("2005-9-1") & "#"

Substituting businessDate with the name of the field returned by rptBlotter that contains the Business Date.

I'm sure this will work if you have a dsr called crYourCrystalReport and this dsr's datasource at design time returns the same fields as does rptBlotter, then when this is run it will return the data defined in strConnection. The problem is something really trivial and you'll most likely kick yourself once it's found.


Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[adale]

Still no good I'm afraid.
I'm trying to go another route now. Specifically, to use a DataEnvironment in the project, then link the reports I import to that. I started on the idea based on the example files installed with CR10. There should be a way of pointing reports to the DE, but I haven't found one that works yet as the CR examples start with blank forms, and mine are highly formatted.

Another route was to load the RPT within code, like older versions of CR, but that doesn't seem to be supported in CR10 now. Although I hope I'm wrong.

My thoughts are that every deployment must have a method for repointing designed reports at different servers. So why am I finding it so difficult.

 

[BobRodes]

<I'm trying to go another route now. Specifically, to use a DataEnvironment in the project

If you want to go that route, I would first do a search for DataEnvironment here, and read what people have to say about the DataEnvironment.

HTH

Bob

 

[adale]

I've got the answer now from

http://www.tek-tips.com/faqs.cfm?fid=5374.

Tested and working well on all reports.

Thankyou again for your help.