How do I create a SQL 2005 stored procedure with LIKE variable and bring in to Dreamweaver

I've been "trying to learn" to do store procedures with a LIKE variable and bring into Dreamweaver CS3.

I have category field in my db table setup as KAT_ID nvarchar (5) that can hold  a max of five possible variations. A, AB, ABC, ABCD, ABCDE in any order. Each letter represents an individual category and some categories overlap, thus containing ABC or BD or ADE or etc....

I want my url to look something like this:
www.mywebsite.com/mypage?Category?=A
www.mywebsite.com/mypage?Category?=BC

I can create RecordSet set in Dreamweaver that pulls the results I need but I'm unable to do so when using a Stored Procedure.

My DW version RS looks like this and retrieves the data on my web page without problems:

<!--#include virtual="/Connections/CONN_String.asp" -->
<%
Dim RecordSet1__MMColParam
RecordSet1__MMColParam = "A"
If (Request.QueryString("Category") <> "") Then
  RecordSet1__MMColParam = Request.QueryString("Category")
End If
%>
<%
Dim RecordSet1
Dim RecordSet1_cmd
Dim RecordSet1_numRows

Set RecordSet1_cmd = Server.CreateObject ("ADODB.Command")
RecordSet1_cmd.ActiveConnection = MM_CONN_MPW_STRING
RecordSet1_cmd.CommandText = "SELECT * FROM dbo.mytable WHERE KAT_ID LIKE ?"
RecordSet1_cmd.Prepared = true
RecordSet1_cmd.Parameters.Append RecordSet1_cmd.CreateParameter("param1", 200, 1, 20, "%" + RecordSet1__MMColParam + "%") ' adVarChar

Set RecordSet1 = RecordSet1_cmd.Execute
RecordSet1_numRows = 0
%>


I've tried two Store Procedure which work when I run the query in SQL Server Express. When I try importing the SP into Dreamweaver I get an error when I try declaring the variable. See jpeg screen shot attachment.
Version 1:
select *
FROM dbo.mytable
WHERE KAT_ID LIKE '%@MMColParam%'
ORDER BY LINK_NAME DESC


Version 2:
ALTER PROCEDURE [mytable].[sp_chooseCategory]
AS
DECLARE @MMColParam nvarchar(5)
select *
FROM dbo.mytable
WHERE KAT_ID LIKE '%@MMColParam%'
ORDER BY LINK_NAME DESC

I'm working with SQL 2005 DB and in classic ASP VB

directionerror.jpg
swaggerkingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
it should be like this

select *
FROM dbo.mytable
WHERE KAT_ID LIKE '%'+@MMColParam+'%'
ORDER BY LINK_NAME DESC
0
swaggerkingAuthor Commented:
aneeshattingal:

When I execute SP in SQL Express with your example I get an error must declare scalar variable "@MMColParam" but if I declare it, it works.


DECLARE @MMColParam nvarchar(5)
select *
FROM dbo.mytable
WHERE KAT_ID LIKE '%'+@MMColParam+'%'
ORDER BY LINK_NAME DESC

But, DW still gives me the "Direction error" message as seen in my previous jpeg attachment.

I get an undefined in the following line:
Command1.Parameters.Append Command1.CreateParameter("MMColParam", 200, undefined,5,Command1__MMColParam)


I get error when running in browser:
http://mysite.com/test.asp?category=A

ADODB.Command error '800a0bb9'
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.


Here is full code in DW.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<!--#include virtual="/Connections/myconnectioinstring.asp" -->
<%

Dim Command1__MMColParam
Command1__MMColParam = "A"
if(request.querystring("Category") <> "") then Command1__MMColParam = request.querystring("Category")

%>
<%

set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_Connection_STRING
Command1.CommandText = "my.sp_simpleCat"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("MMColParam", 200, undefined,5,Command1__MMColParam)
set RS_Simple = Command1.Execute
RS_Simple_numRows = 0

%>
<!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>test</title>
</head>

<body>
<%=(RS_Simple.Fields.Item("LINK_NAME").Value)%>
</body>
</html>
<%
RS_Simple.Close()
RS_Simple = Nothing
%>
0
Aneesh RetnakaranDatabase AdministratorCommented:
change the sp like this
ALTER PROCEDURE [dbo].[sp_chooseCategory]
 @MMColParam nvarchar(5)
AS
select *
FROM dbo.mytable
WHERE KAT_ID LIKE '%'+@MMColParam+'%'
ORDER BY LINK_NAME DESC
go

and the Variable on DW, should be '@MMColParam ' , make sure that '@' is present in the variable name
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
swaggerkingAuthor Commented:
aneeshattingal:
I got it to work using your method. YOU ROCK!!
It took me a bit time to back with you because I ran into a hiccup at first when I started getting an error message

800a0bb9  Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

With a google search I read that I needed to include the file adovbs.inc on every page or just add following into the head:
<!--METADATA TYPE="TypeLib" NAME="Microsoft ActiveX Data Objects 2.5 Library" UUID="{00000205-0000-0010-8000-00AA006D2EA4}" VERSION="2.5"-->

I chose the latter since I couldn't find adovbs.inc on my hosted account and it worked with your provided approach.

I'm unfamiliar with both these and the problem has never come up before even with simple store procedure queries that I've performed in the past.

I've added additional points if you could provide some insight.
0
swaggerkingAuthor Commented:
You're solution was dead-on accurate. Much appreciation.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Adobe Dreamweaver

From novice to tech pro — start learning today.