?
Solved

OleDbAdapter .UPDATE concurrency problems

Posted on 2003-03-03
10
Medium Priority
?
461 Views
Last Modified: 2008-02-01
Hi all,
I guess its easy but I'm running circles here...
I have a OleDBConnection, OleDBAdapter and a DataSet (All created using the wizards), I simply want to change a field in row 0 and update the data source (Access BD)
heres my code:
        Me.OleDbDataAdapter1.Fill(Me.DataSet21)
        Me.DataSet21.BATCHS.Rows(0)("DESCRIPTION") = "ALLO"
        Me.OleDbDataAdapter1.Update(Me.DataSet21)
        Me.DataSet21.AcceptChanges()

It alwasy gives me a concurrency violation... This is a very simple test app, containing only these 4 lines of code... Why?

Tks

Jocelyn Hould
0
Comment
Question by:Simonac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 28

Accepted Solution

by:
iboutchkine earned 200 total points
ID: 8059869
Update is tricky. We cannot update field which is in where clase (because it won't be found) usually it is unique field. User will be prevented of this update. They will get a message: Concurrancy violation
If this field is a key you cannot update it, because adapter cannot find the field an throw the exception concurrncy violation. Adapter runs SQL on the background, something like

Update Table Set Field1 = "abc" where KeyFiled = "def"

If Field1 is a key it will throw an exception


You might try to delete this record and insert it again with different key
0
 
LVL 2

Author Comment

by:Simonac
ID: 8059981
Hi Ibou,
Its not the key, its not indexed, It does allow duplicates, its very straightforward.
What do u mean by "In the where clause" ? My dataset was created using the wizard, and I did not specify any where clause. (I can post the generated code if it would help)
0
 
LVL 2

Author Comment

by:Simonac
ID: 8059999
Hum.. Hold on!
I just removed all fields from the where clause, except for the key, and now it works!

How come the wizard doesnt do that himself, especially since (I checked) the dataset clearly identifies the key? Is the wizard stupid, or is there some esoteric reasons that I dont comprehend??

0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 2

Author Comment

by:Simonac
ID: 8060033
Hum.. Hold on!
I just removed all fields from the where clause, except for the key, and now it works!

How come the wizard doesnt do that himself, especially since (I checked) the dataset clearly identifies the key? Is the wizard stupid, or is there some esoteric reasons that I dont comprehend??

0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 8060038
That is the way how it works  - you cannot update fields that are in the where clause
0
 
LVL 2

Author Comment

by:Simonac
ID: 8060262
50 more points to you if u can give me a good reason as to why the wizard put ALL fields in the where clause, and not only the key

tks
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 8060361
Because wizard works approx like that. It creates the insert, delet and update command and events for them, something like that
    Private WithEvents cmdSelect As New OleDbCommand()
    Private WithEvents cmdInsert As New OleDbCommand()
    Private WithEvents cmdUpdate As New OleDbCommand()
    Private WithEvents cmdDelete As New OleDbCommand()
after that it will create SQL commands

        da.SelectCommand = cmdSelect
        da.InsertCommand = cmdInsert
        da.UpdateCommand = cmdUpdate
        da.DeleteCommand = cmdDelete
and after that it creates SQL for all these commands
  '****select****
        da.SelectCommand.CommandText = "select * from ITH"
        da.SelectCommand.Connection = cn

        '****insert****
        da.InsertCommand.CommandText = "insert into Table values(@param1, @param2, @param3)" 'here go all fields
        da.InsertCommand.Connection = cn
        da.InsertCommand.Parameters.Add("@param1", OleDbType.VarChar, 20, "Item")
etc for each param

        '****update****
        da.UpdateCommand.CommandText = "Update Table Set Item = @param1, Line = @param2, ShopOrder = @param3 where Item = @param1" ' something like that
        da.UpdateCommand.Connection = cn
        da.UpdateCommand.Parameters.Add("@param1", OleDbType.VarChar, 20, "Item")
        da.UpdateCommand.Parameters.Add("@param2", OleDbType.Integer, 30, "Line")
        da.UpdateCommand.Parameters.Add("@param3", OleDbType.VarChar, 15, "ShopOrder")
etc for each param
        '****delete****
        da.DeleteCommand.CommandText = "delete from Table where Item = @param1 and  Line = @param2 and ShopOrder = @param3"
        da.DeleteCommand.Connection = cn
        da.DeleteCommand.Parameters.Add("@param1", OleDbType.VarChar, 20, "Item")
etc for each param


Because it is done on the background by wizard all the fields are included
0
 
LVL 2

Author Comment

by:Simonac
ID: 8064221
Hey!
Yeah,  I saw all this code, and understand it. But it still doenst explain WHY it needs other fields that the key, What possible use is there for them? In what cirsumstances would they be used? It is the wizard thats dumb, or is there some use I havent anticipated?

Tks
0
 
LVL 28

Expert Comment

by:iboutchkine
ID: 8064268
I cannot answer this question. I think that this is a wizard design
0
 
LVL 2

Author Comment

by:Simonac
ID: 8064356
Ok, anyway tks a lot for your help ibout!
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question