So data table 1 must be merged into data table 2 with the 3 column of data table 2 be a calculation of column 2 from table 1 and column 2 from data table 2.
I am assuming that data table 1 and 2 the first column is the primary key for both tables? In this case you have to iterate through all rows. Here is an example (simple console app)
static void Main(string[] args)
{
DataTable dt1 = new DataTable();
dt1.Columns.Add(new DataColumn("Name", typeof(string)));
dt1.Columns.Add(new DataColumn("Value", typeof(decimal)));
DataTable dt2 = new DataTable();
dt2.Columns.Add(new DataColumn("Name", typeof(string)));
dt2.Columns.Add(new DataColumn("Value", typeof(decimal)));
dt2.Columns.Add(new DataColumn("Calc", typeof(decimal)));
Random r = new Random();
for (int i = 0; i < 10; i++)
{
DataRow row = dt1.NewRow();
row[0] = "Row" + i.ToString();
row[1] = r.Next(1, 100);
dt1.Rows.Add(row);
}
Console.WriteLine("Data Table 1");
foreach(var row in dt1.Rows.OfType<DataRow>())
Console.WriteLine("{0} - {1}", row[0], row[1]);
for (int i = 0; i < 5; i++)
{
DataRow row = dt2.NewRow();
row[0] = "Row" + i.ToString();
row[1] = r.Next(1, 100);
row[2] = decimal.Zero;
dt2.Rows.Add(row);
}
Console.WriteLine("Data Table 2 Before Calculation");
foreach (var row in dt2.Rows.OfType<DataRow>())
Console.WriteLine("{0} - {1}", row[0], row[1]);
foreach (var row in dt1.Rows.OfType<DataRow>())
{
var dt2Row = dt2.Rows.OfType<DataRow>().FirstOrDefault(x => x[0].ToString().Equals(row[0].ToString()));
if (dt2Row == null)
{
dt2Row = dt2.NewRow();
dt2Row[0] = row[0];
dt2Row[1] = row[1];
dt2Row[2] = row[1];
dt2.Rows.Add(dt2Row);
}
else
if (dt2Row[1] is decimal && row[1] is decimal && dt2Row[2] is decimal)
dt2Row[2] = (decimal)row[1] + (decimal)dt2Row[1];
}
Console.WriteLine("Data Table 2 After Calculation");
foreach (var row in dt2.Rows.OfType<DataRow>())
Console.WriteLine("{0} - {1} - {2}", row[0], row[1], row[2]);
Console.WriteLine("Finished");
Console.ReadKey();
}
As you see above I create two data tables and fill them with information. Where if you notice i put more rows in data table 1. This was to illistrate lookup of a row and if it exists we do the calculation and if it does not exist (or null) the row
is then created with the calculation as the column 2 in the first data table.
DO NOT COPY PASTE INTO YOUR SOLUTION. THIS IS TEST CODE ONLY
Instead of copy pasting into your solution I recommend you create a console application like i did and just walk through the code (debug) to get a handle on the concept.
CarlzGuillen
Member
2 Points
18 Posts
Inser datatables data to another datatable VB.net
Apr 11, 2012 01:58 AM|LINK
Hello,
I have a datatable with 2 colummns and i have a second datatable with 3 colummns now, i need to pass the same rows i have into
my first datatable to my second one, but the the third colummn i need to calculate it with my second column. my result will be the calculation
of one variable with the data i have in my second columm of the table.
table DataTable data
nvanhaaster@...
Star
9209 Points
1484 Posts
Re: Inser datatables data to another datatable VB.net
Apr 11, 2012 02:45 AM|LINK
So data table 1 must be merged into data table 2 with the 3 column of data table 2 be a calculation of column 2 from table 1 and column 2 from data table 2.
I am assuming that data table 1 and 2 the first column is the primary key for both tables? In this case you have to iterate through all rows. Here is an example (simple console app)
static void Main(string[] args) { DataTable dt1 = new DataTable(); dt1.Columns.Add(new DataColumn("Name", typeof(string))); dt1.Columns.Add(new DataColumn("Value", typeof(decimal))); DataTable dt2 = new DataTable(); dt2.Columns.Add(new DataColumn("Name", typeof(string))); dt2.Columns.Add(new DataColumn("Value", typeof(decimal))); dt2.Columns.Add(new DataColumn("Calc", typeof(decimal))); Random r = new Random(); for (int i = 0; i < 10; i++) { DataRow row = dt1.NewRow(); row[0] = "Row" + i.ToString(); row[1] = r.Next(1, 100); dt1.Rows.Add(row); } Console.WriteLine("Data Table 1"); foreach(var row in dt1.Rows.OfType<DataRow>()) Console.WriteLine("{0} - {1}", row[0], row[1]); for (int i = 0; i < 5; i++) { DataRow row = dt2.NewRow(); row[0] = "Row" + i.ToString(); row[1] = r.Next(1, 100); row[2] = decimal.Zero; dt2.Rows.Add(row); } Console.WriteLine("Data Table 2 Before Calculation"); foreach (var row in dt2.Rows.OfType<DataRow>()) Console.WriteLine("{0} - {1}", row[0], row[1]); foreach (var row in dt1.Rows.OfType<DataRow>()) { var dt2Row = dt2.Rows.OfType<DataRow>().FirstOrDefault(x => x[0].ToString().Equals(row[0].ToString())); if (dt2Row == null) { dt2Row = dt2.NewRow(); dt2Row[0] = row[0]; dt2Row[1] = row[1]; dt2Row[2] = row[1]; dt2.Rows.Add(dt2Row); } else if (dt2Row[1] is decimal && row[1] is decimal && dt2Row[2] is decimal) dt2Row[2] = (decimal)row[1] + (decimal)dt2Row[1]; } Console.WriteLine("Data Table 2 After Calculation"); foreach (var row in dt2.Rows.OfType<DataRow>()) Console.WriteLine("{0} - {1} - {2}", row[0], row[1], row[2]); Console.WriteLine("Finished"); Console.ReadKey(); }As you see above I create two data tables and fill them with information. Where if you notice i put more rows in data table 1. This was to illistrate lookup of a row and if it exists we do the calculation and if it does not exist (or null) the row is then created with the calculation as the column 2 in the first data table.
DO NOT COPY PASTE INTO YOUR SOLUTION. THIS IS TEST CODE ONLY
Instead of copy pasting into your solution I recommend you create a console application like i did and just walk through the code (debug) to get a handle on the concept.
My .Net Blog
My Links are shrunk by t-ny.co
CarlzGuillen
Member
2 Points
18 Posts
Re: Inser datatables data to another datatable VB.net
Apr 11, 2012 04:21 AM|LINK
Thank you on first hand,
But actually im working on VB.net so theres the first fact
second thing is that i have a variable x for example and
and i have colum 1, colum 2 in datatable1
in datatable 2 i have colum1, colum2, colum3
neither of them are Pk for any tables, so basically
datatable2 is going to fill out from
Colum1 from datatable1
Colum2 it needs to be the result from variable ( x * colum2 of datatable1)
colum 3 will be from a parameter
basically something like this is what i need, hope anyone can help
CarlzGuillen
Member
2 Points
18 Posts
Re: Inser datatables data to another datatable VB.net
Apr 16, 2012 12:43 PM|LINK
Thank you anyway
i figure the problem already with a bucle for save the datatable values into variables and them saving them back into another,