vb.net - multiplying values from datatable

Question

I'm working in vb.net and in my program i have two datatables. What i want is to multiply quantity for some IZ in the second datatable with the quantity for the same IZ from first datatable. I want to do that for every IZ.

For example for IZ12554 it would be like 1*460=460 and 0,0025*460=1,15; for IZ10836 it would be 3*2000=6000; 1*2000=2000; 1*2000=2000 etc, and write those results in datatable 3.

DATATABLE 1 Capacity IZ Quantity ID Document ------------------------------------------- DM00006 IZ12554 460 20 PDN00084625 DM00007 IZ10836 2000 1 PDN00084983 DM00010 IZ12641 25200 1 PDN00084997 DM00011 IZ03941 940 1 PDN00084639 DM00087 IZ15137 50 12 PDN00084904 .... ....

DATATABLE 2 IZ MA Quantity Unit ---------------------------------- IZ12554 MA06196 1 KOS IZ12554 MA05545 0,0025 KOS IZ10836 MA06199 3 KOS IZ10836 MA06197 1 KOS IZ10836 MA00892 1 kos IZ12641 MA06199 1 KOS IZ12641 MA32200 0,04 m IZ03941 MA07639 0,0025 KOS IZ03941 MA00896 0,295 M IZ03941 MA06200 1 KOS IZ03941 MA00892 1 kos IZ15137 MA06200 1 KOS IZ15137 MA00557 0,0025 KOS IZ15137 MA00897 0,295 M ... ... DATATABLE 3 - RESULTS IZ MA Quantity Unit ---------------------------------- IZ12554 MA06196 460 KOS IZ12554 MA05545 1,15 KOS IZ10836 MA06199 6000 KOS IZ10836 MA06197 2000 KOS IZ10836 MA00892 2000 kos IZ12641 MA06199 25200 KOS IZ12641 MA32200 1000,8 m IZ03941 MA07639 2,35 KOS IZ03941 MA00896 277,3 M IZ03941 MA06200 940 KOS IZ03941 MA00892 940 kos IZ15137 MA06200 50 KOS IZ15137 MA00557 0,125 KOS IZ15137 MA00897 14,75 M ... ...

I searched for answer, but still don't know how to work with datatables, i'm a complete beginner at programming...

Thanks in advance!

You can easily find tutorials on how to complete a select statement and joint tables through the web... — Fjodr, Jul 07 '15 at 20:02
I agree with @Fjodr this would most likely be easier if you wrote your initial SQL Query to do a join and multiply for you, prior to creating the datatable. I would also venture that it would be much faster as well. — jradich1234, Jul 07 '15 at 20:18
Downvote is a bit harsh in my opinion. He might be doing it at runtime (DataTable being populated by user input or some file reading etc.) instead of doing it directly against a SQL table (thus not having the option of using `SELECT`), so LINQ may better suit his needs. — dotNET, Jul 07 '15 at 23:06

Dylan · Accepted Answer · 2015-07-08T19:56:49.690

1

You could try something similar to this:

DataTable DataTable3 = new DataTable();

DataColumn myDataColumn;
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "IZ";
DataTable3.Columns.Add(myDataColumn);

DataColumn myDataColumn;
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.String");
myDataColumn.ColumnName = "MA";
DataTable3.Columns.Add(myDataColumn);

DataColumn myDataColumn;
myDataColumn = new DataColumn();
myDataColumn.DataType = Type.GetType("System.Int32");
myDataColumn.ColumnName = "Sum_QTY";
DataTable3.Columns.Add(myDataColumn);
//ADD More columns to DataTable Here

foreach(DataRow row in DataTable1.Rows){
 foreach(DataRow row2 in DataTable2.Rows){
   if (row["IZ"].ToString()==row2["IZ"].ToString()){
    //Row3 is the row from Table 3
    row3 = DataTable3.NewRow();

    row3["IZ"]=row["IZ"].ToString();
    row3["MA"]=row2["MA"].ToString();
    row3["Sum_QTY"]=row2["Quantity"]*row["Quantity"];
    //ADD calculations here and add them to row3
    DataTable3.Rows.Add(row3);
  }
 }
}

edited Jul 08 '15 at 19:56

answered Jul 07 '15 at 21:04

Dylan

1,068
12
25

I get error when i put your code in: "System.ArgumentException: Column 'IZ' does not belong to table" But i created column "IZ" in datatable3... what could be wrong here? – kb2487 Jul 08 '15 at 08:10
It sounds like that column doesn't exist. Did you create the Datatable before trying to do that? – Dylan Jul 08 '15 at 16:59
Sorry my bad - i didn't create datatable3 at all, i just created Griedview with column "IZ", that's probably not enough? – kb2487 Jul 08 '15 at 18:06
I edited to include creating a sample DataTable3 if you wanted to look. – Dylan Jul 08 '15 at 18:14
Wow thanks! But now i only have column "IZ" in datatable3... i also want column "MA" (with all MA's) and column "SUM QTY" with sum quantity in datatable3. How can that be done? – kb2487 Jul 08 '15 at 19:21
Added that to the post. Might not be right. General logic to do it. – Dylan Jul 08 '15 at 19:57

score 1 · Answer 2 · edited May 23 '17 at 12:21

As the comments to the question suggest, doing this in the database may be more efficient, but you can use LINQ to DataSet to join the data and perform the calculations:

Dim result =
    From r2 In dt2.AsEnumerable()
    Join r1 In dt1.AsEnumerable() On r2.Field(Of String)("IZ") Equals r1.Field(Of String)("IZ")
    Select
        IZ = r2.Field(Of String)("IZ"),
        MA = r2.Field(Of String)("MA"),
        Quantity = r2.Field(Of Decimal)("Quantity") * r1.Field(Of Integer)("Quantity"),
        Unit = r2.Field(Of String)("Unit")

This gives you an IEnumerable of anonymous types containing the results you want. I don't know of a built-in way to convert that back to a DataTable (if you even need to), but you can do it with a little reflection. See this question for more details, but something like this should work:

<Extension>
Function ToDataTable(Of T)(items As IEnumerable(Of T)) As DataTable
    Dim dt = New DataTable()
    Dim props = GetType(T).GetProperties()
    For Each prop In props
        Dim dc = dt.Columns.Add(prop.Name, prop.PropertyType)
    Next
    For Each item In items
        Dim dr = dt.NewRow()
        For Each prop In props
            dr(prop.Name) = prop.GetValue(item)
        Next
        dt.Rows.Add(dr)
    Next
    Return dt
End Function

You can then:

Dim dt3 = result.ToDataTable()
' or ToDataTable(result) if you don't make it an extension method

Thanks, i tried that but i get error in Line 71 (equal sign is underline in my application): Compiler Error Message: BC30201: Expression expected. Source Error: Line 69: Line 70: Line 71: Dim result = Line 72: From r2 In dt2.AsEnumerable() Line 73: Join r1 In dt1.AsEnumerable() On r2.Field(Of String)("IZ") Equals r1.Field(Of String)("IZ") Is there some code missing or what? It may be stupid question but i'm really a beginner in this... — kb2487, Jul 08 '15 at 09:30
@kb2487 What version of Visual Studio are you using? I can't recreate that error here, but older versions of the VB.NET compiler (pre-VS2010, I think) required a continuation character at the end of each line of a multi-line statement - the `_` character. Also check that you have a reference to `System.Data.DataSetExtensions` (although I think that would be a different error). LINQ was added in VS2008, so before that this will not work at all. That's about all I can guess with the given information. Sorry. — Mark, Jul 08 '15 at 15:46

vb.net - multiplying values from datatable

2 Answers2