aspose file tools*
The moose likes JSP and the fly likes Problem of insert data into database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » JSP
Bookmark "Problem of insert data into database" Watch "Problem of insert data into database" New topic
Author

Problem of insert data into database

CN
Greenhorn

Joined: Jul 25, 2008
Posts: 6
Below is my form
----------------

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>

<%-- Connect to ms excel --%>
<sql:setDataSource var="mp"
driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbcdbc:mp-list" />


<sql:query var="rs" dataSource="${mp}">
SELECT * FROM [MP$]
</sql:query>


<html>
<head>
<title>New Plan Form</title>
</head>
<body>


<form name="form" action="dispaly.jsp" method="post">
Plan Id: <input name="planId" type="text" size="6" value="${param.planId}"/><br/>

<table>
<tr>
<td>No</td>
<td>Part Id </td>
<td>Mps Qty</td>
</tr>
<c:set var="i" value="1" />
<c:forEach var="Row" items="${rs.rows}">

<tr>
<td><cut value="${i}"/>. </td>
<td><input name="partId" type="text" size="25" value="${Row.PartId}" /></td>
<td><input name="qty" type="text" size="15" value="${Row.MpsQty}" /></td>
</tr>

<c:set var="i" value="${i+1}" />

</c:forEach>

<tr>


<td><input type="submit" name="submit" value=" Next " /></td>
</tr>
</table>
</form>

</body>
</html>
________________________________________________________________________

this is display.jsp
-------------------



<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>


<%-- Connect to ms access --%>
<sql:setDataSource var="connection"
driver="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbcdbc:db1" />


<sql:update dataSource="${connection}">
INSERT INTO Temp values (?,?,null)
<sql:param value="${param.partId}"/>
<sql:param value="${param.qty}"/>
</sql:update>
______________________________________________________________________

first i read 10 rows of partId and qty from excel file into textbox, so that i can pass the parameters to display.jsp to insert to ms access database

the problem is only the first row is inserted to database
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30938
    
158

Chen,
Welcome to JavaRanch!

Please note that it is horrible practice to have SQL in JSPs. Those tags are only meant for prototyping.

<sql:update dataSource="${connection}">
INSERT INTO Temp values (?,?,null)
<sql:param value="${param.partId}"/>
<sql:param value="${param.qty}"/>
</sql:update>

This adds one row. I don't see a loop or anything that indicates inserting more than one row in the code.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
CN
Greenhorn

Joined: Jul 25, 2008
Posts: 6
thanks Jeanne.

Can you tell me how to solve this problem?
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30938
    
158

Originally posted by Chen Nee Lim:
thanks Jeanne.

Can you tell me how to solve this problem?

In a servlet call request.getParameterValues(). Loop through those results, calling a JDBC update statement for each one.

To improve performance, you could use a JDBC batch statement. For questions about JDBC, feel free to post in our JDBC forum after taking a look at the JDBC tutorial.

Also note that you will need to merge the two parameters into one so they get submitted together. A common technique is to format them as partId_quantity and tokenize that on the server.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Problem of insert data into database