Stored Procedure call from ASP page problem

Discussion in 'ASP General' started by .Net Sports, Jun 4, 2007.

  1. .Net Sports

    .Net Sports Guest

    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
    .Net Sports, Jun 4, 2007
    #1
    1. Advertising

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



    --
    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.
    Dave Anderson, Jun 4, 2007
    #2
    1. Advertising

  3. ..Net Sports wrote:
    <snip>
    >
    > ''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.
    Bob Barrows [MVP], Jun 4, 2007
    #3
  4. ..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
    Daniel Crichton, Jun 5, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mona
    Replies:
    2
    Views:
    643
    Rajesh Tiwari
    Jun 27, 2003
  2. Steven K

    Stored Procedure call

    Steven K, Mar 3, 2004, in forum: ASP .Net
    Replies:
    2
    Views:
    509
    richlm
    Mar 3, 2004
  3. Sam
    Replies:
    6
    Views:
    9,258
    =?Utf-8?B?U2FuZHk=?=
    May 11, 2005
  4. Mike P
    Replies:
    0
    Views:
    3,280
    Mike P
    Jun 19, 2006
  5. syoung

    Call stored procedure in ASP.NET 2.0

    syoung, Sep 24, 2005, in forum: ASP General
    Replies:
    1
    Views:
    144
    syoung
    Sep 24, 2005
Loading...

Share This Page