MYSQL and using JSP to INSERT DATA into tables

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!
STKAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cheekycjCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
STKAuthor Commented:
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
STKAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

cheekycjCommented:
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
STKAuthor Commented:
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
cheekycjCommented:
yes ignore col1.  If its an autoincrement then the DB will take care of it.

CJ
0
STKAuthor Commented:
Thanks again!!!!!
0
cheekycjCommented:
Glad I could help and Thanx for the "A"

CJ
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JSP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.