Solved

ADO.Net Update Table

Posted on 2004-10-20
6
201 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

785 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