Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

ADO.Net Update Table

Posted on 2004-10-20
6
Medium Priority
?
208 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

636 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