This works really well but no im trying to get it to work in my sql server 2000 database how should i do the stored procedure to get it to work the same as the code below

<%
Dim RsView__varSearch
RsView__varSearch = "%"
if (Request.QueryString("freesearch" <> "" then RsView__varSearch = Request.QueryString("freesearch"
%>
<%
Dim RsView__varPricefr
RsView__varPricefr = "0"
if (Request.QueryString("pricefrom" <> "0" then RsView__varPricefr = Request.QueryString("pricefrom"
%>
<%
Dim RsView__varPriceto
RsView__varPriceto = "99999999"
if (Request.QueryString("priceto" <> "" then RsView__varPriceto = Request.QueryString("priceto"
%>
<%
Dim RsView__varMilagefr
RsView__varMilagefr = "0"
if (Request.QueryString("milagefrom" <> "0" then RsView__varMilagefr = Request.QueryString("milagefrom"
%>
<%
Dim RsView__varMilageto
RsView__varMilageto = "99999999"
if (Request.QueryString("milageto" <> "" then RsView__varMilageto = Request.QueryString("milageto"
%>
<%
Dim RsView__varYearfr
RsView__varYearfr = "0"
if (Request.QueryString("yearfrom" <> "0" then RsView__varYearfr = Request.QueryString("yearfrom"
%>
<%
Dim RsView__varYearto
RsView__varYearto = "99999999"
if (Request.QueryString("yearto" <> "" then RsView__varYearto = Request.QueryString("yearto"
%>
<%
Dim RsView__varModel
RsView__varModel = "<> -1"
if (Request.QueryString("model" > "0" then RsView__varModel = "= "&Request.QueryString("model"
%>
<%
Dim RsView__varMake
RsView__varMake = "<> -1"
if (Request.QueryString("make" > "0" then RsView__varMake = "= "&Request.QueryString("make"
%>
<%
Dim RsView__varCylinderfr
RsView__varCylinderfr = "0"
if (Request.QueryString("cylinderfrom" <> "0" then RsView__varCylinderfr = Request.QueryString("cylinderfrom"
%>
<%
Dim RsView__varCylinderto
RsView__varCylinderto = "99999999"
if (Request.QueryString("cylinderto" <> "" then RsView__varCylinderto = Request.QueryString("cylinderto"
%>
<%
Dim RsView__varCounty
RsView__varCounty = "<> -1"
if (Request.QueryString("county" > "0" then RsView__varCounty = "= "&Request.QueryString("county"
%>
<%
set RsView = Server.CreateObject("ADODB.Recordset"
RsView.ActiveConnection = MM_connfyndet_STRING
RsView.Source = "SELECT mc.ID, mc.name, mc.headline, mc.price, mc.milage, mc.year, mc.model, mc.make, mc.cylinder, mc.county, mc.picurl, mc.verified, mc.date, county.countyID, county.county FROM mc, county WHERE mc.county = county.countyID AND mc.headline LIKE '%" + Replace(RsView__varSearch, "'", "''" + "%' AND mc.price BETWEEN " + Replace(RsView__varPricefr, "'", "''" + " AND " + Replace(RsView__varPriceto, "'", "''" + " AND mc.milage BETWEEN " + Replace(RsView__varMilagefr, "'", "''" + " AND " + Replace(RsView__varMilageto, "'", "''" + " AND mc.year BETWEEN " + Replace(RsView__varYearfr, "'", "''" + " AND " + Replace(RsView__varYearto, "'", "''" + " AND mc.model " + Replace(RsView__varModel, "'", "''" + " AND mc.make " + Replace(RsView__varMake, "'", "''" + " AND mc.cylinder BETWEEN " + Replace(RsView__varCylinderfr, "'", "''" + " AND " + Replace(RsView__varCylinderto, "'", "''" + " AND mc.county " + Replace(RsView__varCounty, "'", "''" + " ORDER BY mc.ID DESC"
RsView.CursorType = 0
RsView.CursorLocation = 2
RsView.LockType = 3
RsView.Open()
RsView_numRows = 0
%>

// Leon

Ouch! that's one hell of a query... here's what the SP should look like:<pre id=code><font face=courier size=2 id=code>CREATE PROCEDURE theName AS
--Variables go here... make one for every @variable in the code
(@v****arch varchar(20), @varPricefr currency ...

GO

SELECT mc.ID, mc.name, mc.headline, mc.price, mc.milage, mc.year, mc.model,
mc.make, mc.cylinder, mc.county, mc.picurl, mc.verified, mc.date, county.countyID, county.county
FROM mc, county
WHERE mc.county = county.countyID
AND mc.headline LIKE '%' + @v****arch + '%' AND mc.price BETWEEN @varPricefr AND @varPriceto
AND mc.milage BETWEEN @varMilagefr AND @varMilageto
AND mc.year BETWEEN @varYearfr AND @varYearto
AND mc.model @varModel AND mc.make @varMake
AND mc.cylinder BETWEEN @varCylinderfr AND @varCylinderto
AND mc.county @varCounty
ORDER BY mc.ID DESC</font id=code></pre id=code>I probably messed up the Statement hacking through it and adding the @ symbols, but you get the idea... each variable will correspond to an entry in the parameter list at the top, then all you have to do is add a command instead of a recordset, and assign values to the parameters.

hope that helps.

Joel Martinez [ ]
----------
E-Commerce Concepts with Ultradev...pre-order yours at
www.basic-ultradev.com/ecomm_concepts/

Edited by - joelmartinez on 11/05/2001 16:01:32