ADO recordset open method - pagecount -1

[tony]

Hi

I have used ADO recordset open method on a ASP page
for the sql command string its a name of a Stored Proc as follows



Set conn = Server.CreateObject("ADODB.connection")
Set RS_Requests = Server.CreateObject("ADODB.Recordset")
Conn.Open Application("emp_ConnectionString")

SQLStr = "sp_Emp_ListActiveRequests"

RS_Requests.Open SQLStr,conn,3

Problem is RS_Requests.pagecount and RS_Requests.recordcount is always -1
but when I use plain SQL like "select * from employers" for the open method
pagecount and recordcount is given correctly.

any ideas?

thanks in advance
TS

[Aaron Bertrand [MVP]]

For the recordcount = -1 issue:
[url]http://www.aspfaq.com/2193[/url]

For better paging techniques:
[url]http://www.aspfaq.com/2120[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"tony" <dd@sdfsf.com> wrote in message
news:ur0xVP93DHA.2296@TK2MSFTNGP11.phx.gbl...

> Hi
>
> I have used ADO recordset open method on a ASP page
> for the sql command string its a name of a Stored Proc as follows
>
>
>
> Set conn = Server.CreateObject("ADODB.connection")
> Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> Conn.Open Application("emp_ConnectionString")
>
> SQLStr = "sp_Emp_ListActiveRequests"
>
> RS_Requests.Open SQLStr,conn,3
>
> Problem is RS_Requests.pagecount and RS_Requests.recordcount is always -1
> but when I use plain SQL like "select * from employers" for the open

method

> pagecount and recordcount is given correctly.
>
> any ideas?
>
> thanks in advance
> TS
>
>

[tony]

Hi Aaron,

Thanks for your post, I'd read that but,
I 'm not using rowcount in my stored proc. theres only one select
query in the SP which is built dynamically.
As normal sql select statement is there any reason why I cant get the
pagecount
when using a SP in the Rs.open method.

thanks
TS




"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:OF6Tgh93DHA.1404@TK2MSFTNGP11.phx.gbl...

> For the recordcount = -1 issue:
> [url]http://www.aspfaq.com/2193[/url]
>
> For better paging techniques:
> [url]http://www.aspfaq.com/2120[/url]
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "tony" <dd@sdfsf.com> wrote in message
> news:ur0xVP93DHA.2296@TK2MSFTNGP11.phx.gbl...

> > Hi
> >
> > I have used ADO recordset open method on a ASP page
> > for the sql command string its a name of a Stored Proc as follows
> >
> >
> >
> > Set conn = Server.CreateObject("ADODB.connection")
> > Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> > Conn.Open Application("emp_ConnectionString")
> >
> > SQLStr = "sp_Emp_ListActiveRequests"
> >
> > RS_Requests.Open SQLStr,conn,3
> >
> > Problem is RS_Requests.pagecount and RS_Requests.recordcount is

always -1

> > but when I use plain SQL like "select * from employers" for the open

> method

> > pagecount and recordcount is given correctly.
> >
> > any ideas?
> >
> > thanks in advance
> > TS
> >
> >

>
>

[Aaron Bertrand [MVP]]

You're opening rs.open with merely a , 3 parameter making the cursor type
adOpenStatic. This might work, but I usually see ,1,1 which makes it an
adOpenKeyset cursor, and an adLockReadOnly locktype.

I pointed you to the article to see that there are better ways to implement
paging, not to help you solve the issue and keep using the inefficient way.
:-) You really should give it a read, instead of blindly continuing in your
present approach...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"tony" <dd@sdfsf.com> wrote in message
news:O19rpr93DHA.1428@TK2MSFTNGP12.phx.gbl...

> Hi Aaron,
>
> Thanks for your post, I'd read that but,
> I 'm not using rowcount in my stored proc. theres only one select
> query in the SP which is built dynamically.
> As normal sql select statement is there any reason why I cant get the
> pagecount
> when using a SP in the Rs.open method.
>
> thanks
> TS
>
>
>
>
> "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
> news:OF6Tgh93DHA.1404@TK2MSFTNGP11.phx.gbl...

> > For the recordcount = -1 issue:
> > [url]http://www.aspfaq.com/2193[/url]
> >
> > For better paging techniques:
> > [url]http://www.aspfaq.com/2120[/url]
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > [url]http://www.aspfaq.com/[/url]
> >
> >
> >
> >
> > "tony" <dd@sdfsf.com> wrote in message
> > news:ur0xVP93DHA.2296@TK2MSFTNGP11.phx.gbl...

> > > Hi
> > >
> > > I have used ADO recordset open method on a ASP page
> > > for the sql command string its a name of a Stored Proc as follows
> > >
> > >
> > >
> > > Set conn = Server.CreateObject("ADODB.connection")
> > > Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> > > Conn.Open Application("emp_ConnectionString")
> > >
> > > SQLStr = "sp_Emp_ListActiveRequests"
> > >
> > > RS_Requests.Open SQLStr,conn,3
> > >
> > > Problem is RS_Requests.pagecount and RS_Requests.recordcount is

> always -1

> > > but when I use plain SQL like "select * from employers" for the open

> > method

> > > pagecount and recordcount is given correctly.
> > >
> > > any ideas?
> > >
> > > thanks in advance
> > > TS
> > >
> > >

> >
> >

>
>

[tony]

Hi Aaron,

Thanks , I donno why them/ microsoft has implemented that pagecount and
recordcount properties in recordset object/model.
And there is no documentation on different behaviours when use with stored
proc.
currently I 'm modifying a project which has already been done by another
developer
we aren't allowed to indulge our selves in butyfiying codes strictly
budgeted project.

Sad sceene again.

thanks
TS










"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:#YV$py93DHA.2608@TK2MSFTNGP09.phx.gbl...

> You're opening rs.open with merely a , 3 parameter making the cursor type
> adOpenStatic. This might work, but I usually see ,1,1 which makes it an
> adOpenKeyset cursor, and an adLockReadOnly locktype.
>
> I pointed you to the article to see that there are better ways to

implement

> paging, not to help you solve the issue and keep using the inefficient

way.

> :-) You really should give it a read, instead of blindly continuing in

your

> present approach...
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>
>
>
> "tony" <dd@sdfsf.com> wrote in message
> news:O19rpr93DHA.1428@TK2MSFTNGP12.phx.gbl...

> > Hi Aaron,
> >
> > Thanks for your post, I'd read that but,
> > I 'm not using rowcount in my stored proc. theres only one select
> > query in the SP which is built dynamically.
> > As normal sql select statement is there any reason why I cant get the
> > pagecount
> > when using a SP in the Rs.open method.
> >
> > thanks
> > TS
> >
> >
> >
> >
> > "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
> > news:OF6Tgh93DHA.1404@TK2MSFTNGP11.phx.gbl...

> > > For the recordcount = -1 issue:
> > > [url]http://www.aspfaq.com/2193[/url]
> > >
> > > For better paging techniques:
> > > [url]http://www.aspfaq.com/2120[/url]
> > >
> > > --
> > > Aaron Bertrand
> > > SQL Server MVP
> > > [url]http://www.aspfaq.com/[/url]
> > >
> > >
> > >
> > >
> > > "tony" <dd@sdfsf.com> wrote in message
> > > news:ur0xVP93DHA.2296@TK2MSFTNGP11.phx.gbl...
> > > > Hi
> > > >
> > > > I have used ADO recordset open method on a ASP page
> > > > for the sql command string its a name of a Stored Proc as follows
> > > >
> > > >
> > > >
> > > > Set conn = Server.CreateObject("ADODB.connection")
> > > > Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> > > > Conn.Open Application("emp_ConnectionString")
> > > >
> > > > SQLStr = "sp_Emp_ListActiveRequests"
> > > >
> > > > RS_Requests.Open SQLStr,conn,3
> > > >
> > > > Problem is RS_Requests.pagecount and RS_Requests.recordcount is

> > always -1

> > > > but when I use plain SQL like "select * from employers" for the open
> > > method
> > > > pagecount and recordcount is given correctly.
> > > >
> > > > any ideas?
> > > >
> > > > thanks in advance
> > > > TS
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[Brynn]

Try paging with my DBConn.asp script here ...

[url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]

<%
Dim yourArray, totalPages
Dim pageNum, perPage

pageNum = Request.QueryString("pagenum")
If pageNum = "" Then: pageNum=1: Else: pageNum=Int(pageNum): End If
perPage = 20

cp_TheConnectionString = "yourConnectionString"
Call cp_DBConn("open")
yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
perPage)
Call cp_DBConn("close")

For theRows = 0 to ubound(yourArray,2)
For theColumns = 0 to ubound(yourArray,1)

Next
Next
%>


Brynn
[url]www.coolpier.com[/url]


On Wed, 21 Jan 2004 10:05:37 +0600, "tony" <dd@sdfsf.com> wrote:

>Hi
>
>I have used ADO recordset open method on a ASP page
>for the sql command string its a name of a Stored Proc as follows
>
>
>
>Set conn = Server.CreateObject("ADODB.connection")
>Set RS_Requests = Server.CreateObject("ADODB.Recordset")
>Conn.Open Application("emp_ConnectionString")
>
>SQLStr = "sp_Emp_ListActiveRequests"
>
>RS_Requests.Open SQLStr,conn,3
>
>Problem is RS_Requests.pagecount and RS_Requests.recordcount is always -1
>but when I use plain SQL like "select * from employers" for the open method
>pagecount and recordcount is given correctly.
>
>any ideas?
>
>thanks in advance
>TS
>
>

Brynn
[url]www.coolpier.com[/url]

I participate in the group to help give examples of code.
I do not guarantee the effects of any code posted.
Test all code before use!

[tony]

Hi Brynn

Thanks for the post.

>yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
> perPage)

Can I use a stored proc instead of plain sql query up there?

regds
TS


"Brynn" <z@z.com> wrote in message
news:400e36fc.2180956@news.comcast.giganews.com...

>
> Try paging with my DBConn.asp script here ...
>
> [url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]
>
> <%
> Dim yourArray, totalPages
> Dim pageNum, perPage
>
> pageNum = Request.QueryString("pagenum")
> If pageNum = "" Then: pageNum=1: Else: pageNum=Int(pageNum): End If
> perPage = 20
>
> cp_TheConnectionString = "yourConnectionString"
> Call cp_DBConn("open")
> yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
> perPage)
> Call cp_DBConn("close")
>
> For theRows = 0 to ubound(yourArray,2)
> For theColumns = 0 to ubound(yourArray,1)
>
> Next
> Next
> %>
>
>
> Brynn
> [url]www.coolpier.com[/url]
>
>
> On Wed, 21 Jan 2004 10:05:37 +0600, "tony" <dd@sdfsf.com> wrote:
>

> >Hi
> >
> >I have used ADO recordset open method on a ASP page
> >for the sql command string its a name of a Stored Proc as follows
> >
> >
> >
> >Set conn = Server.CreateObject("ADODB.connection")
> >Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> >Conn.Open Application("emp_ConnectionString")
> >
> >SQLStr = "sp_Emp_ListActiveRequests"
> >
> >RS_Requests.Open SQLStr,conn,3
> >
> >Problem is RS_Requests.pagecount and RS_Requests.recordcount is always -1
> >but when I use plain SQL like "select * from employers" for the open

method

> >pagecount and recordcount is given correctly.
> >
> >any ideas?
> >
> >thanks in advance
> >TS
> >
> >

>
> Brynn
> [url]www.coolpier.com[/url]
>
> I participate in the group to help give examples of code.
> I do not guarantee the effects of any code posted.
> Test all code before use!

[CJM]

If you can use a "Select *.." query you can aways use an SP:

yourArray = cp_SqlArrayPaging("exec myStoredProc", pageNum,
perPage)

CJM

"tony" <dd@sdfsf.com> wrote in message
news:ext2yDA4DHA.876@TK2MSFTNGP10.phx.gbl...

> Hi Brynn
>
> Thanks for the post.
>

> >yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
> > perPage)

>
> Can I use a stored proc instead of plain sql query up there?
>
> regds
> TS
>
>
> "Brynn" <z@z.com> wrote in message
> news:400e36fc.2180956@news.comcast.giganews.com...

> >
> > Try paging with my DBConn.asp script here ...
> >
> >

[url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]

> >
> > <%
> > Dim yourArray, totalPages
> > Dim pageNum, perPage
> >
> > pageNum = Request.QueryString("pagenum")
> > If pageNum = "" Then: pageNum=1: Else: pageNum=Int(pageNum): End If
> > perPage = 20
> >
> > cp_TheConnectionString = "yourConnectionString"
> > Call cp_DBConn("open")
> > yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
> > perPage)
> > Call cp_DBConn("close")
> >
> > For theRows = 0 to ubound(yourArray,2)
> > For theColumns = 0 to ubound(yourArray,1)
> >
> > Next
> > Next
> > %>
> >
> >
> > Brynn
> > [url]www.coolpier.com[/url]
> >
> >
> > On Wed, 21 Jan 2004 10:05:37 +0600, "tony" <dd@sdfsf.com> wrote:
> >

> > >Hi
> > >
> > >I have used ADO recordset open method on a ASP page
> > >for the sql command string its a name of a Stored Proc as follows
> > >
> > >
> > >
> > >Set conn = Server.CreateObject("ADODB.connection")
> > >Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> > >Conn.Open Application("emp_ConnectionString")
> > >
> > >SQLStr = "sp_Emp_ListActiveRequests"
> > >
> > >RS_Requests.Open SQLStr,conn,3
> > >
> > >Problem is RS_Requests.pagecount and RS_Requests.recordcount is

always -1

> > >but when I use plain SQL like "select * from employers" for the open

> method

> > >pagecount and recordcount is given correctly.
> > >
> > >any ideas?
> > >
> > >thanks in advance
> > >TS
> > >
> > >

> >
> > Brynn
> > [url]www.coolpier.com[/url]
> >
> > I participate in the group to help give examples of code.
> > I do not guarantee the effects of any code posted.
> > Test all code before use!

>
>

[Mike Collier]

> but when I use plain SQL like "select * from employers" for the open
method

> pagecount and recordcount is given correctly

AdoAnywhere highlites all these issues.

FAQ Record Count = -1
[url]http://81.130.213.94/myforum/forum_posts.asp?TID=72&PN=1[/url]

--
Mike Collier BSc( Hons) Comp Sci
Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
forum.
[url]http://www.adoanywhere.com/forum[/url]

[Brynn]

yep, in my script, you can use a stored procedure ...

'// INSERT, UPDATE, DELETE, -or- SP not returning recordset
cp_SqlExecute(yourSQL)

'//SELECT -or- SP returning recordset

yourArray = cp_SqlArray(yourSQL)
yourArray = cp_SqlArray_Paging(yourSQL, pageNum, perPage)


ALSO, let me add this ...

If IsArray(yourArray) Then
For theRows = 0 to ubound(yourArray,2)
For theColumns = 0 to ubound(yourArray,1)
Next
Next
Else
'//No records found code
End If


The nice thing about using my script like this is that you get in and
out of the database connection fast.
The database connection is closed ... AND ... there is never an open
recordset object while working with the data.

Brynn
[url]www.coolpier.com[/url]




On Wed, 21 Jan 2004 15:28:31 +0600, "tony" <dd@sdfsf.com> wrote:

>Hi Brynn
>
>Thanks for the post.
>

>>yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
>> perPage)

>
>Can I use a stored proc instead of plain sql query up there?
>
>regds
>TS
>
>
>"Brynn" <z@z.com> wrote in message
>news:400e36fc.2180956@news.comcast.giganews.com.. .

>>
>> Try paging with my DBConn.asp script here ...
>>
>> [url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]
>>
>> <%
>> Dim yourArray, totalPages
>> Dim pageNum, perPage
>>
>> pageNum = Request.QueryString("pagenum")
>> If pageNum = "" Then: pageNum=1: Else: pageNum=Int(pageNum): End If
>> perPage = 20
>>
>> cp_TheConnectionString = "yourConnectionString"
>> Call cp_DBConn("open")
>> yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
>> perPage)
>> Call cp_DBConn("close")
>>
>> For theRows = 0 to ubound(yourArray,2)
>> For theColumns = 0 to ubound(yourArray,1)
>>
>> Next
>> Next
>> %>
>>
>>
>> Brynn
>> [url]www.coolpier.com[/url]
>>
>>
>> On Wed, 21 Jan 2004 10:05:37 +0600, "tony" <dd@sdfsf.com> wrote:
>>

>> >Hi
>> >
>> >I have used ADO recordset open method on a ASP page
>> >for the sql command string its a name of a Stored Proc as follows
>> >
>> >
>> >
>> >Set conn = Server.CreateObject("ADODB.connection")
>> >Set RS_Requests = Server.CreateObject("ADODB.Recordset")
>> >Conn.Open Application("emp_ConnectionString")
>> >
>> >SQLStr = "sp_Emp_ListActiveRequests"
>> >
>> >RS_Requests.Open SQLStr,conn,3
>> >
>> >Problem is RS_Requests.pagecount and RS_Requests.recordcount is always -1
>> >but when I use plain SQL like "select * from employers" for the open

>method

>> >pagecount and recordcount is given correctly.
>> >
>> >any ideas?
>> >
>> >thanks in advance
>> >TS
>> >
>> >

>>
>> Brynn
>> [url]www.coolpier.com[/url]
>>
>> I participate in the group to help give examples of code.
>> I do not guarantee the effects of any code posted.
>> Test all code before use!

>
>

Brynn
[url]www.coolpier.com[/url]

I participate in the group to help give examples of code.
I do not guarantee the effects of any code posted.
Test all code before use!

[tony]

Hi Mike

Requirement is on ASP page which renders html to the browser (web
application 2 tier).
What will be the ideal Cursor location and ideal cursor type?

thanks & regards

TS




"Mike Collier" <mike@adoanywhere.com> wrote in message
news:OxAJ2aA4DHA.1804@TK2MSFTNGP12.phx.gbl...

> > but when I use plain SQL like "select * from employers" for the open

> method

> > pagecount and recordcount is given correctly

>
> AdoAnywhere highlites all these issues.
>
> FAQ Record Count = -1
> [url]http://81.130.213.94/myforum/forum_posts.asp?TID=72&PN=1[/url]
>
> --
> Mike Collier BSc( Hons) Comp Sci
> Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
> forum.
> [url]http://www.adoanywhere.com/forum[/url]
>
>

[tony]

Thanks Brynn

However anyideas why when stored proc is used in a recordset open method
it doesn't give correct recordcount and pagecount properties.

I 've Tested different cursor types.

regds
TS



"Brynn" <z@z.com> wrote in message
news:400e50d8.8801465@news.comcast.giganews.com...

>
> yep, in my script, you can use a stored procedure ...
>
> '// INSERT, UPDATE, DELETE, -or- SP not returning recordset
> cp_SqlExecute(yourSQL)
>
> '//SELECT -or- SP returning recordset
>
> yourArray = cp_SqlArray(yourSQL)
> yourArray = cp_SqlArray_Paging(yourSQL, pageNum, perPage)
>
>
> ALSO, let me add this ...
>
> If IsArray(yourArray) Then
> For theRows = 0 to ubound(yourArray,2)
> For theColumns = 0 to ubound(yourArray,1)
> Next
> Next
> Else
> '//No records found code
> End If
>
>
> The nice thing about using my script like this is that you get in and
> out of the database connection fast.
> The database connection is closed ... AND ... there is never an open
> recordset object while working with the data.
>
> Brynn
> [url]www.coolpier.com[/url]
>
>
>
>
> On Wed, 21 Jan 2004 15:28:31 +0600, "tony" <dd@sdfsf.com> wrote:
>

> >Hi Brynn
> >
> >Thanks for the post.
> >

> >>yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
> >> perPage)

> >
> >Can I use a stored proc instead of plain sql query up there?
> >
> >regds
> >TS
> >
> >
> >"Brynn" <z@z.com> wrote in message
> >news:400e36fc.2180956@news.comcast.giganews.com.. .

> >>
> >> Try paging with my DBConn.asp script here ...
> >>
> >>

[url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]

> >>
> >> <%
> >> Dim yourArray, totalPages
> >> Dim pageNum, perPage
> >>
> >> pageNum = Request.QueryString("pagenum")
> >> If pageNum = "" Then: pageNum=1: Else: pageNum=Int(pageNum): End If
> >> perPage = 20
> >>
> >> cp_TheConnectionString = "yourConnectionString"
> >> Call cp_DBConn("open")
> >> yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
> >> perPage)
> >> Call cp_DBConn("close")
> >>
> >> For theRows = 0 to ubound(yourArray,2)
> >> For theColumns = 0 to ubound(yourArray,1)
> >>
> >> Next
> >> Next
> >> %>
> >>
> >>
> >> Brynn
> >> [url]www.coolpier.com[/url]
> >>
> >>
> >> On Wed, 21 Jan 2004 10:05:37 +0600, "tony" <dd@sdfsf.com> wrote:
> >>
> >> >Hi
> >> >
> >> >I have used ADO recordset open method on a ASP page
> >> >for the sql command string its a name of a Stored Proc as follows
> >> >
> >> >
> >> >
> >> >Set conn = Server.CreateObject("ADODB.connection")
> >> >Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> >> >Conn.Open Application("emp_ConnectionString")
> >> >
> >> >SQLStr = "sp_Emp_ListActiveRequests"
> >> >
> >> >RS_Requests.Open SQLStr,conn,3
> >> >
> >> >Problem is RS_Requests.pagecount and RS_Requests.recordcount is

always -1

> >> >but when I use plain SQL like "select * from employers" for the open

> >method

> >> >pagecount and recordcount is given correctly.
> >> >
> >> >any ideas?
> >> >
> >> >thanks in advance
> >> >TS
> >> >
> >> >
> >>
> >> Brynn
> >> [url]www.coolpier.com[/url]
> >>
> >> I participate in the group to help give examples of code.
> >> I do not guarantee the effects of any code posted.
> >> Test all code before use!

> >
> >

>
> Brynn
> [url]www.coolpier.com[/url]
>
> I participate in the group to help give examples of code.
> I do not guarantee the effects of any code posted.
> Test all code before use!

[Brynn]

[url]http://www.aspfaq.com/show.asp?id=2193[/url]

Beyond Aaron's article ... I am not sure. I still think that the
performance of your page will increase if you get rid of the recordset

It would be very easy to implement my code

the variables that you have for the rs fields would just
field1 = yourArray(0,theRows)
field2 = yourArray(1,theRows)
etc...

just get rid of theColumns loop and use the above

Brynn
[url]www.coolpier.com[/url]


On Wed, 21 Jan 2004 18:03:06 +0600, "tony" <dd@sdfsf.com> wrote:

>Thanks Brynn
>
>However anyideas why when stored proc is used in a recordset open method
>it doesn't give correct recordcount and pagecount properties.
>
>I 've Tested different cursor types.
>
>regds
>TS
>
>
>
>"Brynn" <z@z.com> wrote in message
>news:400e50d8.8801465@news.comcast.giganews.com.. .

>>
>> yep, in my script, you can use a stored procedure ...
>>
>> '// INSERT, UPDATE, DELETE, -or- SP not returning recordset
>> cp_SqlExecute(yourSQL)
>>
>> '//SELECT -or- SP returning recordset
>>
>> yourArray = cp_SqlArray(yourSQL)
>> yourArray = cp_SqlArray_Paging(yourSQL, pageNum, perPage)
>>
>>
>> ALSO, let me add this ...
>>
>> If IsArray(yourArray) Then
>> For theRows = 0 to ubound(yourArray,2)
>> For theColumns = 0 to ubound(yourArray,1)
>> Next
>> Next
>> Else
>> '//No records found code
>> End If
>>
>>
>> The nice thing about using my script like this is that you get in and
>> out of the database connection fast.
>> The database connection is closed ... AND ... there is never an open
>> recordset object while working with the data.
>>
>> Brynn
>> [url]www.coolpier.com[/url]
>>
>>
>>
>>
>> On Wed, 21 Jan 2004 15:28:31 +0600, "tony" <dd@sdfsf.com> wrote:
>>

>> >Hi Brynn
>> >
>> >Thanks for the post.
>> >
>> >>yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
>> >> perPage)
>> >
>> >Can I use a stored proc instead of plain sql query up there?
>> >
>> >regds
>> >TS
>> >
>> >
>> >"Brynn" <z@z.com> wrote in message
>> >news:400e36fc.2180956@news.comcast.giganews.com.. .
>> >>
>> >> Try paging with my DBConn.asp script here ...
>> >>
>> >>

>[url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]

>> >>
>> >> <%
>> >> Dim yourArray, totalPages
>> >> Dim pageNum, perPage
>> >>
>> >> pageNum = Request.QueryString("pagenum")
>> >> If pageNum = "" Then: pageNum=1: Else: pageNum=Int(pageNum): End If
>> >> perPage = 20
>> >>
>> >> cp_TheConnectionString = "yourConnectionString"
>> >> Call cp_DBConn("open")
>> >> yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
>> >> perPage)
>> >> Call cp_DBConn("close")
>> >>
>> >> For theRows = 0 to ubound(yourArray,2)
>> >> For theColumns = 0 to ubound(yourArray,1)
>> >>
>> >> Next
>> >> Next
>> >> %>
>> >>
>> >>
>> >> Brynn
>> >> [url]www.coolpier.com[/url]
>> >>
>> >>
>> >> On Wed, 21 Jan 2004 10:05:37 +0600, "tony" <dd@sdfsf.com> wrote:
>> >>
>> >> >Hi
>> >> >
>> >> >I have used ADO recordset open method on a ASP page
>> >> >for the sql command string its a name of a Stored Proc as follows
>> >> >
>> >> >
>> >> >
>> >> >Set conn = Server.CreateObject("ADODB.connection")
>> >> >Set RS_Requests = Server.CreateObject("ADODB.Recordset")
>> >> >Conn.Open Application("emp_ConnectionString")
>> >> >
>> >> >SQLStr = "sp_Emp_ListActiveRequests"
>> >> >
>> >> >RS_Requests.Open SQLStr,conn,3
>> >> >
>> >> >Problem is RS_Requests.pagecount and RS_Requests.recordcount is

>always -1

>> >> >but when I use plain SQL like "select * from employers" for the open
>> >method
>> >> >pagecount and recordcount is given correctly.
>> >> >
>> >> >any ideas?
>> >> >
>> >> >thanks in advance
>> >> >TS
>> >> >
>> >> >
>> >>
>> >> Brynn
>> >> [url]www.coolpier.com[/url]
>> >>
>> >> I participate in the group to help give examples of code.
>> >> I do not guarantee the effects of any code posted.
>> >> Test all code before use!
>> >
>> >

>>
>> Brynn
>> [url]www.coolpier.com[/url]
>>
>> I participate in the group to help give examples of code.
>> I do not guarantee the effects of any code posted.
>> Test all code before use!

>
>

Brynn
[url]www.coolpier.com[/url]

I participate in the group to help give examples of code.
I do not guarantee the effects of any code posted.
Test all code before use!

[Aaron Bertrand [MVP]]

> we aren't allowed to indulge our selves in butyfiying codes strictly

> budgeted project.

Maybe not, but you need to get it working, right?

Note that most of the solutions in the article would require very little
re-writing of code...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Mike Collier]

> What will be the ideal Cursor location and ideal cursor type?

For stored procedures I coudn't say wthout connecting to the data, since
their are too many ways to skin the cat - and I would n't want to miss any
option. Sorry I can't be more specific on this one.

--
Mike Collier BSc( Hons) Comp Sci
Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
forum.
[url]http://www.adoanywhere.com/forum[/url]

[Ken Schaefer]

Because you are opening a ForwardOnly cursor (check the cursor type *after*
you open the recordset).

ForwardOnly cursor does not support RecordCount/PageCount etc

In any case, they are *very* expensive properties, and performance sucks if
you use them.

Cheers
Ken


"tony" <dd@sdfsf.com> wrote in message
news:uJ%238KaB4DHA.2136@TK2MSFTNGP12.phx.gbl...
: Thanks Brynn
:
: However anyideas why when stored proc is used in a recordset open method
: it doesn't give correct recordcount and pagecount properties.
:
: I 've Tested different cursor types.
:
: regds
: TS
:
:
:
: "Brynn" <z@z.com> wrote in message
: news:400e50d8.8801465@news.comcast.giganews.com...
: >
: > yep, in my script, you can use a stored procedure ...
: >
: > '// INSERT, UPDATE, DELETE, -or- SP not returning recordset
: > cp_SqlExecute(yourSQL)
: >
: > '//SELECT -or- SP returning recordset
: >
: > yourArray = cp_SqlArray(yourSQL)
: > yourArray = cp_SqlArray_Paging(yourSQL, pageNum, perPage)
: >
: >
: > ALSO, let me add this ...
: >
: > If IsArray(yourArray) Then
: > For theRows = 0 to ubound(yourArray,2)
: > For theColumns = 0 to ubound(yourArray,1)
: > Next
: > Next
: > Else
: > '//No records found code
: > End If
: >
: >
: > The nice thing about using my script like this is that you get in and
: > out of the database connection fast.
: > The database connection is closed ... AND ... there is never an open
: > recordset object while working with the data.
: >
: > Brynn
: > [url]www.coolpier.com[/url]
: >
: >
: >
: >
: > On Wed, 21 Jan 2004 15:28:31 +0600, "tony" <dd@sdfsf.com> wrote:
: >
: > >Hi Brynn
: > >
: > >Thanks for the post.
: > >
: > >>yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
: > >> perPage)
: > >
: > >Can I use a stored proc instead of plain sql query up there?
: > >
: > >regds
: > >TS
: > >
: > >
: > >"Brynn" <z@z.com> wrote in message
: > >news:400e36fc.2180956@news.comcast.giganews.com.. .
: > >>
: > >> Try paging with my DBConn.asp script here ...
: > >>
: > >>
: [url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]
: > >>
: > >> <%
: > >> Dim yourArray, totalPages
: > >> Dim pageNum, perPage
: > >>
: > >> pageNum = Request.QueryString("pagenum")
: > >> If pageNum = "" Then: pageNum=1: Else: pageNum=Int(pageNum): End If
: > >> perPage = 20
: > >>
: > >> cp_TheConnectionString = "yourConnectionString"
: > >> Call cp_DBConn("open")
: > >> yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
: > >> perPage)
: > >> Call cp_DBConn("close")
: > >>
: > >> For theRows = 0 to ubound(yourArray,2)
: > >> For theColumns = 0 to ubound(yourArray,1)
: > >>
: > >> Next
: > >> Next
: > >> %>
: > >>
: > >>
: > >> Brynn
: > >> [url]www.coolpier.com[/url]
: > >>
: > >>
: > >> On Wed, 21 Jan 2004 10:05:37 +0600, "tony" <dd@sdfsf.com> wrote:
: > >>
: > >> >Hi
: > >> >
: > >> >I have used ADO recordset open method on a ASP page
: > >> >for the sql command string its a name of a Stored Proc as follows
: > >> >
: > >> >
: > >> >
: > >> >Set conn = Server.CreateObject("ADODB.connection")
: > >> >Set RS_Requests = Server.CreateObject("ADODB.Recordset")
: > >> >Conn.Open Application("emp_ConnectionString")
: > >> >
: > >> >SQLStr = "sp_Emp_ListActiveRequests"
: > >> >
: > >> >RS_Requests.Open SQLStr,conn,3
: > >> >
: > >> >Problem is RS_Requests.pagecount and RS_Requests.recordcount is
: always -1
: > >> >but when I use plain SQL like "select * from employers" for the open
: > >method
: > >> >pagecount and recordcount is given correctly.
: > >> >
: > >> >any ideas?
: > >> >
: > >> >thanks in advance
: > >> >TS
: > >> >
: > >> >
: > >>
: > >> Brynn
: > >> [url]www.coolpier.com[/url]
: > >>
: > >> I participate in the group to help give examples of code.
: > >> I do not guarantee the effects of any code posted.
: > >> Test all code before use!
: > >
: > >
: >
: > Brynn
: > [url]www.coolpier.com[/url]
: >
: > I participate in the group to help give examples of code.
: > I do not guarantee the effects of any code posted.
: > Test all code before use!
:
:

[Ken Schaefer]

Unless you have a special requirement, you should use a ForwardOnly/ReadOnly
cursor. When used against SQL Server 2000 you will see about a 300-400%
improvement over Static/ReadOnly and Static/Optimistic cursor/locktype
combinations.

Unfortunately, you lose access to built-in properties like .RecordCount.
However, to implement these properties, the cursors that support them end up
being very expensive. If you check using SQL Server Profiler, when you call
..RecordCount on a static cursor, you pull *all* the records from the SQL
Server over to the client, even if you don't need the data!

Instead, there are other ways to get a recordcount. For example you can use
a SELECT COUNT(*) query. Or, if you actually need all the data, you should
use .getRows() to get an array, and evaluate the UBOUND() of the array.

Cheers
Ken


"tony" <dd@sdfsf.com> wrote in message
news:u8iKCYB4DHA.2612@tk2msftngp13.phx.gbl...
: Hi Mike
:
: Requirement is on ASP page which renders html to the browser (web
: application 2 tier).
: What will be the ideal Cursor location and ideal cursor type?
:
: thanks & regards
:
: TS
:
:
:
:
: "Mike Collier" <mike@adoanywhere.com> wrote in message
: news:OxAJ2aA4DHA.1804@TK2MSFTNGP12.phx.gbl...
: > > but when I use plain SQL like "select * from employers" for the open
: > method
: > > pagecount and recordcount is given correctly
: >
: > AdoAnywhere highlites all these issues.
: >
: > FAQ Record Count = -1
: > [url]http://81.130.213.94/myforum/forum_posts.asp?TID=72&PN=1[/url]
: >
: > --
: > Mike Collier BSc( Hons) Comp Sci
: > Offer: Get a copy of AdoAnywhere Browser FREE when you register for the
: > forum.
: > [url]http://www.adoanywhere.com/forum[/url]
: >
: >
:
:

[tony]

Hi Ken,

adUseKeyset is n't a forward only cursor right?
I tried that as well but it also indicated record and page counts as -1
I donno the above change might 'd not affected immidiately as soon as I
changed.
Do I have to kill dllhost or something to get it affected?

thanks
TS

"Ken Schaefer" <kenREMOVE@THISadOpenStatic.com> wrote in message
news:u12nrCK4DHA.564@TK2MSFTNGP11.phx.gbl...

> Because you are opening a ForwardOnly cursor (check the cursor type

*after*

> you open the recordset).
>
> ForwardOnly cursor does not support RecordCount/PageCount etc
>
> In any case, they are *very* expensive properties, and performance sucks

if

> you use them.
>
> Cheers
> Ken
>
>
> "tony" <dd@sdfsf.com> wrote in message
> news:uJ%238KaB4DHA.2136@TK2MSFTNGP12.phx.gbl...
> : Thanks Brynn
> :
> : However anyideas why when stored proc is used in a recordset open

method

> : it doesn't give correct recordcount and pagecount properties.
> :
> : I 've Tested different cursor types.
> :
> : regds
> : TS
> :
> :
> :
> : "Brynn" <z@z.com> wrote in message
> : news:400e50d8.8801465@news.comcast.giganews.com...
> : >
> : > yep, in my script, you can use a stored procedure ...
> : >
> : > '// INSERT, UPDATE, DELETE, -or- SP not returning recordset
> : > cp_SqlExecute(yourSQL)
> : >
> : > '//SELECT -or- SP returning recordset
> : >
> : > yourArray = cp_SqlArray(yourSQL)
> : > yourArray = cp_SqlArray_Paging(yourSQL, pageNum, perPage)
> : >
> : >
> : > ALSO, let me add this ...
> : >
> : > If IsArray(yourArray) Then
> : > For theRows = 0 to ubound(yourArray,2)
> : > For theColumns = 0 to ubound(yourArray,1)
> : > Next
> : > Next
> : > Else
> : > '//No records found code
> : > End If
> : >
> : >
> : > The nice thing about using my script like this is that you get in and
> : > out of the database connection fast.
> : > The database connection is closed ... AND ... there is never an open
> : > recordset object while working with the data.
> : >
> : > Brynn
> : > [url]www.coolpier.com[/url]
> : >
> : >
> : >
> : >
> : > On Wed, 21 Jan 2004 15:28:31 +0600, "tony" <dd@sdfsf.com> wrote:
> : >
> : > >Hi Brynn
> : > >
> : > >Thanks for the post.
> : > >
> : > >>yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
> : > >> perPage)
> : > >
> : > >Can I use a stored proc instead of plain sql query up there?
> : > >
> : > >regds
> : > >TS
> : > >
> : > >
> : > >"Brynn" <z@z.com> wrote in message
> : > >news:400e36fc.2180956@news.comcast.giganews.com.. .
> : > >>
> : > >> Try paging with my DBConn.asp script here ...
> : > >>
> : > >>
> :

[url]http://www.coolpier.com/cp/cp_scripts/script.asp?file=DBConn.asp&view=code[/url]

> : > >>
> : > >> <%
> : > >> Dim yourArray, totalPages
> : > >> Dim pageNum, perPage
> : > >>
> : > >> pageNum = Request.QueryString("pagenum")
> : > >> If pageNum = "" Then: pageNum=1: Else: pageNum=Int(pageNum): End If
> : > >> perPage = 20
> : > >>
> : > >> cp_TheConnectionString = "yourConnectionString"
> : > >> Call cp_DBConn("open")
> : > >> yourArray = cp_SqlArrayPaging("select * from employers", pageNum,
> : > >> perPage)
> : > >> Call cp_DBConn("close")
> : > >>
> : > >> For theRows = 0 to ubound(yourArray,2)
> : > >> For theColumns = 0 to ubound(yourArray,1)
> : > >>
> : > >> Next
> : > >> Next
> : > >> %>
> : > >>
> : > >>
> : > >> Brynn
> : > >> [url]www.coolpier.com[/url]
> : > >>
> : > >>
> : > >> On Wed, 21 Jan 2004 10:05:37 +0600, "tony" <dd@sdfsf.com> wrote:
> : > >>
> : > >> >Hi
> : > >> >
> : > >> >I have used ADO recordset open method on a ASP page
> : > >> >for the sql command string its a name of a Stored Proc as follows
> : > >> >
> : > >> >
> : > >> >
> : > >> >Set conn = Server.CreateObject("ADODB.connection")
> : > >> >Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> : > >> >Conn.Open Application("emp_ConnectionString")
> : > >> >
> : > >> >SQLStr = "sp_Emp_ListActiveRequests"
> : > >> >
> : > >> >RS_Requests.Open SQLStr,conn,3
> : > >> >
> : > >> >Problem is RS_Requests.pagecount and RS_Requests.recordcount is
> : always -1
> : > >> >but when I use plain SQL like "select * from employers" for the

open

> : > >method
> : > >> >pagecount and recordcount is given correctly.
> : > >> >
> : > >> >any ideas?
> : > >> >
> : > >> >thanks in advance
> : > >> >TS
> : > >> >
> : > >> >
> : > >>
> : > >> Brynn
> : > >> [url]www.coolpier.com[/url]
> : > >>
> : > >> I participate in the group to help give examples of code.
> : > >> I do not guarantee the effects of any code posted.
> : > >> Test all code before use!
> : > >
> : > >
> : >
> : > Brynn
> : > [url]www.coolpier.com[/url]
> : >
> : > I participate in the group to help give examples of code.
> : > I do not guarantee the effects of any code posted.
> : > Test all code before use!
> :
> :
>
>

[Bob Barrows]

tony wrote:

> Hi
>
> I have used ADO recordset open method on a ASP page
> for the sql command string its a name of a Stored Proc as follows
>
>
>
> Set conn = Server.CreateObject("ADODB.connection")
> Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> Conn.Open Application("emp_ConnectionString")
>
> SQLStr = "sp_Emp_ListActiveRequests"

I don't know if anyone has mentioned this yet, but the "sp_" prefix should
be reserved for system stored procedures. There is a performance penalty for
using it for regular procedures in that SQL attempts to find the procedure
in the Master database first, before looking in the current database.

>
> RS_Requests.Open SQLStr,conn,3
>
> Problem is RS_Requests.pagecount and RS_Requests.recordcount is
> always -1 but when I use plain SQL like "select * from employers" for
> the open method pagecount and recordcount is given correctly.
>

I have had problems opening a server-side non-forward-only cursor from a
stored procedure in the past. To see the problem yourself, use
"Response.Write RS_Requests.CursorType" after opening the recordset to see
the cursor type. You will see that it is not the one you requested.

For more, see this thread: [url]http://tinyurl.com/3cl6e[/url]

IMO, this is a bug in the SQLOLEDB provider. Interestingly enough, there is
a similar but different bug in the ODBC driver. When using ODBC with the
tests I performed in that thread, I get these results:

Before open, using stored procedure executed via dynamic sql, cursortype = 3
After open, using stored procedure executed via dynamic sql, cursor type =
3; RecordCount = -1

Before open, using stored procedure executed via
procedure-as-connection-method, cursortype = 3
After open, using stored procedure executed via
procedure-as-connection-method, cursor type = 3; RecordCount = -1

Before open, using stored procedure executed via Command object, cursortype
= 3
After open, using stored procedure executed via Command object, cursor type
= 3; RecordCount = -1

Before open, using dynamic sql, cursortype = 3
After open, using dynamic sql, cursor type = 3; RecordCount = 23

So, even though it's giving us the "right" cursortype, the recordcount is
not known. When I add this line:
"rsTest.Supports(adBookmark)=" & rsTest.Supports(adBookmark)

I discover that the only time bookmarks are supported is when using a
dynamic sql statement to open the recordset, rather than the stored
procedure.

This is not the documented behavior.

Workaround: use a client-side cursor (rs.cursorlocation=3) when opening the
recordset on a procedure, or use a more efficient method of getting the
record count.


HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[tony]

Hi Bob

Thanks!!

>I don't know if anyone has mentioned this yet, but the "sp_" prefix should
>be reserved for system stored procedures. There is a performance penalty

for

>using it for regular procedures in that SQL attempts to find the procedure
>in the Master database first, before looking in the current database.

lookup overhead is there I know but unfortunately have to proceed with this
misery.
(erroneous naming convention that has implemented)

> Workaround: use a client-side cursor (rs.cursorlocation=3) when opening

the

> recordset on a procedure, or use a more efficient method of getting the
> record count.
>

Afterall I 've decided to implement a normal paging method which won't use
Recordset
count properties. Hopefully I might get something better.

cheers
TS




"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:O7kTUkN4DHA.2432@TK2MSFTNGP09.phx.gbl...

> tony wrote:

> > Hi
> >
> > I have used ADO recordset open method on a ASP page
> > for the sql command string its a name of a Stored Proc as follows
> >
> >
> >
> > Set conn = Server.CreateObject("ADODB.connection")
> > Set RS_Requests = Server.CreateObject("ADODB.Recordset")
> > Conn.Open Application("emp_ConnectionString")
> >
> > SQLStr = "sp_Emp_ListActiveRequests"

>
> I don't know if anyone has mentioned this yet, but the "sp_" prefix should
> be reserved for system stored procedures. There is a performance penalty

for

> using it for regular procedures in that SQL attempts to find the procedure
> in the Master database first, before looking in the current database.
>

> >
> > RS_Requests.Open SQLStr,conn,3
> >
> > Problem is RS_Requests.pagecount and RS_Requests.recordcount is
> > always -1 but when I use plain SQL like "select * from employers" for
> > the open method pagecount and recordcount is given correctly.
> >

>
> I have had problems opening a server-side non-forward-only cursor from a
> stored procedure in the past. To see the problem yourself, use
> "Response.Write RS_Requests.CursorType" after opening the recordset to see
> the cursor type. You will see that it is not the one you requested.
>
> For more, see this thread: [url]http://tinyurl.com/3cl6e[/url]
>
> IMO, this is a bug in the SQLOLEDB provider. Interestingly enough, there

is

> a similar but different bug in the ODBC driver. When using ODBC with the
> tests I performed in that thread, I get these results:
>
> Before open, using stored procedure executed via dynamic sql, cursortype =

3

> After open, using stored procedure executed via dynamic sql, cursor type =
> 3; RecordCount = -1
>
> Before open, using stored procedure executed via
> procedure-as-connection-method, cursortype = 3
> After open, using stored procedure executed via
> procedure-as-connection-method, cursor type = 3; RecordCount = -1
>
> Before open, using stored procedure executed via Command object,

cursortype

> = 3
> After open, using stored procedure executed via Command object, cursor

type

> = 3; RecordCount = -1
>
> Before open, using dynamic sql, cursortype = 3
> After open, using dynamic sql, cursor type = 3; RecordCount = 23
>
> So, even though it's giving us the "right" cursortype, the recordcount is
> not known. When I add this line:
> "rsTest.Supports(adBookmark)=" & rsTest.Supports(adBookmark)
>
> I discover that the only time bookmarks are supported is when using a
> dynamic sql statement to open the recordset, rather than the stored
> procedure.
>
> This is not the documented behavior.
>
> Workaround: use a client-side cursor (rs.cursorlocation=3) when opening

the

> recordset on a procedure, or use a more efficient method of getting the
> record count.
>
>
> HTH,
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>