Inserting Into MySQL database by click of a button in JSP

Question

Following code is to insert data into MySQL database.

But code is not inserting data to the database by clicking send button.

    <%
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);

    if(request.getParameter("send")!=null){
        String scom=request.getParameter("scompany");
        String porderno=request.getParameter("pono");
        String bdate=request.getParameter("date");
        String drug1=request.getParameter("d1");
        String qty1=request.getParameter("q1");


        //getting todaydate
        Date date = new Date();
        Timestamp timestamp = new Timestamp(date.getTime());

        String sql = "INSERT INTO purchaseorderinfo SET Supplier ='"+scom+"', PONo='"+porderno+"', ExpectedDate='"+bdate+"', PODate='"+timestamp+"' ";
        pst=conn.prepareStatement(sql);


        if((scom!=null && scom.length()>0)
            && (porderno!=null && porderno.length()>0)
            && (bdate!=null && bdate.length()>0)
            && (drug1!=null && drug1.length()>0)
            && (qty1!=null && qty1.length()>0)){

            pst.execute();
          %>
          <script language="javascript">
                alert("Send sucess");
          </script>
          <%    
        }
    }
%>

This is my send button;

<div class="col-md-8">
    <form action="adminpg-purchaseorder.jsp" method="post" id="login-form" role="form" style="display: block">
        <div class="form-group">
            <div class="row">
                <div class="col-sm-6 col-sm-offset-3">
                    <input type="submit" name="send" id="submit" tabindex="1" class="form-control btn btn-login" value="Send">
                </div>
            </div>
        </div>    
    </form>
</div>

How to fix this code for inserting data to the database?

first step is to remove it from your jsp (which should be UI only) and put all the actual java code in servlets. — Stultuske
– Stultuske, Commented Aug 25, 2015 at 9:16
In your function you are getting values from request parameter but in your form there is no such field... — Luffy
– Luffy, Commented Aug 25, 2015 at 9:18
yes I have separate forms for those values :(@Luffy how can I fix this without including only one form? — tenten
– tenten, Commented Aug 25, 2015 at 9:19

Harshit · Accepted Answer · 2015-08-25 09:17:14Z

2

Your INSERT query is wrong. It is similar to UPDATE query. Change it

String sql = "INSERT INTO purchaseorderinfo SET Supplier ='"+scom+"', PONo='"+porderno+"', ExpectedDate='"+bdate+"', PODate='"+timestamp+"' ";

to

String sql = "INSERT INTO purchaseorderinfo(Supplier,PONo,ExpectedDate,PODate)
      VALUES('"+scom+"', '"+porderno+"', '"+bdate+"', '"+timestamp+"' ";

answered Aug 25, 2015 at 9:17

Harshit

5,18711 gold badges51 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Naman Gala Over a year ago

This is not the only change that will help OP to solve his/her problem.

tenten Over a year ago

I've change the code like above but still not inserting data to the database @Harshit Shrivastava

Harshit Over a year ago

@tenten, Check even if you are getting data from the form!!

Saurabh Jhunjhunwala · Accepted Answer · 2015-08-25 09:21:08Z

1

To sum all:

you should not have any business logic in JSP
verify if your connection is made to the DB
update your insert statement
close your connection, may be data is getting inserted but no getting commited
make sure auto commit is on

answered Aug 25, 2015 at 9:21

Saurabh Jhunjhunwala

2,9223 gold badges32 silver badges60 bronze badges

Comments

Vaseph · Accepted Answer · 2015-08-25 09:51:22Z

I would suggest you to seperate java code from jsp file. write your insert code in servlet

    public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
            response.setContentType("text/html");
            PrintWriter pw = response.getWriter();
            String CONN_STRING = "jdbc:mysql://localhost/dbName"; 
            Connection connection;
       try{
           String scom=request.getParameter("scompany");
           String porderno=request.getParameter("pono");
           String bdate=request.getParameter("date");
           String drug1=request.getParameter("d1");
           String qty1=request.getParameter("q1");

          Class.forName("com.mysql.jdbc.Driver");
          conn = DriverManager.getConnection(CONN_STRING, USERNAME, PASSWORD);
          PreparedStatement pst = connection.prepareStatement("insert into purchaseorderinfo values(?,?,?,?,?)"); 
          pst.setString(1,scom);
          pst.setString(2,porderno);      
          pst.setString(3,bdate);
          pst.setString(4,drug1);
          pst.setString(5,qty1);
          int i = pst.executeUpdate();

       if(i!=0){
           pw.println("<br>Data has been inserted");
       }
       else{
          pw.println("failed!");
       }

       } catch (Exception e){
         pw.println(e);
     }
   }

Naman Gala · Accepted Answer · 2015-08-25 09:58:31Z

0

Following changes are required

You should include all required fields in single form. Right now request.getParameter("scompany"); will return null.
Update your insert query as suggested by @HarshitShrivastava. Note: You should use preparedStatement to prevent sql injection.
Verify your database connection is getting created properly.
Commit your connection (if auto commit is false on database side) and also close the connection at the end.
Suggestion: Move your code from JSP to Servlet.

answered Aug 25, 2015 at 9:58

Naman Gala

4,6901 gold badge25 silver badges57 bronze badges

Collectives™ on Stack Overflow

Inserting Into MySQL database by click of a button in JSP

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related