asked on

How to apply a 2nd Store Procedure within the same web page

I have a product details page that user goes to when he wants more information about an individual product.
The url looks something like this. www.mywebsite.com?product.asp?ID=2
and my store procedure looks something like this:
ALTER PROCEDURE [my].[sp_details]
@ID varchar (255)
As
SELECT LINK_ID, CAT_ID, LINK_NAME
FROM tableProducts
WHERE LINK_ID = @ID

On the same page I want to call up 10 similar products that have the same "CAT_ID" as the product being displayed.

I tried added the below SP but I'm not getting the results I need.
ALTER PROCEDURE [my].[sp_RandombyCat]
@ID varchar (255),
@MMColParam nvarchar(5)
As
SELECT TOP 10 LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts
WHERE LINK_ID = @ID AND CAT_ID LIKE '%'+@MMColParam+'%'
ORDER BY NEWID()

I work with: Classic ASP VB, SQL 2005, SQL Server Express, DreamweaverCS3, newbie to SQL

Aneesh

ALTER PROCEDURE [my].[sp_RandombyCat]
@ID varchar (255),
@MMColParam nvarchar(5)
As
EXEC( 'SELECT TOP 10 LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts WHERE LINK_ID IN ( ' +@ID + ') AND CAT_ID LIKE ''%'+@MMColParam+'%''
ORDER BY NEWID()' )

swaggerking

ASKER

aneeshattingal:
I'll give that shot. Unfortunately they just shut down my testing server for maintenance so I won't be able to get back to you with a response for a couple of hours.

Aneesh

ohh ok

swaggerking

ASKER

aneeshattingal:
I inserted your statement but I'm still not getting the results I had hoped for. When I run both stored procedures on my web page I'm only getting identical matching records. I want to display 10 related products that contain the same CAT_ID for any ID that I pull up

If I go to the details page for product.asp?ID=2 which for example purposes is Milk and it has CAT_ID = Beverages it would pull 10 related beverages.
Orange Juice
Water
Dr. Pepper
Sprite
Coffee
etc...

If product.asp?ID=44 was Hotdog and had CAT_ID = Meat I might see related 10 related meat products.
Sirloin
Ground beef
Chicken
Turkey
etc...

Aneesh

can this ID contain more than one IDs separated with commas ? if this is not a comma separated list then you dont really need that dynamic sql

ALTER PROCEDURE [my].[spRandombyCat]
@ID varchar (255),
@MMColParam nvarchar(5)    
As
SELECT TOP 10 * FROM (
SELECT LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts
WHERE LINK_ID = @ID AND CAT_ID LIKE '%'+@MMColParam+'%'
UNION 
SELECT TOP 10 LINK_ID, LINK_NAME, CAT_ID
FROM tableProducts WHERE LINK_ID <> @ID AND CAT_ID LIKE '%'+@MMColParam+'%'
ORDER BY NEWID()
)

Open in new window

swaggerking

ASKER

aneeshattingal:
I really appreciate you helping a newbie like myself and I'm sure I'm making this harder than it need be.

First, I keep getting an error message:
spRandombyCat Line 13 Incorrect syntax near ')'.

Second, As a layman, I'm trying to comprehend your provided statement. I was wondering why you are calling TOP 10 twice from Line 5 and Line 10?

Third, Can this ID contain more than one IDs separated with commas?
Are you referring to LINK_ID or CAT_ID?
CAT_ID can contain more than 1 CAT_ID. Some categories will overlap so that is why I'm using LIKE in the WHERE statement. I'm using alpha characters.
Fruits = a
Vegetables = b
Fruits & Vegetables = ab
Chicken = c
Rice = d
MealPlan = acd

I'm providing jpg page example to what exactly I'm trying accomplish. Maybe this will help.

product.jpg

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

swaggerking

ASKER

Weird, I still only get 1 record showing. For testing purposes I changed all of the CAT_ID to the same value in my table and still only 1 record shows. The record that is showing is based upon it's unique ID.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include virtual="\Connections\mystring.asp" -->
<%
 
Dim Command1__ID
Command1__ID = "1"
if(Request("ID") <> "") then Command1__ID = Request("ID")
 
Dim Command1__MMColParam
Command1__MMColParam = "a"
if(Request("MMColParam") <> "") then Command1__MMColParam = Request("MMColParam")
 
%>
<%
 
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_CONN_mystring_STRING
Command1.CommandText = "mydatabase.sp_RandombyCat"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@ID", 200, 1,255,Command1__ID)
Command1.Parameters.Append Command1.CreateParameter("@MMColParam", 200, 1,5,Command1__MMColParam)
set RS_Random = Command1.Execute
RS_Random_numRows = 0
 
%>
 
<%
Dim Repeat1__numRows
Dim Repeat1__index
 
Repeat1__numRows = -1
Repeat1__index = 0
RS_Random_numRows = RS_Random_numRows + Repeat1__numRows
%>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
 
<body>
<% 
While ((Repeat1__numRows <> 0) AND (NOT RS_Random.EOF)) 
%>
 
<%=(RS_Random.Fields.Item("LINK_NAME").Value)%>
 
<% 
  Repeat1__index=Repeat1__index+1
  Repeat1__numRows=Repeat1__numRows-1
  RS_Random.MoveNext()
Wend
%>
</body>
</html>

Open in new window

product2.jpg

Aneesh

Did you try running from sqlsever

swaggerking

ASKER

Yes, it works PERFECTLY in SQL Server. Something getting lost in translation to DW. I guess I will have to work that one out and/or start a new thread.

swaggerking

ASKER

Statement works exactly how I needed it to work. I still need to work out some bugs on my end but the provide statement work perfectly as intended in SQLserver. Thanks so much for your help.

Aneesh

i think so, i have no idea of dreamweaver ;)

swaggerking

ASKER

I got it to work so I wanted to share it in case some one else has problems running stored procedures in Dreamweaver CS3.

After of day of pulling my hair out and googling I finally figured out why the stored procedure wasn't executing properly in DWcs3.

I needed to define my schema and catalog values in my connection string under the "Advanced" button. Once I did that my SP ran as the gods intended.
advConString.gif