Solved

COUNT field incorrect...

Posted on 2003-12-03
24
3,453 Views
Last Modified: 2007-12-19
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
Comment
Question by:spiderman19
  • 14
  • 9
24 Comments
 
LVL 14

Expert Comment

by:kennethxu
ID: 9872363
>> 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
 

Author Comment

by:spiderman19
ID: 9872577
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
 
LVL 92

Expert Comment

by:objects
ID: 9872598
>    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
 

Author Comment

by:spiderman19
ID: 9872628
object... so sorry....
tq
0
 

Author Comment

by:spiderman19
ID: 9872633
anything wrong with the rest of the code?
0
 
LVL 92

Expert Comment

by:objects
ID: 9872651
nothing obvious I can see. Is the problem still occurring after you set the statement parameters correctly?
0
 

Author Comment

by:spiderman19
ID: 9872687
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
 

Author Comment

by:spiderman19
ID: 9872694
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
 
LVL 92

Expert Comment

by:objects
ID: 9872695
different error :)
Your sql syntax looks incorrect, try getting rid of those brackets:

Update purchase SET ExpDateA = ?,  Date = ? WHERE Warranty = ? ");
0
 
LVL 92

Expert Comment

by:objects
ID: 9872699
> also prompted me error.

what error?
0
 

Author Comment

by:spiderman19
ID: 9872753
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
 
LVL 92

Expert Comment

by:objects
ID: 9872785
whats your database schema?
having a field called Date may be a problem.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:spiderman19
ID: 9872829
?? you sure?

i have checked the inserting is no problem?
hm.. let me remove the date and let you know the result.
0
 

Author Comment

by:spiderman19
ID: 9872846
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
 

Author Comment

by:spiderman19
ID: 9872877
negative, after deleted the space in between the these 2.
0
 
LVL 92

Expert Comment

by:objects
ID: 9872881
whats the db schema?
0
 

Author Comment

by:spiderman19
ID: 9872909
purchase
======
Customer=text(25)
warranty=text(25)
ExpDateA=Date/Time
Date = Date/Time
0
 
LVL 92

Accepted Solution

by:
objects earned 150 total points
ID: 9872918
try [Date]=?
0
 

Author Comment

by:spiderman19
ID: 9872949
can give me the full statement???
0
 

Author Comment

by:spiderman19
ID: 9872956
ps2a = conn.prepareStatement("Update purchase SET ExpDateA = ?,  [Date]= ? WHERE Warranty = ? ");
 
correct?
testing now...
0
 

Author Comment

by:spiderman19
ID: 9873022
DONE!!!

THANK YOU OBJECK...

i think the Date data field is reserved word of Java.. so have to use [].. thanks...
0
 
LVL 92

Expert Comment

by:objects
ID: 9873055
Woo hoo :-)))

I think it an Access reserved word.
0
 

Author Comment

by:spiderman19
ID: 9873105
THANK YOU!!!
0
 
LVL 92

Expert Comment

by:objects
ID: 9877186
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Hyena v12.2 is now available for downloading and is available in English, French, German and Spanish versions.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 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

19 Experts available now in Live!

Get 1:1 Help Now