Solved

ADO.Net Update Table

Posted on 2004-10-20
6
203 Views
Last Modified: 2010-04-23
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??
0
Comment
Question by:flavo
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12366810
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


0
 
LVL 34

Author Comment

by:flavo
ID: 12366819
hmmm now im getting lost..

simple version

change

1
3
4
5
7

to

1
2
3
4
5

The rest is easy
0
 
LVL 19

Accepted Solution

by:
arif_eqbal earned 500 total points
ID: 12366840
???
Use a loop and a counter????
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 34

Author Comment

by:flavo
ID: 12366896
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...
0
 
LVL 19

Expert Comment

by:arif_eqbal
ID: 12367025
That's OK, though you can call the Dispose once after the loop

0
 
LVL 34

Author Comment

by:flavo
ID: 12367043
Giddy-up...

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

Dave
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

840 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