Crystal and Stored Proc w/parameters

[enak]

I am new to Crystal. I am trying to create a report using
ADO DSNless connection and a stored proc that takes a
parameter. The report is called from a VB6 app.

I have created the report from datafields that are
associated to a stored proc. Originally, the report was
using an ODBC connection. I converted the database driver
to ADO. When I do this I am given 2 methods for building
the recordset. The first choice give me a list of things
such as tables, views, etc. but not a stored proc.

The second choice gives me the chance to enter in the SQL
statement. I can call the stored proc from there but I
still need to pass a parameter.

I am using the crystal ocx. How can I make this work?

TIA
Nate

 

[Fursten]

Hi,

I think you should change to SQLOLEDB connection. With this you can access directly the stored procedure (like when using ODBC): databases/convert database driver... choose To: pdsoledb.dll (OLEDB)

After that you will be ask to indicate the data link properties: choose microsoft OLEDB provider for SQL Server... with the properties you want (maybe you should indicate a login that you will use in your application to access SQL SERVER).

Then say that you want to base you report in a stored procedure....choose wich one.

After that you will have access to the fields of your stored procedure in the Insert/database fields.

The parameters should be pass from the vb application, like this:


CrystalOCXObject.addStoredProc Value --> be careful in here... the type of value you pass are important! Sometimes crystal send diferent kind of types to the stored procedure. To have shure everything is well done, open a sql profile scan... and see what is being send to sql server.

If you have more than one parameter, add one of the above line for each of one, in the order the parameters were set in the stored procedure.

Note: If you have your report running and then want to change something in the stored procedure: first drop the stored procedure; then create the new stored procedure (although is almost the same) and then set location in you report to the new stored procedure. If you don´t see the changed stored procedure as a new one.. you could have problems running a changed stored procedure... I think it´s a kind of bug.

When changing type of connection always make a copy of the report first!

Hope this help!

Sérgio Oliveira

 

[enak]

Thanks for your help. I have made some changes based on your suggestions and now I get a message that says "Cannot open SQL Server".

Here is my code so that you can see what I am doing.

Public Sub PrintStandard()

Me.CrystalReport1.Reset

Dim strReportPath As String
Dim sSQL As String
Dim ErrNum As Integer


strReportPath = g_rptPath & g_RptFile

With frmReports.CrystalReport1

.ReportFileName = strReportPath
.ParameterFields(0) = "BatchNo;" & g_Batch & ";True"

.Connect = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;" & _
"password=pwddx;" & _
"User ID=dxuser;" & _
"Initial Catalog=AKDatEx;" & _
"Data Source=AKWEB01" .WindowState = crptMaximized

End With

' frmReports.CrystalReport1.Destination = crptToPrinter
frmReports.CrystalReport1.Action = 1 '1=print

End Sub

I can run the report from Crystal but not from VB.

Thanks!
Nate

 

[Fursten]

Hi,

Just because I´m not sure, try to use: UID and PWD instead of "password" and "User Id" (in the connection). Crystal is not so "compatible" in syntax with ADO

Where are you passing the parameters to the stored procedure? You should use something like addStoredProc and not parameterFields(0)


Don´t forget to drop and create the stored procedure everytime you change something on it (the error you are getting I already get before... beceuse I had changed the stored procedure).

 

[enak]

That fixed it (for now). Thank you!

 

[Guest_imported]

This is great! Thanks Nate for asking the question. I have the exact same question or problem and I was trying hard to find a solution. Then, I was surfing the Internet and found this website. But most important of all, thanks to SergioOliveira for helping out others. Kudos to you!
Now I can go back to my boss and say "It's no problem."

Well, I have a question. Since the report itself is created by pointing to the development SQL server. When it is ready for production, do I have to change the OLEDB connection from my VB app. to point to the Production Server by changing the UID, PWD, Initial Catalog and Data Source?

 

[Fursten]

Hi

If the production server has diferent parameters to login... yes you have. However, in your developing server you should being working already in a enviroment as similar as possible from your production server! So, you should have being developing in a database with the same name and login.... If you aren´t´... you have to assure that you change them in the production server.

For instance, in my applications I use a connectionstring to validate the useres... To use crystal I change inside the code the connection string from ADO to Crystal (that stuff I already saied)... and use the same parameters.

Sérgio Oliveira