Professional Web Applications Themes

ADO/Stored Procedure Problem - ASP Database

After creating a new recordset and setting the cursor type to AdOpenStatic, I call a stored procedure thru the recordset's Open() method, and the cursor type is set to AdOpenForwardOnly. Any idea what's going on or how to get around this?...

  1. #1

    Default ADO/Stored Procedure Problem

    After creating a new recordset and setting the cursor
    type to AdOpenStatic, I call a stored procedure thru the
    recordset's Open() method, and the cursor type is set to
    AdOpenForwardOnly. Any idea what's going on or how to
    get around this?
    James Arnall Guest

  2. #2

    Default Re: ADO/Stored Procedure Problem

    Did you define your variable of AdOpenStatic to 3?

    Ray at work

    "James Arnall" <jamesarnallmsn.com> wrote in message
    news:0c8c01c361c6$ff3f1910$a001280aphx.gbl...
    > After creating a new recordset and setting the cursor
    > type to AdOpenStatic, I call a stored procedure thru the
    > recordset's Open() method, and the cursor type is set to
    > AdOpenForwardOnly. Any idea what's going on or how to
    > get around this?

    Ray at Guest

  3. #3

    Default Re: ADO/Stored Procedure Problem

    James Arnall wrote:
    > After creating a new recordset and setting the cursor
    > type to AdOpenStatic, I call a stored procedure thru the
    > recordset's Open() method, and the cursor type is set to
    > AdOpenForwardOnly. Any idea what's going on or how to
    > get around this?
    1. Why do you need a static cursor? Usually a forward-only cursor is
    sufficient for use in asp.
    2. We need to see the code. FWIW, this code results in a static cursor for
    me (assuming you have the ADO constants defined):
    set rs = server.createobject("adodb.recordset")
    rs.open "proc_name", cn, adOpenStatic,,adCmdStoredProc

    If you need a client-side static cursor, then set the cursorlocation
    property of the rs object to adUseClient before calling the Open method.

    Bob Barrows


    Bob Barrows Guest

  4. #4

    Default Re: ADO/Stored Procedure Problem

    Yes, I am setting the CursorType to AdOpenStatic. Alternately, I tried
    setting it to 3:

    SearchRS.CursorType = AdOpenStatic
    -0r-
    SearchRS.CursorType = 3

    After calling SearchRS.Open, SearchRS.CursorType now = 0.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    James Arnall Guest

  5. #5

    Default Re: ADO/Stored Procedure Problem

    Normally I would stick to a forward-only cursor, but unfortunately I
    have to include legacy code that uses attributes of static cursors, such
    as RecordCount, PageSize, bookmarks, etc.

    The code you included is, essentially, how I'm opening the recordset. I
    just discovered that it works (that is, that the recordset retains the
    static cursor) when my stored procedure contains only a straightforward
    "SELECT", but not when I declare custom variables.

    Thanks for your help!

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    James Arnall Guest

  6. #6

    Default Re: ADO/Stored Procedure Problem

    James Arnall wrote:
    > Normally I would stick to a forward-only cursor, but unfortunately I
    > have to include legacy code that uses attributes of static cursors,
    > such as RecordCount, PageSize, bookmarks, etc.
    >
    > The code you included is, essentially, how I'm opening the recordset.
    > I just discovered that it works (that is, that the recordset retains
    > the static cursor) when my stored procedure contains only a
    > straightforward "SELECT", but not when I declare custom variables.
    >
    Please provide an example. I'm not sure what you mean by "custom variables".

    Bob Barrows


    Bob Barrows Guest

  7. #7

    Default Re: ADO/Stored Procedure Problem

    James Arnall wrote:
    > Normally I would stick to a forward-only cursor, but unfortunately I
    > have to include legacy code that uses attributes of static cursors,
    > such as RecordCount, PageSize, bookmarks, etc.
    >
    > The code you included is, essentially, how I'm opening the recordset.
    > I just discovered that it works (that is, that the recordset retains
    > the static cursor) when my stored procedure contains only a
    > straightforward "SELECT", but not when I declare custom variables.
    >
    PS. Again, if you set the recordset's CursorLocation to adUseClient before
    opening it, you will ALWAYS get a static cursor.

    Bob


    Bob Barrows Guest

  8. #8

    Default Re: ADO/Stored Procedure Problem

    If my stored procedure is defined as follows...

    CREATE sp_MyProc AS

    SELECT * FROM TABLE

    GO

    ... then the recordset I open using this stored proc, if set with a
    static cursor, will retain the static cursor. However, if I redefine my
    procedure as follows...

    CREATE sp_MyProc AS

    DECLARE x int

    SELECT * FROM TABLE

    GO

    ... the recordset will switch to a forward only cursor.

    Does this answer your question?

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    James Arnall Guest

  9. #9

    Default Re: ADO/Stored Procedure Problem

    Sorry, I should have specified which database. I am using SQL Server 7.
    And declaring SET NOCOUNT ON does not help. In fact, simply adding SET
    NOCOUNT ON without declaring any local SQL variables will reset the
    cursortype to forward-only when the recordset is opened. Very puzzling
    indeed.

    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    James Arnall Guest

  10. #10

    Default Re: ADO/Stored Procedure Problem

    James Arnall wrote:
    > Sorry, I should have specified which database. I am using SQL Server
    > 7. And declaring SET NOCOUNT ON does not help. In fact, simply
    > adding SET NOCOUNT ON without declaring any local SQL variables will
    > reset the cursortype to forward-only when the recordset is opened.
    Even when you set cursorlocation to adUseClient? Impossible.
    > Very puzzling indeed.
    >
    Well, I am out of answers until you provide a repro script with an example
    stored procedure that causes the problem, along with the create table
    statement for the table used by your procedure, and the actual asp code used
    to call the procedure.

    Sorry,
    Bob barrows


    Bob Barrows Guest

Similar Threads

  1. stored procedure problem
    By Leon Shaw in forum Dreamweaver AppDev
    Replies: 42
    Last Post: November 7th, 05:13 PM
  2. MSSQL Stored Procedure Problem
    By Sequenzia in forum Coldfusion Database Access
    Replies: 2
    Last Post: October 10th, 07:56 PM
  3. Problem building stored procedure
    By Mukul Gandhi in forum IBM DB2
    Replies: 4
    Last Post: September 19th, 05:52 PM
  4. Stored Procedure/Parameter problem
    By ElmoWatson in forum ASP.NET General
    Replies: 1
    Last Post: August 6th, 01:19 AM
  5. Problem with DBMS_JOB within a stored procedure
    By dwayne in forum Oracle Server
    Replies: 2
    Last Post: July 8th, 03:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139