Re: Help Getting .RecordCount from Stored Proc

From: Val Mazur (group51a_at_hotmail.com)
Date: 09/30/04 

Date: Wed, 29 Sep 2004 23:12:30 -0400

Hi,

Check next KB about it

http://support.microsoft.com/default.aspx?scid=kb;en-us;194973 

-- 
Val Mazur
Microsoft MVP
"Toco" <Toco@discussions.microsoft.com> wrote in message 
news:5AEDA990-21E0-4878-A06B-E3669AACE2E2@microsoft.com...
> 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
>

Relevant Pages

  • Re: DAO peculiarity in A97?
    ... If BOF is True then you won't execute the statements inside the ... If..Then becuase you have told it to continue only if the opposite of BOF is ... only thing you do with it is open a recordset. ... This will force you to DIM each variable you ...
    (comp.databases.ms-access)
  • Re: How to display a query in a list box using a record set?
    ... jim Bob wrote: ... I need to execute ... > Dim rs As Recordset ... > ways of opening/creating a recordset. ...
    (comp.databases.ms-access)
  • Re: ADO cloned recordset doesnt return field names
    ... It had been awhile since I worked with ADO. ... need to specifically loop through the recordset and use ".Name" to get at the ... >> I am using the following function to execute an ADO query. ... > Dim rs As ADODB.Recordset ...
    (microsoft.public.excel.programming)
  • Re: Using a command object as source for recordset
    ... Dim cnn1 As ADODB.Connection ... Dim cmdByRoyalty As ADODB.Command ... ' Open command object with one parameter. ... ' Create recordset by executing the command. ...
    (microsoft.public.vb.database.ado)
  • Help Getting .RecordCount from Stored Proc
    ... I am using VB 6 to execute a stored proc, ... I created a connection, command, and recordset with ADO. ... Dim cnn1 As ADODB.Connection ... Dim cmdProvider As ADODB.Command ...
    (microsoft.public.vb.database.ado)
Quantcast