Stored Procedure with parameters returns empty recordset

[jbradley]

I have a stored procedure:

CREATE PROCEDURE [spGroupProduction]
@StartDate smalldatetime, @EndDate smalldatetime
AS
SELECT Metrix.TRANSID, Metrix.EMPID, Metrix.[Date], 
    Metrix.Account, Metrix.Category, Metrix.TransType, 
    Metrix.Time, Metrix.Department, Metrix.Unit, Metrix.InputDate, 
    Standard.Standard
FROM Metrix LEFT JOIN
    Standard ON Metrix.Category = Standard.Category
WHERE
   Metrix.[Date] BETWEEN @StartDate AND @EndDate

I'm calling this from a VB6 program.

Dim cn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim dtStartDate As Date
Dim dtEndDate As Date

    dtStartDate = DTPicker1.Value
    dtEndDate = DTPicker2.Value
    
    strConnection = "Provider=SQLOLEDB.1;" & _
                    "Integrated Security=SSPI;" & _
                    "Persist Security Info=False;" & _
                    "Initial Catalog=ToolsBars;" & _
                    "Data Source=bocasql1"
    
    Set cn = New ADODB.Connection
        cn.ConnectionString = strConnection
        cn.CursorLocation = adUseClient
    cn.Open
    
    Set rs = New ADODB.Recordset
    
    Set Cmd = New ADODB.Command
    With Cmd
        .ActiveConnection = cn
        .CommandType = adCmdStoredProc
        .CommandText = "spGroupProduction"
        .Parameters.Refresh
        .Parameters(1).Value = dtStartDate
        .Parameters(2).Value = dtEndDate
        Set rs = .Execute()
    End With
    
    Set DataGrid1.DataSource = rs

    Set Cmd = Nothing
    Set cn = Nothing

If in the stored procedure I specify default values for the parameters then when I call the it I get all the expected records returned. If I try to pass the values to the parameters from my program then a recordset is returned (I'm using it to populate a DataGrid and I get the field names in the column headers) but it is empty.

What am I missing here? Any assistance will be greatly appreciated...

Brad

 

[uberskunk]

I can't say exactly why yours isn't working as it seems a very logical way of running a store procedure but have you tried passing the parameters in the .commandtext?

.CommandText = "spGroupProduction " & dtStartDate & ", " & dtEndDate

Also

Try adding
Msgbox rs.recordcount
this will see if the store procedure is infact returning any data.

And one last idea try
DataGrid.refresh

(You never know)

This may work, but hopefully it will move in the right direction.

 

[jbradley]

The problem turned out to be with the age of the data in the test table I was working against. With the default values I was using, 1/1/00 and the current date for the start and end values, I was getting records back. What I failed to notice was that it was all old, so when I passed in values like 4/1/03 and 4/15/03 and didn't get any records I assumed my query was at fault, not that there just wasn't any data that met the criteria.