Need sql help 1

[TonyU]

I have a form submitting to a processing page with the following fields

FirstName
LastName
Company

I'm trying to search all fields if the user has either filled all fields all left some blank. I don't know the syntax on how to do the search

<%
set rsSearch = Server.CreateObject(&quot;ADODB.Recordset&quot
rsSearch.ActiveConnection = MM_mydsn_STRING
rsSearch.Source = &quot;SELECT * FROM Client WHERE CL_Last = '&quot; Replace(rsSearch__MMColParam, &quot;'&quot;, &quot;''&quot and &quot;' or CL_First = &quot; + Replace(rsSearch__firstname, &quot;'&quot;, &quot;''&quot and &quot; or CL_Company = &quot; + Replace(rsSearch__company, &quot;'&quot;, &quot;''&quot + &quot;&quot;
rsSearch.CursorType = 0
rsSearch.CursorLocation = 2
rsSearch.LockType = 3
rsSearch.Open()
rsSearch_numRows = 0
if rsSearch.EOF then
response.redirect &quot;add.asp&quot;
end if
%>

Thanks in advance.

Delete * from brain Where MaxLevel = &quot;Full&quot; and reaction = &quot;Slow&quot; order by StartOver
​

 

[mwolf00]

SQL = &quot;SELECT * FROM client WHERE 1 = 1&quot;

IF trim(rsSearch__MMColParam) <> &quot;&quot; then
sql = sql & &quot; AND CL_Last = '&quot; Replace(rsSearch__MMColParam, &quot;'&quot;, &quot;''&quot
end if

'continue to use this syntax for each field...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rich Cook

 

[Bullschmidt]

If I wanted all the last names that started with S or were blank:

strSQL = &quot;SELECT * FROM MyTable WHERE (LName Like 'S%') Or (LName Is Null)&quot;

Hope this helps a little.

Best regards,
J. Paul Schmidt

http://www.Bullschmidt.com

- Freelance ASP Web Developer

http://www.Bullschmidt.com/DevTip.asp

- ASP Web Developer Tips

 

[TonyU]

mwolf00/ or anyone, what am I replacing on my code? or am I simply adding your suggestion to the bottom of mine? Thanks

^{There's never time to do it right, but there's always time to do it over!}

 

[TonyU]

never mind. I figured it out using mwolf00's suggestion. Thanks again

^{There's never time to do it right, but there's always time to do it over!}