Ignoring blank/empty fields with DRW (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

mstabrey -> 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 -> 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.



mstabrey -> 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 [:D]
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?



rdouglass -> 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.



mstabrey -> 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





rdouglass -> 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?



mstabrey -> 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



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1074219