Newbie: Insert date & time into Access

[Steve Horsley]

This is my first stab at inserting into a database (Access 97).

I can insert strings and integers, but am stumped on inserting dates
and times.
The table in question has separate date and time columns. Below is
some code
that tries to insert a Caller and Time:

void test2(Connection con) throws Exception {
Statement stmt = con.createStatement();
String update = "INSERT INTO [All Calls] (Time, Caller) VALUES
(11:22:33, 'Test Caller')";
System.out.println("Update: " + update);
int rowCount = stmt.executeUpdate(update);
System.out.println("" + rowCount + " rows updated.");
}

I have also tried '11:22:33' and #11:22:33# to no avail.

Here is the exception:
Update: INSERT INTO [All Calls] (Time, Caller) VALUES (11:22:33, 'Test
Caller')
java.sql.SQLException: [Microsoft][ODBC Microsoft Access 97 Driver]
Syntax error in INSERT INTO stat
ement.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:314)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:264)
at test.SqlTest.test2(SqlTest.java:103)
at test.SqlTest.test(SqlTest.java:34)
at test.SqlTest.main(SqlTest.java:116)

If someone could help me to get this working, I would be very
grateful.

Steve.

[dbia...@square.nl]

Steve Horsley <steve....@cwcom.cwplc.com> wrote:
> I can insert strings and integers, but am stumped on inserting dates
> and times.

> java.sql.SQLException: [Microsoft][ODBC Microsoft Access 97 Driver]
> Syntax error in INSERT INTO statement.

Well, evidently it does not recognize "11:22:33" as a valid time.
I suggest you use PreparedStatement and setDate()/setTime().
See the documentation for PreparedStatement in the Java
documentation.

Davide

[Steve Horsley]

dbia...@square.nl wrote in message news:<9fnbr0$4ul73$1...@ID-18487.news.dfncis.de>...

Good plan, but still no joy I'm afraid. The code below gives an SQL Syntax
error. If I comment out the time setting and just set the caller and duration
then it works a treat.

void test4(Connection con) throws Exception {
PreparedStatement pstmt = con.prepareStatement(
"INSERT INTO [All Calls] (Caller, Duration, Time) VALUES (?, ?, ?)");
// "INSERT INTO [All Calls] (Caller, Duration) VALUES (?, ?)");
pstmt.setString(1, "Mr. Angry");
pstmt.setInt(2, 60);
pstmt.setTime(3, new Time(10, 11, 12));
int rowCount = pstmt.executeUpdate();

System.out.println("" + rowCount + " rows updated.");
}

Steve.

[dbia...@square.nl]

Steve Horsley <steve....@cwcom.cwplc.com> wrote:
> Good plan, but still no joy I'm afraid. The code below gives an
> SQL Syntax error.

But "Time" is not a reserved word in SQL (so you can't use
it for a column-name)? If you put the same SQL string directly in
Access what it said ?

Davide

[Steve Horsley]

dbia...@square.nl wrote in message news:<9fqbu4$5hoa4$7...@ID-18487.news.dfncis.de>...

Davide, you're a star!

Renaming the column did the trick! I used a time of #12:13:14# and it worked.
Having been put on the right track, I also found that I can use the column name
"Time" if I put square brackets round it: "[Time]".

All the time, I had been convinced that it was the time format, not the column
name that was my problem. It never occured to me that time was a reserved word.

Thanks again.
Steve.

[dbia...@square.nl]

Steve Horsley <steve....@cwcom.cwplc.com> wrote:
> Having been put on the right track, I also found that I can use the
> column name "Time" if I put square brackets round it: "[Time]".

I knew that Access was evil, but allow users to create a field with
a reserved name without flashing a warning....
Well, glad you solved your problem.

Davide

[Bob Beaty]

If I remember correctly, Access uses pound signs to quote dates and
times. Try #11:22:33# for slightly after 11:20. May work, may not.

Thanks,
Bob (bobb...@mediaone.net)
The Man from S.P.U.D.
We will write no code before it's designed.