Solved

why it's contain more data?? ken, object, TomYates??

Posted on 2003-12-02
48
454 Views
Last Modified: 2010-04-01
i have program like this.

//get information from user entry screen.
// contain 15 input text box with the same name of "warranty"
String [] warranty = request.getParameterValues("warranty");
string [] techcode = request.getParameterValues("techcode");
.
.
    Connection conn=null;
    ResultSet rs=null;
    SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    conn = DriverManager.getConnection("jdbc:odbc:myDSN");
    PreparedStatement ps =null;
    ps = conn.prepareStatement( "Insert into Service values(?,?,?,?,?,?);" );

    out.println("Warranty :" + warranty.length);

    for (int j=0;j<warranty.length;j++) {
        ps.setString( 1, techcode);
        ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
        ps.setString(3, warranty[j]);
        ps.setString(4, filter[j]);
        ps.setInt(5, Integer.parseInt(tech_comm[j]));
        ps.setInt(6, Integer.parseInt(dealer_comm[j]));
        ps.executeQuery();
 }

*******
and i tell you what happend. Although i only enter 3 rows data (including techcode, date, warranty, filter, tech_comm, dealer_comm),  but after i hint the enter, it's going to print out this :
Warranty : 15

And this will cause me error since the rest of the data (started from 4 ) will contain null value.

Thus, i wonder what is happending.. and this cause me error...

Aren't it's (warranty.length) should only contain how many row i key in?

please help...

0
Comment
Question by:ipoh1977
  • 18
  • 10
  • 8
  • +3
48 Comments
 
LVL 15

Expert Comment

by:jimmack
ID: 9857491
If I understand you correctly, then you need to test whether the text fields are empty.  Change:

    for (int j=0;j<warranty.length;j++) {
        ps.setString( 1, techcode);

to

    for (int j=0;j<warranty.length;j++) {
        if ((warranty[j] != null) && (warranty[j].length() > 0))
        {
            ps.setString( 1, techcode);
            .
            .
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9857499
Sorry.  A bit more explanation:

If your form contains 15 warranty fields, then all these values will be sent in the request (whether they contain data or not).

You might be able to leave out the test for null, but better to be safe than sorry ;-)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9857533
yeah, Jim is right :-)

tImyates ;-)
0
 

Author Comment

by:ipoh1977
ID: 9857601
ok. let me test it out.

tImyates, so sorry about that....
:-) he...
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 9857605
again......... got late...........................(throwing mouse around..:-)) why everybody gets easy ones to answer!!!!

( no pun intended)
0
 

Author Comment

by:ipoh1977
ID: 9857610
but surprise it's contain all the 15 row of data... previously in my others code, i no need to check either it's contain null or not. The array will only contain "value" data...

anyway, no harm add the code in...
0
 

Author Comment

by:ipoh1977
ID: 9857742
ai.... error again... i think this time is not as easy as above... i have to post the whole code.. sorry to confuse you...

*****
<%
boolean pass=false;
String techcode=request.getParameter("techcode");
String [] warranty = request.getParameterValues("warranty");
String [] filter = request.getParameterValues("filter");
String [] date = request.getParameterValues("date");
String [] dealer= request.getParameterValues("dealer");
String [] tech_comm=request.getParameterValues("tech_comm");
String [] dealer_comm=request.getParameterValues("dealer_comm");

for (int i=0; i<warranty.length;i++) {
    if (filter[i]==null || date[i]==null || tech_comm[i]==null || dealer_comm==null ) {
        pass=false;
        break;
    }
%>  
<%
SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
pass=true;
}

System.out.println("Pass"+pass);


if (pass) {
int num=0;
try {
    Connection conn=null;
    ResultSet rs=null;
    SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    conn = DriverManager.getConnection("jdbc:odbc:myDSN");
    PreparedStatement ps =null;
    ps = conn.prepareStatement( "Insert into Service values(?,?,?,?,?,?);" );
   
    for (int j=0;j<warranty.length;j++) {
       if ((warranty[j] != null) && (warranty[j].length() > 0)) {
        ps.setString( 1, techcode);
        ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
        ps.setString(3, warranty[j]);
        ps.setString(4, filter[j]);
        ps.setInt(5, Integer.parseInt(tech_comm[j]));
        ps.setInt(6, Integer.parseInt(dealer_comm[j]));
        ps.executeQuery();
        System.out.println(techcode+":"+warranty[j]+":"+filter[j]+":"+tech_comm[j]+":"+dealer_comm[j]);
        out.println("Warranty " + warranty[j]);
    }
    }
    System.out.println("NUM"+num);
} catch (Exception e) {
    e.toString();
    e.printStackTrace();
}
%>

******

I have this exception..
Passtrue
java.sql.SQLException: General error
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:89)
        at org.apache.jsp.tech_0005fconfirm$jsp._jspService(tech_0005fconfirm$jsp.java:122)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)

it's pointing to this line of code (tech_0005fconfirm$jsp.java:122),
>>  ps.executeQuery();

*******
And before that i have make necessary checking...
1. All the data must be enter complete as one. (mean can not let date blanll or warranty blank etc..)
2. Parse the date and int so that it's match the database type.

what would be the wrong one?? any ideas? "General Errors".. ai... lausy exception...

Kludeep, challenging??
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9857819
You shouldn't need the ";" on the end of your prepared statement (AFAIK).

I have to go out for a while now.  Hopefully someone else may be able to help.

Possibly Tom ;-p

:-)
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9857884
*kicks Jim* ;-P

He's right though:

>  ps = conn.prepareStatement( "Insert into Service values(?,?,?,?,?,?);" );

should be:

ps = conn.prepareStatement( "Insert into Service values(?,?,?,?,?,?)" );

(no semi-colon) :-)
0
 

Author Comment

by:ipoh1977
ID: 9857998
jimmack, i can compile the whole code. No error.. and what is AFAIK??
0
 

Author Comment

by:ipoh1977
ID: 9858047
changed like what you suggested... same ...

 ps = conn.prepareStatement("Insert into Service values(?,?,?,?,?,?)");
   
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9858052
The code will compile with the ";" in the SQL string, but I don't think it will run...

Have you tried it without the ";"?

> and what is AFAIK

As Far As I Know :-)

0
 
LVL 35

Expert Comment

by:TimYates
ID: 9858066
Same exception?  odd...

what are the fieldnames in your table?
0
 

Author Comment

by:ipoh1977
ID: 9858068
Passtrue
java.sql.SQLException: General error
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:89)
        at org.apache.jsp.tech_0005fconfirm$jsp._jspService(tech_0005fconfirm$jsp.java:122)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.netbeans.modules.
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9858084
Ahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh!!

    for (int j=0;j<warranty.length;j++) {
       if ((warranty[j] != null) && (warranty[j].length() > 0)) {
        ps.setString( 1, techcode);
        ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
        ps.setString(3, warranty[j]);
        ps.setString(4, filter[j]);
        ps.setInt(5, Integer.parseInt(tech_comm[j]));
        ps.setInt(6, Integer.parseInt(dealer_comm[j]));
        ps.executeQuery();
        System.out.println(techcode+":"+warranty[j]+":"+filter[j]+":"+tech_comm[j]+":"+dealer_comm[j]);
        out.println("Warranty " + warranty[j]);
    }

SHOULD BE:

    for (int j=0;j<warranty.length;j++) {
       if ((warranty[j] != null) && (warranty[j].length() > 0)) {
        ps.setString( 1, techcode);
        ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
        ps.setString(3, warranty[j]);
        ps.setString(4, filter[j]);
        ps.setInt(5, Integer.parseInt(tech_comm[j]));
        ps.setInt(6, Integer.parseInt(dealer_comm[j]));
        ps.executeUpdate();
        System.out.println(techcode+":"+warranty[j]+":"+filter[j]+":"+tech_comm[j]+":"+dealer_comm[j]);
        out.println("Warranty " + warranty[j]);
    }
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9858087
executeUpdate() NOT executeQuery() :-)
0
 

Author Comment

by:ipoh1977
ID: 9858169
this is the schema:

Table name:Service
Columns :  TechName (text)
                  ServiceDate (Date/Time)
                  Warranty (Text)
                  Filter (Text)
                  TCom (Num - Long Integer)
                  DCom (Num - Long Integer)

this is the print out i added in before the ps.execute();

        ps.setString( 1, techcode);
        ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
        ps.setString(3, warranty[j]);
        ps.setString(4, filter[j]);
        ps.setInt(5, Integer.parseInt(tech_comm[j]));
        ps.setInt(6, Integer.parseInt(dealer_comm[j]));
  >> System.out.println(techcode+":"+warranty[j]+":"date[j]+":"+filter[j]+":"+tech_comm[j]+":"+dealer_comm[j]);
        ps.executeQuery();


and this is the error:
Passtrue
FKS:KELVIN:02/02/2003:A:12:12
java.sql.SQLException: General error
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:89)
        at org.apache.jsp.tech_0005fconfirm$jsp._jspService(tech_0005fconfirm$jsp.java:123)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
 
In fact i have entered 2 rows of data... but can't see the second one...
0
 

Author Comment

by:ipoh1977
ID: 9858199
Passtrue
FKS:KELVIN:02/02/2003:A:12:12
java.sql.SQLException: General error
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:136)
        at org.apache.jsp.tech_0005fconfirm$jsp._jspService(tech_0005fconfirm$jsp.java:123)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
        at javax.se


*******

for (int j=0;j<warranty.length;j++) {
       if ((warranty[j] != null) && (warranty[j].length() > 0)) {
        ps.setString( 1, techcode);
        ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
        ps.setString(3, warranty[j]);
        ps.setString(4, filter[j]);
        ps.setInt(5, Integer.parseInt(tech_comm[j]));
        ps.setInt(6, Integer.parseInt(dealer_comm[j]));
        System.out.println(techcode+":"+warranty[j]+":"+date[j]+":"+filter[j]+":"+tech_comm[j]+":"+dealer_comm[j]);
        ps.executeUpdate();
        out.println("Warranty " + warranty[j]);
    }
0
 

Author Comment

by:ipoh1977
ID: 9858206
Timyates... come back to you tomorroow...

thank for your time...
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9858234
Right...here we go...  the definitive way to do it...  this *will* work ;-)

---------------------

Connection conn=null;
try
{
    SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    conn = DriverManager.getConnection("jdbc:odbc:myDSN");
   
    for (int j=0;j<warranty.length;j++)
    {
        if ((warranty[j] != null) && (warranty[j].length() > 0))
        {
            PreparedStatement ps =null;
            try
            {
                ps = conn.prepareStatement( "Insert into Service values(?,?,?,?,?,?);" );
                ps.setString( 1, techcode);
                ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
                ps.setString(3, warranty[j]);
                ps.setString(4, filter[j]);
                ps.setInt(5, Integer.parseInt(tech_comm[j]));
                ps.setInt(6, Integer.parseInt(dealer_comm[j]));
                if( ps.executeUpdate() != 1 )
                {
                    out.println( "Insert failed for " + warranty[ j ] ) ;
                }
                else
                {
                    out.println("Warranty " + warranty[j]);
                }
                System.out.println(techcode+":"+warranty[j]+":"+filter[j]+":"+tech_comm[j]+":"+dealer_comm[j]);
            }
            finally
            {
                try { if( ps != null ) ps.close() ; } catch( Exception ex ) { }
            }
        }
    }
    System.out.println("NUM"+num);
}
catch( Exception e )
{
    e.printStackTrace();
}
finally
{
  try { if( conn != null ) conn.close() ; } catch( Exception ex ) { }
}
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9858239
You have to close your PreparedStatement between each insert...you cannot reuse it :-)

Hope that's ok!

(I havent compiled it, but it should work) :-)
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9858367
>> this *will* work ;-)
>> (I havent compiled it, but it should work) :-)

What was the conclusion on the ";" at the end of the statement (which I notice you've kept in) ?
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9858444
seems to work :-)

I guess it just gets ignored by the driver...
0
 
LVL 35

Expert Comment

by:TimYates
ID: 9858453
> (which I notice you've kept in)

Hehehe...I forgot to remove it ;-)

I think the problem here was reusing the PreparedStatement without first closing and reopening a new one...

maybe......
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 15

Expert Comment

by:jimmack
ID: 9858456
OK :-)
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9860648
didn't have time to read everything and try but here are my 2 cents:

>>     ps = conn.prepareStatement( "Insert into Service values(?,?,?,?,?,?);" );
the sql statement is not correct. remove the ';' in the end. that will make access to think that's 2 statement and try to execute ';' after instert.

>>         ps.executeQuery();
this shoudl be ps.executeUpdate() instead.

>> Aren't it's (warranty.length) should only contain how many row i key in?
warranty.length contains number of warrenty form fields in you form, the only exception to this is checkbox form field, which only returns those checked.

>> I think the problem here was reusing the PreparedStatement without first closing and reopening a new one...

PreparedStatement is designed to be executed multiple times without reopening. This isn't a problem.

If my comment contains answers already given by others, then ignore that part and accept others.
0
 
LVL 92

Expert Comment

by:objects
ID: 9862725
I better have a crack now that everyone else has done the hard work :)

   // Create your statement before the loop and reuse it for each iteration

   PreparedStaement ps = conn.prepareStatement( "Insert into Service values(?,?,?,?,?,?)" );

   for (int j=0;j<warranty.length;j++)
    {
        if ((warranty[j] != null) && (warranty[j].trim().length() > 0))
        {
            try
            {
                ps.setString( 1, techcode);
                ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
                ps.setString(3, warranty[j]);
                ps.setString(4, filter[j]);
                ps.setInt(5, Integer.parseInt(tech_comm[j]));
                ps.setInt(6, Integer.parseInt(dealer_comm[j]));
                if( ps.executeUpdate() != 1 )
                {
                    out.println( "Insert failed for " + warranty[ j ] ) ;
                }
                else
                {
                    out.println("Warranty " + warranty[j]);
                }
                System.out.println(techcode+":"+warranty[j]+":"+filter[j]+":"+tech_comm[j]+":"+dealer_comm[j]);
            }
        }
    }
0
 
LVL 15

Expert Comment

by:jimmack
ID: 9862758
What's the catch?

Or should I say *Where*'s the catch ;-)
0
 
LVL 92

Expert Comment

by:objects
ID: 9862994
you should probably also explicitly state the column names in your insert so there is no mismatching occurring.
0
 

Author Comment

by:ipoh1977
ID: 9863094
OK. guys.. i am back. Let me try one by one suggested by all the "Big name" here... he..
get back you one by one
0
 

Author Comment

by:ipoh1977
ID: 9863298
First try, TimYates suggestion on  Date: 12/02/2003 06:05AM PST --- same error... this time event worst.. no System.out.println() come out..but its pointing to the   if( ps.executeUpdate() != 1 ) (tech_0005fconfirm$jsp.java:163) adn trust me, i hae remove the ";" in the preparedstatement. :-)

Passtrue
java.sql.SQLException: General error
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:136)
        at org.apache.jsp.tech_0005fconfirm$jsp._jspService(tech_0005fconfirm$jsp.java:163)

>>you should probably also explicitly state the column names in your insert
object, is something like this?
PreparedStaement ps = conn.prepareStatement( "Insert into Service values(?,?,?,?,?,?)" );
or
PreparedStatement ps = conn.prepareStatement("Insert into Service (TechName, ServiceDate, Warranty, Filter, TCom,DCom) values (?,?,?,?,?,?)");

Hm... doesn't work also.... this is the error.

Passtrue
java.sql.SQLException: General error
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:136)
        at org.apache.jsp.tech_0005fconfirm$jsp._jspService(tech_0005fconfirm$jsp.java:164)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at org.
and it's pointing to the same place of the code....

:-( I am crying already....
0
 

Author Comment

by:ipoh1977
ID: 9863348
Guys.. let's start from the very very basic one... since it's GENERAL ERROR...

why no one give comments on the database shcema that i have created?? i have a strong feeling on this...

have a look again.

Table name:Service
Columns :  TechName (text)
                 ServiceDate (Date/Time)
                 Warranty (Text)
                 Filter (Text)
                  TCom (Num - Long Integer)
                 DCom (Num - Long Integer)

The catch is the Num-long integer.. i have no idea what is this long integer means.. and what i've planned to insert is 12.22 is possible. But for the testing that i have done, i only enter integer which is 12 ONLY.

Should i change ?? i mean the data type?? because it's look the code is all right...
0
 

Author Comment

by:ipoh1977
ID: 9863448
this is my code :

try
{
   SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
   Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   conn = DriverManager.getConnection("jdbc:odbc:myDSN");
 
    for (int j=0;j<warranty.length;j++)
    {
       if ((warranty[j] != null) && (warranty[j].length() > 0))
        {
           PreparedStatement ps =null;
           try
           {
               ps = conn.prepareStatement("Insert into Service (TechName, ServiceDate, Warranty, Filter, TCom, DCom) values (?,?,?,?,?,?)");
               ps.setString( 1, techcode);
               ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
               ps.setString(3, warranty[j]);
               ps.setString(4, filter[j]);
               ps.setInt(5, Integer.parseInt(tech_comm[j]));
               ps.setInt(6, Integer.parseInt(dealer_comm[j]));
               if( ps.executeUpdate() != 1 ){
                   System.out.println( "Insert failed for " + warranty[ j ] ) ;
               } else {
                   out.println("Warranty " + warranty[j]);
               }
               System.out.println(techcode+":"+warranty[j]+":"+filter[j]+":"+tech_comm[j]+":"+dealer_comm[j]);
           }
           finally
           {
               try { if( ps != null ) ps.close() ; } catch( Exception ex ) { ex.toString(); }
           }
       }
   }
   System.out.println("NUM"+num);
}
catch( Exception e ) {    e.printStackTrace(); }
finally
{
 try { if( conn != null ) conn.close() ; } catch( Exception ex ) { }
}

and this is the error




Passtrue
java.sql.SQLException: General error
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6987)
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
        at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(JdbcOdbcPreparedStatement.java:136)
        at org.apache.jsp.tech_0005fconfirm$jsp._jspService(tech_0005fconfirm$jsp.java:163)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9863943
can you also check the lenght of text column of your table is big enough to hold your data?
0
 
LVL 92

Expert Comment

by:objects
ID: 9864059
you're unnecessarily recreating and closing your statement, though this is not related to your error.

try to do the insert directly into the database (without Java) and see if that gives you a better indictation of what the problem is.
0
 
LVL 14

Accepted Solution

by:
kennethxu earned 130 total points
ID: 9864249
Anyway, tested your scenario on my pc, works fine. winxp, msaccess 2000, tomcat:

=========msaccess.jsp==========
<%@ page import="java.util.*, java.text.*, java.sql.*" %>
<%
String techcode=request.getParameter("techcode");
String [] warranty = request.getParameterValues("warranty");
String [] filter = request.getParameterValues("filter");
String [] date = request.getParameterValues("date");
String [] dealer= request.getParameterValues("dealer");
String [] tech_comm=request.getParameterValues("tech_comm");
String [] dealer_comm=request.getParameterValues("dealer_comm");

Connection conn = null;
PreparedStatement ps = null;
SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

try {
   conn = DriverManager.getConnection("jdbc:odbc:myDSN");
   ps = conn.prepareStatement("Insert into Service (TechName, ServiceDate, Warranty, Filter, TCom, DCom) values (?,?,?,?,?,?)");
 
    for (int j=0;j<warranty.length;j++)
    {
       if(warranty[j].length() > 0) {
         out.println(techcode+",");
         out.println(warranty[j]+",");
         out.println(filter[j]+",");
         out.println(tech_comm[j]+",");
         out.println(dealer_comm[j]+"<br>");
         ps.setString( 1, techcode);
         ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
         ps.setString(3, warranty[j]);
         ps.setString(4, filter[j]);
         ps.setInt(5, Integer.parseInt(tech_comm[j]));
         ps.setInt(6, Integer.parseInt(dealer_comm[j]));
         ps.executeUpdate();
       }
    }
} catch( Exception e ) {
   out.println( "<pre>" );
   e.printStackTrace( new java.io.PrintWriter( out ) );
   out.println( "</pre>" );
} finally {
    try { if( ps != null ) ps.close() ; } catch( Exception ex ) { }
    try { if( conn != null ) conn.close() ; } catch( Exception ex ) { }
}
%>

===========grid.html==============
<form action=msaccess.jsp method=post>
<input type=text name=techcode value=testcode><p>

Warranty:<input type=text name=warranty value=10years>
Filter:<input type=text name=filter value=filterA>
Date:<input type=text name=date value=11/12/2003>
TechCom:<input type=text name=tech_comm value=12>
DealDom:<input type=text name=dealer_comm value=34><p>

Warranty:<input type=text name=warranty value=5years>
Filter:<input type=text name=filter value=filterB>
Date:<input type=text name=date value=12/12/2004>
TechCom:<input type=text name=tech_comm value=56>
DealDom:<input type=text name=dealer_comm value=78><p>

Warranty:<input type=text name=warranty>
Filter:<input type=text name=filter>
Date:<input type=text name=date>
TechCom:<input type=text name=tech_comm>
DealDom:<input type=text name=dealer_comm><p>
<input type=submit>
</form>

============result=============
testcode, 10years, filterA, 12, 34
testcode, 5years, filterB, 56, 78
0
 
LVL 92

Expert Comment

by:objects
ID: 9864275
And were you getting this error way back when this question began??
0
 

Author Comment

by:ipoh1977
ID: 9864504
ken, TQ for that. (Simulating the situation) for me.

I have check the Field Size, it's correct.
TechName - Filed Size=25
ServiceDate = default, no specific
Warranty = Filed Size=8
Filter = 2
TCom-Long integer- default
DCom - Long Integer-default

let me get you the whole page after i have changed like your page does...


Object,
>>And were you getting this error way back when this question began??
what does this mean?
0
 
LVL 92

Expert Comment

by:objects
ID: 9864530
> what does this mean?

Before you asked this question, were you getting this error "java.sql.SQLException: General error" ?
0
 

Author Comment

by:ipoh1977
ID: 9864626
hm.. good idea.. hard code the value... let me see this:

ha... i've sovled it. Thank you for your brainstorming..... :-)
The answer for this is....

this is my original input page

input.jsp
======
Techcode : <input type="text" name="techcode" />

for (int i=0; i<10; i++) {
...
..
..}

and for my updateid.jsp

String [] techcode = request.getParameterValues("techcode");


everything look fine.. but for my Database, I HAVE SET THE TECHCODE AS A PRIMARY KEY!!!
and now it's going to insert a duplicate value while the techcode is still the same....

:-)

general error... i think thats mean by the databse error.. not the coding...

THANK YOU VERY ONe.. i am so happy for it........
0
 

Author Comment

by:ipoh1977
ID: 9864634
By the way, the TCom and Dcom datatype, if it's going be long integer, can i insert 12.20 or 0.35?

or you think i should use Double or Integer??
0
 
LVL 92

Expert Comment

by:objects
ID: 9864663
use Double
0
 

Author Comment

by:ipoh1977
ID: 9864807
friend... got errors... but nothing to shown... what had happend to this code?

try {
} catch (Exception e) { e.printStackTrace(); }

is in the code

Connection conn = null;
PreparedStatement ps = null;
SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
int num=0;
if (pass) {

try {
  conn = DriverManager.getConnection("jdbc:odbc:myDSN");
  ps = conn.prepareStatement("Insert into Service (TechName, ServiceDate, Warranty, Filter, TCom, DCom) values (?,?,?,?,?,?)");
 
   for (int j=0;j<warranty.length;j++)
    {
      if(warranty[j].length() > 0) {
       out.println(techcode+",");
       out.println(warranty[j]+",");
       out.println(filter[j]+",");
       out.println(tech_comm[j]+",");
       out.println(dealer_comm[j]+"<br>");
       ps.setString( 1, techcode);
       ps.setDate(2, new java.sql.Date(df.parse(date[j]).getTime()) );
       ps.setString(3, warranty[j]);
       ps.setString(4, filter[j]);
       ps.setInt(5, Integer.parseInt(tech_comm[j]));
       ps.setInt(6, Integer.parseInt(dealer_comm[j]));
       ps.executeUpdate();
      }
   }
   out.println("Num " +num);
} catch( Exception e ) {
  e.printStackTrace();

but the entry doesnt go in yet.. no exception thrown...
0
 
LVL 92

Expert Comment

by:objects
ID: 9864826
whats the output?
0
 

Author Comment

by:ipoh1977
ID: 9864846
no output... nothing come out.. thats surpirse...
anyway, let me try my luck to troubleshoot first...
0
 
LVL 92

Expert Comment

by:objects
ID: 9864864
only way i could see that could happen is if pass is false.
o/wise at least this line "out.println("Num " +num);" would be executed.
or a stack trace
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9867238
>> e.printStackTrace();
try
e.printStackTrace( new java.io.PrintWriter( out ) );
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 9875383
Glad to know your problem is solved.

I feel guilty to get all the points since everybody helped here and there. I'm going to post points for you guys:

jimmack: http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20816494.html
TimYates: http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20816495.html
objects: http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20816497.html
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installatā€¦
A procedure for exporting installed hotfix details of remote computers using powershell
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now