Thanks all.
I figured out what was going on. It was taking the information from both
fields and adding them to the query string, since I was testing to see if
they all had information typed into them. So I set up a boolean like so:
boolean isntCombined = true;
if ( title != null && ! title.equals ("") && author != null && !
author.equals(""))
{
query = query + "WHERE upper(Title) LIKE upper('%" + title +
"%') AND upper(Author) LIKE upper('%" + author + "%')";
isntCombined = false;
}
if (isntCombined)
{
if ( title != null && ! title.equals ("") )
query = query + "WHERE upper(Title) LIKE upper('%" + title +
"%')";
if ( author != null && ! author.equals ("") )
query = query + "WHERE upper(Author) LIKE upper('%" + author + "%')";
if ( isbn != null && ! isbn.equals ("") )
query = query + "WHERE upper(ISBN) LIKE upper('%" + isbn + "%')";
if (query.indexOf ("WHERE") == -1 )
query = query + "WHERE upper(Author) LIKE upper('" + author + "')";
else
query = query + "OR upper(Author) LIKE upper('" + author + "')";
}
----- Original Message -----
From: Richard Yee <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, November 18, 2001 4:15 PM
Subject: Re: query based on multiple form fields
> Mike,
> Patrick is correct. Your query is still incorrect.
> > query = query + "WHERE upper(Title) LIKE " + "upper('%"
+
> >title + "%')" + "AND upper(Author) LIKE " + "upper('%" + author + "%')";
>
> This string is not what you want "AND upper(Author) LIKE "
> You want "AND upper('Author') LIKE ". See comments below for further
> improvement.
> To debug your code in the future, I'd recommend printing out your query
string.
>
> Some other suggestions on your code:
>
> 1) Instead of using " title != null && ! title.equals ("") ", use
> this "".equals(title)
>
> 2) You might also want to use title.trim() to remove leading and ending
spaces
>
> 3) You can use the String.toUpperCase() method to convert a string to
upper
> case rather than the SQL upper() function. It may make your SQL a little
> cleaner.
>
> 4) There is a typo in this code:
> >if ( title != null && ! title.equals ("") && author != null && !
> >title.equals(""))
> > query = query + "WHERE upper(Title) LIKE " + "upper('%"
+
> >title + "%')" + "AND upper(Author) LIKE " + "upper('%" + author + "%')";
>
> You check for title.equals("") twice instead of checking for
author.equals("")
>
> 5) try this code
> if (!"".equals(title) && !"".equals(author)) {
> // title and author are both not null and empty strings
> title = title.toUpperCase();
> author = author.toUpperCase();
> query = query + "WHERE title LIKE " + "'%" + title + "%'" + "AND
"
> + author + " LIKE " + "'%" + author + "%'";
>
> Regards,
>
> Richard
> At 03:14 PM 11/18/2001 -0500, you wrote:
> >tried that too!! =)
> >
> >the code below works if pass data from only one field. So if I were to
> >submit only the author or only the title or only the isbn, it would work.
> >
> >What I did before was :
> >
> >if ( title != null && ! title.equals ("") && author != null && !
> >title.equals(""))
> > query = query + "WHERE upper(Title) LIKE " + "upper('%"
+
> >title + "%')" + "AND upper(Author) LIKE " + "upper('%" + author + "%')";
> >
> >And that didn't work either
> >
> >
> >
> >----- Original Message -----
> >From: Patrick Quinn-Graham <>
> >To: <[EMAIL PROTECTED]>
> >Sent: Sunday, November 18, 2001 3:06 PM
> >Subject: Re: query based on multiple form fields
> >
> >
> > > See, now you should have said that you were working with SQL :-)
> > > You can't have two "WHERE" clauses, just join them with an AND or an
OR.
> > >
> > > The statement should end up like:
> > > "SELECT First, Second FROM tblName WHERE upper(First) LIKE upper('%" +
> >first
> > > + "%') AND upper(Second) LIKE upper('%" + second + "%')"
> > >
> > > or change the AND to an OR. (Haven't tried with the upper thing, but I
was
> > > always under the impression that Like was case insesitive? so this may
> >just
> > > put additonal load in changing case that needn't be done.)
> > >
> > > Hope this is of use.
> > >
> > > Patrick.
> > >
> > >
> > > -----Original Message-----
> > > From: A mailing list for discussion about Sun Microsystem's Java
Servlet
> > > API Technology. [mailto:[EMAIL PROTECTED]]On Behalf Of
mike
> > > dizon
> > > Sent: Monday, 19 November 2001 8:55 a.m.
> > > To: [EMAIL PROTECTED]
> > > Subject: Re: query based on multiple form fields
> > >
> > >
> > > I've tried that, as you can see from the code below. Say I wanted base
my
> > > search on BOTH the title AND author at the same time? How would I do
that?
> >I
> > > keep getting the error "SQL Not Properly Ended, or something like
that"
> > >
> > > MD
> > >
> > >
> > >
> > >
> > > String title = request.getParameter ("title");
> > > String author = request.getParameter ("author");
> > > String isbn = request.getParameter ("isbn");
> > > String query = "SELECT Title, Author, ISBN, Cost,
> >QuantityOnHand
> > > FROM Products ";
> > >
> > > if ( title != null && ! title.equals ("") )
> > > query = query + "WHERE upper(Title) LIKE " +
"upper('%" +
> > > title + "%')";
> > >
> > > if ( isbn != null && ! isbn.equals ("") )
> > > query = query + "WHERE upper(ISBN) LIKE " + "upper('%" + isbn +
"%')";
> > >
> > > if ( author != null && ! author.equals ("") )
> > > query = query + "WHERE upper(Author) LIKE " + "upper('%" + author
+
> > > "%')";
> > >
> > > if (query.indexOf ("WHERE") == -1 )
> > > query = query + "WHERE upper(Author) LIKE " + "upper('" + author
+
> > > "')";
> > > else
> > > query = query + "OR upper(Author) LIKE " + "upper('" + author +
"')";
> > > ----- Original Message -----
> > > From: Andrew Perepelytsya <>
> > > To: <[EMAIL PROTECTED]>
> > > Sent: Sunday, November 18, 2001 2:26 PM
> > > Subject: Re: query based on multiple form fields
>
>
___________________________________________________________________________
> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
body
> of the message "signoff SERVLET-INTEREST".
>
> Archives: http://archives.java.sun.com/archives/servlet-interest.html
> Resources: http://java.sun.com/products/servlet/external-resources.html
> LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
>
>
___________________________________________________________________________
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff SERVLET-INTEREST".
Archives: http://archives.java.sun.com/archives/servlet-interest.html
Resources: http://java.sun.com/products/servlet/external-resources.html
LISTSERV Help: http://www.lsoft.com/manuals/user/user.html
