aspdb classic SQL stored procedure

Printable View

12-08-2004, 09:26 AM
Forum

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
12-08-2004, 03:31 PM
Forum

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
12-10-2004, 01:22 PM
Frank

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
12-10-2004, 02:08 PM
Forum

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
12-15-2004, 03:15 AM
John

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
12-15-2004, 01:19 PM
Forum

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
12-15-2004, 05:20 PM
John

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
12-16-2004, 02:54 PM
Forum

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.
12-18-2004, 10:53 AM
Frank

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