?
Solved

ADO.Net Update Table

Posted on 2004-10-20
6
Medium Priority
?
207 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
[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
  • 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 2000 total points
ID: 12366840
???
Use a loop and a counter????
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month11 days, 13 hours left to enroll

752 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