Member Avatar for hegRT

Hi,
Im new to JSP and I am trying to add data from a simple registration page using JSP to a table in mysql,

My code seems to be working OK howerver when I check the table in MySQL Control Centre...no new records have been added,

I am able to query existing records that I inserted using MySQL Command Line Client with another JSP query page ,

I would appreciate any advice that anyone has to offer,
Thanks

My HTML form is as follows:

<form action="<%= response.encodeURL("adduser3.jsp") %>" 
        method="post">
<center>
<table cellpadding=4 cellspacing=2 border=0>
<th bgcolor="#CCCCFF" colspan=2>
<font size=5>USER REGISTRATION</font>
<br>
<font size=1><sup>*</sup> Required Fields</font>
</th>
<tr bgcolor="#c8d8f8">
<td valign=top> 
<b>Name<sup>*</sup></b> 
<br>
<input type="text" name="user" value="" size=15 maxlength=20></td>
<td  valign=top>

<tr bgcolor="#c8d8f8">
<td valign=top>
<b>Password<sup>*</sup></b> 
<br>
<input type="password" name="password" value="" size=15  maxlength=20>
<br></td>

</tr>
<tr bgcolor="#c8d8f8">
<td valign=top>
<b>Email<sup>*</sup></b> 
<br>
<input type="text" name="email" size=25 value=""  
maxlength=125></td>
<td  valign=top>

</tr>
<tr bgcolor="#c8d8f8">
<td valign=top>
<b>Message<sup>*</sup></b> 
<br>
<input type="text" name="message" size=50 value=""  
maxlength=150></td>
<td  valign=top>

<br>
</tr>


<tr bgcolor="#c8d8f8">
<td  align=center colspan=2>
<input type="submit" value="Submit"> <input type="reset"  
value="Reset">
</td>
</tr>
</table>
</center>
</form>



My JSP for adding a user is as follows:

<%@ page import="javax.servlet.*" %>
<%@ page import="javax.servlet.http.*" %>

<%@ page import="java.sql.*" %>

<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;



String queryText = "insert into users values(\"" + 
       request.getParameter("user") + "\",\"" +
  request.getParameter("password") + "\",\"" +
    request.getParameter("email") + "\",\"" +
 request.getParameter("message") + "\")";

try {
  Class.forName("org.gjt.mm.mysql.Driver").newInstance();
  con = DriverManager.getConnection("jdbc:mysql://localhost/register",
  "root", "rootpassword");
  stmt = con.createStatement();
  rs = stmt.executeQuery(queryText);
} catch (Exception e) { }

response.sendRedirect("Successreg.jsp");
%>
Edited by TrustyTony because: fixed formatting
  • 2 Contributors
  • 4 Replies
  • 162 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by jwenting
Member Avatar for jwenting

executeQuery cannot be used to execute anything except select statements.

And you shouldn't use scriptlet blocks in JSPs, it's considered very bad, leads to hard to read and harder to maintain code.

Member Avatar for jwenting

And another problem: your insert (if it's executed at all, which is unlikely) doesn't get comitted to the database.
That means it's visible only to the current database session and will be undone when that session terminates.

I suggest you follow Sun's JDBC tutorial or read a GOOD introductory text about Java and JDBC.

Member Avatar for hegRT

Thanks for your advice jwenting,
I got it working using this code

<%@ page import="javax.servlet.*" %>
<%@ page import="javax.servlet.http.*" %>
<%@ page import="java.sql.*" %>

<%



Class.forName("org.gjt.mm.mysql.Driver");

java.sql.Connection connection =

    java.sql.DriverManager.getConnection
                ("jdbc:mysql://localhost/register","root", "rootpassword");

    String query  = "INSERT INTO users VALUES (?,?,?,?)";


    java.sql.PreparedStatement Stmt = 

            connection.prepareStatement(query);

    Stmt.setString(1,request.getParameter("email"));
    Stmt.setString(2,request.getParameter("user"));
    Stmt.setString(3,request.getParameter("password"));
    Stmt.setString(4,request.getParameter("message"));

    Stmt.executeUpdate();

    Stmt.close();
    connection.close();

    response.sendRedirect("Successreg.jsp");

    %>
Edited by Dani because: Fixed formatting
Member Avatar for jwenting

You should really do some exception handling in there.

You MUST ensure that your database resources are ALWAYS released, which means placing the close statements in a finally clause and all other code using them in a try/catch block.

Otherwise you're going to end up with connection starvation when you get exceptions, and can bring the server down.
Maybe not much of a problem for you yet, but it's never to early to start doing the right thing.

And refactor that database code into a servlet, it does NOT belong in a JSP.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.