Problem with an .asp page displaying data based on a form field value.

[Brave]

I'm hoping someone can help me with a small issue.

I have an asp page that displays informaton from an Access database.
I want to create a form that allows users to display only data that
matches their search criteria.


I have made many forms like this in Front Page with the Database
results wizard, but I want to manually code it.


Here is the code I use to display the data from a database.


<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "driver={Microsoft Access Driver (*.mdb)};;DBQ=\\****\****
\DatabaseName.mdb;"
mySQL="SELECT * FROM DatabaseTableName WHERE (TableFieldName =
'FieldValue') ORDER BY TableFieldName ASC"
Set RS = Conn.Execute(mySQL)
If RS.EOF and RS.BOF Then
Response.Write("<font face=""verdana"" size=""2""><p>No records
returned.</p>")


Else


Response.Write "<table><tr>"
Response.Write "<td><b>Table Field Name</b></td>"
Response.Write "</tr>"


Do While Not RS.EOF


Response.Write "<tr>"
Response.Write "<td>"&RS("FieldValue")&"</td>"
Response.Write "</tr>"


RS.MoveNext
Loop
End If


Response.Write "</table>"


RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
%>


What I want to do is add a form like the one below that user can use
to determine the "field value" that is displayed.


<p><form METHOD="POST" ACTION="WebPageName.asp"><input
NAME="FormField" VALUE="<%=Request("FormFieldValue")%>"
size="24"> <input TYPE="submit" value="Search!"></form></p>


So if they type in "Smith" they get all entries that have a field
value of Smith


I'm not sure how to write the query so it looks at the form for the
value it uses to display information.


Any code examples would help me a great deal as I seem to be close,
but I cannot "connect" to two elements.


Thanks for taking the time to read all of this, and for any
assiatance
you can offer.

 

[Jon Spivey]

Hi,
You'd just need to stick a form field on the form then add the form value to
your sql, eg
<form method="get" action="<%=request.servervariables("SCRIPT_NAME")%>">
Name to search for <input type="text" name="Searchname">
<input type="submit">
</form>
<%
if request.form <> "" then
' everything else as you have except
s=trim(replace(request.querystring("SearchName"), "'", ""))
mySQL="SELECT * FROM DatabaseTableName WHERE (TableFieldName ='" & s & "'
ORDER BY TableFieldName ASC"

end if
%>

Cheers,
Jon

 

[Brave]

Hi,
You'd just need to stick a form field on the form then add the form value to
your sql, eg
<form method="get" action="<%=request.servervariables("SCRIPT_NAME")%>">
Name to search for <input type="text" name="Searchname">
<input type="submit">
</form>
<%
if request.form <> "" then
' everything else as you have except
s=trim(replace(request.querystring("SearchName"), "'", ""))
mySQL="SELECT * FROM DatabaseTableName WHERE (TableFieldName ='" & s & "'
ORDER BY TableFieldName ASC"

end if
%>

Cheers,
Jon





















- Show quoted text -

Thanks so much for taking the time to reply!!

 

[Brave]

Thanks so much for taking the time to reply!!- Hide quoted text -

- Show quoted text -

That worked like a dream!!!!!!

I was wondering if I could bother you with another question?

Can I also provide a form field that would allow them to determine the
table field that the database results is sorted by?

If you look at my current code it states to "ORDER BY TableFieldName
ASC"

Can you offer a code sample that would take the order by criteria from
the same form?

I would like to offer a pulldown menu with different table field names
that they can choose to sort by.

Thanks again.

 

[Trevor L.]

That worked like a dream!!!!!!

I was wondering if I could bother you with another question?

Can I also provide a form field that would allow them to determine the
table field that the database results is sorted by?

If you look at my current code it states to "ORDER BY TableFieldName
ASC"

Can you offer a code sample that would take the order by criteria from
the same form?

I would like to offer a pulldown menu with different table field names
that they can choose to sort by.

Thanks again.

Well, Jon would be better at this than I would, but the principle is the
same

Something like:

<form method="get" action="<%=request.servervariables("SCRIPT_NAME")%>">
Name to search for <input type="text" name="SearchName">
Field to order by<input type="text" name="OrderName">

<input type="submit">
</form>
<%
if request.form <> "" then
' everything else as you have except
s=trim(replace(request.querystring("SearchName"), "'", ""))
o=trim(replace(request.querystring("OrderName"), "'", ""))
mySQL="SELECT * FROM DatabaseTableName WHERE TableFieldName ='" & s
& "' ORDER BY " & o & " ASC"
end if
%>

But I am not 100% sure. I am just copying and altering the code, not testing
it.
--
Cheers,
Trevor L.
[ Microsoft MVP - FrontPage ]
MVPS Website: http://trevorl.mvps.org/
----------------------------------------

 

[Stefan B Rusynko]

That will work (provided both form fields always return a valid value)
Alternatively let them sort by any column
See http://www.asp101.com/samples/db_sort_multi.asp
--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
_____________________________________________


| Brave wrote:
| > That worked like a dream!!!!!!
| >
| > I was wondering if I could bother you with another question?
| >
| > Can I also provide a form field that would allow them to determine the
| > table field that the database results is sorted by?
| >
| > If you look at my current code it states to "ORDER BY TableFieldName
| > ASC"
| >
| > Can you offer a code sample that would take the order by criteria from
| > the same form?
| >
| > I would like to offer a pulldown menu with different table field names
| > that they can choose to sort by.
| >
| > Thanks again.
|
| Well, Jon would be better at this than I would, but the principle is the
| same
|
| Something like:
|
| <form method="get" action="<%=request.servervariables("SCRIPT_NAME")%>">
| Name to search for <input type="text" name="SearchName">
| Field to order by<input type="text" name="OrderName">
|
| <input type="submit">
| </form>
| <%
| if request.form <> "" then
| ' everything else as you have except
| s=trim(replace(request.querystring("SearchName"), "'", ""))
| o=trim(replace(request.querystring("OrderName"), "'", ""))
| mySQL="SELECT * FROM DatabaseTableName WHERE TableFieldName ='" & s
| & "' ORDER BY " & o & " ASC"
| end if
| %>
|
| But I am not 100% sure. I am just copying and altering the code, not testing
| it.
| --
| Cheers,
| Trevor L.
| [ Microsoft MVP - FrontPage ]
| MVPS Website: http://trevorl.mvps.org/
| ----------------------------------------
|