CODEWITHSUNDEEP
javasqlms-accessucanaccess
AndyReifman
AndyReifman

Reputation: 1834

Unable to execute update statement in ucanaccess

I have a connection to a Microsoft Access database. Once I make the connection I'm trying to get everything from that table I'm accessing with the following query.

ResultSet rSet = stmt.executeQuery("Select * FROM DraftNightQuery")

As far as I am able to tell, that works fine. I then have a while loop that runs as long as rSet.nex().

In my while loop I'm trying to run an update statement which looks like this:

connec.executeUpdate("UPDATE DraftNightQuery SET OwnerID='"+x+"' WHERE Last='"+split[0]+"' AND First='"+split[1]+ "' ");

and it's throwing this error:

 net.ucanaccess.jdbc.UcanaccessSQLException: INSERT, UPDATE, DELETE or TRUNCATE not permitted for table or view

I'm assuming it has something to do with the update query itself, however that worked for ODBC, and from what I can tell on the UCanAccess website it should work for this as well.

edit:

SQL for query throwing PUBLIC.NZ(DOUBLE) error:

SELECT TotalStats.ID, Players.First, Players.Last, (Nz([TotalStats].[W]*25))-(Nz([TotalStats].[L]*5))+(Nz([TotalStats].[PG]*10))+(Nz([TotalStats].[QS]*10))+(Nz([TotalStats].[SV]*20))-(Nz([TotalStats].[BS]*5))+(Nz([TotalStats].[Holds]*15))+(Nz([TotalStats].[GF]*5))+(Nz([TotalStats].[Innings]*3))-(Nz([TotalStats].[PH]*1))-(Nz([TotalStats].[ER]*2))-(Nz([TotalStats].[PHR]*8))-(Nz([TotalStats].[PBB]*3))+(Nz([TotalStats].[PK]*5))-(Nz([TotalStats].[PHB]*3))-(Nz([TotalStats].[WP]*1))+(Nz([TotalStats].[CG]*50))+(Nz([TotalStats].[ShO]*75)) AS Points, Owners.TeamName, Players.OwnerID, Players.PositionType
FROM Owners RIGHT JOIN (Players LEFT JOIN TotalStats ON Players.ID = TotalStats.ID) ON Owners.OwnerID = Players.OwnerID
WHERE (((Players.PositionType)="Pitch") AND ((Players.DraftStatus)="Drafted"))
ORDER BY (Nz([TotalStats].[W]*25))-(Nz([TotalStats].[L]*5))+(Nz([TotalStats].[PG]*10))+(Nz([TotalStats].[QS]*10))+(Nz([TotalStats].[SV]*20))-(Nz([TotalStats].[BS]*5))+(Nz([TotalStats].[Holds]*15))+(Nz([TotalStats].[GF]*5))+(Nz([TotalStats].[Innings]*3))-(Nz([TotalStats].[PH]*1))-(Nz([TotalStats].[ER]*2))-(Nz([TotalStats].[PHR]*8))-(Nz([TotalStats].[PBB]*3))+(Nz([TotalStats].[PK]*5))-(Nz([TotalStats].[PHB]*3))-(Nz([TotalStats].[WP]*1))+(Nz([TotalStats].[CG]*50))+(Nz([TotalStats].[ShO]*75)) DESC;

Upvotes: 2

Views: 4621

Answers (2)

jamadei
jamadei

Reputation: 1710

I can see two different issues:

-An issue with the nz(double). I just implemented nz(text), so it's a lack of implementation to be fixed in the next version (which I'll release ASAP, likely during the next week).

-The second one is about using an update sql statement on a query. Access select queries aren't physical tables even if they may look like. They are just select queries. If you execute an update on a select queries access can update the data in the underlying tables: the tables used in the query and involved in the update. So it's something sophisticated that the Jet engine can do and that may also lead to bad and unclear SQL code, if improperly used. UCanAccess relies on Hsqldb that in many cases doesn't allow update statements on views. So you have to call the SQL update statement directly on the table you want to update.

Notice that hsqldb supports some advanced features from SQL 2003 standard , (like MERGE INTO), that work with UCanAccess and in several cases may work as "smart"(but standard) substitute solution for your requirements (see this thread).

Upvotes: 3

José A. González
José A. González

Reputation: 36

You can't update a resulset with an stardard SELECT query using UCanAccess. You have two options:

  1. Using a PreparedStatement with this parameters ("SELECT * FROM YourTableName", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE, ResultSet.CLOSE_CURSORS_AT_COMMIT). Execute to generate a Resultset with ExecuteQuery(). Finally, update your Resultset calling this two methods: UpdateString (if field type is String) and UpdateRow.
  2. Use 2 Statements:

    st1 = conn.createStatement();
    rs = st1.executeQuery("SELECT * FROM MyTable");
    while(rs.next()) {
        st2 = conn.createStatement();
        st2.executeUpdate("UPDATE MyTable SET MyField='New Value' WHERE MyField2 LIKE 'Condition'");
    }

Upvotes: 0

Related Questions