asked on

JSP and Querying Database

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>

ASKER CERTIFIED SOLUTION

cheekycj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

STK

ASKER

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?

cheekycj

if they are strings use String varname = results.getString("columname");

CJ

STK

ASKER

Thank-you for you help...the problem was the string to int!!!

cheekycj

Glad I could help and Thanx for the "A"

CJ

STK

ASKER

If i have multiple input from a JSP page how do i get both pieces of information using the same code as above

cheekycj

your form allows the user to input name and what else?

CJ

STK

ASKER

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?

STK

ASKER

STK

ASKER

cheekycj

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