Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

JSP and Querying Database

Posted on 2003-03-25
11
Medium Priority
?
208 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
  • 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
Independent Software Vendors: 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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Choosing the right mix of apps is very much necessary for CPAs for making the most of the latest technology through which they can boost their growth.
Are you a startup company? Being a startup, you may be using shared hosting, or maybe even dedicated hosting. But have you ever given a thought to using cloud computing now? Yes, don’t be surprised, it is possible for startups to opt for cloud compu…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

572 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