Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MYSQL and using JSP to INSERT DATA into tables

Posted on 2003-03-25
8
Medium Priority
?
770 Views
Last Modified: 2011-04-14
I"m learning how to insert data from a JSP page into different tables. I have about 15 fields to add but have know idea how to do it. I have the connection to the database done but don't know where to start to add the data to the table. Could someone post some sample code of how to insert into tables and an explaination of the code...i thank-you in advance!
0
Comment
Question by:STK
  • 4
  • 4
8 Comments
 
LVL 19

Accepted Solution

by:
cheekycj earned 140 total points
ID: 8205453
a good example is here:

http://www.devdaily.com/java/edu/pj/jdbc/jdbc0002/index.shtml

using previous code it would be like this:

<!DOCTYPE html PUBLIC "-//IETF//DTD HTML 2.0//EN">
<html>
   <head>
      <%@ page import="java.io.*" import="java.lang.*" import="java.sql.*" %>
      <title>
         TEST
      </title>
   </head>
   <body>
<%
    String field1, field2, field3;
    Connection dbconn;
    PreparedStatement insertStmt;
    field1 = request.getParameter("field1");
    field2 = request.getParameter("field2");
    field3 = request.getParameter("field3");

    if (field1 != null && field2 != null && field3 != null) {

      try {
        Class.forName("org.gjt.mm.mysql.Driver").newInstance();

        try {

          dbconn = DriverManager.getConnection(
                       "jdbc:mysql://localhost/nutrition", "saundersk",
                       "kevin");
          out.println("2");
          insertStmt = dbconn.prepareStatement(
                    "insert into mytable (col1, col2, col3) values (?, ?, ?) ");
          insertStmt.setString(1, field1);
          insertStmt.setString(2, field2);
          insertStmt.setString(3, field3);
          insertStmt.executeUpdate();
        } catch (SQLException s) {
          out.println("SQL Error " + s.getMessage());
        }
      } catch (Exception e) {
        out.println("General error:" + e.getMessage());
      }
      finally {
        if (insertStmt != null) insertStmt.close();
        if (dbconn != null) dbconn.close();
      }
    } else {
      out.println("one of the fields was empty!");
    }
%>
   </body>
</html>

CJ
0
 

Author Comment

by:STK
ID: 8205799
if one of the field in the table is auto increment how do i specific that i need the next number in the table?
0
 

Author Comment

by:STK
ID: 8205813
Also if i need to enter into two tables do i just need to just the other

 "insert into mytable (col1, col2, col3) values (?, ?, ?) ");
         insertStmt.setString(1, field1);
         insertStmt.setString(2, field2);
         insertStmt.setString(3, field3);
         insertStmt.executeUpdate();

to the code above...meaning there will be two!
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Expert Comment

by:cheekycj
ID: 8205832
if one of the fields is an autoincrement dont insert into that field.  The DB will probably throw an error.  the DB itself takes care of sequenced numbers.

if you need to insert into to two tables use two queries.

CJ
0
 

Author Comment

by:STK
ID: 8206394
so here is my code...all the fields are correct but i get an SQL error syntax error or acces violation message from server You have an error in your SQL syntax near values

The first value in the table is a autoincrement number do i need to puch the first one of col 2 and leave col 1 up to the database to enter?

<%
   String firstname, zip, weight, height, lastname, add1, add2, city, state, country,
   username, pass1, pass2, email;
   
   Connection dbconn = null;
   PreparedStatement insertStmt = null;
   zip = request.getParameter("zip");
   weight = request.getParameter("weight");
   height = request.getParameter("height");
   firstname = request.getParameter("firstname");
   lastname = request.getParameter("lastname");
   add1 = request.getParameter("add1");
   add2 = request.getParameter("add2");
   city = request.getParameter("city");
   state = request.getParameter("state");
   country = request.getParameter("country");
   username = request.getParameter("username");
   pass1 = request.getParameter("pass1");
   pass2 = request.getParameter("pass2");
   email = request.getParameter("email");
   
   
   out.println(zip);
   out.println(weight);
   out.println(height);
   out.println(firstname);
   out.println(lastname);
   out.println(add1);
   out.println(add2);
   out.println(city);
   out.println(state);
   out.println(country);
   out.println(username);
   out.println(pass1);
   out.println(pass2);
   out.println(email);
 
   
   
   
   
   
   
   if (zip != null && weight != null && height != null && firstname != null && lastname != null
   && add1 != null && add2 != null && city != null && state != null && country != null
   && username != null && pass1 != null && pass2 != null && email != null)
   {

     try {
       Class.forName("org.gjt.mm.mysql.Driver").newInstance();

       try {

         dbconn = DriverManager.getConnection("jdbc:mysql://localhost/nutrition", "saundersk","kevin");
         
         insertStmt = dbconn.prepareStatement("insert into member_info (col1, col2, col3, col4, col5, col6, col7, col8, col9,) values (?, ?, ?, ?, ?, ?, ?, ?, ?) ");
         insertStmt.setString(1, firstname);
         insertStmt.setString(2, lastname);
         insertStmt.setString(3, add1);
         insertStmt.setString(4, add2);
         insertStmt.setString(5, city);
         insertStmt.setString(6, state);
         insertStmt.setString(7, zip);
         insertStmt.setString(8, country);
         insertStmt.setString(9, height);
         insertStmt.executeUpdate();
       } catch (SQLException s) {
         out.println("SQL Error " + s.getMessage());
       }
     } catch (Exception e) {
       out.println("General error:" + e.getMessage());
     }
     finally {
       if (insertStmt != null) insertStmt.close();
       if (dbconn != null) dbconn.close();
     }
   } else {
     out.println("one of the fields was empty!");
   }
%>
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8206427
yes ignore col1.  If its an autoincrement then the DB will take care of it.

CJ
0
 

Author Comment

by:STK
ID: 8206601
Thanks again!!!!!
0
 
LVL 19

Expert Comment

by:cheekycj
ID: 8208067
Glad I could help and Thanx for the "A"

CJ
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Often, the users face difficulty in accessing Outlook 2016 PST files on Windows 10 computer. One of the reasons behind it is the improper functioning of MS Outlook when the user tries to open it. MS Outlook suddenly stops working, or it will not op…
The article explains the process to deploy a Self-Service password reset portal I developed a few years ago. Hopefully, it will prove useful to someone.  Any comments, bug reports etc. are welcome...
Loops Section Overview
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question