dcsimg
Results 1 to 9 of 9

Thread: aspdb classic SQL stored procedure

  1. #1
    Join Date
    Dec 2004
    Posts
    5

    aspdb classic SQL stored procedure

    We are getting odd record count when we are running the following page

    Dim x

    Set X=Server.CreateObject("ASP.db")
    x.dbunit = 57
    x.dbDSN = Application("CDTA_ConnectionString")
    x.dbgridinc = 6
    X.dbStoredProc = "sptest"
    x.dbmode = "grid"
    x.dbCountSQL="sptest"
    x.aspdb
    Response.write x.dbRecordCount
    Response.write x.dbLicense
    Response.Write x.dbversion


    sptest only return 218 records from a sql table with no parameters. When the grid first returns it show 1/6 of 218 when I hit next it show 7/12 of 12 if I hit back I get 1/6 of 6 . I've tried the lowspeed but that didn't seem to make any difference.

    Thanks
    Jim

  2. #2
    Join Date
    Dec 2004
    Posts
    5

    Answer?

    We were able to create a working item with the example on the site. When working with it we removed the parameter and started getting the odd results we had seen with our stored procedures. When we added a parameter to our stored procedure the navigation buttons started workinge fine. I'm not sure why we would be required to have a parameter but that seemed to fix our problem. I'm not sure if this is the correct fix but its gotten us by our problem. I'd like a better explination of this.

    Thanks
    Jim

  3. #3
    Join Date
    Oct 2002
    Posts
    933
    how about make an example using PUBS or Northwind in MS-SQL to illustrate the problem so we can run it. Th SP normally is just a pass thru.

    Frank

  4. #4
    Join Date
    Dec 2004
    Posts
    5

    Example of problem

    <%Response.buffer = True%>

    <HTML>
    <HEAD><TITLE>Enterprise SP Examples</TITLE></HEAD>

    <BODY BGColor=#e0e0e0>
    This example shows Enterprise SP Examples.<BR>
    <HR>

    <CENTER><h2>ASP-db Enterprise Stored Procedure Demo </h2>
    The Parameter was removed from your example and replaced with a 1.
    <TABLE Border=3 Cellpadding=15><TR><TD BGCOLOR=WHITE>
    <B>
    CREATE PROCEDURE dbo.byroyalty AS <BR>
    Select au_id from titleauthor where titleauthor.royaltyper = 1 <BR>
    </B>
    </TD></TR></TABLE>

    </CENTER>
    <P>


    <%

    Response.write("<HR>Method #1 (byroyalty) - Using on the fly<br>")

    Set X=Server.CreateObject("ASP.db")

    X.dbQuickProps="1;PUBS;;grid;4,auto,lightgreen"
    X.dbDSN = "DSN=PUBS;uid=sa;pwd=;"
    X.dbStoredProc="byroyalty,4;"
    X.ASPdb


    %>

    </BODY>
    </HTML>



    '*************************Stored Procedure****************************************

    'CREATE PROCEDURE byroyalty
    'AS
    'select au_id from titleauthor
    'where titleauthor.royaltyper >= 1

    '*************************Stored Procedure****************************************




    When this page is run The first page comes up ok with 25 records. When I go to the second it then says there are only 10 records. Once I add back a parameter everything works fine.

    Thanks
    Jim

  5. #5
    Join Date
    Oct 2002
    Posts
    93
    Hi Jim,

    You don't have dbDBtype="SQL" in there. Is that a typo on the forum here or is it missing from your production code too? That can make a huge difference.

    Also, have you tried the dbStoredProcCmdParams parameter instead?

    ex: X.dbStoredProcCmdParams="Procedure Name; Name1, Type1, In-Out1, Size1, Param1, Param2...; Repeat"<BR>

    i.e.: X.dbStoredProcCmdParams="byroyalty;@percentage,3,1 ,5,100"

    Let us know. Thanks,
    John

  6. #6
    Join Date
    Dec 2004
    Posts
    5
    John ,
    I didn't have the dbDBtype="SQL" in there. That didn't seem to make a difference in using the dbStoredProc but when I added it and changed to the dbStoredProcCmdParams it seems to work fine.

    Thanks for the help,

    Jim

  7. #7
    Join Date
    Oct 2002
    Posts
    93
    Hi Jim,

    I didn't catch it earlier, but your dbStoredProc line only had ONE parameter. That's not what the manual says! It says you need TWO - the CommandType and the parameters. Therefore it SHOULD have been:

    dbStoredProc="byroyalty,4,100" for type 4 (stored proc) and 100 (the value). That works FINE. You can even leave it off (byroyalty,,100) if you wish. Just make sure to have the extra comma in there.

    Give it a try!

    Thanks,
    John

  8. #8
    Join Date
    Dec 2004
    Posts
    5

    Parameter

    It wasn't there because the store procedure didn't have any parameters. Thats when the count was wrong. If I had a parameter everything worked correctly. We were rewriting some stored procedures and had some info hard coded for ease of testing when this originally happened.

  9. #9
    Join Date
    Oct 2002
    Posts
    933
    If there is no parameters in a SP then it si a stand alone SP and the syntax is

    X.dbStoredProc="byroyalty"

    If yohave parameters then you can use either on-the-fly- or full params syntax.

    The byroyalty SP has a parameter.

    Frank

Posting Permissions

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