?
Solved

JSP and Querying Database

Posted on 2003-03-25
11
Medium Priority
?
207 Views
Last Modified: 2010-04-01
I'm new to JSP and need your help. I'm trying to get multiple things from MYSql database but i have not been able to do it yet. The connection is working because i can get one field but not duplicates. Please help! My code is below:

In the code below i'm getting an output of
1
1
1
1
1
2
2
2
2
2
 Please help
STK



<html>
<head>
<%@ page
    import = "java.io.*"
    import = "java.lang.*"
    import = "java.sql.*"
%>
<title>
TEST
</title>
</head>
<body>
<%
    String     name;
    Connection dbconn;
    ResultSet results;
    PreparedStatement sql;
    try
    {
         Class.forName("org.gjt.mm.mysql.Driver").newInstance();
         try
         {
              int mem, user, pass1, pass2, email;
              boolean     doneheading = false;
             
              dbconn = DriverManager.getConnection("jdbc:mysql://localhost/nutrition","saundersk","kevin");
       
              name = request.getParameter("name").trim().toUpperCase();
              sql = dbconn.prepareStatement("SELECT * FROM member WHERE UPPER(user_name) = ?");
       
              sql.setString(1, request.getParameter("name"));
              results = sql.executeQuery();
             
     
              while(results.next())
              {
                   if(! doneheading)
                   {
                        out.println("<table border=2>");
                        doneheading = true;
                       
                   }
                   mem = results.getInt("member_id");
                   user = results.getInt("member_id");
                   pass1 = results.getInt("member_id");
                   pass2 = results.getInt("member_id");
                   email = results.getInt("member_id");
                   out.println("<tr><td>" + mem);
                   out.println("<tr><td>" + user);
                   out.println("<tr><td>" + pass1);
                   out.println("<tr><td>" + pass2);
                   out.println("<tr><td>" + email);
         

              }
              if(doneheading)
              {
                   out.println("</table>");
              }
              else
              {
                   out.println("No matches for ");
              }
         }          
         catch (SQLException s)
         {
              out.println("SQL Error");
         }
    }
    catch (ClassNotFoundException err)
    {
         out.println("Class loading error");
       }
%>
</body>
</html>
0
Comment
Question by:STK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 19

Accepted Solution

by:
cheekycj earned 120 total points
ID: 8204631
<html>
<head>
<%@ page
   import = "java.io.*"
   import = "java.lang.*"
   import = "java.sql.*"
%>
<title>
TEST
</title>
</head>
<body>
<%
   String     name;
   Connection dbconn;
   ResultSet results;
   PreparedStatement sql;
   try
   {
        Class.forName("org.gjt.mm.mysql.Driver").newInstance();
        try
        {
             int mem, user, pass1, pass2, email;
             boolean     doneheading = false;
             
             dbconn = DriverManager.getConnection("jdbc:mysql://localhost/nutrition","saundersk","kevin");
     
             name = request.getParameter("name").trim().toUpperCase();
             sql = dbconn.prepareStatement("SELECT * FROM member WHERE UPPER(user_name) = ?");
       
             sql.setString(1, request.getParameter("name"));
             results = sql.executeQuery();
             
   
             while(results.next())
             {
                  if(! doneheading)
                  {
                       out.println("<table border=2>");
                       doneheading = true;
                       
                  }
                  mem = results.getInt("member_id");
                  user = mem;
                  pass1 = mem;
                  pass2 = mem;
                  email = mem;
                  out.println("<tr><td>" + mem);
                  out.println("<tr><td>" + user);
                  out.println("<tr><td>" + pass1);
                  out.println("<tr><td>" + pass2);
                  out.println("<tr><td>" + email);
       

             }
             if(doneheading)
             {
                  out.println("</table>");
             }
             else
             {
                  out.println("No matches for ");
             }
        }          
        catch (SQLException s)
        {
             out.println("SQL Error");
        }
   }
   catch (ClassNotFoundException err)
   {
        out.println("Class loading error");
      }
%>
</body>
</html>

But the more intriguing question is why are you getting the same column and storing it in different variables rather than reusing the same var.

JDBC doesn't allow you to retrieve the same column twice (for some reason.. I haven't found documentation as to why yet) unless you have some third party api that allows it.

CJ
0
 

Author Comment

by:STK
ID: 8205315
the same output....does it have something to do with the fact that all user, pass1, pass2, email are Strings...not int as above.

What would the new code look like if it does?
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8205332
if they are strings use String varname = results.getString("columname");

CJ
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:STK
ID: 8205349
Thank-you for you help...the problem was the string to int!!!
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8205371
Glad I could help and Thanx for the "A"

CJ
0
 

Author Comment

by:STK
ID: 8205401
If i have multiple input from a JSP page how do i get both pieces of information using the same code as above
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8205415
your form allows the user to input name and what else?

CJ
0
 

Author Comment

by:STK
ID: 8205479
Stay i have first name and last name in different text boxes on the first page. Then i try and use them in a query like

sql = dbconn.prepareStatement("SELECT * FROM member WHERE UPPER(first_name) = ? AND UPPER(last_name) = ? ");

do i need to put another
sql.setString(1, request.getParameter("name")); one for the first name and the other for the second. actually this isn't right because i tried this already and it said array out of bound error.

What do i need to do instead?
0
 

Author Comment

by:STK
ID: 8205500
Stay i have first name and last name in different text boxes on the first page. Then i try and use them in a query like

sql = dbconn.prepareStatement("SELECT * FROM member WHERE UPPER(first_name) = ? AND UPPER(last_name) = ? ");

do i need to put another
sql.setString(1, request.getParameter("name")); one for the first name and the other for the second. actually this isn't right because i tried this already and it said array out of bound error.

What do i need to do instead?
0
 

Author Comment

by:STK
ID: 8205506
Stay i have first name and last name in different text boxes on the first page. Then i try and use them in a query like

sql = dbconn.prepareStatement("SELECT * FROM member WHERE UPPER(first_name) = ? AND UPPER(last_name) = ? ");

do i need to put another
sql.setString(1, request.getParameter("name")); one for the first name and the other for the second. actually this isn't right because i tried this already and it said array out of bound error.

What do i need to do instead?
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8205547
yes you do.. like this:
String firstname = request.getParameter("firstnamefield");
String lastname = request.getParameter("lastnamefield");

sql = dbconn.prepareStatement("SELECT * FROM member WHERE UPPER(first_name) = ? AND UPPER(last_name) = ? ");
sql.setString(1, firstname.toUpperCase());
sql.setString(2, lastname.toUpperCase());

results = sql.executeQuery();

while (results.next()) {
  out.println("found results");
}

CJ


0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An introduction to the wonderful sport of Scam Baiting.  Learn how to help fight scammers by beating them at their own game. This great pass time helps the world, while providing an endless source of entertainment. Enjoy!
Introducing Priority Question, our latest feature.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question