How can I update acces database with sum function

Question

0.00/5 (No votes)

See more:

Hi i can update access mdb

but when i add in codes sum()function then get an error like this

System.Data.OleDb.OleDbException: 'Operation must use an updateable query.'

What I have tried:

Dim UPT_OGRLIST = "UPDATE OGRENCILIST  INNER JOIN (SELECT CARKOD,SUM(BORÇ) AS BORÇ FROM HAREKETLER GROUP BY CARKOD) AS HAREKETLER  ON OGRENCILIST.CARKOD=HAREKETLER.CARKOD SET OGRENCILIST.Borç=HAREKETLER.Borç"
       Dim cm_OGRLIST As New OleDbCommand(UPT_OGRLIST, cn_access)
       Dim dr_UOGRLIST As OleDbDataReader = cm_OGRLIST.ExecuteReader

Posted 13-Oct-21 20:26pm

Member 14588284

Updated 14-Oct-21 2:18am

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2021-10-13T21:15:00

Solution 1

You should not be using a data reader to perform an update. You should use OleDbCommand.ExecuteNonQuery Method (System.Data.OleDb) | Microsoft Docs[^].

Posted 13-Oct-21 21:15pm

Richard MacCutchan

Comments

Member 14588284 14-Oct-21 3:16am

i' using of course
i can update table with this code
"UPDATE OGRENCILIST SET BORÇ=500"

Richard MacCutchan 14-Oct-21 3:30am

Yes, but you are trying to use a DaTaReader for the update. You need to use an updateable query as explained in the error message.

Member 14588284 14-Oct-21 3:39am

How can i change my codes?

Richard MacCutchan 14-Oct-21 3:57am

Follow the link I gave you and see how to use the ExecuteNonQuery command.

Member 14588284 14-Oct-21 4:09am

Dim UPT_OGRLIST = "UPDATE OGRENCILIST SET BORÇ=(SELECT SUM(BORÇ) FROM HAREKETLER WHERE CARKOD=OGRENCILIST.CARKOD)"
Dim cm_OGRLIST As OleDbCommand = New OleDbCommand(UPT_OGRLIST, cn_access)
'Dim dr_UOGRLIST As OleDbDataReader = cm_OGRLIST.ExecuteReader **** i removed this line
cm_OGRLIST.ExecuteNonQuery()

But same error

Richard Deeming · Answer 2 · 2021-10-13T22:22:00

Solution 2

The "Operation must use an updateable query" error usually indicates that your database file is stored in a folder to which the current user does not have permission to write.

For example, if you have stored your database file in your application folder, and your application is installed under the "Program Files" directory, the user would need to be an administrator of the local machine, and your application would need to run elevated before you could write to the folder.

Check the NTFS permissions on the folder where your database file is stored. If it's on a network share, check the permissions on the share as well.

Posted 13-Oct-21 22:22pm

Richard Deeming

Comments

Richard MacCutchan 14-Oct-21 4:38am

I have always understood that ExecuteReader is for queries only. So was my answer incorrect?

Richard Deeming 14-Oct-21 4:40am

You're not wrong, but I don't think using ExecuteReader for a query that doesn't return any results would generate the error from the question.

Richard MacCutchan 14-Oct-21 5:20am

Thanks, I need to do some more study and experimentation.

Member 14588284 14-Oct-21 4:52am

i checed permissions thats ok.
always i can update table but with this code i get error.

"UPDATE OGRENCILIST SET BORÇ=500" --ok
"UPDATE OGRENCILIST SET BORÇ=(SELECT SUM(BORÇ) FROM HAREKETLER WHERE CARKOD=OGRENCILIST.CARKOD)" --error

Richard Deeming 14-Oct-21 4:58am

Looks like Access doesn't support that syntax.

You could try:

UPDATE OGRENCILIST As a
INNER JOIN (SELECT CARKOD, SUM(BORÇ) As NewBORÇ FROM HAREKETLER GROUP BY CARKOD) As b
ON b.CARKOD = a.CARKOD
SET a.BORÇ = b.NewBORÇ

Member 14588284 14-Oct-21 5:12am

Same error :(

Member 14588284 14-Oct-21 6:39am

i use same query with ORACLE
its working. but for access not working

Richard Deeming 14-Oct-21 6:40am

Microsoft Access and Oracle are two completely separate products, with their own idiosyncrasies and language differences. You can't simply take a query that works in one and expect it to work in the other.

Member 14588284 14-Oct-21 6:46am

of course i dont think oracle and access works same querys

but i find theese codes for access

https://stackoverflow.com/questions/17068079/ms-access-complex-grouping-and-sum
https://stackoverflow.com/questions/42285550/ms-access-sql-update-join-query-with-sum-from-another-table

Richard Deeming 14-Oct-21 6:53am

One of those has essentially the same syntax I suggested in my comment, but the answer has not been accepted.

The other isn't issuing an UPDATE query.

Member 14588284 · Answer 3 · 2021-10-14T02:18:00

Dim UPT_OGRLIST = "SELECT CARKOD,TBORC,TALACAK,TALACAK-TBORC AS BAKIYE FROM " &
           "(SELECT A.CARKOD,IIF( TBORC IS NULL,0,TBORC)AS TBORC,IIF( TALACAK IS NULL,0,TALACAK)AS TALACAK FROM (SELECT CARKOD,BORÇ,ALACAK,BAKiYE AS BAKIYE FROM OGRENCILIST)A " &
           "LEFT JOIN (SELECT CARKOD,SUM(BORÇ) AS TBORC,SUM(ALACAK)AS TALACAK  FROM HAREKETLER GROUP BY CARKOD)B ON A.CARKOD=B.CARKOD )"
       Dim dc = New OleDbCommand(UPT_OGRLIST, cn_access)
       Dim rows As OleDb.OleDbDataReader
       rows = dc.ExecuteReader
       If rows.HasRows Then
           Do While rows.Read()
               Dim cm_OGRLIST As New OleDbCommand("UPDATE OGRENCILIST SET BORÇ='" & rows(1) & "',ALACAK='" & rows(2) & "',BAKiYE='" & rows(2) & "' WHERE CARKOD='" & rows(0) & "'", cn_access)
               cm_OGRLIST.ExecuteNonQuery()
           Loop
       End If

ichanged my codes like this :)
make it do -loop