You must Sign In to post a response.
  • Category: Visual Studio

    Displaying the data in gridview if selected all form drop down list

    Below is my code with two drop downs one for selecting the batch and other for selecting the user now to this i had added another drop down named status with done and not done when user selects batch corresponding users that are belonging to that batch are displayed in second drop down to this list i had to add another one name named all when user selects all after selecting batchs all the users data should be displayed if we select all below is my aspx and code behind and if the selected status is done only the records that are done should be displayed in gridview and if the selected status is notdone only the records that are notdone should be displayed in gridview can any one please help me out its urgent

    <%@ Page Title="" Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="BatchUSer.aspx.cs" Inherits="Default2" %>

    <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

    <div id="main" style="background-color: #E2E6E9;">
    <center> <h3>REVIEW WORKS</h3></center>
    <table id="tblmodule" style="background-color: #E2E6E9; height: 100%; width: 901px;">
    <tr>
    <td style="text-align: right; width: 260px;">
    <label><strong>Select Batch</strong></label><asp:Label ID="lblBatchErr" runat="server" Text="*" ForeColor="Red" /></td>
    <td style="text-align: left; width: 99px;">
    <asp:DropDownList ID="ddlbatchname" runat="server" OnSelectedIndexChanged="ddlbatchname_SelectedIndexChanged" Width="120px" AutoPostBack="true" style="margin-left: 0px"></asp:DropDownList>
    </td>
    <td style="text-align: right; width: 117px;" colspan="2">
    <label><strong>Select User</strong></label><asp:Label ID="lblusererr" runat="server" Text="*" ForeColor="Red" /></td>
    <td style="text-align: left; width: 159px;" colspan="2">
    <asp:DropDownList ID="ddluser" runat="server" AutoPostBack="true" Width="120px" >
    </asp:DropDownList>
    </td>
    <td style="text-align: right; height: 22px; width: 99px;">
    <label><strong>Status</strong></label><asp:Label ID="Label1" runat="server" Text="*" ForeColor="Red" />
    </td>
    <td style="text-align: right; height: 22px; width: 99px;">
    <asp:DropDownList ID="ddlstatus" runat="server" AutoPostBack="true" Width="120px" style="margin-left: 5px" OnSelectedIndexChanged="ddlstatus_SelectedIndexChanged" >
    <asp:ListItem Value="0">--Select--</asp:ListItem>
    <asp:ListItem Value="1">Done</asp:ListItem>
    <asp:ListItem Value="2">Not Done</asp:ListItem>
    </asp:DropDownList>
    </td>
    <td style="text-align: left">
    <asp:Button ID="btngo" runat="server" OnClick="btngo_Click" Text="GO" style="margin-left: 0px"/>

    </td>
    </tr>
    <tr>
    <td style="text-align: center; height: 24px;" colspan="3">
    </td>
    <td style="text-align: center; height: 24px;" colspan="2">
    </td>
    <td style="text-align: left; height: 24px;" colspan="2">
    <asp:Label ID="lblerrmsg" runat="server" ForeColor="Red"></asp:Label>
    </td>
    <td> <strong> <asp:Label ID="lblmandatory" runat="server" Text="*Fields are mandatory" ForeColor="Red"></asp:Label></strong></td>
    </tr>

    <tr>
    <td colspan="7">
    <asp:GridView ID="grdbatch" runat="server" Width="100%" CssClass="mydatagrid" HeaderStyle-CssClass="header" OnRowDataBound="grdbatch_RowDataBound" RowStyle-CssClass="rows" AutoGenerateColumns="false"
    PageSize="10" OnPageIndexChanging="grdbatch_PageIndexChanging" AllowPaging="true" EmptyDataText="No Records Available">
    <Columns>
    <asp:TemplateField HeaderText="S.No." Visible="false">
    <ItemTemplate>
    <asp:Label ID="lblID" runat="server" Text='<%#Bind("ReviewStatus") %>'></asp:Label>
    </ItemTemplate>
    </asp:TemplateField>
    <asp:BoundField DataField="CreatedDate" HeaderText="Date" />
    <asp:BoundField DataField="UserID" HeaderText="Trainee" />
    <asp:BoundField DataField="AuditoName" HeaderText="AudioName" />
    <asp:BoundField DataField="LineCount" HeaderText="LineCount" />
    <asp:HyperLinkField DataTextField="HyperLink" DataNavigateUrlFields="AudioID,UserID" DataNavigateUrlFormatString="~/QaMarks.aspx?AudioID={0}&UserId={1}" HeaderText="Status" ItemStyle-Width = "150" />
    </Columns>
    </asp:GridView>
    </td>
    </tr>

    <tr>
    <td style=" text-align: right" colspan="7">
    </td>
    </tr>

    </table>
    </div>
    </asp:Content>

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;

    public partial class Default2 : System.Web.UI.Page
    {
    Admin adm;
    SqlConnection con = new SqlConnection(@"server=EHSD208; user id=sa; password=Access#123;database=MTTrainingTest");
    protected void Page_Load(object sender, EventArgs e)
    {
    lblerrmsg.Text = "";
    if (!Page.IsPostBack)
    {
    batch();
    }
    }
    protected void batch()
    {
    SqlCommand cmd = new SqlCommand("select * from Batches ORDER BY ID", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    ddlbatchname.DataSource = dt;
    ddlbatchname.DataTextField = "BatchNo";
    ddlbatchname.DataValueField = "ID";
    ddlbatchname.DataBind();
    ddlbatchname.Items.Insert(0, new ListItem("-- Select --", "0"));
    ddluser.Items.Insert(0, new ListItem("-- Select --", "0"));
    }
    protected void ddlbatchname_SelectedIndexChanged(object sender, EventArgs e)
    {
    lblerrmsg.Text = "";
    int ModuleId = int.Parse(ddlbatchname.SelectedItem.Value);
    if (ModuleId > 0)
    {
    //string query = string.Format("select EmpID, UserID from Users where BatchName = {0} and state=1", "'"+ModuleId+"'");
    string query = string.Format("select EmpID, UserID from Users where BatchName = {0} and state=1", "'" + ModuleId + "'");
    BindDropDownList(ddluser, query, "UserID", "EmpID", "--Select--","ALL");
    }
    else
    {
    string query = string.Format("select EmpID, UserID from Users where BatchName = {0} and state=1", "'" + ModuleId + "'");
    BindDropDownList(ddluser, query, "UserID", "EmpID", "---Select---","ALL");
    }
    }
    private void BindDropDownList(DropDownList ddl, string query, string text, string value, string defaultText,string DEfault)
    {
    lblerrmsg.Text = "";
    con.Open();
    SqlCommand cmd = new SqlCommand(query, con);
    using (SqlDataAdapter sda = new SqlDataAdapter())
    {
    cmd.Connection = con;
    ddl.DataSource = cmd.ExecuteReader();
    ddl.DataTextField = text;
    ddl.DataValueField = value;
    ddl.DataBind();
    con.Close();
    }
    ddl.Items.Insert(0, new ListItem(defaultText, "1"));
    ddl.Items.Insert(0, new ListItem(DEfault, "0"));
    }

    public void Bindgrid()
    {
    lblerrmsg.Text = "";
    string query = "select Mts.LineCount,Mts.AudioID,Mts.ReviewStatus,convert(varchar(10), mts.CreatedDate,101) as CreatedDate,u.UserID,si.AuditoName from MTWorkStatus Mts,users u,subjectItems si where " +
    "u.BatchName='" + ddlbatchname.SelectedValue + "' and u.empid='" + ddluser.SelectedValue + "' and mts.TraineeID=u.Empid and si.ID= Mts.AudioID";
    SqlCommand cmd = new SqlCommand(query, con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    dt.Columns.Add("HyperLink", typeof(string));
    // dtfill.Columns.Add("httppath", typeof(string));
    foreach (DataRow row in dt.Rows)
    {
    row["HyperLink"] = "NOT DONE";
    }
    for (int i = 0; i <= dt.Rows.Count - 1; i++)
    {
    string ReviewStatus = dt.Rows[i]["ReviewStatus"].ToString();
    if (ReviewStatus == "True")
    {
    dt.Rows[i]["HyperLink"] = "DONE";
    }
    }
    dt.AcceptChanges();
    grdbatch.DataSource = dt;
    grdbatch.DataBind();
    }
    protected void ddluser_SelectedIndexChanged(object sender, EventArgs e)
    {
    lblerrmsg.Text = "";

    }
    protected void grdbatch_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
    lblerrmsg.Text = "";
    Bindgrid();
    grdbatch.PageIndex = e.NewPageIndex;
    grdbatch.DataBind();
    }
    protected void btngo_Click(object sender, EventArgs e)
    {
    lblerrmsg.Text = "";
    if (ddlbatchname.SelectedValue == "0" && ddluser.SelectedValue == "0")
    {
    lblerrmsg.Text = "Please Enter (*) Fields";
    return;
    }
    if (ddlbatchname.SelectedValue == "0")
    {
    lblerrmsg.Text = "Please Select Batch";
    return;
    }
    if (ddluser.SelectedValue == "0")
    {
    lblerrmsg.Text = "Please Select User";
    return;
    }
    Bindgrid();
    }
    protected void grdbatch_RowDataBound(object sender, GridViewRowEventArgs e)
    {
    foreach (TableCell tc in e.Row.Cells)
    {
    tc.BorderStyle = BorderStyle.None;
    tc.BorderWidth = 0;
    tc.BorderColor = System.Drawing.Color.Transparent;
    }
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
    Label lblID = (Label)e.Row.FindControl("lblID");
    string qry = "SELECT m.AudioID, m.Completed,m.ReviewStatus FROM MTWorkStatus m , SubjectItems s where m.Completed='True' and m.ReviewStatus='True' and m.cretedby='" + ddluser.SelectedItem.Text + "'";
    SqlCommand cmd = new SqlCommand(qry, con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    for (int i = 0; i < dt.Rows.Count; i++)
    {
    string status = dt.Rows[i]["ReviewStatus"].ToString();
    if (lblID.Text == status)
    {
    e.Row.BackColor = System.Drawing.Color.LightGray;
    e.Row.Enabled = false;
    e.Row.ToolTip = "This Record is Completed";
    }
    }
    }
    }
    protected void ddlstatus_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
    }
    Now when user selects All from second dropdown and status as done from third drop down all the users in that batch with their status done should be displayed in gridview when selected not done all the user with their status notdone should be displayed in gridview if selected only particular user and done or not done they should be displayed how can i do this
  • #766455
    Hi

    Include the Review status column in your Query.

    try to changes following code need.


    create table Users
    (
    EmpID int primary key identity(1,1),
    UserID int ,
    BatchName varchar(40),
    )

    create table MTWorkStatus
    (
    TraineeID int,
    LineCount int,
    AudioID int,
    ReviewStatus varchar(40),
    CreatedDate datetime,
    Completed varchar(40),
    cretedby varchar(40)
    )

    select * from subjectItems

    create table subjectItems
    (
    AuditoName varchar(40)
    )
    alter table subjectItems add id varchar(40)

    update subjectItems set id=1

    create table Batches
    (
    ID int primary key identity(1,1),
    BatchNo varchar(40)
    )

    Insert into Batches values('9-11'),('11-1'),('1-3')
    Insert into subjectItems values('A'),('B'),('C')
    Insert into MTWorkStatus values(1,1,1,'A',GETDATE(),'ACT',1)
    Insert into MTWorkStatus values(2,2,2,'B',GETDATE(),'ACT',2)

    Insert into Users values(1,'Batch1'),(2,'Batch2')

    Insert into Users values(1,1,1),(2,2,2)

    alter table USERS add state int






    Server Side Code



    public void Bindgrid()
    {
    lblerrmsg.Text = "";
    string query = "select Mts.LineCount,Mts.AudioID,Mts.ReviewStatus,convert(varchar(10), mts.CreatedDate,101) as CreatedDate,u.UserID,si.AuditoName from MTWorkStatus Mts,users u,subjectItems si where " +
    "u.BatchName='" + ddlbatchname.SelectedValue + "' and u.empid='" + ddluser.SelectedValue + "' and Reviewstatus='" + ddlstatus.SelectedItem.Text + "' and mts.TraineeID=u.Empid and si.ID= Mts.AudioID";
    SqlCommand cmd = new SqlCommand(query, con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    dt.Columns.Add("HyperLink", typeof(string));
    // dtfill.Columns.Add("httppath", typeof(string));
    foreach (DataRow row in dt.Rows)
    {
    row["HyperLink"] = "NOT DONE";
    }
    for (int i = 0; i <= dt.Rows.Count - 1; i++)
    {
    string ReviewStatus = dt.Rows[i]["ReviewStatus"].ToString();
    if (ReviewStatus == "True")
    {
    dt.Rows[i]["HyperLink"] = "DONE";
    }
    }
    dt.AcceptChanges();
    grdbatch.DataSource = dt;
    grdbatch.DataBind();
    }


    Name : Dotnet Developer-2015
    Email Id :kumaraspcode2009@gmail.com

    'Not by might nor by power, but by my Spirit,' says the LORD Almighty.

  • #766460
    Hi,

    You are asking several questions

    1) based on batch dropdown selection you want to display users in dropdownlist.

    OnDropdownlist selectedIndex changed event bind the user details with batch id as input parameter.

    protected void ddlbatch_OnSelectedIndexChanged(object sender, EventArgs e)
    {
    DataSource ds=//get data from db, pass batch id from dropdownlist as input parameter and get the user details from database
    ddlUser.DataSource=ds;
    ddlUser.DataTextField="UserName";
    ddlUser.DataValueField="UserId";
    ddlUser.DataBind();
    }


    2) based on batch, user, status selection you want to show the result in gridview. for that you have to pass all 3 as input parameter and get the data from database and bind the same to gridview.


    protected void btnGo_Click(object sender, EventArgs e)
    {
    BindGrid();
    }

    protected void BindGrid()
    {
    DataSet ds=//pass ddlbatch, ddlUser, ddlStatus values as input parameters and fetch data from database.
    gv.DataSource=ds;
    gv.DataBind();
    }

    If you want to know how to make StoredProcedure for passing input parameters,

    create procedure proc_name
    (
    @batch varchar(100)=null,
    @user varchar(100)=null,
    @status varchar(100)=null
    )
    as
    begin
    select TextField, ValueField
    from tablename
    where (Col1 = @batch OR @batch IS NULL)
    and (Col2 = @user OR @user IS NULL)
    and (Col3 = @status OR @status IS NULL)
    end

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments