In LISTING 2, I have a SPROC that returns a recordset and a recordcount in
SQL QA. I can access the Recordset with no problem. How can I grab the
Recordcount with ASP code at the same time I'm getting the Recordset? In QA,
the RecordCount displays below the Recordset.
The below USAGE code will display my SPROC results.
USAGE:
EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID',
'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate' ************************
LISTING 1: ASP CODE
Set objConn = CreateObject("ADODB.Connection")
objConn.Open myDSN
Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT_WITH_PAGING " & SParms
objRS.Open strSQL, objConn
************************
LISTING 2: SPROC
CREATE PROCEDURE SELECT_WITH_PAGING (
@strFields VARCHAR(4000) ,
@strPK VARCHAR(100),
@strTables VARCHAR(4000),
@intPageNo INT = 1,
@intPageSize INT = NULL,
@blnGetRecordCount BIT = 0,
@strFilter VARCHAR(8000) = NULL,
@strSort VARCHAR(8000) = NULL,
@strGroup VARCHAR(8000) = NULL
)
AS
DECLARE @blnBringAllRecords BIT
DECLARE @strPageNo VARCHAR(50)
DECLARE @strPageSize VARCHAR(50)
DECLARE @strSkippedRows VARCHAR(50)
DECLARE @strFilterCriteria VARCHAR(8000)
DECLARE @strSimpleFilter VARCHAR(8000)
DECLARE @strSortCriteria VARCHAR(8000)
DECLARE @strGroupCriteria VARCHAR(8000)
DECLARE @intRecordcount INT
DECLARE @intPagecount INT
--******** NORMALIZE THE PAGING CRITERIA
--if no meaningful inputs are provided, we can avoid paging and execute a
more efficient query, so we will set a flag that will help with that
(blnBringAllRecords)
IF @intPageNo < 1
SET @intPageNo = 1
SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
IF @intPageSize IS NULL OR @intPageSize < 1 -- BRING ALL RECORDS, DON'T DO
PAGING
SET @blnBringAllRecords = 1
ELSE
BEGIN
SET @blnBringAllRecords = 0
SET @strPageSize = CONVERT(VARCHAR(50), @intPageSize)
SET @strPageNo = CONVERT(VARCHAR(50), @intPageNo)
SET @strSkippedRows = CONVERT(VARCHAR(50), @intPageSize * (@intPageNo -
1))
END
--******** NORMALIZE THE FILTER AND SORTING CRITERIA
--if they are empty, we will avoid filtering and sorting, respectively,
executing more efficient queries
IF @strFilter IS NOT NULL AND @strFilter != ''
BEGIN
SET @strFilterCriteria = ' WHERE ' + @strFilter + ' '
SET @strSimpleFilter = ' AND ' + @strFilter + ' '
END
ELSE
BEGIN
SET @strSimpleFilter = ''
SET @strFilterCriteria = ''
END
IF @strSort IS NOT NULL AND @strSort != ''
SET @strSortCriteria = ' ORDER BY ' + @strSort + ' '
ELSE
SET @strSortCriteria = ''
IF @strGroup IS NOT NULL AND @strGroup != ''
SET @strGroupCriteria = ' GROUP BY ' + @strGroup + ' '
ELSE
SET @strGroupCriteria = ''
--************************** NOW START DOING THE REAL WORK
--!NOTE: for potentially improved performance, use sp_executesql instead of
EXEC
IF @blnBringAllRecords = 1 --ignore paging and run a simple select
BEGIN
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + @strFilterCriteria +
@strGroupCriteria + @strSortCriteria
)
END-- WE HAD TO BRING ALL RECORDS
ELSE --BRING ONLY A PARTICULAR PAGE
BEGIN
IF @intPageNo = 1 --in this case we can execute a more efficient query,
with no subqueries
EXEC (
'SELECT TOP ' + @strPageSize + ' ' + @strFields + ' FROM ' + @strTables +
@strFilterCriteria + @strGroupCriteria + @strSortCriteria
)
ELSE --execute a structure of subqueries that brings the correct page
EXEC (
'SELECT ' + @strFields + ' FROM ' + @strTables + ' WHERE ' + @strPK + '
IN ' + '
(SELECT TOP ' + @strPageSize + ' ' + @strPK + ' FROM ' + @strTables +
' WHERE ' + @strPK + ' NOT IN ' + '
(SELECT TOP ' + @strSkippedRows + ' ' + @strPK + ' FROM ' + @strTables
+ @strFilterCriteria + @strGroupCriteria + @strSortCriteria + ') ' +
@strSimpleFilter +
@strGroupCriteria +
@strSortCriteria + ') ' +
@strGroupCriteria +
@strSortCriteria
)
END
--IF WE NEED TO RETURN THE RECORDCOUNT
IF @blnGetRecordCount = 1
IF @strGroupCriteria != ''
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables
+ @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)'
)
ELSE
EXEC (
'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria
+ @strGroupCriteria
)
GO 12 2936
scott wrote: In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm getting the Recordset? In QA, the RecordCount displays below the Recordset.
The below USAGE code will display my SPROC results.
USAGE: EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate' ************************ LISTING 1: ASP CODE Set objConn = CreateObject("ADODB.Connection") objConn.Open myDSN Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT_WITH_PAGING " & SParms
objRS.Open strSQL, objConn
************************ LISTING 2: SPROC
CREATE PROCEDURE SELECT_WITH_PAGING ( @strFields VARCHAR(4000) , @strPK VARCHAR(100), @strTables VARCHAR(4000), @intPageNo INT = 1, @intPageSize INT = NULL, @blnGetRecordCount BIT = 0, @strFilter VARCHAR(8000) = NULL, @strSort VARCHAR(8000) = NULL, @strGroup VARCHAR(8000) = NULL )
AS
<snip> --IF WE NEED TO RETURN THE RECORDCOUNT IF @blnGetRecordCount = 1 IF @strGroupCriteria != '' EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)' ) ELSE EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria ) GO
I will leave it to others to comment on this use of dynamic sql (think "sql
injection") ...
You're returning a second resultset containing the count, so you would use
the recordset's NextRecordset method to access it:
set objRS = objRS.NextRecordset
reccount=objRS("recordcount")
I would be more inclined to use an output parameter, using a Command object
to retrieve the value.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Bob Barrows [MVP] wrote: I would be more inclined to use an output parameter, using a Command object to retrieve the value.
Can your preferred SP-as-method-of-connection technique[1] deal with output
parameters?
[1] I don't use it because it seem not to work in JScript
--
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Dave Anderson wrote: Bob Barrows [MVP] wrote: I would be more inclined to use an output parameter, using a Command object to retrieve the value.
Can your preferred SP-as-method-of-connection technique[1] deal with output parameters?
No. That technique can only be used if there are no output parameters whose
values need to be retrieved, and if the developer is not interested in
retrieving the Return parameter value. An explicit Command object must be
used in either of these situations.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Dave Anderson wrote: Bob Barrows [MVP] wrote: I would be more inclined to use an output parameter, using a Command object to retrieve the value.
Can your preferred SP-as-method-of-connection technique[1] deal with output parameters? [1] I don't use it because it seem not to work in JScript
It seems to work fine for me:
<%@ Language=JavaScript %>
<%
var cn,strConn, rs;
cn = new ActiveXObject("ADODB.Connection")
strConn = "provider=sqloledb;data source=xxxxx;user id = xxxx;" +
"password=xxxx;initial catalog=pubs"
cn.open(strConn)
rs = new ActiveXObject("ADODB.Recordset")
//uncomment the following to test optional argument
//cn.RecordCountProblemDemo("test",rs)
//comment the following when testing optional argument
cn.RecordCountProblemDemo(rs)
Response.Write(rs.getstring());
rs.close()
cn.close()
%>
The procedure is:
CREATE PROCEDURE [dbo].[RecordCountProblemDemo]
(@SomeVarThatDoesntGetUsed VARCHAR(11) = NULL)
AS
BEGIN
SET NOCOUNT ON
IF @SomeVarThatDoesntGetUsed IS NULL
SET @SomeVarThatDoesntGetUsed = 'hello'
SELECT au_fname, au_lname,
@SomeVarThatDoesntGetUsed FROM authors
END
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
scott wrote: In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm getting the Recordset? In QA, the RecordCount displays below the Recordset.
A useful generic way to get the recordcount is use GetRows() and examine the
dimensions:
RSArray = RS.GetRows()
RecordCount = UBound(RSArray,2) http://msdn.microsoft.com/library/en...mthgetrows.asp
This has several advantages. The most obvious is that you can order the
output any way you like.
Secondly, you only need one SELECT in your stored procedure.
Furthermore, GetRows() + array iteration is much faster than stepping
through a recordset with MoveNext().
In addition, you can immediately close your connection (or use it for
something else) after GetRows(), which can give you additional performance
gains.
Lastly, if you have a [SELECT Count] to measure the size of a [SELECT],
every change to one requires a change to the other. Measuring the output
array always returns an accurate count, no matter what changes are made to
the SELECT.
--
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
Dave Anderson wrote: scott wrote: In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm getting the Recordset? In QA, the RecordCount displays below the Recordset.
A useful generic way to get the recordcount is use GetRows() and examine the dimensions:
I second this. I can't believe I failed to mention it.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
1st off, thanks for educating me on the NextRecordset prop, didn't know such
exists. However, for learning purposes could you either supply a few lines
of code similating retrieving a recordset followed by an output parameter? A
link to an example would be fine.
I didn't realize I could do 2 things at once with ASP.
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uj**************@TK2MSFTNGP10.phx.gbl... scott wrote: In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm getting the Recordset? In QA, the RecordCount displays below the Recordset.
The below USAGE code will display my SPROC results.
USAGE: EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate' ************************ LISTING 1: ASP CODE Set objConn = CreateObject("ADODB.Connection") objConn.Open myDSN Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT_WITH_PAGING " & SParms
objRS.Open strSQL, objConn
************************ LISTING 2: SPROC
CREATE PROCEDURE SELECT_WITH_PAGING ( @strFields VARCHAR(4000) , @strPK VARCHAR(100), @strTables VARCHAR(4000), @intPageNo INT = 1, @intPageSize INT = NULL, @blnGetRecordCount BIT = 0, @strFilter VARCHAR(8000) = NULL, @strSort VARCHAR(8000) = NULL, @strGroup VARCHAR(8000) = NULL )
AS <snip> --IF WE NEED TO RETURN THE RECORDCOUNT IF @blnGetRecordCount = 1 IF @strGroupCriteria != '' EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)' ) ELSE EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria ) GO
I will leave it to others to comment on this use of dynamic sql (think "sql injection") ...
You're returning a second resultset containing the count, so you would use the recordset's NextRecordset method to access it:
set objRS = objRS.NextRecordset reccount=objRS("recordcount")
I would be more inclined to use an output parameter, using a Command object to retrieve the value.
Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
OK, here's a simple procedure:
create procedure OutputTest (
@out int output) AS
BEGIN
SET NOCOUNT ON
SET @out = 33
exec sp_who2
END
In ASP, you would do this (I'm using the ADO constants for readability. You
should #include the adovbs.inc file, or use one of the methods shown here to
define the constants - http://www.aspfaq.com/show.asp?id=2112):
<%
dim cn, cmd, rs, params
set cn=createobject("adodb.connection")
cn.open "<valid connection string>"
set cmd=createobject("adodb.command")
cmd.CommandText="OutputTest"
cmd.ActiveConnection= cn
cmd.CommandType = adCmdStoredProc
set params=cmd.parameters
params.append cmd.createparameter("@RETURN_VALUE", _
adInteger, adParamReturnValue)
params.append cmd.createparameter("@out", adInteger, _
adParamOutput)
set rs=cmd.Execute
'the recordset has to be "consumed" before the output
'value is available, so:
response.write rs.GetString
rs.close
response.write "output parameter: " & params(1).value
cn.close:set cn=nothing
set rs=nothing
%>
But again, GetRows is a better solution for your situation.
Bob Barrows
scott wrote: 1st off, thanks for educating me on the NextRecordset prop, didn't know such exists. However, for learning purposes could you either supply a few lines of code similating retrieving a recordset followed by an output parameter? A link to an example would be fine.
I didn't realize I could do 2 things at once with ASP. "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:uj**************@TK2MSFTNGP10.phx.gbl... scott wrote: In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm getting the Recordset? In QA, the RecordCount displays below the Recordset.
The below USAGE code will display my SPROC results.
USAGE: EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate' ************************ LISTING 1: ASP CODE Set objConn = CreateObject("ADODB.Connection") objConn.Open myDSN Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT_WITH_PAGING " & SParms
objRS.Open strSQL, objConn
************************ LISTING 2: SPROC
CREATE PROCEDURE SELECT_WITH_PAGING ( @strFields VARCHAR(4000) , @strPK VARCHAR(100), @strTables VARCHAR(4000), @intPageNo INT = 1, @intPageSize INT = NULL, @blnGetRecordCount BIT = 0, @strFilter VARCHAR(8000) = NULL, @strSort VARCHAR(8000) = NULL, @strGroup VARCHAR(8000) = NULL )
AS <snip> --IF WE NEED TO RETURN THE RECORDCOUNT IF @blnGetRecordCount = 1 IF @strGroupCriteria != '' EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)' ) ELSE EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria ) GO
I will leave it to others to comment on this use of dynamic sql (think "sql injection") ...
You're returning a second resultset containing the count, so you would use the recordset's NextRecordset method to access it:
set objRS = objRS.NextRecordset reccount=objRS("recordcount")
I would be more inclined to use an output parameter, using a Command object to retrieve the value.
Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
I have 1 small problem with NextRecordSet property. The below does work, but
I can't use the "NextRecordset" line until I finish my 1st RecordSet Loop.
If I try and move the "NextRecordset" part at beginning, I get errors. I'd
like to be able to display the iNumRecords (total records rendered by
NextRecordset) before I display my Recordset table. Is there a way around
this?
Set objConn = CreateObject("ADODB.Connection")
objConn.Open sDSN
Set objRS = Server.CreateObject("ADODB.Recordset")
sSQL = "SELECT_WITH_PAGING " & SParms
objRS.Open sSQL, objConn
Do While Not objRS.EOF
' Display Records
objRS.MoveNext
Loop
Set objRS = objRS.NextRecordset
iNumRecords=objRS("RecordCount")
Response.Write "<br>iNumRecords: " & iNumRecords & "<br>"
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:u$**************@TK2MSFTNGP09.phx.gbl... OK, here's a simple procedure:
create procedure OutputTest ( @out int output) AS BEGIN SET NOCOUNT ON SET @out = 33 exec sp_who2 END
In ASP, you would do this (I'm using the ADO constants for readability. You should #include the adovbs.inc file, or use one of the methods shown here to define the constants - http://www.aspfaq.com/show.asp?id=2112):
<% dim cn, cmd, rs, params set cn=createobject("adodb.connection") cn.open "<valid connection string>" set cmd=createobject("adodb.command") cmd.CommandText="OutputTest" cmd.ActiveConnection= cn cmd.CommandType = adCmdStoredProc set params=cmd.parameters params.append cmd.createparameter("@RETURN_VALUE", _ adInteger, adParamReturnValue) params.append cmd.createparameter("@out", adInteger, _ adParamOutput) set rs=cmd.Execute 'the recordset has to be "consumed" before the output 'value is available, so: response.write rs.GetString rs.close response.write "output parameter: " & params(1).value cn.close:set cn=nothing set rs=nothing %>
But again, GetRows is a better solution for your situation.
Bob Barrows scott wrote: 1st off, thanks for educating me on the NextRecordset prop, didn't know such exists. However, for learning purposes could you either supply a few lines of code similating retrieving a recordset followed by an output parameter? A link to an example would be fine.
I didn't realize I could do 2 things at once with ASP. "Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message news:uj**************@TK2MSFTNGP10.phx.gbl... scott wrote: In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm getting the Recordset? In QA, the RecordCount displays below the Recordset.
The below USAGE code will display my SPROC results.
USAGE: EXEC SELECT_WITH_PAGING 'CustomerID, ShipName', 'OrderID', 'Northwind.dbo.Orders', 3, 10, 1, '', 'OrderDate' ************************ LISTING 1: ASP CODE Set objConn = CreateObject("ADODB.Connection") objConn.Open myDSN Set objRS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT_WITH_PAGING " & SParms
objRS.Open strSQL, objConn
************************ LISTING 2: SPROC
CREATE PROCEDURE SELECT_WITH_PAGING ( @strFields VARCHAR(4000) , @strPK VARCHAR(100), @strTables VARCHAR(4000), @intPageNo INT = 1, @intPageSize INT = NULL, @blnGetRecordCount BIT = 0, @strFilter VARCHAR(8000) = NULL, @strSort VARCHAR(8000) = NULL, @strGroup VARCHAR(8000) = NULL )
AS <snip>
--IF WE NEED TO RETURN THE RECORDCOUNT IF @blnGetRecordCount = 1 IF @strGroupCriteria != '' EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM (SELECT COUNT(*) FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria + ') AS tbl (id)' ) ELSE EXEC ( 'SELECT COUNT(*) AS RECORDCOUNT FROM ' + @strTables + @strFilterCriteria + @strGroupCriteria ) GO
I will leave it to others to comment on this use of dynamic sql (think "sql injection") ...
You're returning a second resultset containing the count, so you would use the recordset's NextRecordset method to access it:
set objRS = objRS.NextRecordset reccount=objRS("recordcount")
I would be more inclined to use an output parameter, using a Command object to retrieve the value.
Bob Barrows -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
-- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
will the GetRows() method work with a SPROC that returns a recordset, then a
recordcount?
"Dave Anderson" <GT**********@spammotel.com> wrote in message
news:ew**************@TK2MSFTNGP10.phx.gbl... scott wrote: In LISTING 2, I have a SPROC that returns a recordset and a recordcount in SQL QA. I can access the Recordset with no problem. How can I grab the Recordcount with ASP code at the same time I'm getting the Recordset? In QA, the RecordCount displays below the Recordset.
A useful generic way to get the recordcount is use GetRows() and examine the dimensions:
RSArray = RS.GetRows() RecordCount = UBound(RSArray,2)
http://msdn.microsoft.com/library/en...mthgetrows.asp This has several advantages. The most obvious is that you can order the output any way you like.
Secondly, you only need one SELECT in your stored procedure.
Furthermore, GetRows() + array iteration is much faster than stepping through a recordset with MoveNext().
In addition, you can immediately close your connection (or use it for something else) after GetRows(), which can give you additional performance gains.
Lastly, if you have a [SELECT Count] to measure the size of a [SELECT], every change to one requires a change to the other. Measuring the output array always returns an accurate count, no matter what changes are made to the SELECT. -- Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use of this email address implies consent to these terms. Please do not contact me directly or ask me to contact you directly for assistance. If your question is worth asking, it's worth posting.
scott wrote: will the GetRows() method work with a SPROC that returns a recordset, then a recordcount?
Why would you bother? The dimensions of the array TELL YOU the recordcount.
--
Dave Anderson
Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms. Please do not contact
me directly or ask me to contact you directly for assistance. If your
question is worth asking, it's worth posting.
scott wrote: I have 1 small problem with NextRecordSet property. The below does work, but I can't use the "NextRecordset" line until I finish my 1st RecordSet Loop. If I try and move the "NextRecordset" part at beginning, I get errors. I'd like to be able to display the iNumRecords (total records rendered by NextRecordset) before I display my Recordset table. Is there a way around this?
Yes. Use GetRows to move your data into an array, then call NextRecordset
(however, the Nextrecordset will no longer be necessary because you'll be
able to use ubound to determine the number of records that were returned
from the procedure ... )
If you're truly adverse to arrays for some reason, you can create a copy of
your recordset using the Clone method.
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" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: teddysnips |
last post by:
SQL Server 2000
I have a stored procedure that uses an extended SPROC to send an email
notification to my customers when a document is distributed.
However, the SPROC has an unexpected side...
|
by: Radu |
last post by:
Hi. It seems to be very simple, actually, but I don't know if it is
feasible in TSQL. I have a sproc which gathers in one place many calls
to different other sprocs, all of them taking a...
|
by: Radu |
last post by:
Hi. I have lots of processing to do on the server - from the client
(Access) I call a sproc which returns a recordset (the sproc is
essentially a big "select"). With the obtained data , I need to...
|
by: Chad Micheal Lawson via .NET 247 |
last post by:
I'm stumped at this point and I'm tired of trying things so I'mposting in hopes of some guru with a sharp eye. I have anasp.net app running on a local Win XP Pro box. Within the app,I call a SPROC...
|
by: skirkby |
last post by:
This will be obvious to some - but not me I'm afraid...
I am using an SQL data link from my ASP application to a SPROC - this all
works fine on standard SELECT statements and JOIN in to a...
|
by: Mike L. |
last post by:
Hi,
I got this 'EXECUTE permission denied on object <mySproc>' error message
everytime I try executing my SQL server Sproc.
What's this and how to fix this err?
many thnaks in advance,
mike
|
by: seevion |
last post by:
My first post (beginner).. I hope it is clear enough and appreciate your taking the time to consider helping.
I have an existing sproc that takes a cart transaction from a table and inserts shared...
|
by: =?Utf-8?B?UlBhcmtlcg==?= |
last post by:
I used the wizard to generate a typed dataset for my table and let it create
my SPROCs. It created everything, and the GetData() method and the custom
GetByUserName query works great, but when I...
|
by: Looch |
last post by:
Hi All,
I originally wrote a sproc with one of the parameters set as
SqlDBType.nvarchar,8. The parameter in the sproc was defined as 8
cahracters and the column in the table was also set to...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |