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
Solved

no records are returned if some search fields are left blank

Posted on 2004-04-24
8
132 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
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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 …
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

829 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