Re: how do i insert into data base

From:

Lew <lew@lewscanon.com>

Newsgroups:

comp.lang.java.programmer,comp.lang.java.databases

Date:

Sat, 16 Feb 2008 19:04:42 -0500

Message-ID:

<A4qdncHQB4eH5yranZ2dnUVZ_gmdnZ2d@comcast.com>

Stanimir Stamenkov wrote:

I haven't exactly got what problem you're having. You insert into
an SQL database using an INSERT statement. You would get the info
to insert making the user post back the form he has filled to the
server.

mak wrote:

There is two procedure.
1st a user selects the subject, month, year. And clicks submit to get
the student who has taken that subject. Here in fornt of all the
students roll no. a text box is comes in which user has to put the
total no. of lecture he has attended in that subject.
all this happns in the same form.
2nd after entering the required field(i.e text box.) user clicks on
submit button...Now the textbox data should go in other table along
with students Roll. no.

Now I'm not able to understand how should i insert the value in the
'attendance' table.

I'll attempt an answer, but be aware that I've been wrong before. I'm also
cross-posting (again) in order to unify your threads.

Side note:
Your listings will only be usable if you don't over-indent them. Use spaces,
not TABs, for Usenet indentation, and keep the indentation level to a small
number, two, three or four spaces (pick one). Also, most times they should be
complete, yet short, and illustrative.
<http://www.physci.org/codes/sscce.html>

Now back to your code:

[code]
String str1 = "select roll_no from student where sem_id = (select
sem_id from subject where course_id ='bsc_it' and sub_id =
'"+getsub1+"')";

You've already been warned upthread about plugging in values like 'getsub1'
directly into SQL statements. Google for "SQL injection attack" for more details.

ResultSet rs = stmt.executeQuery(str1);
%>

One way to control your logic better is to have the JSP submit its form to a
servlet, which servlet in turn will call full-fledged Java classes to do its
logic. Search the Sun site for "Model 2 architecture" for some details on this.

Best practices dictate having no raw Java code ("scriptlet") in a JSP. Use
tag libraries, especially the JSP Standard Tag Library (JSTL), and the Model 2
architecture instead.
<http://java.sun.com/javaee/5/docs/tutorial/doc/bnakc.html>
<http://java.sun.com/developer/technicalArticles/javaserverpages/servlets_jsp/index.html>
<http://java.sun.com/blueprints/guidelines/designing_enterprise_applications_2e/web-tier/web-tier5.html>
(follow the links, too)

Why is the 'readonly' attribute there?

   </tr>
<% while(rs.next())
    {
%>
<%
      stu_roll = rs.getString("roll_no");
%>
   <tr bordercolor="#CC3366">
     <td><% out.println(stu_roll); %>

The newline will look like any old whitespace to the HTML renderer. Better is
the tag <c:out> or the old-fashioned '<%= stu_roll %>'. Also, side note, Java
convention is to name variables without underscores, except for compile-time
constants. Use compound word with each word part capitalized except the
first: 'stuRoll'.

</td>
<td> <input type="text" name="att" /> </td>

Is this the value you want to insert?

</tr> 

<%
   }

  con.close();
}
catch(SQLException e)
{
    out.println("Exception in SQL" + e);
}
%>
[/code]

You really need the Model-View-Controller (MVC) architecture, of which "Model
2" is an example, to do this easily.

Create a controller servlet, let's call it 'com.lewscanon.servlet.Controller'
for the sake of argument. In your web.xml, map it to a reasonable URL, let's
say '/Controller' within your application.

   <form action="Controller" method="post">

In the Controller, you pass the input parameters to the business logic, in
this case the parameter named "att".

public class Controller extends HttpServlet
{
  ...
  protected void doPost( HttpServletRequest req, HttpServletResponse rsp )
     throws ServletException, IOException
  {
    String screen = req.getParameter( "screen" );
    String att = req.getParameter( "att" );
    // probably shouldn't hard-code "att"

    MyDao dao = new MyDao( connectionInfo );
    boolean success = dao.insertAtt( att );
    req.setAttribute( "success", Boolean.valueOf( success ));
    // this puts the result in the request so the next JSP can retrieve it

    String nextPg = lookupView( screen, success );
    // returns next JSP to view, depending on previous 'screen'
    // and on the 'success' of the business logic.
    // An enum is probably better than a boolean for 'success'

    RequestDispatcher rd = req.getRequestDispatcher( nextPg );
    rd.forward( req, rsp );
  }
  ...
}

Now your MyDao class's 'insertAtt()' method handles the messy database logic.
    You create or retrieve a connection there, instantiate a PreparedStatement
with an INSERT command, plug in the att value, execute, and Bob's your uncle.

Obviously I've left out a ton of detail, such as error-checking, or how do you
build a map of valid next views based on previous screen and logic outcome, or
how you organize your business logic in a class hierarchy of logic handlers
with a common supertype.

--
Lew