Problems in inserting data into MySQL table from JSP....

Hi,

I am using a JSP from to collect data from user and then inserting those values into a table in MySQL.But it is returning 'null' always.I am able to query the DB and view the entire table and everything else is fine. But the INSERT is not working somehow!!!
Please help me in solving my problem!!!
The code is as follows:

<html>
<title>
DBMS Project
</title>
<body bgcolor=#FFFFFF>
<font color=black>
 
<img src="logo.JPG" width="280" height="120"/>
 
</font>
 

 
       <%@ page import="java.sql.*" %>
       <%@ page import="java.io.*" %>
	<%
	    try {
		   Class.forName("com.mysql.jdbc.Driver").newInstance();
		   Connection conn = DriverManager.getConnection("jdbc:mysql://url.com:3306/DB", "guest", "1234567");
	%>
 
 
	<% 
	    String action = request.getParameter("action");
	    if(action != null && action.equals("insert"))  {
	    conn.setAutoCommit(false);
	    PreparedStatement pstmt = conn.prepareStatement(("INSERT INTO Table VALUES(?, ?, ?, ?, ?, ?, ?)"));
	    pstmt.clearParameters();
	    pstmt.setInt(1, Integer.parseInt(request.getParameter("Col1")));
	    pstmt.setString(2, request.getParameter("Col2"));
	    pstmt.setInt(3, Integer.parseInt(request.getParameter("Col3")));
	    pstmt.setString(4, request.getParameter("Col4"));
	    pstmt.setString(5, request.getParameter("Col5"));
	    pstmt.setString(6, request.getParameter("Col6"));
	    pstmt.setString(7, request.getParameter("Col7"));
	    pstmt.executeUpdate();
	    conn.commit();
	    conn.setAutoCommit(true);
	    }
	%>
 
	<%
	    Statement stmt = conn.createStatement();
	    ResultSet rs = stmt.executeQuery("SELECT * FROM Table");
	%>
 
 
<table border="1" cellspacing="6" > 
  <tr>
        <th>Col1</th>
	<th>Col2</th>
	<th>Col3</th>
        <th>Col4</th>
	<th>Col5</th>
	<th>Col6</th>
        <th>Col7</th>
 
  </tr>   
 
	<tr>
	    <form action="Page.jsp" method="get">
	    <input type="hidden" value="insert" name="action">
	    <th><input value="" name="Col1" size="9"></th>
	    <th><input value="" name="Col2" size="10"></th>
	    <th><input value="" name="Col3" size="15"></th>
	    <th><input value="" name="Col4" size="15"></th>
	    <th><input value="" name="Col5" size="10"></th>
	    <th><input value="" name="Col6" size="10"></th>
	    <th><input value="" name="Col7" size="10"></th>
	    <th><input type="submit" value="Insert"></th>
	</form>
	</tr>
 
	<%
	    while(rs.next()) {
	%>
 
	
 
<tr>
	<td> <%= rs.getInt("col1") %></td>
	<td> <%= rs.getString("col2") %></td>
	<td> <%= rs.getInt("col3") %></td>
	<td> <%= rs.getString("col4") %></td>
	<td> <%= rs.getString("col5") %></td>
        <td> <%= rs.getString("col6") %></td>
        <td> <%= rs.getString("col7") %></td>
</tr>
 
	<%
	    }
	%>
 
 
</table>
 
<%
	rs.close();  
	stmt.close();
	conn.close();
	}  catch (SQLException sqle) {
	   out.println(sqle.getMessage());
	}  catch (Exception e) {
	   out.println(e.getMessage());
	}
%>
</body>
</html>

Thank You!!!

abhishek2301

Light Poster

27 posts since Nov 2007

Reputation Points: 10

Solved Threads: 0

4 Years Ago

0

Few thinks to this
1) JSP should not be use any more to communicate with DB, you need to use servlets
2) Do not use GET method for action POST is better
3) You have no validation on your data, which is very bad (don't expect user to always provide correct data, even if this is school work. Teachers like to check on this sort of mistakes)
Check Sun website and these tutorials on how to do it

peter_budo

Code tags enforcer

Moderator

15,436 posts since Dec 2004

Reputation Points: 2,806

Solved Threads: 902

4 Years Ago

0

Please follow the suggestions outlined by peter_budo.

Also, your best perhaps to avoid embedding scriplet code and use tag notation. See JSTL for example.

You might be new to this area, try and follow best practices and it will make life far easier.

tommybobby