|
| |
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
 |
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
RE: Conditional Search Form like Access - 1/12/2006 13:40:13
PS - I'm doing this in FP2002
-S
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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::%'"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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") &"%'"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
slincolnFELLERS
Posts: 59
Joined: 8/6/2004
From: Oklahoma City - OK
Status: offline
|
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
| |
|
 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
|
|
|
Printable Version 
