#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2002
    Posts
    20
    Rep Power
    0

    using mysql and jsp to extract records


    I'm new to JSP

    Here is portion of my code. searchStudent refers to the name of the textfield where the first name is entered.

    SQL statement.
    rs=statement.executeQuery("SELECT * FROM students WHERE firstname='request.getParameter("searchStudent")'");

    ......

    while (rs.next())
    {
    String studentID=rs.getString(1);
    String firstName=rs.getString(2);
    String lastName=rs.getString(3);
    }

    ....

    it gives me an error message
    Generated servlet error:
    C:\Apache Tomcat 4.0\work\localhost\Testing\searchstudent1$jsp.java:83: ')' expected.
    rs=statement.executeQuery("SELECT * FROM students WHERE firstname='request.getParameter("searchStudent")'");

    however can I display only those records specified in the texfield

    Help

    Leslie
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2002
    Location
    Stockholm, Sweden
    Posts
    1
    Rep Power
    0
    Try this instead
    rs=statement.executeQuery("SELECT * FROM students WHERE firstname='" + request.getParameter("searchStudent") + "'");
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2002
    Posts
    20
    Rep Power
    0

    concatenating strings and columns in mysql statement in a jsp page


    Hi

    Thanx for the previous suggestion, it worked. However I would like to know how to concatenate two strings in an mysql statement.

    Here is a fragment of my code

    /*Search student refers to the name of the textfield either student number, first name, last name, or both first name and last name*/

    //the var student is the student number
    //the var first is the first name
    //the var last is the last name

    String student = request.getParameter("searchStudent");
    String first = request.getParameter("searchStudent");
    String last = request.getParameter("searchStudent");

    student.trim();
    first.trim();
    last.trim();

    Class.forName("org.gjt.mm.mysql.Driver").newInstance();
    connection = DriverManager.getConnection(connectionURL, "", "");
    statement=connection.createStatement();

    //Query statment
    //This statment search for all records form students table relating
    //first name, last name, first name and last name,student number
    //of particular student

    //Whats wrong with this statement
    rs=statement.executeQuery("SELECT * FROM students WHERE first+last='" +first+last+"' OR firstname='" + first + "' OR lastname='" + last + "' OR studentid='" + student + "'");

    while (rs.next()){
    String studentID=rs.getString(1);
    String firstName=rs.getString(2);
    String lastName=rs.getString(3);

    .......

    I'm getting this error
    javax.servlet.ServletException: Column not found: Unknown column 'first' in 'where clause'

    I want the text field to accept a full name for example "Tom Jones"
    It will then search the firstname and lastname from the student table and display those records for that persons name.

    Please Help

    Leslie
  6. #4
  7. No Profile Picture
    aHVoPw==
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2001
    Posts
    1,058
    Rep Power
    15
    in:
    "SELECT * FROM students WHERE first+last='"
    is there such a column named first+last?
    (you shouldn't name a column with a plus in it.. use underscore would look nicer...)
    Visit my new IIS Tutorial Series!
  8. #5
  9. No Profile Picture
    Moderator =(8^(|)
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Feb 2002
    Location
    Sacramento, CA
    Posts
    1,710
    Rep Power
    14
    just put an AND clause in your query.

    WHERE
    (first = first AND last = last ) OR . . .

IMN logo majestic logo threadwatch logo seochat tools logo