Conditional Search Form like Access (Full Version)

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



Message

slincolnFELLERS -> Conditional Search Form like Access (1/11/2006 18:36:50)

I'm trying to create an asp file that will get results from an existing Access DB (one table).

What I would like my users to have is the query options they get when in the full version of access.

I've got say.. 5 different fields and sometimes users search clientname, opendate between x AND y.

Is there anyway to somewhat easily create query flexibility like that in an asp? Without tons of code that is.

:0) Thanks

More details as needed. - Sam



BeTheBall -> RE: Conditional Search Form like Access (1/12/2006 13:09:55)

There are many ways to do what you suggest, but I think I would need to know specifically what you want to do. The general idea is to create an html form with form fields into which the user can insert criteria and then plug the form values into your SQL. Are you going to be doing this in pure ASP or via FrontPage?



slincolnFELLERS -> RE: Conditional Search Form like Access (1/12/2006 13:36:50)

I think doing the criteria form and submitting that info as some kind of Sql search would be best.

There are at least 3 fields that people search on and not all 3 at the same time.

The files can be html and asp...what ever it takes.

Sam



slincolnFELLERS -> RE: Conditional Search Form like Access (1/12/2006 13:40:13)

PS - I'm doing this in FP2002

-S



BeTheBall -> RE: Conditional Search Form like Access (1/12/2006 16:42:45)

OK. So what are those three field? Do any of them rely on each other or could a user complete any of the three and leave the other two blank? Have you played with the Database Results Wizard at all? Usually the wizard will create both your search form and your results region for you.



slincolnFELLERS -> RE: Conditional Search Form like Access (1/12/2006 17:58:52)

They are all independent. It's just a basic DB, one table and about 8 fields total. My users sometimes search (in access) by the clientname, mattername, filenumber or what not.
I did the DRW, but with the Ands and Ors if one field is blank, then it doesn't really bring up the results desired. I noticed one of your posts with a conditional sql with 3 different fields, but some times they'll use 2 or more fields...so I don't know that that one would help. I'd like to have the flexabiliy of access's search query, but if I have to limit it..then so be it.

Ponder on that for a while.



BeTheBall -> RE: Conditional Search Form like Access (1/12/2006 20:43:40)

What I would do is use one text box. Then add a group of radio buttons where the user marks the field he or she wishes to search by. So, say for example your text box is named, T1. You add a radio button group that would look something like this:

<input name="r1" type="radio" value="field1">
<input name="r1" type="radio" value="field2">
<input name="r1" type="radio" value="field3">

You name each button the same name, but the value should match the names of the db fields you may be searching.

Then your SQL becomes something like this:

SELECT * FROM tblName WHERE ::r1:: = '::t1::'

The above will work perfectly if all the db fields are text datatypes. If not, we will need to tweak this idea somewhat. Now, keep in mind this approach allows the user to search on any field they choose from the radio buttons, but they can only search on one field, not a combination of fields. However, it sounds like perhaps that would meet your needs.

By the way, if you check my avatar carefully you will see that I am THE one man gopher show! [:D]



slincolnFELLERS -> RE: Conditional Search Form like Access (1/13/2006 12:09:12)

How does the data that a user types into a text field get passed on with the radio button?

The dateopened / dateclosed fields are data/time and not text... [&o]

my brain hurtz [:(]

You aren't the one man gopher show...you are the one and only GOPHER show.

-S



BeTheBall -> RE: Conditional Search Form like Access (1/14/2006 11:21:10)

Two items get passed, the value of the textbox and the value of the radio button group. The text box value is what we will search on while the value of the radio button group is the field we will search. I understand you have a date field, but do you have at least two text fields you want to search on? If so, let's get this working on just the text fields and then move on to adding the date field. Baby steps.



slincolnFELLERS -> RE: Conditional Search Form like Access (1/16/2006 13:38:38)

What is this that you put up in that sql query?
SELECT * FROM tblName WHERE ::r1:: = '::t1::'

r1 =t1? Where's t1 coming from??



BeTheBall -> RE: Conditional Search Form like Access (1/16/2006 13:43:11)

r1 is what we named the radio buttons and t1 is what we named the text box.



slincolnFELLERS -> RE: Conditional Search Form like Access (1/16/2006 13:43:52)

Never mind, I figured that out. The initail DRW had put this Encoding stuff in there. When I re-added as just a text field...wahlah...T1



slincolnFELLERS -> RE: Conditional Search Form like Access (1/16/2006 13:54:13)

I'm getting an operation failed error in the data portion. Here's my code as it stands right now. Be a second pair of eyes and see if something bad jumps out. This is not ALL of the code, just the start of body, down to just below sqlquery

<form BOTID="0" METHOD="POST" ACTION="Sam.asp">
<table BORDER="0">
<tr>
<td><b>ClientName</b></td>
<td>
<input type="text" name="T1" size="20"></td>
</tr>
<tr>
<td><b>MatterName</b></td>
<td>
<input type="text" name="T1" size="20"></td>
</tr>
</tr>
</table>

<p>Client Name <input type="radio" value="ClientName" name="R1"> <br>
Matter Name <input type="radio" name="R1" value="MatterName"><br>

<input TYPE="Submit"><input TYPE="Reset"></p>
<p> </p>
</form>
<table width="100%" border="1">
<thead>
<tr>
<td><b>ClientName</b></td>
<td><b>MatterName</b></td>
<td><b>FileName</b></td>
<td><b>FileNumber</b></td>
<td><b>FileSortNumber</b></td>
<td><b>Attorney</b></td>
<td><b>DateOpened</b></td>
<td><b>DateClosed</b></td>
<td><b>ClosedFileNumber</b></td>
<td><b>Notes</b></td>
<td><b>Locator</b></td>
</tr>
</thead>
<tbody>
<!--#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 Main WHERE ::R1:: = '::T1::' ORDER BY DateOpened ASC,ClientName ASC,FileNumber ASC"
fp_sDefault="ClientName=&MatterName="
fp_sNoRecords="<tr><td colspan=11 align=left width=""100%"">No records returned.</td></tr>"



BeTheBall -> RE: Conditional Search Form like Access (1/16/2006 15:11:59)

Probably the issue talked about here:

http://www.frontpagewebmaster.com/m-175524/tm.htm

You may want to replace the _fpclass files as suggested in the above thread.



slincolnFELLERS -> RE: Conditional Search Form like Access (1/16/2006 16:01:38)

Changing that gives me this:

Unable to find operator in query string. Query string currently is SELECT * FROM Main WHERE ::R1:: = '::T1::' ORDER BY DateOpened ASC,ClientName ASC,FileNumber ASC

So there's something screwy with either a " or the ::R1:: or something. What do you think?



BeTheBall -> RE: Conditional Search Form like Access (1/16/2006 19:16:41)

If you read further down in the thread I referenced you will see a post from Spooky wherein he suggests you replace the files in the _fpclass folder with the 2000 version. He also provides a copy of the 2000 version. That is often the easiest way to get around the MS bug.



slincolnFELLERS -> RE: Conditional Search Form like Access (1/17/2006 13:52:13)

Okay, just starting from the basics to see if the T1 value gets passed to the SQL query, I created a Test.asp
Inserted a DB Result with just ClientName (contains)
Removed the value of the text field <Encoded.blaa blaa> and made it into a regular Text Field named T1

So now my SQL looks like so:
fp_sQry="SELECT * FROM Main WHERE ClientName LIKE '::T1::'"


Well, I get jack squat when doing submitting any text. Is there any way to tell if anything is being passed to the sql query with the ::T1:: value in there?

It basically gives me no records found, even when there is nothing in the clientname field...

-Sam



BeTheBall -> RE: Conditional Search Form like Access (1/17/2006 15:07:03)

When using a LIKE query, you will want to include the wildcard character.

fp_sQry="SELECT * FROM Main WHERE ClientName LIKE '%::T1::%'"



slincolnFELLERS -> RE: Conditional Search Form like Access (1/17/2006 15:50:41)

It's always good to know what the heck you're doing isn't it [;)]

Another lesson learned.

Awesome, we've got that working with the Radio buttons and all that jazz...now...to add the date field :0)

Now it's time to go conditional on it's backside. hee hee



slincolnFELLERS -> RE: Conditional Search Form like Access (1/17/2006 18:21:12)

Okay, I'm trying to do something I found in another thread, but she ain't working...imagine that [&:]

I'm putting this into the code and replacing my fp_sQry with fp_sQry=myquery

<%
Dim searchfield
Dim Usertext
usertext = Request.Form ("::T1::")
searchfield = Request.Form ("::R1::")
if (searchfield ="ClientName") Then
myquery = "SELECT * FROM MAIN WHERE (ClientName Like '%::usertext::%')"
elseif (searchfield = "MatterName")Then
myquery = "SELECT * FROM MAIN WHERE (MatterName Like '%::usertext::%')"
else
response.write("Hello me homies")
response.end
end if
%>

as you can guess, it's failing miserably. I put the end part in there to make sure it was getting through the If statement...and it is. With out it, I get conditional errors.

What's your take on all this, should I go a diff route?

-S



slincolnFELLERS -> RE: Conditional Search Form like Access (1/23/2006 13:43:50)

Hello!? Any one?? Spooky? It's been days...I hope this just means you're thinking really hard about it and you have not just become mayor of Stumpsville USA

-S



BeTheBall -> RE: Conditional Search Form like Access (1/23/2006 13:47:10)

quote:

<%
Dim searchfield
Dim Usertext
usertext = Request.Form ("::T1::")
searchfield = Request.Form ("::R1::")
if (searchfield ="ClientName") Then
myquery = "SELECT * FROM MAIN WHERE (ClientName Like '%::usertext::%')"
elseif (searchfield = "MatterName")Then
myquery = "SELECT * FROM MAIN WHERE (MatterName Like '%::usertext::%')"
else
response.write("Hello me homies")
response.end
end if
%>


Neither ClientName nor MatterName look like date fields? Is the above still an accurate picture of what you are working with?



slincolnFELLERS -> RE: Conditional Search Form like Access (1/23/2006 13:51:32)

You are correct. At this point, I'm not even introducing the date field. Just trying one Text box (T1) with 2 radio buttons (both R1) to see if I can get it to do a conditional search.



BeTheBall -> RE: Conditional Search Form like Access (1/23/2006 16:09:45)

And this doesn't work?

fp_sQry="SELECT * FROM Main WHERE ::R1:: LIKE '%::T1::%'"

If not, try:

fp_sQry="SELECT * FROM Main WHERE "& Request("R1") &" LIKE '%"& Request("T1") &"%'"



slincolnFELLERS -> RE: Conditional Search Form like Access (1/23/2006 16:42:53)

Nope, I get this error with both

Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine



slincolnFELLERS -> RE: Conditional Search Form like Access (1/23/2006 17:17:18)

never mind, I'm retarded. My database wasn't in sync.

I put in this and it worked fine: fp_sQry="SELECT * FROM Main WHERE ::R1:: LIKE '%::T1::%'"

Now I'd like to experiment with 2 text fields and some if thens Maybe 3 radio buttons, client name, matter name, and both so if both is selected the sql query would be select * from main where clientname like '%::t1::%' and mattername like '%::t2::%' type thing.

sam



slincolnFELLERS -> RE: Conditional Search Form like Access (1/23/2006 17:52:10)

Whee! I put this in and it works [;)]

<%
Dim searchid
searchid = request.form ("R1")
if searchid = "Both" then
myqry = "Select * from main where (clientname like '%::T1::%' and mattername like '%::T2::%')"
elseif searchid = "ClientName" then
myqry = "Select * from main where clientname like '%::T1::%'"
elseif searchid = "MatterName" then
myqry = "Select * from main where mattername like '%::T2::%'"
End if
%>


I'm going to start messing w/the date field option now.

Question - Do you know how to get it NOT to automatically start a DB request when you go to the page until the Submit button is pushed?

I've notice that if I just type in the address, it starts chuggin' on the DB using the last input I used...even though I've defaulted all fields blank...maybe I should default it as something I know isn't there??? So it dies quickly?

-Sam



BeTheBall -> RE: Conditional Search Form like Access (1/23/2006 18:36:51)

Find this snippet of code:

<table width="100%" border="1">
<thead>
<tr>
<td><b>ClientName</b></td>
<td><b>MatterName</b></td>
<td><b>FileName</b></td>
<td><b>FileNumber</b></td>
<td><b>FileSortNumber</b></td>
<td><b>Attorney</b></td>
<td><b>DateOpened</b></td>
<td><b>DateClosed</b></td>
<td><b>ClosedFileNumber</b></td>
<td><b>Notes</b></td>
<td><b>Locator</b></td>

Add this just before it:

<%If Request.Form("T1") <> "" Then %>

Then find the closing table tag for your results table and right after it put:

<%End If%>

What that does is tell the code not to execute anything until T1 has a value.



slincolnFELLERS -> RE: Conditional Search Form like Access (1/24/2006 12:51:36)

That worked, but I also had to put an "Else" at the end of my If Then statement....just so it wasn't looking for values (I was getting conditional errors)

elseif searchid = "FileNumber" then
myqry = "Select * from main where (filenumber like '%::T3::%') Order By Clientname ASC, FileNumber ASC"
else
response.write("Enter some text")
response.end
End if

I put in the DateOpened field and it works just fine as long as the user puts in the input formatted as so #mm/dd/yy# They are pretty use to having to put in the #'s but I'm messing w/the sql code to see if I can just add it in there automatically?? You know, like so:
select * from main where dateopened > '#'+::T4::+'#'

I saw some treads where people were adding things into the sql string...I just need to search and try.... unless you just know how and want to contribute. I think we're good to go though for now.

Thanks for all your help!

-Sam



slincolnFELLERS -> RE: Conditional Search Form like Access (1/24/2006 13:23:29)

Actually, putting that statement in before/after the table generation caused a problem if no text was put into the T1 field. If I did a search by T2 only, then the data would come out in an unformatted way (because T1 was blank...so no table generation)

I took those statements out and left my write statement in the IfThenElse portion at the bottom, that will serve my purpose [:)]

I'm just going to put some little statement like...Welcome to blaa blaa blaa... Instructions basically.

-S



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1083984