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
Solved

VB.NET update DataTable using ADO.NET

Posted on 2013-05-10
11
873 Views
Last Modified: 2013-05-12
Hi experts,

how is it possible to update one column of DataTable in memory with values from second DataTable using ADO.NET? Both data tables have one column identical (ID) and sources are different databases (servers)

I would like to update column Price from first DataTable to second DataTable. Both data tables are from different data sources, thats why I can't use SQL command. Or is it possible to use SQL for it?

I do not want to use LINQ in this case, although LINQ is newer technology.

I have Visual Basic 2010 and .NET Framework 4.0.

Thanks.
0
Comment
Question by:skdan
  • 6
  • 5
11 Comments
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39157669
Hi,

First of all it is possible to use SQL if there is a linked server set up, or the dbs are on the same server but i don't think this is your case.

I am not in a pc to write code but it goes like this:
        For Each row As DataRow In dt1.rows
            dt2.Select("ID=" + row("ID"))
            dt2.rows(0)("Price") = row("Price")
        Next

Open in new window

I have assumed dt1 and dt2 are your tables.

Again i would prefer either a liked server or LINQ.

Giannis
0
 

Author Comment

by:skdan
ID: 39157717
Hi Giannis,

thank you for answer.

I know, these are questions of beginner, but I kindly ask you for answers.

1. I use 3 different data sources and I use separate connections to each one (MS Access file, DBase files, MySQL server). Is there some possibility to use SQL as if data were on one server?

2. Is it possible to use LINQ similar like SQL queries (across more connections to databases)? I mean not only equivalent of SELECT command, but also UPDATE, JOIN, INNER JOIN commands etc. I mean if LINQ can completely substitute SQL for these purposes.

Thanks.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39157746
Hi,

1.I know you could do it if you had an SQL Server running. You could create linked server to MySql, Access and DBase. So in my first post i though you actually had an SQL Server. I don't know if this is doable from Access, sorry.

2.LINQ is great utility for getting data so SELECT is preferable with it. For Joins i have written an article found here and i hope you find helpful as an entry point.

For Inserts and Updates LINQ is limmited to row by row process, as a datatable would, so you might find it useful. I like it being strongly typed. There is actually a way of updating one datatable object without iterating through its every row with LINQ and this is creating a third datatable with a SELECT and then loading the datatable you need to update with the one you just created. See the following example:

First of all create a vb.net console application
Add a reference to your project to System.Data.DataSetExtensions
Then paste the next code to your Sub Main()
        'DECLARE the two tables
        Dim dt1 As New DataTable
        dt1.Columns.Add("RANGE", GetType(String))
        dt1.Columns.Add("desc", GetType(String))
        dt1.Columns.Add("qty", GetType(Integer))

        Dim dt2 As New DataTable
        dt2.Columns.Add("RANGE", GetType(String))
        dt2.Columns.Add("desc", GetType(String))
        dt2.Columns.Add("Qty", GetType(Integer))


        'Load data to tables
        Dim dr As DataRow = dt1.NewRow
        dr("Range") = "N001"
        dr("desc") = "cv plug"
        dr("qty") = 5
        dt1.Rows.Add(dr)

        dr = dt1.NewRow
        dr("Range") = "N002"
        dr("desc") = "car plug"
        dr("qty") = 3
        dt1.Rows.Add(dr)

        dr = dt2.NewRow
        dr("Range") = "N001"
        dr("desc") = "cv plug"
        dr("qty") = 0
        dt2.Rows.Add(dr)

        dr = dt2.NewRow
        dr("Range") = "N002"
        dr("desc") = "car plug"
        dr("qty") = 0
        dt2.Rows.Add(dr)

    
        'Declare a clone of the tables
        Dim dt3 As New DataTable
        dt3.Columns.Add("RANGE", GetType(String))
        dt3.Columns.Add("desc", GetType(String))
        dt3.Columns.Add("Qty", GetType(Integer))

        'This is just for display to show you the values before the update
        For Each row In dt2.Rows
            Console.WriteLine(row("Range").ToString + " " + row("desc").ToString + " " + row("qty").ToString)
        Next

        'Here the update happens
        Dim query = From a In dt1.AsEnumerable _
                   Join b In dt2.AsEnumerable _
                   On a.Field(Of String)("Range") Equals b.Field(Of String)("Range")
                   Select dt3.LoadDataRow( _
                   { _
                    a.Field(Of String)("Range"), _
                    a.Field(Of String)("desc"), _
                    a.Field(Of Integer)("qty") _
                   }, True)

        'Copy the updated data to dt2
        dt2 = query.CopyToDataTable


        'This is just for display to show you the values after the update
        For Each row In dt2.Rows
            Console.WriteLine(row("Range").ToString + " " + row("desc").ToString + " " + row("qty").ToString)
        Next

        Console.ReadLine()

Open in new window



The above code is a sample i have used in another question here in EE. It demonstrates how you could update a table using LINQ. It will certainly need some modification to fit your case.

Giannis
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

Author Comment

by:skdan
ID: 39158135
Hi,

I will try your solution soon, and I accept what you say, but for me is still not clear, how it was solved in .NET earlier, because if I remember well, LINQ started in .NET 3.5.

I believe, that work with multiple data sources was possible in .NET 2.0 (Visual Studio 2005) with ADO.NET technology, but I don't know how programers solved this in that time. Do you have some informations about that?

Thanks.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39158143
Do you mean TableAdapters?
0
 

Author Comment

by:skdan
ID: 39158456
I do not mean nothing specific, I try only imagine, that there is year 2005, I have Visual Studio 2005 and .NET Framework 2.0 and I would like to use 3 different data sources on different servers (MS Access, dBase, MySQL), use SQL queries for update one data source from second data source.

How was it possible in that time? Which technology was necessary to use and how?

Thanks.
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39158537
0
 

Author Comment

by:skdan
ID: 39159230
Hi,

thank you for link. Do you have some example of code in Visual Basic .NET, where I can solve my problem using this technology?

We can create this situation for demonstration (see attached files):

1. We have two MS Access database files.

2. The first database file has one table "Prod1" and 3 columns (ProductID, ProductName, ProductPrice)

3. The second database file has one table "Prod2" and 2 columns (ProductID, ProductPrice)

4. I would like to update column ProductPrice of table "Prod2" with values from column ProductPrice of table "Prod1".

When both tables are in one database file, I simply use SQL command (I copied it from MS Access):

UPDATE Prod1 INNER JOIN Prod2 ON Prod1.ProductID = Prod2.ProductID SET Prod2.ProductPrice = [Prod1]![ProductPrice];

Open in new window


But when they are in different database files, not linked, I don't know, how to do it.

Thanks.
Prod.zip
0
 
LVL 23

Accepted Solution

by:
Ioannis Paraskevopoulos earned 500 total points
ID: 39159360
Hi,

Create a project.
From the menu choose 'View'->'Server Explorer'
In the 'Server Explorer' window add the two connections to your Access DB files
Add a new item (Data->DataSet) in your project for Prod1
In the designer of the dataset drag and drop the first table from the Server Explorer
Add a new item (Data->DataSet) in your project for Prod2
In the designer of the dataset drag and drop the second table from the Server Explorer

Now you have two DataSets with one table each. Each DataSet has one TableAdapter.
The TableAdapters have two parts. The first(upper) part has the columns of the Adapter. The lower one has all the defined methods of the Adapter. By default there are going to be a Fill and GetData methods that you will normally use to load data to the Adapter.

Now we are going to add a method to Prod2TableAdapter for Update.

Right Click on the name of the lower section of Prod2TableAdapter and select 'Add Query'
Choose SQL Statements
Choose 'UPDATE'
Replace the generated query with:
UPDATE `Prod2` SET `ProductPrice` = ? WHERE (`ProductID` = ?)

Open in new window

Give a name for your method

Now you have created the method to call for update rows in Prod2.

Next step is to add the datasets in your form.

Open the designer of your form
From the toolbox open Data group and double click on DataSet
From the dropdown select the first DataSet
Repeat for Prod2DataSet
Add a button to your form
Add a listbox on your form

In the code of the form add the following:
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim Prod1 As New DataTable
        Dim Prod1TableAdapter As New Prod1DataSetTableAdapters.Prod1TableAdapter
        Dim Prod2 As New DataTable
        Dim Prod2TableAdapter As New Prod2DataSetTableAdapters.Prod2TableAdapter

        Prod1 = Prod1TableAdapter.GetData
        Prod2 = Prod2TableAdapter.GetData

        ListBox1.Items.Add("Prod1 Before Update")
        ListBox1.Items.Add("-------------------")

        For Each row As DataRow In Prod1.Rows
            ListBox1.Items.Add(row(0).ToString + " " + row(1).ToString + " " + row(2).ToString)
        Next


        ListBox1.Items.Add("                   ")


        ListBox1.Items.Add("Prod2 Before Update")
        ListBox1.Items.Add("-------------------")

        For Each row As DataRow In Prod2.Rows
            ListBox1.Items.Add(row(0).ToString + " " + row(1).ToString + " " + row(2).ToString)
        Next

        ListBox1.Items.Add("                   ")


        ListBox1.Items.Add("Prod2 After Update")
        ListBox1.Items.Add("-------------------")

        For Each row As DataRow In Prod1.Rows
            Prod2TableAdapter.UpdateQuery(row(2), row(0))
        Next

        Prod2 = Prod2TableAdapter.GetData

        For Each row As DataRow In Prod2.Rows
            ListBox1.Items.Add(row(0).ToString + " " + row(1).ToString + " " + row(2).ToString)
        Next


    End Sub

Open in new window


Time to test.....Run the project....Hit the button.....Check the data in the ListBox......


Hope it helps,

Giannis
0
 

Author Comment

by:skdan
ID: 39159448
Hi Giannis,

your tutorial is really excellent. It works great and it will be very helpful for my purposes.

Thank you very much :-)
0
 
LVL 23

Expert Comment

by:Ioannis Paraskevopoulos
ID: 39159538
Glad i could help...
0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

856 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