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
----------

i always returns -1. Thanks for the help!! Feel free to tell me a better
way to do this, but please show me all the code I would need to
implement.

*** Sent via Developersdex http://www.developersdex.com ***

Joey Martin wrote:
Show quoteHide quoteNothing 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 ... :-)

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

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.

*** Sent via Developersdex http://www.developersdex.com ***

Joey Martin wrote:
I suspect you continued to use Execute instead of Open.

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)
        next
        response.write "</tr>"
    next
    response.write "</table>"
else
    response.write "Procedure failed to return records"
end if

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?


*** Sent via Developersdex http://www.developersdex.com ***

Joey Martin wrote:
Just include them in the correct order (the same order they are declared in
the procedure)

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

LEFT JOIN problem
How can I type in the combo box <select> ?
How to change all relative paths in a website???
What is the real IP address of the site visitor?
ASP 0126
Pinpoint unspecified error for objXMLHttp.send objXML statement
Classic ASP, <SELECT> postback, Browser Navigation
Script from variables
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Problem with Instr to find a space