2

How can I update acces database with sum function

 2 years ago
source link: https://www.codeproject.com/Questions/5315141/How-can-I-update-acces-database-with-sum-function
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

See more:

Hi i can update access mdb

but when i add in codes sum()function then get an error like this
Copy Code
System.Data.OleDb.OleDbException: 'Operation must use an updateable query.'


What I have tried:
Copy Code
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
Comments
i' using of course
i can update table with this code
"UPDATE OGRENCILIST SET BORÇ=500"
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.
How can i change my codes?
Follow the link I gave you and see how to use the ExecuteNonQuery command.
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
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.
Comments
I have always understood that ExecuteReader is for queries only. So was my answer incorrect?
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.
Thanks, I need to do some more study and experimentation.
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
Looks like Access doesn't support that syntax.

You could try:
Copy Code
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Ç
Same error :(
i use same query with ORACLE
its working. but for access not working
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.
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
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.
Copy Code
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
Comments
You've now introduced a SQL Injection[^] vulnerability. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
Copy Code
Dim cm_OGRLIST As New OleDbCommand("UPDATE OGRENCILIST SET BORÇ = ?, ALACAK = ?, BAKiYE = ? WHERE CARKOD = ?", cn_access)
cm_OGRLIST.Parameters.AddWithValue("BORC", rows(1))
cm_OGRLIST.Parameters.AddWithValue("ALACAK", rows(2))
cm_OGRLIST.Parameters.AddWithValue("BAKiYE", rows(3))
cm_OGRLIST.Parameters.AddWithValue("CARKOD", rows(0))
cm_OGRLIST.ExecuteNonQuery()
Thanks all :))

Add your solution here

Preview

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email   Password  

 

Your Email   Optional Password  

StrengthToo short

 

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:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. 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.
  4. 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)


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK