|
 |
asp_databases thread: Counting Form Fields and Creating a SQL string
Message #1 by "Eli Rothman" <elirothman@h...> on Sun, 28 Jan 2001 20:34:32 -0000
|
|
I'm having the hardest time with this for some reason.
I have a SQL Server database that contains information on a library of
books. I want to create an asp page that allows users to query this
databse by filling in any (or all) of four fields in a form. (e.g. Author,
Title, Publisher, and/or Subject.
How would I test to see which of these four fields had values and then
dynamically build a WHERE condition in a sql string with the fields'
infromation (putting 'AND's in the right places and omitting the fields
that have no values assigned)?
If it turns out that no fields were filled out, I'd like to ask the user
to try again.
Thanks, Eli
Message #2 by "Michael Wong \(Supernet\)" <go2net@p...> on Mon, 29 Jan 2001 13:25:42 +0800
|
|
Eli,
Actually, it is pretty simple to do in asp.
At the top of your form hanlder page...
'================================================
Dim strSQL, strWhere, xFieldValue
' Grab value of author field. Include this field in the
' WHERE clause if it is not empty.
xFieldValue = Trim(Request.Form("author"))
If xFieldValue <> "" Then
' Do not remove any empty space in this string
strWhere = " and author = '" & xFieldValue & "'"
End If
' Grab value of title field. Include this field in the
' WHERE clause if it is not empty.
xFieldValue = Trim(Request.Form("title"))
If xFieldValue <> "" Then
' Do not remove any empty space in this string
strWhere = strWhere & " and title = '" & xFieldValue & "'"
End If
' Grab value of publisher field. Include this field in the
' WHERE clause if it is not empty.
xFieldValue = Trim(Request.Form("publisher"))
If xFieldValue <> "" Then
' Do not remove any empty space in this string
strWhere = strWhere & " and publisher = '" & xFieldValue & "'"
End If
' Grab value of subject field. Include this field in the
' WHERE clause if it is not empty.
xFieldValue = Trim(Request.Form("subject"))
If xFieldValue <> "" Then
' Do not remove any empty space in this string
strWhere = strWhere & " and subject = '" & xFieldValue & "'"
End If
' Get rid of the first and
If strWhere <> "" Then
strWhere = Mid(strWhere, InStr(strWhere, "and")+3)
End If
' Check if user has filled in at least one field. Abort and return
' user to the query form if not so.
If strWhere = "" Then
Response.Redirect("your_query_form.asp?status=fail")
Response.End
End If
' If it goes this far, we have at least one field filled in. Then construct
' the entire SQL string.
strSQL = "Select * from books where " & strWhere
Your ADO code goes here...
'================================================
Try it out and good luck.
Cheers.
Michael Wong
----- Original Message -----
From: "Eli Rothman" <elirothman@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, January 29, 2001 7:55 PM
Subject: [asp_databases] Counting Form Fields and Creating a SQL string
> I'm having the hardest time with this for some reason.
>
> I have a SQL Server database that contains information on a library of
> books. I want to create an asp page that allows users to query this
> databse by filling in any (or all) of four fields in a form. (e.g. Author,
> Title, Publisher, and/or Subject.
>
> How would I test to see which of these four fields had values and then
> dynamically build a WHERE condition in a sql string with the fields'
> infromation (putting 'AND's in the right places and omitting the fields
> that have no values assigned)?
>
> If it turns out that no fields were filled out, I'd like to ask the user
> to try again.
>
> Thanks, Eli
>
Message #3 by "Dallas Martin" <dmartin@z...> on Mon, 29 Jan 2001 00:48:17 -0500
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_001A_01C0898D.2B733B20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
First, use JavaScript to test that at least one field was completed:
<script language=3D"JavaScript1.2">
<!--
function checkform()
{
if ((document.forms[0].author.length =3D=3D 0) &&
(document.forms[0].title.length =3D=3D 0) &&
(document.forms[0].publisher.length =3D=3D 0) &&
(document.forms[0].subject.length =3D=3D 0))
{
alert("Please enter a value in either the author, title, publisher
or subject fields\n";
return false;
}
return true;
}
/ /-->
</script>
Next on the receiving ASP page
author =3D request.form("author")
title =3Drequest.form("title")
publisher =3D request.form("publisher")
subject =3D request.form("subject")
I assume that you have at least one identity field in the table,
Hence your SELECT statement is trivial.
strSQL =3D "SELECT * FROM books WHERE book_id IS NOT NULL"
If NOT isempty(author) then
strSQL =3D strSQL & " AND author =3D '" & author & "'"
end if
If NOT isempty(title) then
strSQL =3D strSQL & " AND title =3D '" & title & "'"
end if
REPEAT for the other search criteria
Also, change your WHERE operator to suit, ie. LIKE or IN, etc.
Cheers,
Dallas Martin
Cheers,
Dallas Martin
----- Original Message -----
From: "Eli Rothman" <elirothman@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, January 29, 2001 6:55 AM
Subject: [asp_databases] Counting Form Fields and Creating a SQL string
> I'm having the hardest time with this for some reason.
>
> I have a SQL Server database that contains information on a library of
> books. I want to create an asp page that allows users to query this
> databse by filling in any (or all) of four fields in a form. (e.g.
Author,
> Title, Publisher, and/or Subject.
>
> How would I test to see which of these four fields had values and then
> dynamically build a WHERE condition in a sql string with the fields'
> infromation (putting 'AND's in the right places and omitting the
fields
> that have no values assigned)?
>
> If it turns out that no fields were filled out, I'd like to ask the
user
> to try again.
>
> Thanks, Eli
>
> ---
> FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
> INSIGHTS IN YOUR INBOX!
> Get the latest and best C++, Visual C++, Java, Visual Basic, and XML
tips, tools, and
> developments from the experts. Sign up for one or more of EarthWeb?s
> FREE IT newsletters at http://www.earthweb.com today!
$subst('Email.Unsub')
>
Message #4 by "Wally Burfine" <oopconsultant@h...> on Mon, 29 Jan 2001 14:48:25 -0000
|
|
You should also verify on the client side that at least one filter has been
specified before submitting the page.
>From: "Michael Wong \(Supernet\)" <go2net@p...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Re: Counting Form Fields and Creating a SQL string
>Date: Mon, 29 Jan 2001 13:25:42 +0800
>
>Eli,
>
>Actually, it is pretty simple to do in asp.
>
>At the top of your form hanlder page...
>
>'================================================
>Dim strSQL, strWhere, xFieldValue
>
>' Grab value of author field. Include this field in the
>' WHERE clause if it is not empty.
>xFieldValue = Trim(Request.Form("author"))
>If xFieldValue <> "" Then
> ' Do not remove any empty space in this string
> strWhere = " and author = '" & xFieldValue & "'"
>End If
>
>' Grab value of title field. Include this field in the
>' WHERE clause if it is not empty.
>xFieldValue = Trim(Request.Form("title"))
>If xFieldValue <> "" Then
> ' Do not remove any empty space in this string
> strWhere = strWhere & " and title = '" & xFieldValue & "'"
>End If
>
>' Grab value of publisher field. Include this field in the
>' WHERE clause if it is not empty.
>xFieldValue = Trim(Request.Form("publisher"))
>If xFieldValue <> "" Then
> ' Do not remove any empty space in this string
> strWhere = strWhere & " and publisher = '" & xFieldValue & "'"
>End If
>
>' Grab value of subject field. Include this field in the
>' WHERE clause if it is not empty.
>xFieldValue = Trim(Request.Form("subject"))
>If xFieldValue <> "" Then
> ' Do not remove any empty space in this string
> strWhere = strWhere & " and subject = '" & xFieldValue & "'"
>End If
>
>' Get rid of the first and
>If strWhere <> "" Then
> strWhere = Mid(strWhere, InStr(strWhere, "and")+3)
>End If
>
>' Check if user has filled in at least one field. Abort and return
>' user to the query form if not so.
>If strWhere = "" Then
> Response.Redirect("your_query_form.asp?status=fail")
> Response.End
>End If
>
>' If it goes this far, we have at least one field filled in. Then construct
>' the entire SQL string.
>strSQL = "Select * from books where " & strWhere
>
>Your ADO code goes here...
>'================================================
>
>Try it out and good luck.
>
>Cheers.
>
>Michael Wong
>
>----- Original Message -----
>From: "Eli Rothman" <elirothman@h...>
>To: "ASP Databases" <asp_databases@p...>
>Sent: Monday, January 29, 2001 7:55 PM
>Subject: [asp_databases] Counting Form Fields and Creating a SQL string
>
>
> > I'm having the hardest time with this for some reason.
> >
> > I have a SQL Server database that contains information on a library of
> > books. I want to create an asp page that allows users to query this
> > databse by filling in any (or all) of four fields in a form. (e.g.
>Author,
> > Title, Publisher, and/or Subject.
> >
> > How would I test to see which of these four fields had values and then
> > dynamically build a WHERE condition in a sql string with the fields'
> > infromation (putting 'AND's in the right places and omitting the fields
> > that have no values assigned)?
> >
> > If it turns out that no fields were filled out, I'd like to ask the user
> > to try again.
> >
> > Thanks, Eli
> >
Message #5 by "Eli Rothman" <elirothman@h...> on Mon, 29 Jan 2001 22:38:51 -0600
|
|
Thanks so much.
Worked like a charm.
Eli
----- Original Message -----
From: "Michael Wong (Supernet)" <go2net@p...>
To: "ASP Databases" <asp_databases@p...>
Sent: Sunday, January 28, 2001 11:25 PM
Subject: [asp_databases] Re: Counting Form Fields and Creating a SQL string
> Eli,
>
> Actually, it is pretty simple to do in asp.
>
> At the top of your form hanlder page...
>
> '================================================
> Dim strSQL, strWhere, xFieldValue
>
> ' Grab value of author field. Include this field in the
> ' WHERE clause if it is not empty.
> xFieldValue = Trim(Request.Form("author"))
> If xFieldValue <> "" Then
> ' Do not remove any empty space in this string
> strWhere = " and author = '" & xFieldValue & "'"
> End If
>
> ' Grab value of title field. Include this field in the
> ' WHERE clause if it is not empty.
> xFieldValue = Trim(Request.Form("title"))
> If xFieldValue <> "" Then
> ' Do not remove any empty space in this string
> strWhere = strWhere & " and title = '" & xFieldValue & "'"
> End If
>
> ' Grab value of publisher field. Include this field in the
> ' WHERE clause if it is not empty.
> xFieldValue = Trim(Request.Form("publisher"))
> If xFieldValue <> "" Then
> ' Do not remove any empty space in this string
> strWhere = strWhere & " and publisher = '" & xFieldValue & "'"
> End If
>
> ' Grab value of subject field. Include this field in the
> ' WHERE clause if it is not empty.
> xFieldValue = Trim(Request.Form("subject"))
> If xFieldValue <> "" Then
> ' Do not remove any empty space in this string
> strWhere = strWhere & " and subject = '" & xFieldValue & "'"
> End If
>
> ' Get rid of the first and
> If strWhere <> "" Then
> strWhere = Mid(strWhere, InStr(strWhere, "and")+3)
> End If
>
> ' Check if user has filled in at least one field. Abort and return
> ' user to the query form if not so.
> If strWhere = "" Then
> Response.Redirect("your_query_form.asp?status=fail")
> Response.End
> End If
>
> ' If it goes this far, we have at least one field filled in. Then
construct
> ' the entire SQL string.
> strSQL = "Select * from books where " & strWhere
>
> Your ADO code goes here...
> '================================================
>
> Try it out and good luck.
>
> Cheers.
>
> Michael Wong
>
> ----- Original Message -----
> From: "Eli Rothman" <elirothman@h...>
> To: "ASP Databases" <asp_databases@p...>
> Sent: Monday, January 29, 2001 7:55 PM
> Subject: [asp_databases] Counting Form Fields and Creating a SQL string
>
>
> > I'm having the hardest time with this for some reason.
> >
> > I have a SQL Server database that contains information on a library of
> > books. I want to create an asp page that allows users to query this
> > databse by filling in any (or all) of four fields in a form. (e.g.
Author,
> > Title, Publisher, and/or Subject.
> >
> > How would I test to see which of these four fields had values and then
> > dynamically build a WHERE condition in a sql string with the fields'
> > infromation (putting 'AND's in the right places and omitting the fields
> > that have no values assigned)?
> >
> > If it turns out that no fields were filled out, I'd like to ask the user
> > to try again.
> >
> > Thanks, Eli
>
|
|
|
View Cart
