asked on

SQL Query

On my classic ASP page I provide users a search option with the instructions "Search by any of the below". When at lease the StartDate and EndDate are selected the query returns the correct values. But when users don't select the StartDate and EndDate, the query won't return the expected results. I would like all data returned when no date is selected. This is my query, I would appreciate your help:

SELECT * FROM myTable WHERE (Store LIKE '%" & Request.Form("Store") & "%' AND (RcvdDate between '" & Request.Form("StartDate") & "' AND '" & Request.Form("EndDate") & "') AND Product LIKE '%" & Request.Form("Product") & "%' AND Emp_Name LIKE '%" & replace(request.Form("Emp_Name"),"'","''") & "%' AND Irreg LIKE '%" & Request.Form("Irreg") & "%' AND Emp_Id LIKE '%" & Request.Form("Emp_Id") & "%' AND Supervisor LIKE '%" & replace(request.Form("Supervisor"),"'","''") & "%' AND Authorized LIKE '%" & Request.Form("Authorized") & "%' AND Paid LIKE '%" & Request.Form("Paid") & "%') order by RcvdDate

ASKER CERTIFIED SOLUTION

plummet

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Stefan Motz

ASKER

This is perfect; thank you very much for your quick response.

Scott Fell

I would suggest building your sql. I twill speed up your results. The more "like %" you have, the slower your query will run.

startDate=""
endDate=""
sql_date_range=""
if request.form("StartDate")<>"" then
	if isdate(request.form("StartDate")) then
		startDate=request.form("StartDate")
		sql_date_range=" RcvdDate >= '"&startDate&"' "
	end if
end if
if request.form("EndDate")<>"" then
	if isdate(request.form("EndDate")) then
		endDate=request.form("EndDate")
		if startDate<>"" then
			sql_date_range=" RcvdDate >= '"&startDate&"' AND RcvdDate <= '"&endDate&"' "
			else
			sql_date_range=" RcvdDate <= '"&endDate&"' "
		end if
	end if
end if


sql="SELECT * FROM myTable WHERE "
sql=sql& "(Store LIKE '%" & Request.Form("Store") & "%' "& sql_date_range
sql=sql& "AND Product LIKE '%" & Request.Form("Product") & "%' AND Emp_Name LIKE '%" & replace(request.Form("Emp_Name"),"'","''") & "%'  "
sql=sql& "AND Irreg LIKE '%" & Request.Form("Irreg") & "%' AND Emp_Id LIKE '%" & Request.Form("Emp_Id") & "%' AND Supervisor LIKE '%" & replace(request.Form("Supervisor"),"'","''") & "%' "
sql=sql& "AND Authorized LIKE '%" & Request.Form("Authorized") & "%' AND Paid LIKE '%" & Request.Form("Paid") & "%') order by RcvdDate

response.write "My SQL now looks like this "&sql

Open in new window

I strongly suggest for each item where you have some field = request.form("somefield") and that field is blank, just don't use it.

As far as security goes, your not scrubbing your data except for employeename and supervisor. You are running the risk of sql injection.

At the very least, you can make a clean function like

Function clean(x)
   x=replace(x,"'") 'single quotes
end if
'usage
sql="store like %"&clean(request.form("store")&"%"

Open in new window

Stefan Motz

ASKER

Padas, thank you very much, this is very good to know. I'm posting a question based on your advice, would you mind taking a look at it?
This is where you can find it:

https://www.experts-exchange.com/questions/28183771/SQL-Query.html