Hello all,

I hope someone can help me with this because this is driving me NUTS!



I'm calling a SQL Server 7 stored procedure from an ASP page using ADO.  I 

set the CursorLocation to adUseClient, the CursorType to adOpenStatic, and 

set the PageSize proprty to 10.  The recordset returned contains one 

record but the PageCount property still returns -1.  When I query



Does my connection to the database need to be *special* in any way? I'm 

utilizing an ODBC dsn connection - would this affect it?  The code works 

fine for MS Access database but not for SQL Server.  



Here is the code that I'm using in case it helps at all:



----

set oCmd = Server.CreateObject("ADODB.Command")

set RS = Server.CreateObject("ADODB.Recordset")

RS.CursorLocation = adUseClient

RS.CursorType = adOpenStatic

with oCmd

	.ActiveConnection = oConn

	.CommandText = "GetMessages"

	.CommandType = Application("adCmdStoredProc")

	

	'//Create and append all parameters

	.Parameters.Append .CreateParameter

("@ProgramID",adInteger,adParamInput,0)



	'//Assign parameter values

	.Parameters("@ProgramID") = 6

	Set RS = .execute

end with

set oCmd = Nothing



RS.PageSize = 10  

Response.Write("RS.PageCount = " & RS.PageCount)

----



Thanks!

Jenifer



------------------------------------------------------------------

Hi Jennifer, seeing your oConn code would help figure out why it works in 

access but not SQL. 



How many records should it return?? is 1 record the right answer.



If I remember correctly, you should use .createparamater before you use 

.append



dan

This is the code I always use for stored procedures:



Sub GetExtractID(ByVal Value) 

Dim adoCn 

Dim adoCmd 

Dim adoRs

 'Open connection to database. 

Set adoCn = Server.CreateObject("ADODB.Connection") 

adoCn.open ConnectString() 

' Set up command for stored procedure and parameters 

Set adoCmd = Server.CreateObject("ADODB.Command") 

With adoCmd 

.ActiveConnection = adoCn

 .CommandType = 4 ' adCmdStoredProc 

.CommandText = "gsp_ProcedureName" 

.Parameters("@Parameter") = Value

End With

 'Create recordset using the above preexisting connection 

Set adoRs = Server.CreateObject ("ADODB.Recordset") 

With adoRs 

.CursorLocation = 3 ' Client side 

.Open adoCmd 

End With 

	'work with recordset from here



'Clean Up 

adoRs.Close 

adoCn.Close 

Set adoRs = Nothing 

Set adoCmd = Nothing 

Set adoCn = Nothing 

End Sub



Josh King

GeoAccess Inc.

xxx.xxx.xxxx  x5237

xxx.xxx.xxxx  x5237



-----Original Message-----

From: jenifer@s... [mailto:jenifer@s...]

Sent: Wednesday, February 28, 2001 10:56 PM

To: ASP_ADO_RDS

Subject: [asp_ado_rds] PageCount property STILL returning -1 even with

adUseClient set



Hello all,

I hope someone can help me with this because this is driving me NUTS!



I'm calling a SQL Server 7 stored procedure from an ASP page using ADO.  I

set the CursorLocation to adUseClient, the CursorType to adOpenStatic, and

set the PageSize proprty to 10.  The recordset returned contains one

record but the PageCount property still returns -1.  When I query



Does my connection to the database need to be *special* in any way? I'm

utilizing an ODBC dsn connection - would this affect it?  The code works

fine for MS Access database but not for SQL Server. 



Here is the code that I'm using in case it helps at all:



----

set oCmd = Server.CreateObject("ADODB.Command")

set RS = Server.CreateObject("ADODB.Recordset")

RS.CursorLocation = adUseClient

RS.CursorType = adOpenStatic

with oCmd

        .ActiveConnection = oConn

        .CommandText = "GetMessages"

        .CommandType = Application("adCmdStoredProc")

       

        '//Create and append all parameters

        .Parameters.Append .CreateParameter

("@ProgramID",adInteger,adParamInput,0)



        '//Assign parameter values

        .Parameters("@ProgramID") = 6

        Set RS = .execute

end with

set oCmd = Nothing



RS.PageSize = 10 

Response.Write("RS.PageCount = " & RS.PageCount)

----



Thanks!

Jenifer



------------------------------------------------------------------

I want to thank Dan and Josh for their responses!  Using the .open method 

of the recordset object instead of the .execute method of the command 

object solved my problem!  Thanks - that was driving me nuts!!









> Hi Jennifer, seeing your oConn code would help figure out why it works 

in 

> access but not SQL. 

> 

> How many records should it return?? is 1 record the right answer.

> 

> If I remember correctly, you should use .createparamater before you use 

> .append

> 

> dan