Solved

no records are returned if some search fields are left blank

Posted on 2004-04-24
8
134 Views
Last Modified: 2010-04-25
DW MX 2004 7.01, ASP VBScript, Access DB

I have created a standard search/results set. If I submit the search page with some fields left blank, the results page will have no records. The search page form submission method is GET. Here is my SQL statement:

SELECT *
FROM tasks
WHERE request_date = #VARrequest_date#
AND requested_by = 'VARrequested_by'
AND job_ep = 'VARjob_ep'
AND deadline = 'VARdeadline'

I would like to return records based on whatever fields the user fills in, and ignore the blank ones.


thanks,
John
0
Comment
Question by:johnmarshall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 8

Expert Comment

by:alexhogan
ID: 10910553
The query that you have now;

SELECT *
FROM tasks
WHERE request_date = #VARrequest_date#
AND requested_by = 'VARrequested_by'
AND job_ep = 'VARjob_ep'
AND deadline = 'VARdeadline'

will return results only if the request_date and the requested_by and the job_ep and the deadline equal the variables that you are sending to the statement.  In other words you are asking that all the fields be filled out in order to return results.

If you are just wanting the query to return results based on a single field then you would use OR.

SELECT *
FROM tasks
WHERE request_date = #VARrequest_date#
OR requested_by = 'VARrequested_by'
OR job_ep = 'VARjob_ep'
OR deadline = 'VARdeadline'

If you are wanting to use any combination of the fields then you would have to use additional code to identify your query.

For instance..,

Using Get as the method on your form, if someone filled in the fields requested_by and deadline, you would get simething that looks like this;

submittedpage.php?rb=John%20Doe&dl=050104

Where the url parameters rb and dl represent requested_by and deadline respectively.

You then set up a conditional statement that tracks how many of the possible combinations of the fields have been filled in. (the easiest way to do this is to give an inital value to all the fields and the ones that don't match the initial value are the ones you'll look for)

From there you would set up the values for the WHERE clause of your query by combining all of the filled in fileds into a string that's passed to the variable that holds the query.

sqlquery = "SELECT *
                  FROM tasks
                  WHERE "

This is what you get from the conditional statement

temp1 = "requested_by = 'VARrequested_by'"
temp2 = "deadline = 'VARdeadline'"

Now concat

sqlquery .= temp1
sqlquery .= "AND"
sqlquery .= temp2

This will give you the complete query;

sqlquery = "SELECT *
                  FROM tasks
                  WHERE requested_by = 'VARrequested_by'
                  AND deadline = 'VARdeadline'";

Now obviously this is psuedo code.  You didn't say what language you were using, but you should get the idea.
0
 

Author Comment

by:johnmarshall
ID: 10910787
What you seem to be proposing is a method for manipulating the data in different ways, the most relevent being combining fields. My needs are very simple though. I want to retrieve records that contain all the data entered. The more information the user enters, the less records retrieved. If I use OR and submit a blank form, it retrieves all the records, but the opposite should happen since I didn't enter any data. It seems to be treating blank fields as wildcards when it should be NULL or something else that would ignore it.

For now, I think entering a default value in the fields will do the trick, but its not very clean or elegant. It's also tricky because I have 'Text Areas' that could potentially contain the default text.


John
0
 

Author Comment

by:johnmarshall
ID: 10910792
Oh, and I'm using ASP VBScript with an Access DB in Dreamweaver MX.

John
0
Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

 
LVL 23

Expert Comment

by:Saqib Khan
ID: 10911767
then on the search Result page Replace the NULL values with something, rather then setting some default value for textarea.

like this

VARrequested_by = Request("VARrequested_by")

if LEN(VARrequested_by) < 0 Then

VARrequested_by = "Not For Search"

End if

0
 
LVL 8

Expert Comment

by:alexhogan
ID: 10918440
johnmarshall,

I think you misunderstood what I was proposing.  My fault.

I'm not suggesting that you manipulate the data but rather the query statement.  Instead of using a single blanket query that could return inaccurate results you simple make the query more dynamic so it will produce the results that you require based on the user input on your form.  This way your are insured to retrieve accurate results.
0
 

Author Comment

by:johnmarshall
ID: 10970123
Well, I have solved this problem and it was very simple. I changed this:

AND field = 'VARfield'

to this:

AND field LIKE '%VARfield%'

...and now it accepts empty fields as a valid value. I'm leaving this question open
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 11535532
PAQed, with points refunded (250)

Computer101
E-E Admin
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question