Stored Procedure call from ASP page problem

[.Net Sports]

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

<br><%= rset("company") %><br>
<%= rset("city") %>&nbsp;<%= rset("state") %> <br>
<%= rset("phone") %><br>
<i><%= rset("descript") %>&nbsp;</i><br>
<% 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

 

[Dave Anderson]

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

<br><%= rset("company") %><br>
<%= rset("city") %>&nbsp;<%= rset("state") %> <br>
<%= rset("phone") %><br>
<i><%= rset("descript") %>&nbsp;</i><br>
<% 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/mdobjconnection.asp

 

[Bob Barrows [MVP]]

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

 

[Daniel Crichton]

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