SQL query...need help quick

The Mighty Dubs

Hi All, Im in desperate need of help here

I need to create a select statement that works(for starters).

basically I have 6 options in the search.
1st field is a wild card field i.e. Keyword
2nd field is from a dropdown list i.e. catagories
3rd field is a text field where you enter a customer name

4th, 5th & 6th are text fields for codes. So they need to be absolute (if you get my drift).

I tried the following statement but its not working correctly

******************************
SELECT *
FROM dbo.qryProdSearch
WHERE ItemDesc LIKE '%rsSearch%' AND CustName LIKE '%rsSearch1' AND SuppCode Like '%rsSearch2%' AND SCode LIKE '%rsSearch3%' AND GroupID LIKE '%rsSearch4%' AND SItemID LIKE '%rsSearch5%'

**********************

And these are the fields I declared

********************************

<%
Dim rsSearchRes__rsSearch
rsSearchRes__rsSearch = "%"
If (Request.QueryString("txtSearch") <> "") Then
rsSearchRes__rsSearch = Request.QueryString("txtSearch")
End If
%>
<%
Dim rsSearchRes__rsSearch1
rsSearchRes__rsSearch1 = "%"
If (Request.QueryString("txtSupplier") <> "") Then
rsSearchRes__rsSearch1 = Request.QueryString("txtCust")
End If
%>
<%
Dim rsSearchRes__rsSearch2
rsSearchRes__rsSearch2 = "%"
If (Request.QueryString("txtSuppCode") <> "") Then
rsSearchRes__rsSearch2 = Request.QueryString("txtSuppCode")
End If
%>
<%
Dim rsSearchRes__rsSearch3
rsSearchRes__rsSearch3 = "%"
If (Request.QueryString("txtSCode") <> "") Then
rsSearchRes__rsSearch3 = Request.QueryString("txtSCode")
End If
%>
<%
Dim rsSearchRes__rsSearch4
rsSearchRes__rsSearch4 = "%"
If (Request.QueryString("txtgroupid") <> "") Then
rsSearchRes__rsSearch4 = Request.QueryString("txtgroupid")
End If
%>
<%
Dim rsSearchRes__rsSearch5
rsSearchRes__rsSearch5 = "%"
If (Request.QueryString("txtitemid") <> "") Then
rsSearchRes__rsSearch5 = Request.QueryString("txtitemid")
End If
%>


****************************
Any ideas...all very welcome

Seth Brundle

I presume its ASP & MSSQL
is the SQL running from your ASP?
Are you using double quotes to concatenate variables to your string?
What is the difference between rsSearchRes__rsSearch and rsSearch?

bonkey

SELECT *
FROM dbo.qryProdSearch
WHERE ItemDesc LIKE '%rsSearch%' AND CustName LIKE '%rsSearch1' AND SuppCode Like '%rsSearch2%' AND SCode LIKE '%rsSearch3%' AND GroupID LIKE '%rsSearch4%' AND SItemID LIKE '%rsSearch5%'

Looks to me like this is going to search on the literal values "%rsSearch%" and so forth, rather than using the values in the variables of the same name.

How you assign values to the vars (teh code you supplied) isn't of much relevance. Its how you build the SQL string and eubsequently execute it thats of interest.

jc