VB 6.0 - Crystal reports with Sqlserver stored procedures using Paramater

Asked By sidiq ahmed on 10-Jan-07 03:41 AM


i want to create Crystal reports using Stored procedured Sqlserver , normat stored procedure without arguments is ok,  but i want to know stored procedure with arguments

using vbcode, and how to call in VB6

plz help me, thanks in advance

Attach Stored Procedure to RPT - sundar k replied to sidiq ahmed on 10-Jan-07 04:27 AM

One way is to attach the Stored Procedure directly to your RPT file, so that you can avoid lot of vb coding and you can just execute the RPT file.

If you want to invoke a stored procedure in your RPT file, then you can very well attach the Stored Procedure to your RPT file instead of calling the same in your VB Code,

In your Crystal Report Application-> goto Database Expert and select the Stored Procedure you want to add, select it and add it to your Report, if the procedure has any input parameters , then after attaching the procedure to your RPT file, your rpt file will have those parameters as Report Parameters(you need not create those Report parameters, based on the Input parameters of the procedure it will get created).

But main thing which you have to note is, your procedure should return a output parameter as a resultset. Whenever you execute your RPT file, it in turn executes stored procedure and the output resultset from stored procedure will get  displayed in the Report.

Otherway is to use ADODB.COmmand object to execute Stored Procedure and store the returned values in ADODB.Recordset and attach the Recordset object to Report.Database.SetDatasource method.

Attach Stored Procedure which has Input parameter to RPT - sidiq ahmed replied to sundar k on 10-Jan-07 11:09 AM

thanx Buddy,

( i am using stored procedure in crystal reports and that stored procedure has input parameter to take)

but my problem is stored procedure which has input parameters to take, in crystal reports , and how to execute the stored procedure in vb6 code , that is the main problem

please look over it

ReplaceSelectionFormula - sundar k replied to sidiq ahmed on 10-Jan-07 11:54 AM

Did you tried ReplaceSelectionFormula?

With CrystalReport1
.ReportFileName = sample.rpt
.WindowState = crptMaximized
.Destination = crptToWindow
.ReplaceSelectionFormula "{table.field} = '" & text1.Text & "'"
.Action = 1
end with

u did not understood my problem, plz see it stored procedures in crystal reports which has input parameters
sidiq ahmed replied to sundar k on 10-Jan-07 12:23 PM

hello venkatesh,

 i will explain my problem step step,

1> stored procedure which returns data without arguments works fine for , here i can pass arguments as selection formula.

2> But stored procedures which requires parameter  which has to be passed from vb code does not work for me , ie i want to call a report which uses stored procedure which has input parameter ,

3> i want to know how call this report in vb6 by passing parameters  to that stored procedure which is in Crystal report

please help me venkatesh , i am facing a lot of problem in it

but cudnt figure where exactly you are struck! - sundar k replied to sidiq ahmed on 10-Jan-07 09:39 PM

looks like a minor issue for me! may be my understanding is still wrong ! sorry! let me try again,

When you attach your Stored Procedure , Crystal Report automatically creates parameter fields basedon the input parameters you have in your Procedure. Lets say your procedure input parameer is I_EMP_NO , once you add this Proc to your RPT , you can see a parameter field named I_EMP_NO in the Parameter fields list of Crystal Field Explorer. You can treat this as normal Crystal Parameter field and can use it in your Selection Formula expression, lets say you want to filter your RPT file based on I_EMP_NO, you will goto to Report -> Editi Selection Formula and would say,

{Table.field} = <your {?I_EMP_NO}  field}>

Whenever you Refresh your RPT , it will ask for I_EMP_NO field value, if you enter it , your report will get filtered based on EMp_NO field.

Now to use the same in your vbcode, you would use ReplaceSelectionFormula method,

.ReplaceSelectionFormula "{Table.field} = <EMP NO field Value from your VB Front-end>"

lets say for ex.,

.ReplaceSelectionFormula "{Table.field} = 100"

didnt that work for you?

excel - harshana peiris replied to sidiq ahmed on 13-Jan-07 11:41 PM
end of post
What version of crytal report are you using - sandeep singh replied to sidiq ahmed on 16-Oct-07 04:51 PM


please let me know which version of crystal report are you using

i had used the SP with parameters in CR

might be code help full to you