VB Forum / Database Access / September 2004

Hello.  I am using VB 6 to execute a stored proc, which receives input
parameters.  I created a connection, command, and recordset with ADO.  The
CreatParamater method was used as metadata for type, direction, ect.  .  What
I don't know is if my parameters are not processed correctly, or if I am not
reading the recordset correctly.  My goal is to export the recodset into an
external file, but right now I am testing it by retrieving a recordCount.  
Although I am not receiving an error message, the MsgBox displays a
.RecordCount of -1.  How can this be?  I can execute the SP normally with no
problems.  The input paramters are set to the values of 2 text boxes on the
form.  If any one can shwo me what I am doing wrong, I thank you.  I think my
problem may be how I open my recordset.  Here is code:

Dim cnn1 As ADODB.Connection
Dim cmdProvider As ADODB.Command
Dim prmStartDate As ADODB.Parameter
Dim prmEndDate As ADODB.Parameter
Dim rstProvider As ADODB.Recordset
Dim strCnn As String

Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
     "Data Source=MSSQL;Initial Catalog=ClientData;User
Id=pwuser;Password=stlblues; "
cnn1.Open strCnn

Set cmdProvider = New ADODB.Command
Set cmdProvider.ActiveConnection = cnn1

cmdProvider.CommandText = "sp_ProviderLetterError"
cmdProvider.CommandType = adCmdStoredProc

Set prmStartDate = cmdProvider.CreateParameter("@prmStartDate", _
     adDate, adParamInput)
Set prmEndDate = cmdProvider.CreateParameter("@prmEndDate", _
     adDate, adParamInput)
cmdProvider.Parameters.Append prmStartDate
cmdProvider.Parameters.Append prmEndDate

prmStartDate.Value = Me.txtStartDate
prmEndDate.Value = Me.txtEndDate

Set cmdProvider.ActiveConnection = cnn1
Set rstProvider = cmdProvider.Execute

With rstProvider
       MsgBox "OK", vbOKOnly, "There are " & .RecordCount & " records on
this file"
End With
   
rstProvider.Close
cnn1.Close

My recordset always comes out as -1...why is this?  I have tested query
normally and there is data.  Perhaps another issue is my cursortype.

Toco