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

•

What is DaniWeb IT Discussion Community?

You're currently browsing the JSP section within the Web Development category of DaniWeb, a massive community of 455,973 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,819 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our JSP advertiser: Lunarpages JSP Web Hosting

Thread Tools Display Modes

Views: 2492 | Replies: 2

•

Join Date: Nov 2007

Posts: 1

Reputation: abhishek2301 is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

abhishek2301 abhishek2301 is offline

Offline

Newbie Poster

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

Nov 25th, 2007

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:

Help with Code Tags

(Toggle Plain Text)

<html>
<title>
DBMS Project
</title>
<body bgcolor=#FFFFFF>
<font color=black>
 
<img src="logo.JPG" width="280" height="120"/>
 
</font>
 
<br>
 
       <%@ 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>

<html>
<title>
DBMS Project
</title>
<body bgcolor=#FFFFFF>
<font color=black>
 
<img src="logo.JPG" width="280" height="120"/>
 
</font>
 
<br>
 
       <%@ 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!!!

•

Join Date: Dec 2004

Location: London or Slovakia

Posts: 2,625

Reputation: peter_budo is just really nice

Rep Power: 12

Solved Threads: 311

peter_budo peter_budo is offline

Offline

Code tags enforcer

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

Nov 25th, 2007

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

Learn to see in another's calamity the ills which you should avoid.
Publilius Syrus
(~100 BC)
LJC - London Java Community, JAVAWUG (Java Web User Group), Coding the Architecture

•

Join Date: Dec 2007

Posts: 6

Reputation: tommybobby is an unknown quantity at this point

Rep Power: 0

Solved Threads: 1

tommybobby tommybobby is offline

Offline

Newbie Poster

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

Dec 3rd, 2007

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.