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
194 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
[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
  • 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

739 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