Solved

Need To Search All Fields In Column If No Value Is Entered On A Web Form (Querying MS Access DB)

Posted on 2007-03-25
12
185 Views
Last Modified: 2010-04-25
I have a problem that's driving me insane.

I am developing a web form.  Right now, it has just two fields, though it will have much more once I can solve this rather vexing problem!  The first field is hidden, with a predetermined value (field name/id is "Organization" and it's predetermined value is "Bloomfield Garfield Corp"), and the second is a simple text field called "FirstName".  I am querying an Access Database using ASP powered by VBScript and of course SQL.

Here is the problem:  if the user leaves the FirstName field blank, no results are returned.  This is decidedly NOT the desired outcome!  What needs to happen is, if the user leaves the FirstName field blank, the results page needs to show ALL the FirstNames in the DB associated with the predetermined org (Bloomfield Garfiled Corp).  Then, if a user types in a first name, only results with that first name are shown, if any (this works fine!)

I have searched and searched and searched news groups and tech support groups, etc., and almost without fail, they say I need to use % as a default value for the FirstName field.  I have done this in both the HTML form (ie, <input name="FirstName" type="text" id="FirstName" value="%" /> ), and even more importantly, I have done so in the SQL (see below) but it simply refuses to work.  Can anyone help me?  I am about to go certifiably mad!  My wife won't go near me!

Thanks in advance for any help.  My SQL code is below, as well.  I am generating most of the code using Dreamweaver 8, though my SQL statements are custom written.  I am pretty sure that's the problem, as I am somewhat code-challenged, as you have probably already guessed!!  I am viewing the web pages in latest IE on a machine running Windows XP.  Web server I am publishing to is Microsost box running IIS.

Thanks again in advance for any help!

Tom

Here is the SQL:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/wn/Connections/atlast.asp" -->
<%
Dim Result__MMColParam
Result__MMColParam = "%"
If (Request.QueryString("FirstName")  <> "") Then
  Result__MMColParam = Request.QueryString("FirstName")
End If
%>
<%
Dim Result__Org
Result__Org = "%"
If (Request.QueryString("Organization")  <> "") Then
  Result__Org = Request.QueryString("Organization")
End If
%>
<%
Dim Result
Dim Result_numRows

Set Result = Server.CreateObject("ADODB.Recordset")
Result.ActiveConnection = MM_atlast_STRING
Result.Source = "SELECT *  FROM EmployementData  WHERE Organization = '" + Replace(Result__Org, "'", "''") + "' AND FirstName = '" + Replace(Result__MMColParam, "'", "''") + "'"
Result.CursorType = 0
Result.CursorLocation = 2
Result.LockType = 1
Result.Open()

Result_numRows = 0
%>
<%
Dim Listing__MMColParam
Listing__MMColParam = "Bloomfield Garfield Corp"
If (Request("MM_EmptyValue") <> "") Then
  Listing__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim Listing
Dim Listing_numRows

Set Listing = Server.CreateObject("ADODB.Recordset")
Listing.ActiveConnection = MM_atlast_STRING
Listing.Source = "SELECT * FROM EmployementData WHERE Organization = '" + Replace(Listing__MMColParam, "'", "''") + "'"
Listing.CursorType = 0
Listing.CursorLocation = 2
Listing.LockType = 1
Listing.Open()

Listing_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = 10
Repeat1__index = 0
Result_numRows = Result_numRows + Repeat1__numRows
%>

If you want to view and/or test the actual web pages, they are here, but you will need username and pass;

http://66.207.128.46/wn/bgc-wep4y/query.asp

U:  bgc-wep4y
P:  bgc-wep4y

If you want to see a successful query, search for "Jim" "Jimi" or "Janis".  Back-end DB is small, and with dummy data, until I can iron out this issue!

0
Comment
Question by:tomstraw
  • 6
  • 3
  • 3
12 Comments
 
LVL 10

Expert Comment

by:slamhound
ID: 18790603
Try this:
Result.Source = "SELECT *  FROM EmployementData  WHERE Organization Like '" + Replace("*" & Result__Org & "*", "'", "''") + "' AND FirstName Like '" + Replace("*" & Result__MMColParam & "*", "'", "''") + "'"

So basically I'm adding wildcards to the query string. If there is no data in the parameter, the query string becomes just "**" which is a global wildcard and grabs everything.
0
 

Author Comment

by:tomstraw
ID: 18790717
Most definitely appreciate the fast response, slamdog.  Unfortunately, it's still not returning any results with a blank "FirstName" field.  Very odd.  I wonder sometimes if it's a setting in Access or something--some setting that's not allowing wildcards?  A wild theory perhaps, but that's what I am reduced to at this point! :-)
0
 
LVL 10

Expert Comment

by:slamhound
ID: 18790813
Some things to try:
-Make sure you are using the "like" comparision instead of "="
-Make sure you make this change to all your queries.
-Try without one of the "*"
-Validate that you can get data displayed if there are no filters on the query. ie remove the WHERE claus all together just to test.
-Dump your query string to the screen and past it here so I can see it please.
0
 
LVL 3

Expert Comment

by:stejam
ID: 18791164
If I understand you right there are two scenarios:
1) User types in name - brings up all name hits for Company
2) User leaves name blank - needs to bring up all names

If using Dreamweaver simply put two Show Regions on the results page. Have a show region if recordset is not empty for scenario 1 above and a show region if recordset is empty for scenario 2. Both available under Server Behaviours.

If you do the above and there are no hits on scenario 1 you'll cover that in the solution in scenario 2.

0
 

Author Comment

by:tomstraw
ID: 18792212
stejam, thanks for the response.  Very simple but elegant solution!  However, I am pretty sure it will only work with the two fields.  II will be able to check in about an hour or so to verify for sure)  Like I said in original post, I have to add a few more fields to the form (LastName and four others), all of which need a "search all" option as well.  I was just doing the two fields first to see how I could solve the "Search All when left blank" option and then rubber-stamp it on the other form fields.

slamhound:  thanks for sticking with me!  I will send you that code when I can get to the machine I am working o in about an hour or so.
0
 

Author Comment

by:tomstraw
ID: 18792908
Slamhound,

As promised, here are the query strings I've tried, copied right from the DW dialog box.

First, my original SQL:

SELECT *
FROM EmployementData
WHERE Organization = 'Org' AND FirstName = 'MMColParam'

Name                     Default Value                       Runtime Value
MMColParam                 %                                   Request.QueryString("FirstName")  
Org                               %                                   Request.QueryString("Organization")  

Then, I simply copy/pasted your suggested code in the alt solution, replacing the old query string:

SELECT *
FROM EmployementData
WHERE Organization Like '" + Replace("*" & Result__Org & "*", "'", "''") + "' AND FirstName Like '" + Replace("*" & Result__MMColParam & "*", "'", "''") + "'

Name                     Default Value                       Runtime Value
MMColParam                 %                                   Request.QueryString("FirstName")  
Org                               %                                   Request.QueryString("Organization")  

Now, when that didn't work, I did try a few different things, like you suggested, such as removing some "*", but no go there, either.

I hope this helps.  I do have a couple statements/questions in response to your post:

(1)  I did test without the "where" statement, and I was able to retrieve all DB records on a test.  I figured this would indeed work, simply because the form does work now quite well, as long as you enter a valid first name; it's leaving it blank that returns no results. And it's not an error--just no results returned, so I figured I was communicating with the DB nicely, just not getting the desired behavior/outcome.
2) When you told me to add your new code to ALL queries on the page, did you mean here, too?  (see below)

Set Listing = Server.CreateObject("ADODB.Recordset")
Listing.ActiveConnection = MM_atlast_STRING
Listing.Source = "SELECT * FROM EmployementData WHERE Organization = '" + Replace(Listing__MMColParam, "'", "''") + "'"
Listing.CursorType = 0
Listing.CursorLocation = 2
Listing.LockType = 1
Listing.Open()
 This query/recordset is set up simply to call the name of the org dynamically on the page's heading, and doesn't have anything to do with the query.  Are you sure I should change it anyway?  I didn't try this, so maybe that's whay your suggestion didn't work?  I will try it while I wait for new response.

Again, I appreciate your patience and your help!

T
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:tomstraw
ID: 18793111
Stejam,

I experimented a bit in DW, and as I suspected, the solution only works with the 2 scenarios.  Once I add other fields that must "Search All" if left blank, it doesn't really work, b/c DW only seems to support two scenarios (ie, if no results are returned on first try, then display THIS on 2nd try, but you can't so anything after that"--meaning, if no results are found on 2nd try, you can't try a third option, etc )  Maybe there IS a way to do this, but it's not obvious to me.  I am going to keep trying for a bit, anyway.  Purely in terms of code, I am sure there is some sort of LOOP command, but that is *definitely* beyond my abilities!

Thanks for the suggestion, and I must say that even if I were only working with the two fields, I might never have thought of it on my own!  It's too darn bad I have to add those other fields . . . !
0
 

Author Comment

by:tomstraw
ID: 18793652
Slamhound,

Bingo!  I think I (actually you) have got to the bottom of this!

Just based on your original post, I started playing around with using LIKE instead of AND but also with enclosing the wildcard symbol (%) around the colnames--a suggestion I was seeing a lot in the discussion groups about this topic.  I then came up with this fairly simple string which seems to work great:

SELECT *  FROM EmployementData  WHERE Organization LIKE '%Org%' AND FirstName LIKE '%MMColParam%'

I am going to start adding those other fields--I don't see why it shouldn;t work, but I've learned to be *cautiously* optimistic about these things!  In any case, slamdog, thanks for stimulating my "gray matter" sufficiently enough to finally get me some positive results!
0
 
LVL 3

Expert Comment

by:stejam
ID: 18795710
Hmm if you use ANDs for all of your above you'll probably run into trouble as the SQL will look to satisfy all fields. If you want a better strike rate you could use OR in your WHERE statement.

Just my $.02
0
 
LVL 10

Accepted Solution

by:
slamhound earned 500 total points
ID: 18796648
Ansi SQL uses "%" for wildcard but Access (all hail MS) uses "*". If you've got a non Access back end (eg MS SQL) "%" is definatly the way to go.

I'm glad you're along the path to enlightenment :)
0
 

Author Comment

by:tomstraw
ID: 18805627
Stejam

.02 most definitely appreciated!  I agree:  using operator AND between each lessens reults, but my client seems to want it that way.  It's strange:  I've come across this method twice just in the last few weeks, and I had never seen it before in 10 years now of web design / development.  Specifically, "this method" is a form where if left blank, all results are returned, and with each field filled in, the results get narrowerr and narrower.  Again, client wants it that way.  A few weeks ago, I came across the same thing in a database search of tax-free properties.  Because the form fields were checkboxes instead of open-ended text fields, I worked around the problem by making a "Select All" feature with Javascript on the front end (code/environment I am MUCH more comfortable in!), but this time, I couldn't do that, b/c the fields were open-ended text fields!

In any case, I appreciate the feedback . . .
0
 
LVL 3

Expert Comment

by:stejam
ID: 18805647
No worries, good luck!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now