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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BennyBosticConnect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.