Solved

ADO.Net Update Table

Posted on 2004-10-20
6
206 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 500 total points
ID: 12366840
???
Use a loop and a counter????
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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 …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

691 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