navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

Microsoft MVP

 

Ignoring blank/empty fields with DRW

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> Ignoring blank/empty fields with DRW 		Page: [1]
 
mstabrey

 

Posts: 4
Joined: 2/28/2008
Status: offline

  		Ignoring blank/empty fields with DRW - 2/28/2008 6:00:33   
Hi all

I have a Frontpage designed search form that has five fields, 3 text, 2 dropdowns.
All the fields are all empty/blank to start with. The user can enter data into any number of the 5 fields. Some might, therefore, be left unfilled when the form is submitted to the DRW.
In the results, I would like only the records that match and are common to ALL the FILLED in fields returned for viewing.

My problem is that the fields that are left unfilled by the user are being included by the DBW - so I'm getting "No records returned".

The DBW seems to be including the blank/empty fields in its filtering. I'd like them to be ignored, or some other solution which achieves the same thing. Oh yes, I'm using the "And" filter for each.

Thanks for any help!

Mart
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Ignoring blank/empty fields with DRW - 2/28/2008 10:41:48   
Hi and Welcome to OutFront.

It looks to me that you'll need to build your query on-the-fly. Here's an example of a DRW that uses this method:

http://www.rogerdouglass.com/wildcards.htm

What it does is essentially build the query by checking each field for a value. If it has one, add that field to the query; if it doesn't, just ignore it.

Hope it helps.

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to mstabrey)
mstabrey

 

Posts: 4
Joined: 2/28/2008
Status: offline

  		RE: Ignoring blank/empty fields with DRW - 2/28/2008 13:57:13   
Roger

Gee, thanks for this! It looks like you've tried to recreate my situation!
I must admit however that I am completely raw at this so I really am none the wiser :)
I did copy the entire page you gave me and put it into a page in FP, but I don't even know if I was supposed to do with that!

Thanks anyway!

Mart

PS: I am unsure if my logic is correct in what I am trying to achieve. Would I be correct in wanting to ignore the blank fields, or would there be another way of getting around this? As an example, is it wrong for fields that have yet to be populated in the Access DB to be left with nothing in them? Is it normal to do this? Also, is there possibly a wildcard I could use in my text and drop-down fields?

(in reply to rdouglass)
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Ignoring blank/empty fields with DRW - 2/28/2008 16:03:30   
Sorry, that was just an example page I had pre-built already and I hope it didn't confuse you.

The basic premise is that we check each field before the DWR to decide what fields we want to use in our search. If the field has something, we want to use it; if not, we want to ignore it.

The first chunck of "red code" was for another form but essentially looks at each field being posted and builds the search query as it checks each field. Then once the query is built, we pass it to the FrontPage DWR.

That page was not intended to be specifically for your page but was an example. If you can post the DRW you currently have, I may be able to show you what yours would look like.

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to mstabrey)
mstabrey

 

Posts: 4
Joined: 2/28/2008
Status: offline

  		RE: Ignoring blank/empty fields with DRW - 2/29/2008 2:57:21   
Thanks Roger!

A bit of background first - the search process happens across three pages.
Page 1 has the search form.
Page 2 returns the results, but only the "Book Title" field is displayed as a hyperlink.
Page 3 displays all the details of the relevant book - clicked through from page 2.

I have pasted the search form from page 1 and DBW from page 2.

Search Form
__________ 

<form method="post" action="searchresults.asp" />
<input type="hidden" name="fpdbr_0_PagingMove" value="  |<  " />
<p class="Head2">South African Children's Books in Print (SACBIP)</p>
<table>
<tr>
<td style="width: 85px"><p class="smalleft">Book Title</p></td>
<td colspan="4">
<input name="BookTitle" type="text" style="width: 265px; height: 20px;" class="sacbip" value='<%=Server.HtmlEncode(Request("BookTitle"))%>' /></td></tr>
<tr>
<td style="width: 85px"><p class="smalleft">Author Surname</p></td>
<td colspan="4">
<input name="AuthorSurname" type="text" style="width: 265px" class="sacbip" value='<%=Server.HtmlEncode(Request("AuthorSurname"))%>' /></td></tr>
<tr>
<td style="width: 85px"><p class="smalleft">Illustrator Surname</p>
</td>
<td colspan="4">
<input name="IllustratorSurname" type="text" style="width: 265px" class="sacbip" value='<%=Server.HtmlEncode(Request("IllustratorSurname"))%>' /></td></tr>
<tr>
<td style="width: 85px"><p class="smalleft">Geography:</p></td>
<td>
<select name="KeywordGeography" value='<%=Server.HtmlEncode(Request("KeywordGeography"))%>' class="sacbip" size="1">
<option value="%" selected="">Select ...</option>
<option>Cape Town</option>
<option>Johannesburg</option>
<option>Durban</option>
<option>Western Cape</option>
<option>Eastern Cape</option>
<option>Kwazulu-Natal</option>
<option>Gauteng</option>
<option>Free State</option>
<option>Mpumalanga</option>
<option>North-West</option>
<option>Northern Cape</option>
<option>Limpopo</option>
<option>Namibia</option>
<option>Botswana</option>
<option>Lesotho</option>
<option>Swaziland</option>
<option>Zimbabwe</option>
<option>Mozambique</option>
</select></td>
<td></td>
<td >
<p class="smalleft">Literary flavour:</p>
</td>
<td>
<select name="KeywordLiteraryFlavour" value='<%=Server.HtmlEncode(Request("KeywordLiteraryFlavour"))%>' class="sacbip" >
<option value="%" selected>Select ...</option>
<option>Funny</option>
<option>Light Humour</option>
<option>Normality</option>
<option>Strong Drama</option>
<option>Tragedy</option>
<option>Unreal Adventure</option>
<option>Fantasy</option>
<option>Folktale</option>
<option>Legend</option>
<option>Traditional Africa</option>
<option>Science Fiction</option>
<option>Space</option>
<option>Talking Animals</option>
</select></td></tr>
</table>
<div><br />
<input name="Submit" type="submit" value="Search" />
<input name="Reset" type="reset" value="Reset Search" /></div>
</form>


DBW
____ 

<!--webbot bot="DatabaseRegionStart" s-columnnames="ID,BookTitle,OriginalTitle,Language,OtherLanguageEditions,CoverPicture,Publisher,Notes,YearFirstPublished,ISBN,SuggestedReadingAge,Genre,Awards,Format,Review,KeywordGeography,KeywordMainThemes,KeywordHistory,KeywordCentralCharacters,KeywordLiteraryFlavour,AuthorSurname,AuthorFirst Name,AuthorPicture,AuthorAwards,AuthorWebsite,IllustratorSurname,IllustratorFirst Name,IllustratorPicture,IllustratorAwards,IllustratorWebsite" s-columntypes="3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202" s-dataconnection="SACBIP" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice="BookTitle" s-menuvalue="BookTitle" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="SACBIP" s-displaycolumns="BookTitle" s-criteria="[BookTitle] CNT {BookTitle} + [AuthorSurname] CNT {AuthorSurname} + [IllustratorSurname] CNT {IllustratorSurname} + [KeywordGeography] CNT {KeywordGeography} + [KeywordLiteraryFlavour] CNT {KeywordLiteraryFlavour} |" s-order="[BookTitle] +" s-sql="SELECT * FROM SACBIP WHERE (BookTitle LIKE '%::BookTitle::%' AND AuthorSurname LIKE '%::AuthorSurname::%' AND IllustratorSurname LIKE '%::IllustratorSurname::%' AND KeywordGeography LIKE '%::KeywordGeography::%' AND KeywordLiteraryFlavour LIKE '%::KeywordLiteraryFlavour::%') ORDER BY BookTitle ASC" b-procedure="FALSE" clientside="" suggestedext="asp" s-defaultfields="BookTitle=&AuthorSurname=&IllustratorSurname=&KeywordGeography=&KeywordLiteraryFlavour=" s-norecordsfound="No books were found that match your choice." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the start of a Database Results region. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr></table>" b-usedotnet="FALSE" currentext="" sa-inputtypes="202,202,202,202,202" b-datagridformat="FALSE" b-dgridalternate="TRUE" sa-crittypes="202,202,202,202,202" b-wastableformat="FALSE" startspan="" --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM SACBIP WHERE (BookTitle LIKE '%::BookTitle::%' AND AuthorSurname LIKE '%::AuthorSurname::%' AND IllustratorSurname LIKE '%::IllustratorSurname::%' AND KeywordGeography LIKE '%::KeywordGeography::%' AND KeywordLiteraryFlavour LIKE '%::KeywordLiteraryFlavour::%') ORDER BY BookTitle ASC"
fp_sDefault="BookTitle=&AuthorSurname=&IllustratorSurname=&KeywordGeography=&KeywordLiteraryFlavour="
fp_sNoRecords="No books were found that match your choice."
fp_sDataConn="SACBIP"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="BookTitle"
fp_sMenuValue="BookTitle"
fp_sColTypes="&ID=3&BookTitle=202&OriginalTitle=202&Language=202&OtherLanguageEditions=202&CoverPicture=202&Publisher=202&Notes=202&YearFirstPublished=202&ISBN=202&SuggestedReadingAge=202&Genre=202&Awards=202&Format=202&Review=202&KeywordGeography=202&KeywordMainThemes=202&KeywordHistory=202&KeywordCentralCharacters=202&KeywordLiteraryFlavour=202&AuthorSurname=202&AuthorFirst Name=202&AuthorPicture=202&AuthorAwards=202&AuthorWebsite=202&IllustratorSurname=202&IllustratorFirst Name=202&IllustratorPicture=202&IllustratorAwards=202&IllustratorWebsite=202&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan="" i-checksum="70" --><p class="book">
		<a href="searchresults-2.asp?BookTitle=<%=FP_FieldURL(fp_rs,"BookTitle")%>">
		<!--webbot bot="DatabaseResultColumn" s-columnnames="ID,BookTitle,OriginalTitle,Language,OtherLanguageEditions,CoverPicture,Publisher,Notes,YearFirstPublished,ISBN,SuggestedReadingAge,Genre,Awards,Format,Review,KeywordGeography,KeywordMainThemes,KeywordHistory,KeywordCentralCharacters,KeywordLiteraryFlavour,AuthorSurname,AuthorFirst Name,AuthorPicture,AuthorAwards,AuthorWebsite,IllustratorSurname,IllustratorFirst Name,IllustratorPicture,IllustratorAwards,IllustratorWebsite" s-column="BookTitle" b-tableformat="FALSE" b-hashtml="FALSE" b-makelink="FALSE" clientside="" b-menuformat="" preview="<font size="-1"><<</font>BookTitle<font size="-1">>></font>" s-columntypes="3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202" startspan="" --><%=FP_FieldVal(fp_rs,"BookTitle")%><!--webbot bot="DatabaseResultColumn" endspan="" i-checksum="19565" --></a></p>
		<!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside="" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the end of a Database Results region.</font></td></tr></table>" startspan="" --><!--#include file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan="" i-checksum="62730" --></div>


Phew!

Cheers

Mart




< Message edited by rdouglass -- 2/29/2008 14:13:58 >

(in reply to rdouglass)
rdouglass

 

Posts: 9167
From: Biddeford, ME USA
Status: offline

  		RE: Ignoring blank/empty fields with DRW - 2/29/2008 14:28:42   
I would think something close to this for the second one. You shouldn't have to change the first. 

<% dim mySQL

mySQL = ""

If trim(Request.form("BookTitle")&"") > "" Then 'check if value
	mySQL = mySQL & "(BookTitle LIKE '%" & trim(Request.form("BookTitle")&"") & "')"
End If

If trim(Request.form("AuthorSurname")&"") > "" Then
	If mySQL > "" Then 'check to see if we need to insert the AND clause
		mySQL = mySQL & " AND "
	End If
	mySQL = mySQL & "(AuthorSurname LIKE '%" & trim(Request.form("AuthorSurname")&"") & "')"
End If

If trim(Request.form("IllustratorSurname")&"") > "" Then
	If mySQL > "" Then
		mySQL = mySQL & " AND "
	End If
	mySQL = mySQL & "(IllustratorSurname LIKE '%" & trim(Request.form("IllustratorSurname")&"") & "')"
End If

If trim(Request.form("KeywordGeography")&"") > "" Then
	If mySQL > "" Then
		mySQL = mySQL & " AND "
	End If
	mySQL = mySQL & "(KeywordGeography LIKE '%" & trim(Request.form("KeywordGeography")&"") & "')"
End If

If trim(Request.form("KeywordLiteraryFlavour")&"") > "" Then
	If mySQL > "" Then
		mySQL = mySQL & " AND "
	End If
	mySQL = mySQL & "(KeywordLiteraryFlavour LIKE '%" & trim(Request.form("KeywordLiteraryFlavour")&"") & "')"
End If

If mySQL > "" Then 'check if we have anything - if we di we add the WHERE
	mySQL = " WHERE " & mySQL
End If

%>

<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM SACBIP" & mySQL & " ORDER BY BookTitle ASC"
fp_sDefault="BookTitle=&AuthorSurname=&IllustratorSurname=&KeywordGeography=&KeywordLiteraryFlavour="
fp_sNoRecords="No books were found that match your choice."
fp_sDataConn="SACBIP"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice="BookTitle"
fp_sMenuValue="BookTitle"
fp_sColTypes="&ID=3&BookTitle=202&OriginalTitle=202&Language=202&OtherLanguageEditions=202&CoverPicture=202&Publisher=202&Notes=202&YearFirstPublished=202&ISBN=202&SuggestedReadingAge=202&Genre=202&Awards=202&Format=202&Review=202&KeywordGeography=202&KeywordMainThemes=202&KeywordHistory=202&KeywordCentralCharacters=202&KeywordLiteraryFlavour=202&AuthorSurname=202&AuthorFirst Name=202&AuthorPicture=202&AuthorAwards=202&AuthorWebsite=202&IllustratorSurname=202&IllustratorFirst Name=202&IllustratorPicture=202&IllustratorAwards=202&IllustratorWebsite=202&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<p class="book">
		<a href="searchresults-2.asp?BookTitle=<%=FP_FieldURL(fp_rs,"BookTitle")%>">
		<!--#include file="_fpclass/fpdbrgn2.inc"-->


This is a 'dieted' DRW so you won't be able to make changes using the wizard and be sure to back up what you have. But this is how I'd attempt it. I did not test any of the syntax nor did I test the diet but it should be pretty close.

Does that make any sense what I did? Just keep in mind I'm checking for a value for each field and building the query along the way. I built it so that if all fields are blank, all records should be returned.

Does that help any?

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to mstabrey)
mstabrey

 

Posts: 4
Joined: 2/28/2008
Status: offline

  		RE: Ignoring blank/empty fields with DRW - 3/1/2008 5:27:20   
Thanks Roger.

Two things:

1. I get this error message:

Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'Mid'
/_fpclass/fpdbrgn1.inc, line 281


2. In your code there is no Booktitle hyperlink which would take the user to the books details.

Thanks for your time here Roger. It is much appreciated! If ever you need a place to stay if you should venture down to Africa, let me know!

Mart

(in reply to rdouglass)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> Ignoring blank/empty fields with DRW 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.