Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    139

    Unanswered: Is it above correct statement insert date in Access database table

    Code:
    String sqlCommand2= "INSERT INTO Orders VALUES('1021','0021','"+new java.util.Date()+"','"+new java.util.Date()+"',"+OrderTotals2.getTotal()+","+OrderTotals2.getShipping()+","+OrderTotals2.getTax()+")" ;
       
    //  OrderNumber, CustID,date-ordered,date-shipped,....
    sttmnt1.executeUpdate(sqlCommand2);
    Is it above correct statement insert date in Access database table OR this is for MySQL... I have Access and 3 tables: Customer,Orders, OrderDetail although only first populated each script execution, well?

    Is it this correct order populate tables correct:Customer,Orders, OrderDetail ?

    Please Tell me and for below, is it correct code?
    Code:
       List lineList2 = OrderTotals2.getLineList();
    
            for (int i = 0; i < lineList2.size(); i++)  //lineList2.length
            {
              OrderPlantItem lineItem0 = (OrderPlantItem)lineList2.get(i);
    
              String sqlCommand3= "INSERT INTO OrderDetail VALUES('1021',"+i+1+","+lineItem0.getPlantID()+","+lineItem0.getQty()+","+lineItem0.getSubtotal()+")" ;
    
                sttmnt2.executeUpdate(sqlCommand3);
            }
    Last edited by lse123; 06-12-10 at 06:05.

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Code:
    String sqlCommand2= "INSERT INTO Orders VALUES('1021','0021','"
        + new java.util.Date() +"','"
        + new java.util.Date() + "',"
        + OrderTotals2.getTotal() + ","
        + OrderTotals2.getShipping() + ","
        + OrderTotals2.getTax() + ")" ;
       
    //  OrderNumber, CustID,date-ordered,date-shipped,....
    sttmnt1.executeUpdate(sqlCommand2);
    My Java's a little rusty, but I believe that arguments to string concatenation will be coerced to strings through the default toString method. So, that's valid Java, at least.

    The real issue is whether your dates are formatted correctly to pass to Access. The default string coercion will return a human readable representation of the Java Date value, but you probably need to use a specific formatting method to return a string that Access understands as a date / time. It would help to know exactly what the column type is, since Access handles a couple of different date / time types.

    IIRC, Access will take a date formatted in the local system format surrounded by hashes. So #Jan 1, 2010# would probably work as a date literal. Not sure if #Jan 1, 2010 12:30AM# would work for the time. You'll want to check the docs (search for "date literal")and then run some tests to see what the DBMS accepts.

    Once you know how to format the date, Java has a number of methods to format Date objects, especially in the Calendar class. I can't link you to the docs without knowing what version of Java you're using, but Java Date and Java Calendar are helpful Google searches.

  3. #3
    Join Date
    May 2007
    Posts
    139
    go and try one format of fixed date to see if works, below?
    I use java 6 update 17.

    Code:
    String sqlCommand2= "INSERT INTO Orders VALUES('1021','0021',
         '#Jan 1, 2010 12:30AM#',
         '#Jan 1, 2010#',"
        + OrderTotals2.getTotal() + ","
        + OrderTotals2.getShipping() + ","
        + OrderTotals2.getTax() + ")" ;
       
    //  OrderNumber, CustID,date-ordered,date-shipped,....
    sttmnt1.executeUpdate(sqlCommand2);

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access, or more properly JET prefers dates in US format MM / DD / YYYY or MM / DD / YY. , I think it can also accept ISO date format YYYY DD MM

    so you need to make certain your SQL complies with that.

    To make life easier for yourself I'd recommend that you include a column list to clearly tell the SQL engine what columns you want to insert/update
    I'd also check your SQL is actually valid use an appropriate debugging / break point to prove that. I often display the SQL then copy and paste it into the target SQL engines query browser and make certain it works there if I'm unsure if the SQL is correct.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    May 2007
    Posts
    139

    May use in Access and

    May use in Access and

    long time = claim.getClaimDate().getTime();
    java.sql.Date date = new java.sql.Date(time);

    and insert var: date ???

  6. #6
    Join Date
    Aug 2010
    Posts
    1
    Great! I also need this information. Thanks for sharing guys!

Posting Permissions

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