[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3554
  • Last Modified:

COUNT field incorrect...

spider is here. hope my java skill can like most of you, can run and no longer crawing.. ha...

this my code,
******
 
if (num!=0) {
try {
  ps2a = conn.prepareStatement("Update purchase SET (ExpDateA = ?,  Date = ?) WHERE Warranty = ? ");
  ps2b = conn.prepareStatement("Update purchase SET (ExpDateB = ?,  Date = ?) WHERE Warranty = ? ");
  ps2c = conn.prepareStatement("Update purchase SET (ExpDateC = ?,  Date = ?) WHERE Warranty = ? ");
  ps2d = conn.prepareStatement("Update purchase SET (ExpDateD = ?,  Date = ?) WHERE Warranty = ? ");
  ps2e = conn.prepareStatement("Update purchase SET (ExpDateE = ?,  Date = ?) WHERE Warranty = ? ");
  ps2f = conn.prepareStatement("Update purchase SET (ExpDateF = ?,  Date = ?) WHERE Warranty = ? ");

    for (int j=0;j<warranty.length;j++)
    {
      if(warranty[j].length() > 0) {
           if (filter[j].equals("A")) {
               ps.setString( 1, date[j]);  
               ps.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
               ps.setString(3, warranty[j]);
               number=ps2a.executeUpdate()+number;
          }
          if (filter[j].equals("B")) {
               ps.setString( 1, date[j]);
               ps.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
               ps.setString(3, warranty[j]);
               number=ps2b.executeUpdate()+number;
          }
           if (filter[j].equals("C")) {
               ps.setString( 1, date[j]);
               ps.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
               ps.setString(3, warranty[j]);
               number=ps2c.executeUpdate()+number;
          }
           if (filter[j].equals("D")) {
               ps.setString( 1, date[j]);
               ps.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
               ps.setString(3, warranty[j]);
               number=ps2d.executeUpdate()+number;
          }
           if (filter[j].equals("E")) {
               ps.setString( 1, date[j]);
               ps.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
               ps.setString(3, warranty[j]);
               number=ps2e.executeUpdate()+number;
          }
           if (filter[j].equals("F")) {
               ps.setString( 1, date[j]);
               ps.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
               ps.setString(3, warranty[j]);
               number=ps2f.executeUpdate()+number;
          }
       }
    }
.
.
.
..

but afer i execute it, it prompt me this error...

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver]COUNT field incorrect
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
      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:165)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
      at org.netbeans.modules.

*****

I totally absolutely didn't use the COUNT statement on my program, not evene the above frst statement before this whole slack..

and i have count the "?" in my preparedstatement, how can i sovle this problem?

0
spiderman19
Asked:
spiderman19
  • 14
  • 9
1 Solution
 
kennethxuCommented:
>> at org.apache.jsp.tech_0005fconfirm$jsp._jspService(tech_0005fconfirm$jsp.java:165)
can you tell us error is from which line of code?
0
 
spiderman19Author Commented:
ken, tech_0005fconfirm$jsp.java:165) is referring to;

for (int j=0;j<warranty.length;j++)
    {
     if(warranty[j].length() > 0) {
          if (filter[j].equals("A")) {
              ps.setString( 1, date[j]);  
               ps.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
              ps.setString(3, warranty[j]);
>>>>>   number=ps2a.executeUpdate()+number;
         }
0
 
objectsCommented:
>    ps.setString( 1, date[j]);  
>    ps.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
>    ps.setString(3, warranty[j]);
>    number=ps2a.executeUpdate()+number;

You're setting the parameter values on ps, but executing ps2a.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
spiderman19Author Commented:
object... so sorry....
tq
0
 
spiderman19Author Commented:
anything wrong with the rest of the code?
0
 
objectsCommented:
nothing obvious I can see. Is the problem still occurring after you set the statement parameters correctly?
0
 
spiderman19Author Commented:
yes, the update statement prompted me error pointing to this place...>>>                               number=ps2a.executeUpdate()+number;

.
.
.
 ps2a = conn.prepareStatement("Update purchase SET (ExpDateA = ?,  Date = ?) WHERE Warranty = ? ");
  ps2b = conn.prepareStatement("Update purchase SET (ExpDateB = ?,  Date = ?) WHERE Warranty = ? ");
  ps2c = conn.prepareStatement("Update purchase SET (ExpDateC = ?,  Date = ?) WHERE Warranty = ? ");
  ps2d = conn.prepareStatement("Update purchase SET (ExpDateD = ?,  Date = ?) WHERE Warranty = ? ");
  ps2e = conn.prepareStatement("Update purchase SET (ExpDateE = ?,  Date = ?) WHERE Warranty = ? ");
  ps2f = conn.prepareStatement("Update purchase SET (ExpDateF = ?,  Date = ?) WHERE Warranty = ? ");
.
.
.
    for (int j=0;j<warranty.length;j++)
                    {
                      if(warranty[j].length() > 0) {
                           if (filter[j].equals("A")) {
                               ps2a.setString( 1, date[j]);  
                               ps2a.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
                               ps2a.setString(3, warranty[j]);
>>>                               number=ps2a.executeUpdate()+number;
                          }

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
      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:158)
      at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:107)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
0
 
spiderman19Author Commented:
i change to this :
ps2a = conn.prepareStatement("Update purchase SET ExpDateA = ?,  Date = ? WHERE Warranty = ? ");
  ps2b = conn.prepareStatement("Update purchase SET ExpDateB = ?,  Date = ? WHERE Warranty = ? ");
  ps2c = conn.prepareStatement("Update purchase SET ExpDateC = ?,  Date = ? WHERE Warranty = ? ");
  ps2d = conn.prepareStatement("Update purchase SET ExpDateD = ?,  Date = ? WHERE Warranty = ? ");
  ps2e = conn.prepareStatement("Update purchase SET ExpDateE = ?,  Date = ? WHERE Warranty = ? ");
  ps2f = conn.prepareStatement("Update purchase SET ExpDateF = ?,  Date = ? WHERE Warranty = ? ");


also prompted me error.
0
 
objectsCommented:
different error :)
Your sql syntax looks incorrect, try getting rid of those brackets:

Update purchase SET ExpDateA = ?,  Date = ? WHERE Warranty = ? ");
0
 
objectsCommented:
> also prompted me error.

what error?
0
 
spiderman19Author Commented:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
      at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
      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:158)
      at org.apache

pointing to

    if(warranty[j].length() > 0) {
                           if (filter[j].equals("A")) {
                               ps2a.setString( 1, date[j]);  
                               ps2a.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
                               ps2a.setString(3, warranty[j]);
>>>>>>>>             number=ps2a.executeUpdate()+number;
                          }

this is my preparedstatement.
 ps2a = conn.prepareStatement("Update purchase SET ExpDateA = ?,  Date = ? WHERE Warranty = ? ");
  ps2b = conn.prepareStatement("Update purchase SET ExpDateB = ?,  Date = ? WHERE Warranty = ? ");
  ps2c = conn.prepareStatement("Update purchase SET ExpDateC = ?,  Date = ? WHERE Warranty = ? ");
 
0
 
objectsCommented:
whats your database schema?
having a field called Date may be a problem.
0
 
spiderman19Author Commented:
?? you sure?

i have checked the inserting is no problem?
hm.. let me remove the date and let you know the result.
0
 
spiderman19Author Commented:
ps2a = conn.prepareStatement("Update purchase SET ExpDateA = ? WHERE Warranty = ? ");
  ps2b = conn.prepareStatement("Update purchase SET ExpDateB = ? WHERE Warranty = ? ");
  if(warranty[j].length() > 0) {
           if (filter[j].equals("A")) {
               ps2a.setString( 1, date[j]);  
             //  ps2a.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
               ps2a.setString(2, warranty[j]);
               number=ps2a.executeUpdate()+number;
          }
          if (filter[j].equals("B")) {
               ps2b.setString( 1, date[j]);
//               ps2b.setDate(2, new java.sql.Date(df.parse(todayDate).getTime()) );
               ps2b.setString(2, warranty[j]);
               number=ps2b.executeUpdate()+number;
          }

it's updated fine...

but i would like to update another field.. how??

do you think this is the error?

ps2a = conn.prepareStatement("Update purchase SET ExpDateA = ?,  Date = ? WHERE Warranty = ? ");

ps2a = conn.prepareStatement("Update purchase SET ExpDateA = ?, Date = ? WHERE Warranty = ? ");

the space between (ExpDateA=?, and Date )??
0
 
spiderman19Author Commented:
negative, after deleted the space in between the these 2.
0
 
objectsCommented:
whats the db schema?
0
 
spiderman19Author Commented:
purchase
======
Customer=text(25)
warranty=text(25)
ExpDateA=Date/Time
Date = Date/Time
0
 
objectsCommented:
try [Date]=?
0
 
spiderman19Author Commented:
can give me the full statement???
0
 
spiderman19Author Commented:
ps2a = conn.prepareStatement("Update purchase SET ExpDateA = ?,  [Date]= ? WHERE Warranty = ? ");
 
correct?
testing now...
0
 
spiderman19Author Commented:
DONE!!!

THANK YOU OBJECK...

i think the Date data field is reserved word of Java.. so have to use [].. thanks...
0
 
objectsCommented:
Woo hoo :-)))

I think it an Access reserved word.
0
 
spiderman19Author Commented:
THANK YOU!!!
0
 
objectsCommented:
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.

  • 14
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now