I need help in comparing 2 datatables and then making column values null.
My Requirement -
1. I have 2 datatables -
datatable tableA = new datatable();
tableA = Method1(); //return type of Method1 is datatable.
datatable tableB = new datatable();
tableB = Method2(); //return type of Method2 is datatable.
2.
I have distinct values in tableA.
| IN_NUMBER |
| 122 |
| 12345 |
| 34567 |
| 123 |
| 456 |
I have multiple values in tableB.
| IN_NUMBER | W_AMT | WH_AMT | Remarks | SrNo |
| 122 | 10 | 100 | Test | 1 |
| 122 | 20 | 200 | Test | 2 |
| 12345 | 30 | 300 | verified | 3 |
| 12345 | 40 | 400 | verified | 4 |
| 34567 | 50 | 500 | test | 5 |
| 456 | 60 | 600 | approved | 6 |
| 123 | 70 | 700 | approved | 7 |
3. I need result as follows -
| IN_NUMBER | W_AMT | WH_AMT | Remarks |
| 122 | 10 | 100 | Test |
| 122 | |||
| 12345 | 30 | 300 | verified |
| 12345 | |||
| 34567 | 50 | 500 | test |
| 456 | 60 | 600 | approved |
| 123 | 70 | 700 | approved |
Logic -
I have to take IN_NUMBER Column values from tableA.
Say - 122
I need to check how many times it is appearing in tableB.
Here - 2 times.
Now, I need to keep the column values W_AMT, WH_AMT, Remarks in only one row for IN_NUMBER 122.
-------------------
For value 34567, I do not make any changes as it is appearing only once.
-------
Again for value 12345, I need to remove column values W_AMT, WH_AMT, Remarks from one row.
-------
In tableB, there is a SrNo (unique primary key).
----
How do I achieve this ?
Vikram AgrawalPosted Mar 17, 2015, 7:46 AM
Hi Riddhi try this
I think it will work fine
for (int index = 0; index < table.Rows.Count; index++)
{
flag= false;
var newrow = dtNew.NewRow();
string In_Number = table.Rows[index]["IN_Number"].ToString();
newrow["SrNo"] = index + 1 ;
newrow["IN_Number"] = In_Number ;
for( int i = 0 ; i< dtNew.Rows.Count; i++)
{
if (dtNew.Rows[i]["IN_Number"].ToString() == IN_Number )
{
newrow["W_Amt"] = String.Empty;
newrow["WH_Amt"] = String.Empty;
newrow["Remarks"] = String.Empty;
flag = True
break;
}
}
if (!flag) Then
{
newrow["W_Amt"] = table.Rows[index]["W_Amt"];
newrow["WH_Amt"] = table.Rows[index]["WH_Amt"];
newrow["Remarks"] = table.Rows[index]["Remarks"];
}
dtNew.Rows.Add(newrow);
}
still if it is not giving proper result then please debug this code and see why it is giving blank rows from 5 to 8
Thanks
Riddhi ValechaPosted Apr 8, 2015, 8:07 AM
I have already accepted Vikram's answer.....
mukesh kumarPosted Apr 8, 2015, 3:19 AM
Gowtham RajamanickamPosted Apr 5, 2015, 1:28 AM
Vikram AgrawalPosted Mar 19, 2015, 6:29 AM
Riddhi ValechaPosted Mar 19, 2015, 5:20 AM
THIS CODE IS RUNNING VERY WELL....
THANKS THANKS THANKS THANKS THANKS A TON....YOU SAVED ME.....
Riddhi ValechaPosted Mar 17, 2015, 7:14 AM
---
This is inserting blank rows...
Please let me know if you need any other information.
Vikram AgrawalPosted Mar 17, 2015, 6:35 AM
hi riddhi please provide the data of DataTable newDT.
I want to see what is the structure and what are the values its displaying
Riddhi ValechaPosted Mar 17, 2015, 6:26 AM
But, THis is inserting blank records in the datatable.
I don't want any blank record.
IN_NUMBER will never be null.
Vikram AgrawalPosted Mar 17, 2015, 6:21 AM
I have multiple values in tableB.
3. I need result as follows -
approved
pls give the o/p of newDT how it contains all the rows
Riddhi ValechaPosted Mar 17, 2015, 6:08 AM
It is giving IN_NUMBER.
It is making an issue with the following if-condition -
Whenever the IF-Condition results in false, it increases blank rows.
if (dtNew.Rows[i]["INWARD_NUMBER"].ToString() == table.Rows[index]["INWARD_NUMBER"].ToString())
------------
Also,
There is one IN_NUMBER - 11357, in which, the column values - W_AMT, WH_AMT, Remarks - are blank in database.
-----------
Vikram AgrawalPosted Mar 17, 2015, 5:40 AM
Riddhi ValechaPosted Mar 17, 2015, 5:34 AM
From this logic..
I got the desired result from Srno - 1,2,3,4,9,10.
For SrNo - 5,6,7,8 - Rows are blank.
What to do to fill those records?
Riddhi ValechaPosted Mar 17, 2015, 5:34 AM
From this logic..
I got the desired result from Srno - 1,2,3,4,9,10.
For SrNo - 5,6,7,8 - Rows are blank.
What to do to fill those records?
Vikram AgrawalPosted Mar 17, 2015, 4:38 AM
Use this code
private void button1_Click(object sender, Eventargs e)
{
table = new DataTable();
table =Method1; // Here return whole table instead of one column from method 1
#region AddSRNOColumnAtPositionZeroAndFillITWithSerialNumbers
DataTable dtNew =new DataTable();
dtNew.Columns.Add("SrNo");
dtNew.Columns.Add("IN_Number");
dtNew.Columns.Add("W_Amt");
dtNew.Columns.Add("WH_Amt");
dtNew.Columns.Add("Remarks");
for (int index = 0; index < table.Rows.Count; index++)
{
var newrow = dtNew.NewRow();
newrow["SrNo"] = index + 1 ;
for( int i = 0 ; i< dtNew.Rows.Count - 1;i++)
{
if (dtNew.Rows[i]["IN_Number"].ToString() == table.Rows[index]["IN_Number"].ToString() )
{
newrow["IN_Number"] = table.Rows[index]["IN_Number"];
newrow["W_Amt"] = String.Empty;
newrow["WH_Amt"] = String.Empty;
newrow["Remarks"] = String.Empty;
flag = True
break;
}
}
if (!flag) Then
{
newrow["IN_Number"] = table.Rows[index]["IN_Number"];
newrow["W_Amt"] = table.Rows[index]["W_Amt"];
newrow["WH_Amt"] = table.Rows[index]["WH_Amt"];
newrow["Remarks"] = table.Rows[index]["Remarks"];
}
dtNew.Rows.Add(newrow);
}
#endregion
//Here no need to Use DataView, you can use newDt for displaying this data.
}
Riddhi ValechaPosted Mar 17, 2015, 4:09 AM
private void button1_Click(object sender, Eventargs e)
{
table = new DataTable();
table =Method1;
#region AddSRNOColumnAtPositionZeroAndFillITWithSerialNumbers
DataColumn Col = table.Columns.Add("SrNo", typeof(System.Int32));
Col.SetOrdinal(0);
for (int index = 1; index <= table.Rows.Count; index++)
{
if (table.Rows[index - 1]["SrNo"].ToString().Trim().Length == 0)
{
table.Rows[index - 1]["SrNo"] = index;
}
}
#endregion
#region GetColumnValues
DataView dv = new DataView(table);
DataTable dt = dv.ToTable(true, "IN_NUMBER"); //distinct IN_NUMBER
#endregion
}
---------
From here, I need to
1. access each record from datatable-dt,
2. check how many times each IN_NUMBER is present in datatable-table.
3. If more than once, then select all SrNo from column.
4. Keep W-AMT, WH_AMT and Remarks column in SrNo which is minimum(say for IN-NUMBER - 122, I need to keep columns values W-AMT, WH_AMT and Remarks for SrNo - 1).
5.Remove the column values W-AMT, WH_AMT and Remarks for SrNo - 2.
6. If IN_NUMBER is present say 3 times, then I need to remove the column values W-AMT, WH_AMT and Remarks for SrNo - 2 and SrNo -3.
---
---------------------
Vikram AgrawalPosted Mar 17, 2015, 3:36 AM
Hi Riddhi,
actually this code is creating one more DataTable i.e. dtNew which is created based on old DataTable and for all duplicate IN_Numbers we are providing empty values.
your TableB contains all the columns which is returning from the method2()
so use TableB inspite of TableA.
can you please share your button_Click code and Method1() code here? so we will be able to help more.
Thanks
Riddhi ValechaPosted Mar 17, 2015, 3:23 AM
My 2 datatables are as follows -
tableA = new Datatable();
tableA = Method1(); //return type of method is datatable.
Now,
In front-end on button click event, I have written -
DataView dv = new DataView(table);
DataTable dt = dv.ToTable(true, "IN_NUMBER");
in datatable dt, there is just one column - INWARD_NUMBER.
Other columns - W_AMT, WH_AMT and Remarks in other table.
The code here is giving an exception - table does not contain column W_AMT.
Please explain your logic... so that I can proceed further...
Vikram AgrawalPosted Mar 17, 2015, 3:03 AM
Well, try with this code it will give you desired result
DataTable dtOld = new DataTable();
DataTable dtNew As New DataTable ();
bool flag=false;
dtNew.Columns.Add("IN_Number");
dtNew.Columns.Add("W_Amt");
dtNew.Columns.Add("WH_Amt");
dtNew.Columns.Add("Remarks");
foreach(DataRow row In dtOld.Rows)
{
var newrow = dtNew.NewRow();
for( int i = 0 ; i< dtNew.Rows.Count - 1;i++)
{
if (dtNew.Rows[i]["IN_Number"].ToString() == row["IN_Number"].ToString() )
{
newrow["IN_Number"] = row["IN_Number"];
newrow["W_Amt"] = String.Empty;
newrow["WH_Amt"] = String.Empty;
newrow["Remarks"] = String.Empty;
flag = True
break;
}
}
if (!flag) Then
{
newrow["IN_Number"] = row["IN_Number"];
newrow["W_Amt"] = row["W_Amt"];
newrow["WH_Amt"] = row["WH_Amt"];
newrow["Remarks"] = row["Remarks"];
}
dtNew.Rows.Add(newrow);
}
//Now your dtNew is ready with your desired result.
Riddhi ValechaPosted Mar 17, 2015, 2:25 AM
tableA = new Datatable();
tableA = Method1(); //return type of method is datatable.
Now,
In front-end on button click event, I have written -
DataView dv = new DataView(table);
DataTable dt = dv.ToTable(true, "IN_NUMBER");
-------
So, in datatable dt, I have all unique IN_NUMBER.
--
Total rows in datatable dt is less and total rows in datatable table is more...
There are 3 IN_NUMBERS that are present 3 times, in all the rows, column values
W_AMT, WH_AMT and Remarks are there.
------
I am working on it.
I am not getting how to get total records from datatable table where IN_NUMBER (say 122) is present.
--------
Then, I can make values null using SrNo column - since it is unique.
Jignesh TrivediPosted Mar 17, 2015, 2:09 AM
Same thing is happening in my previous c# code right?
Riddhi ValechaPosted Mar 17, 2015, 2:02 AM
tableA = new Datatable();
tableA = Method1(); //return type of method is datatable.
Now,
In front-end on button click event, I have written -
DataView dv = new DataView(table);
DataTable dt = dv.ToTable(true, "IN_NUMBER");
-------
So, in datatable dt, I have all unique IN_NUMBER.
--
Requirement -
I have to access all IN_NUMBERS from datatable dt, (say I start with 122).
I have to find out how many times IN_NUMBER - 122 is present in tableA. (Here it is two times).
If IN_NUMBER is present just once, then I don't have to do anything.
If IN_NUMBER is present more than one time (say 2 times, 3 times), then I have to show the W_AMT, WH_AMT, and Remarks columns in only 1 row.
Eg- IN_NUMBER 122 is present 2 times in tableA (SrNo - 1 and 2). So, Columns W_AMT, WH_AMT, and Remarks should be displayed against only one row (say SrNo -1)
and should be null for other row (say SrNo - 2).
------------
Same for IN_NUMBER 12345.
So, Columns W_AMT, WH_AMT, and Remarks should be displayed against only one row (say SrNo -3)
and should be null for other row (say SrNo - 4).
Jignesh TrivediPosted Mar 17, 2015, 1:57 AM
Hi,
you want to same result in SQL in single query...
try...
Create table #Temp
(
id int,
IN_NUMBER varchar(50),
W_AMT varchar(20),
WH_AMT varchar(30),
Remarks varchar(30)
)
insert into #Temp values (1,'122', '10', '100', 'Test')
insert into #Temp values (2,'122', '20', '200', 'Test')
insert into #Temp values (3,'12345', '30', '300', 'verified')
insert into #Temp values (4,'12345', '40', '400', 'verified')
insert into #Temp values (5,'34567', '50', '500', 'test')
insert into #Temp values (6,'456', '60', '600', 'approved')
insert into #Temp values (7,'123', '70', '700', 'approved')
select a.id, IN_NUMBER,
case when p.id is null then '' else a.W_AMT end as W_AMT,
case when p.id is null then '' else a.WH_AMT end as WH_AMT,
case when p.id is null then '' else a.Remarks end as Remarks
from #temp a Left outer join (
select min(id) as id from #temp
group by IN_NUMBER) p on a.id = p.id
hope this will help you.
Jignesh TrivediPosted Mar 17, 2015, 1:49 AM
I am not getting your point can please elaborate?
Riddhi ValechaPosted Mar 17, 2015, 1:41 AM
Yes... I need the solution in front-end....
@Jignesh, I tried your solution, I am almost near to the result.
The issue is -
I am not getting the number of times one IN_NUMBER is present.
Eg-IN_NUMBER - 122 is present two times.
So, I need to keep the columns (W_AMT, WH_AMT, Remarks) values in just one row.
and remove from the second row.
I am stuck up there !!
PLease guide...
THanks a ton...
Vikram AgrawalPosted Mar 17, 2015, 12:44 AM
Hi Riddhi Valecha,
if you want in SQL then try this script it will give you result as per your requirement. Made some changes as you needed.
DECLARE @TOTROWS INT,@IDX INT=1
DECLARE @IN_NUMBER INT,@W_AMT VARCHAR(100)
CREATE TABLE #TEMP (ID INT IDENTITY,IN_NUMBER INT,W_AMT VARCHAR(100))
CREATE TABLE #TMP (IN_NUMBER INT,W_AMT VARCHAR(100))
INSERT INTO #TEMP (IN_NUMBER,W_AMT)
SELECT IN_NUMBER,W_AMT FROM TABLE_B
SET @TOTROWS=@@ROWCOUNT
WHILE(@IDX<=@TOTROWS)
BEGIN
SELECT @IN_NUMBER = IN_NUMBER,@W_AMT = W_AMT FROM #TEMP WHERE ID=@IDX
IF NOT EXISTS(SELECT IN_NUMBER FROM #TMP WHERE IN_NUMBER=@IN_NUMBER)
INSERT INTO #TMP (IN_NUMBER,W_AMT) VALUES (@IN_NUMBER,@W_AMT)
ELSE
INSERT INTO #TMP (IN_NUMBER,W_AMT) VALUES (@IN_NUMBER,NULL)
SET @IDX+=1
END
SELECT * FROM #TMP -- THIS TABLE CONTAINS YOUR DESIRED RESULT
DROP TABLE #TEMP
DROP TABLE #TMP
Thanks
Jignesh TrivediPosted Mar 17, 2015, 12:37 AM
Hi,
Try following code
DataTable dt = new DataTable();
dt.Columns.Add("IN_NUMBER");
dt.Columns.Add("W_AMT");
dt.Columns.Add("WH_AMT");
dt.Columns.Add("Remarks");
dt.Columns.Add("SrNo ");
dt.Rows.Add(new object[] { "122", "10", "100", "Test", "1" });
dt.Rows.Add(new object[] { "122", "20", "200", "Test", "2" });
dt.Rows.Add(new object[] { "12345", "30", "300", "verified", "3" });
dt.Rows.Add(new object[] { "12345", "40", "400", "verified", "4" });
dt.Rows.Add(new object[] { "34567", "50", "500", "test", "5" });
dt.Rows.Add(new object[] { "456", "60", "600", "approved", "6" });
dt.Rows.Add(new object[] { "123", "70", "700", "approved", "7" });
string IN_NUMBER = Convert.ToString(dt.Rows[0]["IN_NUMBER"]);
for (var i = 1; i <= dt.Rows.Count - 1; i++)
{
if (IN_NUMBER == Convert.ToString(dt.Rows[i]["IN_NUMBER"]))
{
dt.Rows[i]["W_AMT"] = "";
dt.Rows[i]["WH_AMT"] = "";
dt.Rows[i]["Remarks"] = "";
}
IN_NUMBER = Convert.ToString(dt.Rows[i]["IN_NUMBER"]);
}
hope this will help you.
Jignesh TrivediPosted Mar 17, 2015, 12:09 AM