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
