Stored Procedure call from ASP page problem

I need to call an stored procedure from an asp script, I don't need to
do an Output parameter, only input parameters on 3 vars, but I get a
"BOF EOF not true or record has been deleted" error when trying to
call the page with the correct querystrings. The stored procedure
looks like it's properly called , as i tried misspelling the stored
procedure in the "CmdSP.CommandText = "resultsSP" " line, and the
error prompted no such stored procedure. The connection string is
correct:
'''''''''

<% Dim countyQ,stateQ,categQ
countyQ = request.querystring("county")
stateQ = request.querystring("state")
categQ = request.querystring("category")
%>
<%
Dim CmdSP
Dim rset
Dim adCmdSPStoredProc
Dim adParamReturnValue
Dim adParaminput
Dim adParamOutput
Dim adInteger
Dim iVal
Dim oVal
Dim adoField
Dim adVarChar

adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200

iVal = 5
oVal = 3


Set CmdSP = Server.CreateObject("ADODB.Command")
CmdSP.ActiveConnection = conn
CmdSP.CommandText = "resultsSP"
CmdSP.CommandType = adCmdSPStoredProc


'-- define the first parameter - the one the procedure will return
'-- the calls are:
'-- CmdSP.Parameters.Append: append this parameter to the
collection for this command object
'-- CmdSP.CreateParameter(): creates the parameter using the
values given:
'-- "@countyQ" is the name of the parameter for later reference
'-- adVarChar (value = 200) indicates this parameter is a
string datatype
'-- adParamInput (value = 1) indicates this parameter is for
input
'-- 20 is the size of the string in characters
'-- "M" is an arbitrary initial value for this parameter


CmdSP.Parameters.Append CmdSP.CreateParameter("@countyQ", adVarChar,
adParaminput, 40, "")

CmdSP.Parameters.Append CmdSP.CreateParameter("@stateQ", adVarChar,
adParaminput, 2, "")

CmdSP.Parameters.Append CmdSP.CreateParameter("@categQ", adVarChar,
adParaminput, 25, "")




Set rset = CmdSP.Execute
%>


<%If Not rset.BOF Then%>
<%
Do While Not rset.EOF%>


<%= rset("company") %>

<%= rset("city") %> <%= rset("state") %>

<%= rset("phone") %>

<%= rset("descript") %> 

<% rset.MoveNext
Loop
%>
<%End If%>

''here is the sproc creation

CREATE PROCEDURE resultsSP


@countyQ varchar(40),
@stateQ varchar(2),
@categQ varchar(25)


AS
BEGIN
SELECT * FROM general WHERE county='" + @countyQ + "' and state='" +
@stateQ + "' and category='" + @categQ + "' order by company
end

'' the resulting error on the asp page in question:
ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted.
Requested operation requires a current record.

/auto-results.asp, line 0

'''''
TIA
netsports

Re: Stored Procedure call from ASP page problem

..Net Sports wrote:
> I need to call an stored procedure from an asp script, I don't
> need to do an Output parameter...
>
> ...CREATE PROCEDURE resultsSP
> @countyQ varchar(40),
> @stateQ varchar(2),
> @categQ varchar(25)
> AS
> BEGIN
> SELECT * FROM general WHERE county='" + @countyQ + "' and state='" +
> @stateQ + "' and category='" + @categQ + "' order by company
> end

Seriously, there is no need to use a command object here. This will suffice:

Set rset = CreateObject("ADODB.Recordset")
CmdSP.resultsSP countyQ, stateQ, categQ, rset

Then...

> <%Do While Not rset.EOF%>
>
>
<%= rset("company") %>

> <%= rset("city") %> <%= rset("state") %>

> <%= rset("phone") %>

> <%= rset("descript") %> 

> <% rset.MoveNext
> Loop
> %>

See the section labeled, "Execute a stored procedure as a native method of a
Connection object":
http://msdn.microsoft.com/library/en-us/ado270/htm/mdobjconn ection.asp



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

Re: Stored Procedure call from ASP page problem

..Net Sports wrote:

>
> ''here is the sproc creation
>
> CREATE PROCEDURE resultsSP
>
>
> @countyQ varchar(40),
> @stateQ varchar(2),
> @categQ varchar(25)
>
>
> AS
> BEGIN
> SELECT * FROM general WHERE county='" + @countyQ + "' and state='" +

http://www.aspfaq.com/show.asp?id=2096

> @stateQ + "' and category='" + @categQ + "' order by company
> end

Have you tried running this procedure in query analyzer? It looks like
you are concatenating strings to create a dynamic sql statement, but you
never execute it ...? If I was writing this procedure it would look like
this:

CREATE PROCEDURE resultsSP


@countyQ varchar(40),
@stateQ varchar(2),
@categQ varchar(25)


AS
BEGIN
/*the following line prevents informational messages from
being returned as extra resultsets*/
/************************************
SET NOCOUNT ON
*************************************/
SELECT company,city,phone,descript
FROM general WHERE county= @countyQ and state=
@stateQ and category= @categQ order by company
end


Also, you are going to entirely too much trouble to execute this
procedure. Without output parameters, and with no need to read a return
parameter value, an explicit Command object is not needed. Try this:
Oh wait ... it appears Dave has already touched on this. See his reply.

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

Re: Stored Procedure call from ASP page problem

..Net wrote on Mon, 04 Jun 2007 09:43:27 -0700:

> I need to call an stored procedure from an asp script, I don't need to
> do an Output parameter, only input parameters on 3 vars, but I get a
> "BOF EOF not true or record has been deleted" error when trying to
> call the page with the correct querystrings. The stored procedure
> looks like it's properly called , as i tried misspelling the stored
> procedure in the "CmdSP.CommandText = "resultsSP" " line, and the
> error prompted no such stored procedure. The connection string is
> correct:
> '''''''''

> ''here is the sproc creation
>
> CREATE PROCEDURE resultsSP
>
> @countyQ varchar(40),
> @stateQ varchar(2),
> @categQ varchar(25)
>
> AS
> BEGIN
> SELECT * FROM general WHERE county='" + @countyQ + "' and state='" +
> @stateQ + "' and category='" + @categQ + "' order by company
> end

This is wrong. When using parameters like this, you don't treat them as
strings to concatenate into the query, change it to this:

SELECT * FROM general WHERE county= @countyQ and state= @stateQ and
category= @categQ order by company


I think the problem you were having is that you were testing for rst.BOF,
but your SP wasn't even returning a recordset that you could test for BOF.

Dan