CODEWITHSUNDEEP
sqlasp.netsql-server-2005drop-down-menu
HEEN
HEEN

Reputation: 4735

Add Values to dropdownlist on the basis of SQL Query

I have a dropdownlist whose values are binded by a query which is 

select emp_card_no, 
       emp_name + '-' 
                + cast(emp_card_no as varchar) 
                + '(' 
                + datename(MM,a.dt_of_leave) 
                + ' - ' 
                + cast(year(a.dt_of_leave)as varchar)
                +')' emp_name 
from emp_mst a 
where month(a.dt_of_leave) >= month(getdate())-1  
  and year(a.dt_of_leave) = 
        case when month(getdate())=1 then year(getdate())-1 
             else year(getdate()) 
        end
order by emp_name

whose results look something like this.

[![SQL output][1]][1]

Now what I want is, in Emp_name column, I want to add text as PROCESS OR PENDING after (April - 2016) on the basis of query which is below

select emp_mkey, * from emp_mon_day
where emp_mkey = 312
 and month = 4
 and year = 2016

If query returns any result then PROCESS otherwise PENDING.

NOTE

1st query column Emp_card_no is the Emp_mkey in emp_mon_day table.

Also see the code for binding dropdownlist

protected void funfillEmployee()
{
    DataTable DtCombo = new DataTable();
    string strdate = System.DateTime.Now.ToString("dd/MM/yyyy");

    DtCombo = ObjPriDal.ExecuteDataTable("select emp_card_no, emp_name + '-' + cast(emp_card_no as varchar)+ '(' + datename(MM,a.dt_of_leave)  + ' - ' + cast(year(a.dt_of_leave)as varchar)+')' emp_name  " +
                         "  from emp_mst a where month(a.dt_of_leave) >= month(getdate())-1  and  year(a.dt_of_leave)= case " +
                         "   when  month(getdate())=1 then year(getdate())-1 else year(getdate()) end order by emp_name ");

    cmbEmp_Name.DataTextField = "emp_name";
    cmbEmp_Name.DataValueField = "emp_card_no";
    cmbEmp_Name.DataSource = DtCombo;
    cmbEmp_Name.DataBind();
    cmbEmp_Name.Items.Insert(0, new ListItem("--Select--", "0"));
    DtCombo.Clear();
}

Let me know how to do that.

I am using SQL-server-2005

Upvotes: 2

Views: 42

Answers (1)

Thomas G
Thomas G

Reputation: 10226

Try something like this :

select a.emp_card_no, 
       a.emp_name + '-' 
                  + cast(a.emp_card_no as varchar) 
                  + '(' 
                  + datename(MM,a.dt_of_leave) 
                  + ' - ' 
                  + cast(year(a.dt_of_leave)as varchar)
                  +') '
                  +                
                    case when m.emp_mkey IS NULL 
                         then 'PENDING' 
                         else 'PROCESS' 
                    end                  
                  emp_name 
from emp_mst a 
LEFT JOIN emp_mon_day m ON m.Emp_mkey = a.Emp_card_no 
                        AND m.month = month(a.dt_of_leave) 
                        AND  m.year = year(a.dt_of_leave)
where month(a.dt_of_leave) >= month(getdate())-1  
  and year(a.dt_of_leave) = 
        case when month(getdate())=1 then year(getdate())-1 
             else year(getdate()) 
        end
order by emp_name

but you might have adapt that part 

AND m.month = month(a.dt_of_leave) AND  m.year = year(a.dt_of_leave)

as month and year are reserved words and I doubt you named your columns like this, but you did not specified other names

Upvotes: 2

Related Questions