Problem adding registration data to mysql database

[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");
%>

[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.

[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.

[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");

    %>

[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.