asked on

ADO.Net Update Table

tblLineItemData
LineItemID (Text)
Other fields not interested in

What I want to do is say I have the following data

0001-01
0003-01
0004-01
0001-02
0003-02

I want to update it to (filtered update to do only right(lineitemID,2) = 01

0001-01
0002-01
0003-01
0001-02
0003-02

I could do this in my sleep with DAO and Access, but I don't know where to start with ADO.Net, tried for a few hours without any luck with datatables and stuff. I know I could just loop with a while datareader,read with update commands in side, but was looking for the "right" way to do it.

Also, id like to try lock the table while doing it to stop other users trying to update at the same time.

Any ideas??

arif_eqbal

Well I could not get you Query
If you are looking for a method that would get you the desired 01 out of 0002-01 then
Str.Substring(Str.IndexOf("-") + 1)
this would do (where Str contains the complete String)

Now As for how to update it to database will depend How you are dealing with the dataBase

One of the better ways would be to Trap the RowUpdating Event of the DataAdapter and set the values rather than going in loop

Use AddHandler to Handle the rowUpdating Event of dataAdapter

AddHandler DataADapter1.RowUpdating, AddressOf RowUpdating

Then on the RowUpdating Function write

Private Sub RowUpdating(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatingEventArgs) Handles DA.RowUpdating
Dim DR As DataRow = e.Row
Dim Str As String = CType(DR.Item("MyCol"), String)
DR.Item("MyCol") = Str.Substring(Str.IndexOf("-") + 1)
End Sub

i.e. here for the particular column that holds the complete value 0002-01 trim and get the last part and assign it to the Col value so that it is updated to the Database

flavo

ASKER

hmmm now im getting lost..

simple version

change

1
3
4
5
7

to

1
2
3
4
5

The rest is easy

ASKER CERTIFIED SOLUTION

arif_eqbal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

flavo

ASKER

somthing like :

dim i as long = 1
while dr.read
sQUp = "update tblMyTable set myField = i where myField = dr("myField").tostring
cmd = new oledbcommand(sQUp, con)
cmd.executenonquery
cmd.dispose
i = i + 1
end while

Ok, that's what i thought..

hmm... just woried about record locking, and weather or not its needed..... actually all my other code (checking con-currencey) should be ok... i hope...

arif_eqbal

That's OK, though you can call the Dispose once after the loop

flavo

ASKER

Giddy-up...

Just not sure on the "right" way to do things in .Net... yet

Dave