|
 |
asp_databases thread: user defined queries
Message #1 by "crystal" <crystalightening@j...> on Thu, 21 Jun 2001 20:17:23
|
|
I want the user to defined their own reports via query form using an asp
to search an access database. My problem is how to determine which fields
from which tables the user is trying to retrieve. Here's my code so far.
Thank you in advance.
<% @LANGUAGE = VBScript %>
<% Option Explicit %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%
Dim con, Rs, queryStr
Set con = Server.CreateObject("ADODB.Connection")
con.Open "DSN=DB2774A;UID=2774;PWD=156515"
queryStr = Request.form("query")
Set Rs = con.Execute(queryStr)
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<table>
<%
Do While Not Rs.EOF
%>
<tr>
<td><% = Rs("????") %>
</td>
<%
Rs.MoveNext
Loop
%>
</table>
</body>
</html>
<%
Rs.Close
con.Close
%>
Message #2 by slau@p... on Fri, 22 Jun 2001 05:49:20
|
|
I'm guessing you want to do this in ASP.
Come up with a string that slowly builds itself. So, if you have a form:
1) First name, 2) Last Name, 3)phone number 4) id number:
Then do something like this. Supposing that the text fields are labled as
such: First name = FName, Last Name = LName, phone number = Pnum, id
number = idNum. We'll assume the fields in the db are as the same as
above, underscores for spaces.
Then:
if Request.Form("FName") <> "" then
query_str = "First_Name like " & Request.Form("FName") & "%"
end if
if Request.Form("LName") <> "" then
query_str = query_str & "Last_Name like " & Request.Form("LName") & "%"
end if
if Request.form("Pnum") <> "" then
query_str = query_str & "Pnum = " & Request.Form("Pnum")
end if
if Request.form("idNum) <> "" them
query_str = query_str & "Id_Number = " & Request.form("IdNum")
end if
Note that if any of these if conditions fail, it isn't appended to the
string. And then, you'd do something like this:
objRS.open "select * from dataTable where" & query_str, objConn.
Hopefully that helped... I don't know if the last sql line will give you
an error or not, I'll check when I'm at work tomorrow.
Hopefully this helps,
Cheers,
Sandra
> I want the user to defined their own reports via query form using an asp
> to search an access database. My problem is how to determine which
fields
> from which tables the user is trying to retrieve. Here's my code so far.
> Thank you in advance.
> <% @LANGUAGE = VBScript %>
> <% Option Explicit %>
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <%
> Dim con, Rs, queryStr
>
> Set con = Server.CreateObject("ADODB.Connection")
> con.Open "DSN=DB2774A;UID=2774;PWD=156515"
>
> queryStr = Request.form("query")
>
> Set Rs = con.Execute(queryStr)
> %>
> <html>
> <head>
> <title>Untitled Document</title>
> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
> </head>
>
> <body bgcolor="#FFFFFF">
> <table>
> <%
> Do While Not Rs.EOF
> %>
> <tr>
> <td><% = Rs("????") %>
> </td>
> <%
> Rs.MoveNext
> Loop
> %>
>
> </table>
> </body>
> </html>
>
> <%
> Rs.Close
> con.Close
|
|
|
View Cart
