VB-Call to Paramater in SQL Server to open Crystal Reports

I have a Crystal Report with data based on a stored procedure that has several parameters, @DateCompleted and @DateReceived. I would like a form in VB to pass the parameter values to the stored procedure and then open the Crystal Report based on the data supplied. I assume that I need two controls on the form in VB to be the references for the parameters. Can someone supply some help in how I go about setting up this code to pass the parameter values to SQL Server from the VB form and then open the Crystal Report?
LVL 1
CareyMBilyeuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

manojaminCommented:
Read this for SQl Stored Procedures from VB

HOWTO: Retrieve Values in SQL Server Stored Procedures w/ ADO

http://support.microsoft.com/support/kb/articles/Q194/7/92.ASP

0
chris_aCommented:
Be carefull to test on several SQL servers, I have found problems where identical sql servers give different results for stored procedures, the trigger for the problems seems to be SPs with date aggregates!
0
BennyBosticCommented:
This example uses two date time picker controls to get the dates:

    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim conn as ADODB.Connection
    Dim vParameters(1 To 2) As Variant
   
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command
    Set conn = New ADODB.Connection

    conn.ConnectionString = funsConnectString
    conn.Open
    Set cmd.ActiveConnection = conn
           
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText ="sp_SQLStoredProc"
           
    vParameters(1) = dtpDateCompleted.Value
    vParameters(2) = dtpDateReceived.Value  
    Set rs = cmd.Execute(Parameters:=vParameters)
.....
' Move through your rs as usual.
.....
' Close all and release references.

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

BennyBosticCommented:
Also make sure that the order of the parameters are passed in the way they should be:
vParameters(1) = first parameter
vParameters(2) = second parameter

Didn't make that clear, sorry.

:^)
0
CareyMBilyeuAuthor Commented:
I have to decline the answer but will reaccept the answer based on the following question: Your answer references ADO Connections, however we are use ing (gasp) RDO. Other than the reference to ADO vs. RDO, does this make a difference??
0
BennyBosticCommented:
Private Sub subGetReport()

    Dim rs As rdoResultset
    Dim cn As New rdoConnection
    Dim qd As New rdoQuery
    Dim cl As rdoColumn
      
    cn.Connect = "Insert Connection String Here"
    cn.CursorDriver = rdUseOdbc
    cn.EstablishConnection rdDriverNoPrompt

    Set qd.ActiveConnection = cn
    qd.SQL = "{ ? = call dbo.ByRoyalty (?) }"
   
    qd(0).Direction = rdParamReturnValue
    qd(1).Direction = rdParamInput
    qd(2).Direction = rdParamInput


    qd.rdoParameters(1) = dtpDateCompleted.Value
    qd.rdoParameters(2) =     dtpDateReceived.Value

    Set rs = qd.OpenResultset  (rdOpenForwardOnly, rdConcurReadOnly)

    For Each cl In rs.rdoColumns
        Debug.Print cl.Name,
    Next
    Debug.Print

   Do Until rs.EOF
     For Each cl In rs.rdoColumns
        Debug.Print cl.Value,
          Next
          rs.MoveNext
          Debug.Print
    Loop

    rs.Close
    qd.Close
    cn.Close

End Sub

For future reference:
<a HREF = "http://support.microsoft.com/support/kb/articles/Q166/2/11.ASP">Using RDO to retrieve data from parameterized stored procedures.</a>

<a HREF = "http://support.microsoft.com/support/kb/articles/Q228/8/00.ASP">RDO and parameterized stored procedures.</a>
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CareyMBilyeuAuthor Commented:
Benny, I accept the answer but have a question. This is working fine, it outputs to the immediate window, but how do I get it to supply the Crystal Report with the same data? That was the original request, to supply the data to Crystal Reports.

Appreciate your help.

Carey
0
BennyBosticCommented:
I'm sorry to say that I can't help much with Crystal Reports, never used it.  But, the data is there, you see where it prints to the immediate window, that is where you send it to Crystal Reports.  If you're using a data bound then just set all the reports data properties after opening the returned recordset.  Sorry I can't help more.
0
BennyBosticCommented:
I'm sorry to say that I can't help much with Crystal Reports, never used it.  But, the data is there, you see where it prints to the immediate window, that is where you send it to Crystal Reports.  If you're using a data bound then just set all the reports data properties after opening the returned recordset.  Sorry I can't help more.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.