TTX and Stored Procedure

krishnakumarkn
krishnakumarkn used Ask the Experts™
on
Hi All
 
I have a vb application from where I execute a sql server stored procedure and populate an ado recordset. I am designing a report using the ttx file. Then I pass this recordset to the Crystal Report and use the export functionality of the rdc to export to PDF.

I am having a strange problem. If I use a parameterised stored procedure, the report will not show any data. I checked the recordset. The recordset contains all the data I want. If I remove this parameter from the stored procedure, the report will show all the data. Why this strange behaviour? Please see the code below..

    Dim objRptOptions As ExportOptions
    Dim objCrysApp As New CRAXDRT.Application
    Dim objCrysRep As Report


    Dim adoCon As New ADODB.Connection
    Dim adoCom As New ADODB.Command
    Dim adoRec As ADODB.Recordset

    adoCon.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=AAMI_PARL_OCT_14_2002;Data Source=A02"
    adoCon.Open
   
    Set adoCom.ActiveConnection = adoCon
    adoCom.CommandText = "KKTESTASSETCLASS"
    adoCom.CommandType = adCmdStoredProc
    adoCom.Parameters(1).Value = 20

    Set adoRec = New ADODB.Recordset
    With adoRec
        .CursorType = adOpenKeyset
        .LockType = adLockBatchOptimistic
    End With
    'adoRec.Open "Select AssetClassId,AssetClassName From AssetClass where AssetClassId<" & intCount, adoCon, adOpenKeyset, adLockBatchOptimistic
    Set adoRec = adoCom.Execute()  '

    If adoRec.EOF Then MsgBox "No Data": Exit Sub
   
    objCrysRep.Database.Tables(1).SetDataSource adoRec

  Set objRptOptions = objCrysRep.ExportOptions
    With objRptOptions
        .DestinationType = 1
        .FormatType = &H1F '&H20
        .DiskFileName = strPDFStore & "\" & strPDFFile
    End With
    objCrysRep.DisplayProgressDialog = False
   
    objCrysRep.Export False

Regards
Krishna Kumar
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Why is this line commented out?

   'adoRec.Open "Select AssetClassId,AssetClassName From AssetClass where AssetClassId<" & intCount, adoCon, adOpenKeyset, adLockBatchOptimistic

Is that line changing the SELECT part of the procedure?

mlmcc
Dear mlmcc

That slect I used for testing. I created an sql statement and then executed to see whether the report works. If I use the connection object, the report works without any problem.

My stored procedure name is KKTESTASSETCLASS. I use a command object to execute the Stored Procedure. After around 4 hrs of trying, I added another line to the recordset properties. I changed the cursor location to the client and cursor type to static. After that the report worked.

But I really dont know why this behaviour is not documented in any of the manuals or in the crystal documents...

Regards
Krishna Kumar
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Glad you got it working.  If you feel I helped acept this as the answer.  If you feel you got the answer on your own, submit a 0 point question in the community support area requisting this question be deleted.

mlmcc
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Commented:
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately one week.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:
http://www.experts-exchange.com/help.jsp#hs5

mnye
EE Cleanup Volunteer
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Recommend PAQ.

mlmcc

Commented:
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

PAQ - no points refunded

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

mnye
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial