ASP Stored Procedures and Crystal Reports 8

[krshaikh]

Hi all,

I have converted my VB applciation in asp. I have to use Crystal Reports for reporting as in the VB Application.
there were no issues until I started building a report that uses stored procedures and accepts Date parameters.

The example available from Crystal decisions says the following lines for passing date parameters to stored procedure.

'===========================================================
' WORK WITH STORED PROCEDURE PARAMETERS
'===========================================================

' Param Field Type VBScript Call Type Number
'
' NumberField CDbl 7
' CurrencyField CDbl 8
' Boolean CBool 9
' DateField CDate 10
' StringField CStr 12

Set StoredProcParamCollection = Session("oRpt").ParameterFields

Set ThisParam = StoredProcParamCollection.item(1)
Set ThisParam2 = StoredProcParamCollection.item(2)
Set ThisParam3 = StoredProcParamCollection.item(3)

'this is my com component)
set objDLL = Server.CreateObject("prjASPde.clsFunctions")

objdll.StoredProcDt1 = request.querystring("startdate")
objdll.StoredProcDt2 = request.querystring("enddate")

NewParamValue = "12000"
NewParamValue2 = objdll.stprocdt1
NewParamValue3 = objdll.stprocdt2

ThisParam.SetCurrentValue cstr("NewParamValue"),12
ThisParam2.SetCurrentValue cstr("NewParamValue2"),12
ThisParam3.SetCurrentValue cstr("NewParamValue3"),12

--------------------------------------------------------

Please note that parameter 2 and 3 are the date parameters.

To pass date parameters to crystal report based on stored procedure we have to convert the date in this format as shown below
2004-07-04 00:00:00.000

To achieve this I pass date values to objdll (my com component) which returns the formated date as showN above.

When it comes to display the report i get this error message

-2147192179 Error detected by database DLL

The error is quitE understandable I am passing parameters in the wrong format. But where exactly have I made the mistake. The same thing is working fine in VB application. Crystal report converts date parameters for stored procedures into strings. I have tried using the following lines of code too,

ThisParam2.SetCurrentValue cdate("NewParamValue2"),10
ThisParam3.SetCurrentValue cdate("NewParamValue3"),10

If I do that ASP doesn't recognize the cdate function for some unknown reason.

Please see if anybody can help.

Regards,

- krshaikh -

 

[Tarwn]

Take out the quotes. Rigjt now since they hae quotes around them (NewParamValue2,3,etc) They are being trated as strings rather than as variables, So when you try to cDate them itthrows an error because it cannot convert the string "NewParamValue2" to a date.

ThisParam2.SetCurrentValue cDate(NewParamValue2),10

-T

_{01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111}
The never-completed website:

http://www.tiernok.com/

 

[krshaikh]

Hi,

Thanks for the help Tarwn, it didn't workout though.

Regards,

- krshaikh -