Greetings,

i have an issue while using flex ODBC with java through NetBeans. the code below is the SQL statement that i am applying, then the function below it is the java function that i have in my file, and the last thing is the error i get when running the program. i would appreciate any help in identifying why it would give me such an error and whether my sql statement is correct although i know it is.

Thank you,

Ivan Khedhr

INSERT INTO "F29" ( "DEPLETE", "CATEGORY", "STKDESC", "INVACCTN") VALUES ('A', 'A', 'A', 22)

 public void sqlCommand3() {
  PreparedStatement ps;
 
  try {
   ps = conn.prepareStatement("insert into \"F29\" ( \"DEPLETE\", \"CATEGORY\", \"STKDESC\", \"INVACCTN\") VALUES ('A', 'A', 'A', 22)");
   ps.executeUpdate();
   
   ps.close();
   ps = null;

  } catch (Exception e) {
   e.printStackTrace();
   ps = null;
  }
 }

java.sql.SQLException: [FlexODBC] Column not found: DEPLETE
        at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
Closing
        at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
        at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(JdbcOdbc.java:4831)
        at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:475)
        at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:443)
        at fw.dft.DataFlexMain.sqlCommand3(DataFlexMain.java:189)
        at fw.dft.DataFlexMain.main(DataFlexMain.java:18)

i am certain that the column in question exists in the file i am trying to insert data into. Thank you in advance.

Regarding:

INSERT INTO "F29" ( "DEPLETE", "CATEGORY", "STKDESC", "INVACCTN") VALUES ('A', 'A', 'A', 22)

If you get Error: [FlexODBC] Column not found: DEPLETE
 
Try running VB Demo (found in the FlexODBC program group) and look at the columns in the table by doing
 
SELECT *.* Top 1 from F29
 
or find the F29.tag file on your disk (most likely in the data folder) as it has the column names in it.
 
If F29.tag has:
 
@STKN
@DEPLETE
CATEGORY
STKDESC
....
.....
......
INVACCTN
 
remove the @ to make the column visible. And yes to have to have a columnname for every value you want to insert and no you don't have to insert all 80 columns, just the ones required by the table index setup to create a unique/valid record.

Thanks for your reply Tom.

The error below is what i got after doing what you told me to.

"insert into \"F29\" ( \"DEPLETE\", \"CATEGORY\", \"STKDESC\", \"INVACCTN\") VALUES ('Y', 'Franks', 'Abcde', 22)"

java.sql.SQLException: [FlexODBC][FLEXERR] Duplicate records not allowed in file

Because of these errors we thought it would be better and easier to try the update command.

When trying to update the data, my update runs ok but the table doesn't get updated. below is the code and error for the update.

"update \"F29\" SET \"DEPLETE\"='Franks' WHERE \"STKN\"='1'" ------- the result i get after running this update command (without any errors) is below.

|1| |OPTION Q508 HT750| |N| |BATTERY| |NiMH Ultra-High Capacity Battery|

Please advise.

Thank you,

Ivan Khedhr

Thanks Tom.

we are going to look into that, but what about the update statement? how come it doesn't work?