Click here to Skip to main content
15,441,434 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi..

I have a table called Hikes. Well the data into it is inserted through an excel sheet from the .aspx page..

The columns goes as follows..


EmpID Name Jan-11 Feb-11 Mar-11

and so on it continues..

It has data of 3 years..

And now i have .aspx page where i can give empid.. on entering the empid ,based on it i need to fetch the data of that particular employee in a gridview..

The gridview should have the folowing

EmpID,Name and with that i need to show the hike amount and hike date(like Jan-12 , Feb-13)


Suppose if the starting salary is 6000 on Jan-11 and salary is hiked on Feb-12 as 11000 and Aug-13 as 16000 ..

now i need to show only those values from all the values..

i need to carry this to a new datatable and bind it to gridview..
how to do row wise distinct..?

Help me to proceed further
Posted
Updated 7-Nov-13 22:19pm
v3
Comments
ArunRajendra 8-Nov-13 1:10am    
Try using distinct.
Harsha24 8-Nov-13 1:22am    
yes..but here i need to do row wise distinct and show that ..how??

1 solution

hey harsha,
Try this code

C#
string conStr,sqlQuery;
int sal;
SqlConnection con = new SqlConnection(conStr);
SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = null;
DataTable dt = new DataTable();
con.Open();
cmd.Connection = con;

//make data for testing  #saltable for salaries and #salhike to store salary hikes
sqlQuery = "create table #saltable(empid varchar(5),empname varchar(10),jan11 int,feb11 int,mar11 int)";
cmd.CommandText = sqlQuery;
cmd.ExecuteNonQuery();
sqlQuery = "create table #salhike(empid varchar(5),hike int,date varchar(10))";
cmd.CommandText = sqlQuery;
cmd.ExecuteNonQuery();
sqlQuery = "insert into #saltable select '12','ram',10,10,20";
cmd.CommandText = sqlQuery;
cmd.ExecuteNonQuery();

sqlQuery = "SELECT empid,empname,jan11,feb11,mar11 from #salTable where empid='"+ txtItem.Text +"'";
da = new SqlDataAdapter(sqlQuery, con);
da.Fill(dt);
sal = Convert.ToInt32(dt.Rows[0][2].ToString());
for (int i = 0; i < dt.Rows.Count; i++)
    for (int j = 2; j < dt.Columns.Count; j++) //from third column to last            {
        //if there is hike in salary
        if (sal < Convert.ToInt32(dt.Rows[i][j].ToString()))
        {
            string hike = Convert.ToString(Convert.ToInt32(dt.Rows[i][j].ToString()) - sal);
            sal = Convert.ToInt32(dt.Rows[i][j].ToString());   //set this as new base value
            //insert difference in a table
            sqlQuery = "INSERT into #salHike(empid,hike,date)";
            sqlQuery += " SELECT '"+ txtItem.Text +"',"+ hike +",'"+ dt.Columns[j].ColumnName +"'";
            cmd.CommandText = sqlQuery;
            cmd.ExecuteNonQuery();
        }
    }
dt = new DataTable();

sqlQuery = "SELECT * from #salHike";
da = new SqlDataAdapter(sqlQuery, con);
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();


Output -

empid hike date
12 10 mar11
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900