p2p.wrox.com: Archives - asp_web

asp_web_howto thread: HELP! How to use SQL Stored Procedure in ASP

Message #1 by info@e... on Fri, 22 Jun 2001 05:01:21

Can someone please help me? 



I am having trouble passing a value as a URL parameter with a stored 

procedure. I have a product detail page that needs to change product 

everytime a different value is passed. It has to look like 

this: "http://www.mysite.com/product.asp?sku=44444". Now, if I were to 

replace the sku 44444 with a 99999, the product will have to change. Now, 

I have a stored procedure with SQL, below are the codes. The codes DO work 

right now. However, they are only displaying one product "052843" - see 

below. FYI - the reason I am using a stored procedure is for better 

performance and for a price markup. Please respond and tell me what to add 

or change. Thanks!





<---SQL Stored Procedure Begins--->



CREATE PROCEDURE spMarkup AS

/* I'm setting up some holding variables here. This way, if we get asked

** to run the query again with a slightly different value, we'll only have

** to change it in one place.

*/

DECLARE @Markup	money

DECLARE @Multiplier	money

DECLARE @Sku	varchar



SELECT @Markup = .05

SELECT @Multiplier = @Markup + 1



SELECT *,

	Cost * @Multiplier AS "Marked Up Price", "Our Price"

	CASE WHEN FLOOR(Cost * @Multiplier + .24)

		> FLOOR(Cost * @Multiplier)

			THEN FLOOR(Cost * @Multiplier) + .99

	WHEN FLOOR(Cost * @Multiplier + .5) >

		FLOOR(Cost * @Multiplier)

			THEN FLOOR(Cost * @Multiplier) + .99

	ELSE FLOOR(Cost * @Multiplier) + .99

	END



FROM Price p  LEFT JOIN Images i ON i.sku=p.sku  JOIN MasterVendors m ON 

m.SubVendorNumber = p.VndrNumber

WHERE p.Sku = '052843'

ORDER BY p.Sku

GO



<---SQL Stored Procedure Ends--->



<---ASP Code Begins--->



<%

set spMarkup = Server.CreateObject("ADODB.Command")

spMarkup.ActiveConnection = MM_connDK_STRING

spMarkup.CommandText = "dbo.spMarkup"

spMarkup.Parameters.Append spMarkup.CreateParameter("@RETURN_VALUE", 3, 4)

spMarkup.CommandType = 4

spMarkup.CommandTimeout = 0

spMarkup.Prepared = true

set rsProducts = spMarkup.Execute

rsProducts_numRows = 0

%>



<---ASP Code Ends--->



Regards,

Elmer M.

Message #2 by "Ken Schaefer" <ken@a...> on Fri, 22 Jun 2001 18:07:36 +1000

(change the value in varChar to be the appropriate length)



CREATE PROCEDURE spMarkup



    @PKU varChar(50)



AS



    ' rest of proc here



    WHERE p.Sku = @PKU

    ORDER BY p.Sku



GO



And in ASP:



spMarkup.Parameters.Append spMarkup.CreateParameter("@RETURN_VALUE", 3, 4)

spMarkup.Parameters.Append spMarkup.CreateParameter("@PKU, adVarChar,

adParamInput, 50, strPKU)



Cheers

Ken









~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: <info@e...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Sent: Friday, June 22, 2001 5:01 AM

Subject: [asp_web_howto] HELP! How to use SQL Stored Procedure in ASP





: Can someone please help me?

:

: I am having trouble passing a value as a URL parameter with a stored

: procedure. I have a product detail page that needs to change product

: everytime a different value is passed. It has to look like

: this: "http://www.mysite.com/product.asp?sku=44444". Now, if I were to

: replace the sku 44444 with a 99999, the product will have to change. Now,

: I have a stored procedure with SQL, below are the codes. The codes DO work

: right now. However, they are only displaying one product "052843" - see

: below. FYI - the reason I am using a stored procedure is for better

: performance and for a price markup. Please respond and tell me what to add

: or change. Thanks!

:

:

: <---SQL Stored Procedure Begins--->

:

: CREATE PROCEDURE spMarkup AS

: /* I'm setting up some holding variables here. This way, if we get asked

: ** to run the query again with a slightly different value, we'll only have

: ** to change it in one place.

: */

: DECLARE @Markup money

: DECLARE @Multiplier money

: DECLARE @Sku varchar

:

: SELECT @Markup = .05

: SELECT @Multiplier = @Markup + 1

:

: SELECT *,

: Cost * @Multiplier AS "Marked Up Price", "Our Price"

: CASE WHEN FLOOR(Cost * @Multiplier + .24)

: > FLOOR(Cost * @Multiplier)

: THEN FLOOR(Cost * @Multiplier) + .99

: WHEN FLOOR(Cost * @Multiplier + .5) >

: FLOOR(Cost * @Multiplier)

: THEN FLOOR(Cost * @Multiplier) + .99

: ELSE FLOOR(Cost * @Multiplier) + .99

: END

:

: FROM Price p  LEFT JOIN Images i ON i.sku=p.sku  JOIN MasterVendors m ON

: m.SubVendorNumber = p.VndrNumber

: WHERE p.Sku = '052843'

: ORDER BY p.Sku

: GO

:

: <---SQL Stored Procedure Ends--->

:

: <---ASP Code Begins--->

:

: <%

: set spMarkup = Server.CreateObject("ADODB.Command")

: spMarkup.ActiveConnection = MM_connDK_STRING

: spMarkup.CommandText = "dbo.spMarkup"

: spMarkup.Parameters.Append spMarkup.CreateParameter("@RETURN_VALUE", 3, 4)

: spMarkup.CommandType = 4

: spMarkup.CommandTimeout = 0

: spMarkup.Prepared = true

: set rsProducts = spMarkup.Execute

: rsProducts_numRows = 0

: %>

:

: <---ASP Code Ends--->

Message #3 by info@e... on Sat, 23 Jun 2001 09:43:02

Thanks for the info, I tried the example that you gave me and I received a 

Windows 2000 error. It was saying something about "arguments" on the 

parameter line. Do you know what that may be? I tried using it on an empty 

asp page & received the same thing. 



FYI, I fixed the asp code so that it looks like this:



spMarkup.Parameters.Append spMarkup.CreateParameter("@SKU", adVarChar, 

adParamInput, 50, strSKU)



Someone please help! Why am I getting that error? & how do I fix it? 

Thanks everyone!!!



Regards,

Elmer M.







> (change the value in varChar to be the appropriate length)

> 

> CREATE PROCEDURE spMarkup

> 

>     @PKU varChar(50)

> 

> AS

> 

>     ' rest of proc here

> 

>     WHERE p.Sku = @PKU

>     ORDER BY p.Sku

> 

> GO

> 

> And in ASP:

> 

> spMarkup.Parameters.Append spMarkup.CreateParameter("@RETURN_VALUE", 3, 

4)

> spMarkup.Parameters.Append spMarkup.CreateParameter("@PKU, adVarChar,

> adParamInput, 50, strPKU)

> 

> Cheers

> Ken

> 

>

Message #4 by "Ken Schaefer" <ken@a...> on Sun, 24 Jun 2001 15:06:15 +1000

What is the /exact/ error you are getting? Is it really that hard to cut and

paste it into an email?



.CreateParameter accepts the following arguments:



(name, type, input/output/return, size, value)



so, you need to make sure the type is correct (eg adVarChar, adInteger etc),

and the size is correct (eg adInteger is 4, adVarChar depends on how big

your field is, and you need to set value to be whatever value you want to

pass to the database. As well, you need to create as many params as the

stored procedure will expect.



If you are still have problems, please post the relevent ASP Code (mark the

line the error is happening on), plus your stored procedure as it now

stands, plus the exact error message and number.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: <info@e...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Sent: Saturday, June 23, 2001 9:43 AM

Subject: [asp_web_howto] Re: HELP! How to use SQL Stored Procedure in ASP





: Thanks for the info, I tried the example that you gave me and I received a

: Windows 2000 error. It was saying something about "arguments" on the

: parameter line. Do you know what that may be? I tried using it on an empty

: asp page & received the same thing.

:

: FYI, I fixed the asp code so that it looks like this:

:

: spMarkup.Parameters.Append spMarkup.CreateParameter("@SKU", adVarChar,

: adParamInput, 50, strSKU)

:

: Someone please help! Why am I getting that error? & how do I fix it?

: Thanks everyone!!!

:

: Regards,

: Elmer M.

:

:

:

: > (change the value in varChar to be the appropriate length)

: >

: > CREATE PROCEDURE spMarkup

: >

: >     @PKU varChar(50)

: >

: > AS

: >

: >     ' rest of proc here

: >

: >     WHERE p.Sku = @PKU

: >     ORDER BY p.Sku

: >

: > GO

: >

: > And in ASP:

: >

: > spMarkup.Parameters.Append spMarkup.CreateParameter("@RETURN_VALUE", 3,

: 4)

: > spMarkup.Parameters.Append spMarkup.CreateParameter("@PKU, adVarChar,

: > adParamInput, 50, strPKU)

: >

: > Cheers

: > Ken

: >

: >

:

: ---

: * Fast, Full-Featured Microsoft� Excel Web Reports & Charts!

: A breakthrough in high performance Web application development,

SoftArtisans

: ExcelWriter 1.1 supports native Excel charting, image insertion, and

: advanced functions & formatting. One click generates presentation-quality

: Excel spreadsheets-and ExcelWriter performs over 100 times faster than the

: Excel Object. Several editions, including ExcelWriterFREE, are available.

: http://www.softartisans.com/softartisans/excelwriter.html>

:




$subst('Email.Unsub')

Message #5 by info@e... on Wed, 27 Jun 2001 08:00:31

Hello there,



Sorry for not including the code. Below is the exact error that I am 

getting and the exact SQL sproc as it appears now. I hope you can help me. 

Thanks a lot!



Regards,

Elmer M.



<--- ERROR BEGINS HERE --->

The page cannot be displayed 

There is a problem with the page you are trying to reach and it cannot be 

displayed. 



---------------------------------------------------------------------------

-----



Please try the following:



Click the Refresh button, or try again later.



Open the localhost home page, and then look for links to the information 

you want. 

HTTP 500.100 - Internal Server Error - ASP error

Internet Information Services



---------------------------------------------------------------------------

-----



Technical Information (for support personnel)



Error Type:

ADODB.Command (0x800A0BB9)

Arguments are of the wrong type, are out of acceptable range, or are in 

conflict with one another.

/dksystems/shop/product.asp, line 19 



<--- ERROR ENDS HERE --->



<---SQL Stored PROC BEGINS HERE --->

CREATE PROCEDURE spMarkup AS

/* I'm setting up some holding variables here. This way, if we get asked

** to run the query again with a slightly different value, we'll only have

** to change it in one place.

*/



DECLARE @Markup	money

DECLARE @Multiplier	money

DECLARE @Sku	varchar(50)



SELECT @Markup = .05

SELECT @Multiplier = @Markup + 1



SELECT *,

	Cost * @Multiplier AS "Marked Up Price", "Our Price"

	CASE WHEN FLOOR(Cost * @Multiplier + .24)

		> FLOOR(Cost * @Multiplier)

			THEN FLOOR(Cost * @Multiplier) + .99

	WHEN FLOOR(Cost * @Multiplier + .5) >

		FLOOR(Cost * @Multiplier)

			THEN FLOOR(Cost * @Multiplier) + .99

	ELSE FLOOR(Cost * @Multiplier) + .99

	END



FROM Price p  LEFT JOIN Images i ON i.sku=p.sku  JOIN MasterVendors m ON 

m.SubVendorNumber = p.VndrNumber

WHERE p.Sku = @Sku

ORDER BY p.Sku

GO



<--- SQL ENDS HERE --->













> What is the /exact/ error you are getting? Is it really that hard to cut 

and

> paste it into an email?

> 

> .CreateParameter accepts the following arguments:

> 

> (name, type, input/output/return, size, value)

> 

> so, you need to make sure the type is correct (eg adVarChar, adInteger 

etc),

> and the size is correct (eg adInteger is 4, adVarChar depends on how big

> your field is, and you need to set value to be whatever value you want to

> pass to the database. As well, you need to create as many params as the

> stored procedure will expect.

> 

> If you are still have problems, please post the relevent ASP Code (mark 

the

> line the error is happening on), plus your stored procedure as it now

> stands, plus the exact error message and number.

> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> ----- Original Message -----

> From: <info@e...>

> To: "ASP Web HowTo" <asp_web_howto@p...>

> Sent: Saturday, June 23, 2001 9:43 AM

> Subject: [asp_web_howto] Re: HELP! How to use SQL Stored Procedure in ASP

> 

> 

> : Thanks for the info, I tried the example that you gave me and I 

received a

> : Windows 2000 error. It was saying something about "arguments" on the

> : parameter line. Do you know what that may be? I tried using it on an 

empty

> : asp page & received the same thing.

> :

> : FYI, I fixed the asp code so that it looks like this:

> :

> : spMarkup.Parameters.Append spMarkup.CreateParameter("@SKU", adVarChar,

> : adParamInput, 50, strSKU)

> :

> : Someone please help! Why am I getting that error? & how do I fix it?

> : Thanks everyone!!!

> :

> : Regards,

> : Elmer M.

> :

> :

> :

> : > (change the value in varChar to be the appropriate length)

> : >

> : > CREATE PROCEDURE spMarkup

> : >

> : >     @PKU varChar(50)

> : >

> : > AS

> : >

> : >     ' rest of proc here

> : >

> : >     WHERE p.Sku = @PKU

> : >     ORDER BY p.Sku

> : >

> : > GO

> : >

> : > And in ASP:

> : >

> : > spMarkup.Parameters.Append spMarkup.CreateParameter("@RETURN_VALUE", 

3,

> : 4)

> : > spMarkup.Parameters.Append spMarkup.CreateParameter("@PKU, adVarChar,

> : > adParamInput, 50, strPKU)

> : >

> : > Cheers

> : > Ken

> :

Message #6 by Pieter Schutte <psc@a...> on Wed, 27 Jun 2001 14:08:42 +0200


The problem is in the way you call the stored procedure from the ASP page.

You need to include the adovbs.inc file in your page if you are going to use

the constants, otherwise you need to give the numerical value of the

constants you use to call the stored proc



-----Original Message-----

From: info@e... [mailto:info@e...]

Sent: Wednesday, June 27, 2001 10:01 AM

To: ASP Web HowTo

Subject: [asp_web_howto] Re: HELP! How to use SQL Stored Procedure in

ASP





Hello there,



Sorry for not including the code. Below is the exact error that I am 

getting and the exact SQL sproc as it appears now. I hope you can help me. 

Thanks a lot!



Regards,

Elmer M.

Message #7 by "Ken Schaefer" <ken@a...> on Thu, 28 Jun 2001 13:51:20 +1000

www.adopenstatic.com/faq/800a0bb9step2.asp



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: <info@e...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Sent: Wednesday, June 27, 2001 8:00 AM

Subject: [asp_web_howto] Re: HELP! How to use SQL Stored Procedure in ASP





: Hello there,

:

: Sorry for not including the code. Below is the exact error that I am

: getting and the exact SQL sproc as it appears now. I hope you can help me.

: Thanks a lot!

:

: Regards,

: Elmer M.

:

: <--- ERROR BEGINS HERE --->

: The page cannot be displayed

: There is a problem with the page you are trying to reach and it cannot be

: displayed.

:

: --------------------------------------------------------------------------

-

: -----

:

: Please try the following:

:

: Click the Refresh button, or try again later.

:

: Open the localhost home page, and then look for links to the information

: you want.

: HTTP 500.100 - Internal Server Error - ASP error

: Internet Information Services

:

: --------------------------------------------------------------------------

-

: -----

:

Message #8 by info@e... on Thu, 28 Jun 2001 06:42:55

Hello Ken,



I tried what you suggested & I also visited the site. Thanks for the info. 

However, I tried what they said on the website step 2:



Add:



<!-- #include virtual="/includes/adovbs.inc" --> 



& I received an error"file not found" so I tried changing the "virtual" 

to "file" & I received the following error:



Error Type:

Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure spMarkup has no 

parameters and arguments were supplied.

/dksystems/shop/product.asp, line 24



Do you know what this means? Is it even reading the include file?? Thanks 

for all your help.



Regards,

Elmer M.







> www.adopenstatic.com/faq/800a0bb9step2.asp

> 

> Cheers

> Ken

> 

> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

> ----- Original Message -----

> From: <info@e...>

> To: "ASP Web HowTo" <asp_web_howto@p...>

> Sent: Wednesday, June 27, 2001 8:00 AM

> Subject: [asp_web_howto] Re: HELP! How to use SQL Stored Procedure in ASP

> 

> 

> : Hello there,

> :

> : Sorry for not including the code. Below is the exact error that I am

> : getting and the exact SQL sproc as it appears now. I hope you can help 

me.

> : Thanks a lot!

> :

> : Regards,

> : Elmer M.

> :

> : <--- ERROR BEGINS HERE --->

> : The page cannot be displayed

> : There is a problem with the page you are trying to reach and it cannot 

be

> : displayed.

> :

> : -----------------------------------------------------------------------

---

> -

> : -----

> :

> : Please try the following:

> :

> : Click the Refresh button, or try again later.

> :

> : Open the localhost home page, and then look for links to the 

information

> : you want.

> : HTTP 500.100 - Internal Server Error - ASP error

> : Internet Information Services

> :

> : -----------------------------------------------------------------------

---

> -

> : -----

> :

> 

>

Message #9 by "Ken Schaefer" <ken@a...> on Thu, 28 Jun 2001 18:51:26 +1000

The first error shows that the file couldn't be found.

The second error shows that the file has been found. Instead, you are doing

something wrong with your Command object - the stored procedure is expecting

no parameters, but you are supplying parameters, and SQL Server doesn't know

what to do with them.



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: <info@e...>

To: "ASP Web HowTo" <asp_web_howto@p...>

Sent: Thursday, June 28, 2001 6:42 AM

Subject: [asp_web_howto] Re: HELP! How to use SQL Stored Procedure in ASP





: Hello Ken,

:

: I tried what you suggested & I also visited the site. Thanks for the info.

: However, I tried what they said on the website step 2:

:

: Add:

:

: <!-- #include virtual="/includes/adovbs.inc" -->

:

: & I received an error"file not found" so I tried changing the "virtual"

: to "file" & I received the following error:

:

: Error Type:

: Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)

: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure spMarkup has no

: parameters and arguments were supplied.

: /dksystems/shop/product.asp, line 24

:

: Do you know what this means? Is it even reading the include file?? Thanks

: for all your help.

:

: Regards,

: Elmer M.

:

:

:

: > www.adopenstatic.com/faq/800a0bb9step2.asp

: >

: > Cheers

: > Ken

: >

Return to Index

P2P Archives