Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    12

    Unanswered: add record in SQL

    hi all, i hope to get some help from u guys. i want to add record to DB but it doesn't work, the record cant be added to db. i've a java clas like below:

    public class CustomerDataBean {
    private Connection connection;
    private PreparedStatement addRecord, getRecords;
    private PreparedStatement sqlFind;

    // construct CustomerDataBean object
    public CustomerDataBean() throws Exception
    {
    // load the Cloudscape driver
    Class.forName( "COM.cloudscape.core.RmiJdbcDriver" );

    // connect to the database
    connection = DriverManager.getConnection(
    "jdbc:rmi:jdbc:cloudscape:customers");

    connection.setAutoCommit(false);

    sqlFind = connection.prepareStatement(
    "SELECT email, password " +
    "FROM custinfor " +
    "WHERE email = ? AND password = ? " );

    getRecords =
    connection.prepareStatement(
    "SELECT * FROM custinfor"
    );

    addRecord = connection.prepareStatement(
    "INSERT INTO custinfor (email, firstName, lastName, password, verify, address, phone, creditCard ) " +
    "VALUES ( ?, ?, ?, ?, ?, ?, ?, ? )" );

    }

    public void addCustomer( CustomerBean customer ) throws SQLException
    {
    addRecord.setString( 1, customer.getEmail() );
    addRecord.setString( 2, customer.getFirstName() );
    addRecord.setString( 3, customer.getLastName() );
    addRecord.setString( 4, customer.getPwd() );
    addRecord.setString( 5, customer.getVPwd() );
    addRecord.setString( 6, customer.getAdd() );
    addRecord.setString( 7, customer.getPhone() );
    addRecord.setInt( 8, customer.getCC() );

    addRecord.executeUpdate();
    }

    i called addCustomer method at JSP.
    thanks.

  2. #2
    Join Date
    Oct 2003
    Posts
    7

    Re: add record in SQL

    Humzmzzzzz
    I think you did not include the excuteUpdate() in ur insert statement. Try again.
    Could be the driver problem
    By the way wat DB are you using

  3. #3
    Join Date
    Sep 2003
    Posts
    12
    my find method is like tis:
    i call it in jsp after user fill in and click submit.

    public boolean findUser(String email, String password){
    try{

    sqlFind.setString(1, email);
    sqlFind.setString(2, password);
    ResultSet resultSet = sqlFind.executeQuery();
    if(!resultSet.next())
    return false;
    else
    return true;


    }
    catch (SQLException sqlException){
    return false;
    }
    }

  4. #4
    Join Date
    May 2003
    Location
    Hong Kong
    Posts
    25
    Have you called Connection.commit() ?

  5. #5
    Join Date
    Sep 2003
    Posts
    12
    yes i called connection.commit() and i try to display out all the records, i found out that the new record tat i just added is there, but the password, address field is null. how come the things tat i insert become null value?

  6. #6
    Join Date
    Oct 2003
    Posts
    7

    Talking

    Okie! U might wan to try hard code insertion into the dbase or do a print to screen to see all the values from the gui are captured. Then u will know if the dbase connection is having problem or the table field setting are not correct. Need more details from you
    Originally posted by jess88
    yes i called connection.commit() and i try to display out all the records, i found out that the new record tat i just added is there, but the password, address field is null. how come the things tat i insert become null value?

  7. #7
    Join Date
    Sep 2003
    Posts
    12
    hi, this is the JSP page where i input all values and click submit, it will call the addCustomer from CUstomerDataBean class, i already post it above. The email, firat name , last name and phone field can be inserted into the database correctly, only the password and address field is null, and the CC field is 0.

    <?xml version = "1.0"?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
    <!-- register.jsp -->

    <%-- page settings --%>
    <%@ page errorPage = "ErrorPage.jsp" %>

    <%-- beans used in this JSP --%>
    <jsp:useBean id = "customer" scope = "page"
    class = "com.deitel.advjhtp1.products.CustomerBean" />
    <jsp:useBean id = "customerData" scope = "request"
    class = "com.deitel.advjhtp1.products.CustomerDataBean " />

    <html xmlns = "http://www.w3.org/1999/xhtml">

    <head>
    <title>Register As New Member</title>

    <link rel = "stylesheet" href = "styles.css"
    type = "text/css" />

    </style>
    </head>

    <body>
    <jsp:setProperty name = "customer" property = "*" />

    <% // start scriptlet
    if ( customer.getEmail() == null ||
    customer.getFirstName() == null ||
    customer.getLastName() == null ){

    %> <%-- end scriptlet to insert fixed template data --%>

    <form method = "post" action = "register.jsp">
    <p class = "bigFont">Registration Form</p>
    <p class = "bold">Please enter your first name, last name and email
    address to register as a new member.</p>

    <table align="center">

    <tr>
    <td>Email</td>

    <td>
    <input type = "text" name = "email" />
    </td>
    </tr>


    <tr>
    <td>First name</td>

    <td>
    <input type = "text" name = "firstName" />
    </td>
    </tr>

    <tr>
    <td>Last name</td>

    <td>
    <input type = "text" name = "lastName" />
    </td>
    </tr>

    <tr>
    <td>Password</td>

    <td>
    <input type = "password" name = "password" />
    </td>
    </tr>

    <tr>
    <td>Confirm Password</td>

    <td>
    <input type = "password" name = "verify" />
    </td>
    </tr>

    <tr>
    <td>Address</td>

    <td>
    <input type = "text" name = "address" />
    </td>
    </tr>

    <tr>
    <td>Phone Number</td>

    <td>
    <input type = "text" name = "phone" />
    </td>
    </tr>

    <tr>
    <td>Credit Card Number</td>

    <td>
    <input type = "text" name = "creditCard" />
    </td>
    </tr>

    <tr align = "center">
    <td colspan = "2">
    <input type = "submit"
    value = "Register" />
    </td>
    </tr>
    </table>
    </form>
    <% // continue scriptlet

    } // end if
    else {
    System.out.println(customer.getPwd());
    customerData.addCustomer( customer );
    %>
    <%-- end scriptlet to insert jsp:forward action --%>

    <%-- forward to display customer database contents --%>
    <jsp:forward page = "CustView.jsp" />

    <% // continue scriptlet

    } // end else

    %> <%-- end scriptlet --%>
    </body>

    </html>

  8. #8
    Join Date
    Oct 2003
    Posts
    7
    Okie ur input page look fine to me, what u can do is upon submit, do a print of the variables u have received in register.jsp to check if all the variable is correctly received. Then maybe u can might wan tell me what db you are using. Might be the driver u are using is not compatible

    Here my example for connecting to mysql
    >>>>>>>>>>>>>>>
    String sqlStatement = "Insert blar blar blar into table values";
    Connection DBConn = null;
    Statement stmt =null;
    //DB setting
    try{

    Class.forName("com.mysql.jdbc.Driver").newInstance ();
    DBConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbase?user=XXXX&password=XXXX");
    stmt = DBConn.createStatement();

    stmt.excute.Update();

    }//try
    catch (SQLException ex) {
    System.out.println("error " + ex.getMessage());

    }//catch
    ///////////////////////////////////////
    DBConn.close();

    might give it a try

  9. #9
    Join Date
    Sep 2003
    Posts
    12
    yes i did a print screen at register.jsp but the password, address and cc value is null/0. some fields eg: email, firstname, lastname and phone no can be saved correctly. i wonder why only certain fields will turn to null. inside my db is like this:
    connect 'jdbc:rmi://localhost:1099/jdbc:cloudscape:customers;create=true'
    ;

    drop table custProducts
    ;
    drop table custinfor
    ;
    drop table products
    ;

    create table custinfor (
    email varchar (30) NOT NULL,
    firstName varchar (20) NOT NULL,
    lastName varchar (30) NOT NULL,
    password varchar (20),
    verify varchar (20),
    address varchar (100),
    phone varchar (20) NOT NULL,
    creditCard int NOT NULL,
    constraint pk_custinfor primary key (email)
    )
    ;

    create table products (
    productID varchar (20) NOT NULL,
    productName varchar (100) NOT NULL,
    description varchar (100) NOT NULL,
    price real NOT NULL,
    imageFile varchar (50) NOT NULL,
    constraint pk_products primary key (productID)
    )
    ;

    create table custProducts (
    email varchar (30),
    productID varchar (20),
    quantity int,
    total real,
    constraint fk_custProducts_1 foreign key (email)
    references custinfor (email),
    constraint fk_custProducts_2 foreign key (productID)
    references products (productID)
    )
    ;

    and the coding tat i call the db is posted at the first msg.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •