Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    4

    Unanswered: Problem with Insert into Access with Java JDBC ODBC driver

    Hallo,

    I need to write a simple java application that will be running on a machine with a user that might not be near any technical help. I want to use the java app as the front end and Access as the backend but I have a problem inserting data into the database.

    The URL: String url = "jdbcdbc:gill_db";. This gill_db has been defined as an ODBC datasource and works fine when I select data from the database.

    Registering the driver: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

    The connection Connection con = DriverManager.getConnection(url, "", "");
    There is no username and/or password necessary for this DB

    The statment: Statement istmnt = con.createStatement();

    The insert SQl: x_insert = "INSERT INTO Ceremonies VALUES ("
    + cer.getCeremonyNum() + ", \""
    + cer.getCeremonyDesc() + "\", "
    + cerdate + ", \""
    + cer.getCeremonyPlace()
    + "\")";

    The execution of istmnt.executeUpdate(x_insert); returns the following error:
    SQL Exception: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

    What could be wrong to get this error.

    I wrote the same insert for an ORACLE table and it works 100%

    Please help me solving this problem please.

    Phlip

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    I know this question is a few days old and you it is likely that you already solved it but this is my input just the same. I get these types of errors sometimes as well when working in Java, it is usually a field error, either data type, name or the number of arguments. Try wrapping your date field with the # symbol that access seems to love.

    Steve

  3. #3
    Join Date
    Aug 2006
    Posts
    4
    Hi and Thanx.

    I changed the code to a prepared statement like this:
    String q_insert = "INSERT INTO Ceremonies VALUES (?, ?, ?, ?)";

    PreparedStatement ceremonyStat = con.prepareStatement(q_insert);
    Date DATE;
    ceremonyStat.setLong(1, cer.getCeremonyNum());
    ceremonyStat.setString(2, cer.getCeremonyDesc());
    ceremonyStat.setDate(3, (Date) cer.getCeremonyDate());
    ceremonyStat.setString(4, cer.getCeremonyPlace());

    System.out.println(q_insert + "\n");
    Boolean r;
    try
    {
    istmnt.execute(q_insert);

    But now I get the message:
    SQL Exception: [Microsoft][ODBC Microsoft Access Driver]Optional feature not implemented

    I have tried the # around the date.

    Can anybody help?

    Phlip

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    We know how to pass it from VBA to the Jet engine, but I think you're more likely to get help on how to do it using your technology in a forum dedicated to that technology.

    In VBA, one might do something like this
    Code:
    SQL = "INSERT INTO myTable (myNum, myDesc, myDate) VALUES (" & _
          myNumeric & ", '" & myText & "', #" & format(myDate, "yyyy-mm-dd") & "#)"
    which would/should result in a string looking like this

    INSERT INTO myTable (Field1, Field2, Field3) VALUES (42, 'Arthur Dent', #2001-05-03#)

    which Jet should be able to understand. If there's a chance the text might contain double quotes, you'll need to double them up.
    Roy-Vidar

Posting Permissions

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