Assign value to HTML textbox from JSP

Question

Hello I am creating a web page to add some information about given product.I need to enter id,name,description and image as information.I need the id to be auto generated.I am using jsp and database as access.I am fetching the count(*)+1 value from database and assigning to my html text box but its showing as null.can i get some help?

Code:

<body>
<%@page import="java.sql.*"%>
<%! String no; %>
<%
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:pd");
ResultSet rs = null;
Statement st = con.createStatement();
String sql = ("select count(*)+1 from products");
st.executeUpdate(sql);
while (rs.next()) { 
no=rs.getString("count(*)+1");
}
rs.close();
st.close();
con.close();
}
catch(Exception e){}
%> 
<Form name='Form1' action="productcode.jsp" method="post">
<table width="1024" border="0">
  <tr>
    <td width="10">&nbsp;</td>
    <td width="126">Add Product: </td>
    <td width="277">&nbsp;</td>
    <td width="583">&nbsp;</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>Product Id:</td>
    <td><label>
      <input type="text" name="id" value="<%= no%>"/>
    </label></td>
    <td>&nbsp;</td> 
.... and so on

JDandChips · Answer 1 · 2012-09-15T09:55:21.003

2

  {..}.executeQuery("Select max(id) from tablename");

This will return the ID with the largest number. Its more efficient than select * from tablename order by id desc limit 1.

However, it looks like your trying to guess/generate an ID of an object which doesn't yet exist in the database. This isn't the best way, and you may find that the ID your generating may be different to that generated by your DB. It could also cause duplication errors if two people are trying to create two new objects at the same time.

I would suggest that you don't provide a product id until the "create product" button has been pressed. Use @@IDENTITY; in your SQL command to set the new ID to your product safely.

edited Sep 15 '12 at 09:55

answered Sep 15 '12 at 09:36

JDandChips

9,780
3
30
46

my id field is in var char format so when i select id it default returns max id . and +1 for good suggestion. – prakash_d22 Sep 15 '12 at 10:00
can I ask why you are using varchar as an identity? It sounds unusual to save the number id as a string, perhaps there is a better way? – JDandChips Sep 15 '12 at 10:03
great, did this help solve your issue? if so, would you make it as an answer? – JDandChips Sep 17 '12 at 09:57

Aravindhan · Accepted Answer · 2012-09-15T09:30:53.117

1

You wrote a query to get the data like this

          String sql = ("select count(*)+1 from products");
          st.executeUpdate(sql);

The statement

            executeUpdate()

only used to do the operations like INSERT,UPDATE

try

             rs=st.executeQuery(sql);

instead of executeUpdate()

edited Sep 15 '12 at 09:30

answered Sep 15 '12 at 09:22

Aravindhan

3,566
7
26
42

thanks the code i working but i want the max value i.e pid which is string and +1 to it means it will act as id for the next product any sugesstions plz? – prakash_d22 Sep 15 '12 at 09:29
select * from tablename order by id desc limit 1 – Aravindhan Sep 15 '12 at 09:32

Assign value to HTML textbox from JSP

2 Answers2

Linked