Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

OleDbAdapter .UPDATE concurrency problems

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
Simonac
Asked:
Simonac
  • 6
  • 4
1 Solution
 
iboutchkineCommented:
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
 
SimonacAuthor Commented:
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
 
SimonacAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
SimonacAuthor Commented:
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
 
iboutchkineCommented:
That is the way how it works  - you cannot update fields that are in the where clause
0
 
SimonacAuthor Commented:
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
 
iboutchkineCommented:
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
 
SimonacAuthor Commented:
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
 
iboutchkineCommented:
I cannot answer this question. I think that this is a wizard design
0
 
SimonacAuthor Commented:
Ok, anyway tks a lot for your help ibout!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now