Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Multiple Search Parameters (use coalesce?)

Author  Topic  RR Starting Member 2 Posts Posted - 2001-03-07 : 06:01:42 I have a similar problem to a lot of the questions in the developers forum. The problem is this - I have 5 pull down menus which should allow users to search the database using all or none of the menus. If more than one menu is selected from then the WHERE clause should perform a boolean AND search. I have a search that works but only if I make a selection from ALL the fields. If I leave one field as it's default value ('%' for strings and '0' for integers) then the recordset is empty. This is my code so far: <%@LANGUAGE="VBSCRIPT"%> <!--#include file="../../Connections/insidebikes_on_dell.asp" --> <% Dim rsResults__strMake rsResults__strMake = "%" if (Request.Form("Make") <> "") then rsResults__strMake = Request.Form("Make") %> <% Dim rsResults__strmotorcycleORscooter rsResults__strmotorcycleORscooter = "%" if (Request.Form("motorcycleORscooter") <> "") then rsResults__strmotorcycleORscooter = Request.Form("motorcycleORscooter") %> <% Dim rsResults__strTopSpeed rsResults__strTopSpeed = "0" if (Request.Form("TopSpeed") <> "") then rsResults__strTopSpeed = Request.Form("TopSpeed") %> <% Dim rsResults__strCC rsResults__strCC = "0" if (Request.Form("CC") <> "") then rsResults__strCC = Request.Form("CC") %> <% Dim rsResults__strInsGroup rsResults__strInsGroup = "0" if (Request.Form("InsGroup") <> "") then rsResults__strInsGroup = Request.Form("InsGroup") %> <% Dim rsResults__strOrderBy rsResults__strOrderBy = "%" if (Request.Form("OrderBy") <> "") then rsResults__strOrderBy = Request.Form("OrderBy") %> <% set rsResults = Server.CreateObject("ADODB.Recordset") rsResults.ActiveConnection = MM_insidebikes_on_dell_STRING rsResults.Source = "SELECT * FROM dbo.tblbikeinfo WHERE Make = COALESCE('" + Replace(rsResults__strMake, "'", "''") + "',Make) AND motorcycleORscooter = COALESCE('" + Replace(rsResults__strmotorcycleORscooter, "'", "''") + "',motorcycleORscooter) AND TopSpeed BETWEEN COALESCE('" + Replace(rsResults__strTopSpeed, "'", "''") + "'-50,TopSpeed) and COALESCE('" + Replace(rsResults__strTopSpeed, "'", "''") + "',TopSpeed) AND CC BETWEEN COALESCE('" + Replace(rsResults__strCC, "'", "''") + "'-200,CC) and COALESCE('" + Replace(rsResults__strCC, "'", "''") + "',CC) AND InsGroup = COALESCE('" + Replace(rsResults__strInsGroup, "'", "''") + "',InsGroup) ORDER BY " + Replace(rsResults__strOrderBy, "'", "''") + "" rsResults.CursorType = 0 rsResults.CursorLocation = 2 rsResults.LockType = 3 rsResults.Open() rsResults_numRows = 0 %> <% Dim Repeat1__numRows Repeat1__numRows = 10 Dim Repeat1__index Repeat1__index = 0 rsResults_numRows = rsResults_numRows + Repeat1__numRows %> I thought that the coalesce keyword would allow me to do what I want but it seems I have misunderstood. Can anyone help me please? Rachel