Urgent: getting recordcount after running Stored Procedure

[Joey Martin]

The last thing I want to be doing is working on Father's day when i have
4 kids wanting to play, but I have no choice. I am hoping someone can
quickly bail me out.

I have an ASP page the passes parameters to a SP and it all works
PERFECT. But, now I want to get the total # of records retrieved.

I always get a -1. I have ready 50 articles on how to fix this with
adUseClient, etc, etc, but none seem to work.

Can you please look at my code and let me know what I am doing wrong??
PLEASE and Thank you!!


--my Code---
Set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="Provider=sqloledb;Data Source=xxx;Initial
Catalog=xxx;User Id=xxx;Password=xxx!!"
conn.Open

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

comm.ActiveConnection = conn
comm.CommandText="sp_Search"

comm.CommandType=adCmdStoredProc
comm.Parameters.Append comm.CreateParameter("@keyword", adVarChar,
adParamInput, 1000, thekeyword)


Set rs = Server.CreateObject("ADODB.recordset")
Set rs = comm.Execute
i=rs.RecordCount

 

[Bob Barrows]

The last thing I want to be doing is working on Father's day when i
have 4 kids wanting to play, but I have no choice. I am hoping
someone can quickly bail me out.

I have an ASP page the passes parameters to a SP and it all works
PERFECT. But, now I want to get the total # of records retrieved.

I always get a -1. I have ready 50 articles on how to fix this with
adUseClient, etc, etc, but none seem to work.

Can you please look at my code and let me know what I am doing wrong??
PLEASE and Thank you!!


--my Code---
Set conn=Server.CreateObject("ADODB.Connection")
conn.ConnectionString="Provider=sqloledb;Data Source=xxx;Initial
Catalog=xxx;User Id=xxx;Password=xxx!!"
conn.Open

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

comm.ActiveConnection = conn
comm.CommandText="sp_Search"

Nothing to do with your problem, but you need to get out of the habit of
using the"sp_" prefix for non-system stored procedures. SQL Server assumes
any procedure starting with that prefix is a system procedure and will
search for it in the master database first. Only when it fails to find it in
the master database will it look in the current database for it. Now you
might not think that's a big deal, and timewise it probably isn't. The
problems start when you mistakenly give your procedure the same name as an
existing system procedure ... have fun debugging why you get strange results
when calling your procedure ...

comm.CommandType=adCmdStoredProc
comm.Parameters.Append comm.CreateParameter("@keyword", adVarChar,
adParamInput, 1000, thekeyword)


Set rs = Server.CreateObject("ADODB.recordset")
Set rs = comm.Execute
i=rs.RecordCount

Of course it does! You are opening a default server-side, forward-only
cursor which does not support recordcount. Using Execute causes ADO to
create a new recordset object with the default cursorlocation and cursortype
properties as set in the connection object: server-side, forward-only, and
assign that recordset to the variable on the left side of the equal sign.
This of course makes the Set rs = Server.CreateObject("ADODB.recordset")
statement redundant at best.

You said you had seen recommendations to use a client-side cursor ... why
haven't you taken that advice?

Set rs = Server.CreateObject("ADODB.recordset")
rs.cursorlocation = 3 'adUseClient
rs.Open comm
i=rs.RecordCount

My preferred technique is to avoid the recordcount property totally. It is
simply not needed - you just don't realize it. Here is how I prefer to call
procedures and process returned records:

Set conn=CreateObject("ADODB.Connection")
'The "Server." is simply not needed

conn.Open "Provider=sqloledb;" & _
"Data Source=xxx;Initial Catalog=xxx;User Id=xxx;Password=xxx!!"

set rs = CreateObject("ADODB.recordset")
conn.sp_Search thekeyword, rs
if not rs.eof then arData=rs.getrows
rs.close:set rs=nothing
conn.close: set conn=nothing
if isarray(ardata) then
response.write "Procedure returned " ubound(ardata,2)+1 & " records"
'loop through the array to process the records
else
response.write "Procedure failed to return records"
end if

Why do i prefer this technique?
1. simplicity - the only time I use an explicit command object is when I
have output parameters or I need to read the Return parameter
2. it accomplishes my goal of getting out of the database as fast as
possible so the connection is available to be used by another thread
3. Looping through an array is much faster than looping through a recordset.
See here for further information about recordset iteration:
http://www.aspfaq.com/show.asp?id=2467

recordcount failure:
http://www.aspfaq.com/show.asp?id=2193

For a further explanation of the effect of cursor type on record count, see
here:
http://msdn.microsoft.com/library/en-us/ado270/htm/mdprorecordcount.asp

 

[Joey Martin]

Thank you. I had at one point done the rs.cursorlocation = 3 but it did
not work. Maybe I was putting it in the wrong place, or using other
parameters with it. Regardless, it works.

Now on to your preferred method. I am not great with arrays, so I am
trying to figure out how to display my results, like street_name, city,
etc. I will do some research, but if you can help out, I would greatly
appreciate it.

 

[Bob Barrows]

Thank you. I had at one point done the rs.cursorlocation = 3 but it
did not work. Maybe I was putting it in the wrong place, or using
other parameters with it. Regardless, it works.

I suspect you continued to use Execute instead of Open.

Now on to your preferred method. I am not great with arrays, so I am
trying to figure out how to display my results, like street_name,
city, etc. I will do some research, but if you can help out, I would
greatly appreciate it.

One of the links I posted has sample code, but ...
if the sql used to return the results is:
select street_name, city, state ...

Then the first field, street_name will have an index value of 0 in the first
dimension of the getrows array. city will be 1 and state will be 2. The
second dimension is the rows (records). So, ardata(0,0) will contain the
street_name from the first record. ardata(1,0) will contain the city from
the same record. To loop through the array, declare two variables:
dim iRow, iColumn
if isarray(ardata) then
response.write "Procedure returned " ubound(ardata,2)+1 & " records"
response.write "<table><tr><th>Street Name</th><th>City</th>" & _
"<th>State</th></tr>"
'loop through the array to process the records
for iRow = 0 to ubound(ardata,2)
response.write "<tr>"
for iColumn = 0 to ubound(ardata,1)
response.write "<td>" & ardata(iColumn,iRow) & "</td>"
next
response.write "</tr>"
next
response.write "</table>"
else
response.write "Procedure failed to return records"
end if

 

[Joey Martin]

Awesome. Thanks again. One last question, I need to pass 4 parameters to
the SP, not just one. I was just showing one as an example.

So, in your code:
set rs1 = CreateObject("ADODB.recordset")
conn.sp_Search thekeyword, rs1


How do I pass ZIP and CITY and STATE along with THEKEYWORD?

 

[Bob Barrows]

Awesome. Thanks again. One last question, I need to pass 4 parameters
to the SP, not just one. I was just showing one as an example.

So, in your code:
set rs1 = CreateObject("ADODB.recordset")
conn.sp_Search thekeyword, rs1


How do I pass ZIP and CITY and STATE along with THEKEYWORD?

Just include them in the correct order (the same order they are declared in
the procedure)

conn.sp_Search zip,city,state,thekeyword, rs1