Is it above correct statement insert date in Access database table

lse123 · #1 (**permalink**) 06-12-10, 05:01

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);
        }

sco08y · #2 (**permalink**) 06-25-10, 13:35

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.

lse123 · #3 (**permalink**) 06-25-10, 13:52

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);

healdem · #4 (**permalink**) 06-25-10, 13:58

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.