I've looked at this from every angle, but can see nothing wrong.

The MS Access table "LocationLinks" contains only 6 fields.
LocationLinkKey - autonumber, primary key.
LocationKey - long integer, required, indexed, duplicates OK
LocationType - string length 4, not required, not indexed
RelatedLocationKey - long integer, required, indexed, duplicates OK
RelatedLocationType - string length 4, not required, not indexed
RelationshipType - string length 1, not required, not indexed.

From Java, I check first to see whether the entry exists, then try inserting it if it doesn't. This is why the SELECT, which has no problems, immediately precedes the INSERT. I've provided a few earlier lines of log to show that similar inserts on other tables work just fine.
The log shows each method name on entry and each SQL statement used.

In both the log extract and code extract, the relevant code is highlighted.

I'm using Oracle JDeveloper 11g.

The relevant part of the log is:-

sql = 'INSERT INTO LocationNameLinks(LocationKey, LocationNameKey) VALUES(8264000000003, 164478)'
getLocationNameLink
sql = 'SELECT LocationNameLinkKey FROM LocationNameLinks WHERE LocationKey = 8264000000003 AND LocationNameKey = 164478'
Source breakpoint occurred at line 372.
addLocationLink
getLocationLink
sql = 'SELECT LocationLinkKey FROM LocationLinks WHERE LocationKey = 8263000000002 AND LocationType = 'GB30' AND RelatedLocationKey = 8264000000003 AND RelatedLocationType = 'GB40''
sql = 'INSERT INTO LocationLinks(LocationKey, LocationType, RelatedLocationKey, RelatedLocationType, RelationshipType) VALUES(8263000000002, 'GB30', 8264000000003, 'GB40', '3')'
SQLException: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
SQLState: 22005
Message: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.
Vendor ErrorCode: -3030

The relevant code follows (it's all Micky Mouse stuff, I'm a beginner in all this): -

public static long addLocationLink(long locationKey1, String useType1,
                                       long locationKey2, String useType2,
                                       String relnType) {
        System.out.println("addLocationLink");
        long locationLinkKey =
            getLocationLink(locationKey1, useType1, locationKey2, useType2);
        if (locationLinkKey != 0) {
            return locationLinkKey;
        }
        String sql =
            "INSERT INTO LocationLinks(LocationKey, LocationType, RelatedLocationKey, RelatedLocationType, RelationshipType) " +
            "VALUES(" + locationKey1 + ", '" + useType1 + "', " +
            locationKey2 + ", '" + useType2 + "', '" + relnType + "')";
        processAdvDB('U', sql);
        try {
            ADVstmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } // end catch
        locationLinkKey =
                getLocationLink(locationKey1, useType1, locationKey2, useType2);

        return locationLinkKey;

    public static void processAdvDB(char processKey, String sql) {
        System.out.println("sql = '" + sql + "'");
        try {
            Statement stmt;
            ADVstmt = ADVConn.createStatement();
            if (processKey == 'U') {
                ADVstmt.executeUpdate(sql);
            } else {
                ADVrs = ADVstmt.executeQuery(sql);
            } // end else
       } catch (SQLException SQLe) {
            while (SQLe != null) {
                System.out.println("SQLException: " + SQLe);
                System.out.println("SQLState: " + SQLe.getSQLState() + "");
                System.out.println("Message: " + SQLe.getMessage() + "");
                System.out.println("Vendor ErrorCode: " + SQLe.getErrorCode() +
                                   "");
                System.out.println("");

                SQLe = SQLe.getNextException();
            } // end while
        } catch (Exception e) {
            e.printStackTrace();
        } // end catch


    } // end method processAdvDB

That's all the relevant information (I believe). Can you spot the error?
Thanks in advance for any suggestions. Going bananas here!

Recommended Answers

All 3 Replies

What is the datatype of RelationshipType? Based on the name, I would expect a numeric datatype. However, you are inserting a string into it: '3'. Could that could be the problem?

Sorry, you mention that it is a string - so forget what I said.

Another issue: can long integer in Access hold such high values (e.g.: 8264000000003). Isn't it too big for that data type? Test if you code works when inserting some smaller values for LocationKey and RelatedLocationKey.

Absolutely correct! Thanks. The field should have been Double, not Long.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.