[Solved] ASP.Net GridView not displaying records based on DropDownList selection using C#

RichardS...

SOLVED CLOSED ACTIVE ANSWERED
User: RichardSa
Posted: on Mar 14, 2022 10:41 PM
Forum: Data Controls
Answer: 2
Views: 2750

Hello I am trying to display records from database based on DropdownList selection. But when I navigate to the page, nothing is displayed in the gridview, also nothing is displayed when I make a selection in the DropdownList

From the gridview user can also delete records based on selection.

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{ 
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        DataRowView dr = (DataRowView)e.Row.DataItem;
        if (ddlTables.SelectedValue.ToLower() == "team")
        {
            (e.Row.Cells[1].Controls[0] as Button).Visible = false;
        }
        else if (ddlTables.SelectedValue.ToLower() == "cards")
        {
            (e.Row.Cells[1].Controls[0] as Button).Visible = true;
        }
        else if (ddlTables.SelectedValue.ToLower() == "certificates")
        {
            (e.Row.Cells[1].Controls[0] as Button).Visible = false;
        }
        else if (ddlTables.SelectedValue.ToLower() == "reciepts")
        {
            (e.Row.Cells[1].Controls[0] as Button).Visible = false;
        }
 
        if (dr.Row.Table.Columns.Contains("image"))
        {
            if (dr["image"] != DBNull.Value)
            {
                string imageUrl = "data:image/jpg;base64," + Convert.ToBase64String((byte[])dr["image"]);
                (e.Row.FindControl("Image1") as Image).ImageUrl = imageUrl;
            }
        }
    }
    GridViewRow row = e.Row;
    List<TableCell> cells = new List<TableCell>();
    foreach (DataControlField column in GridView1.Columns)
    {
        if (row.Cells.Count > 0)
        {
            TableCell cell = row.Cells[0];
            row.Cells.Remove(cell);
            cells.Add(cell);
        }
    }
    row.Cells.AddRange(cells.ToArray());
}
protected void OnRowDeleting(object sender, GridViewDeleteEventArgs e)
{
    int Uid = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values[0]);
    string query = "";
    if (ddlTables.SelectedValue.ToLower() == "team")
    {
        query = "DELETE FROM Users WHERE Uid = @Uid";
    }
    if (ddlTables.SelectedValue.ToLower() == "cards")
    {
        query = "DELETE FROM CardTbl WHERE Uid = @Uid";
    }
 
    if (ddlTables.SelectedValue.ToLower() == "certificates")
    {
        query = "DELETE FROM CertTbl WHERE Uid = @Uid";
    }
 
    using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename =| DataDirectory |\\Dataregister.mdf; Integrated Security = True"))
    {
        using (SqlCommand cmd = new SqlCommand(query))
        {
            cmd.Parameters.AddWithValue("@Uid", Uid);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }
    }
}
 
protected void Recsearch_Click(object sender, EventArgs e)
{
    if (ddlTables.SelectedIndex > 0)
    {
        string query = "";
        if (ddlTables.SelectedValue.ToLower() == "Team")
        {
            query = "SELECT Uid,email,Role,Name,CreateDate FROM Users WHERE (CreatedBy=@CreatedBy OR CreatedBy = '')";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " AND CreateDate BETWEEN @From AND @To";
            }
        }
 
        if (ddlTables.SelectedValue.ToLower() == "cards")
        {
            query = "SELECT Uid,image,CreatedBy,Role,CreatedDate FROM CardTbl WHERE (CreatedBy=@CreatedBy OR CreatedBy = '')";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " AND CreatedDate BETWEEN @From AND @To";
            }
        }
 
        if (ddlTables.SelectedValue.ToLower() == "certificates")
        {
            query = "SELECT Uid,image,CreatedBy,Role,CreatedDate FROM CertTbl WHERE (CreatedBy=@CreatedBy OR CreatedBy = '')";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " AND CreatedDate BETWEEN @From AND @To";
            }
        }
 
        if (ddlTables.SelectedValue.ToLower() == "reciepts")
        {
            query = "SELECT Uid,image,CreatedBy,Name,CreatedDate FROM tablereceipt WHERE (CreatedBy=@CreatedBy OR CreatedBy = '')";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " AND CreatedDate BETWEEN @From AND @To";
            }
        }
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True"))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@CreatedBy", createby.Text);
                    if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
                    {
                        cmd.Parameters.AddWithValue("@From", TextBox1.Text);
                        cmd.Parameters.AddWithValue("@To", Dat.Text);
                    }
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                }
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

arjunv

0 3

Reply
ANSWER
User: arjunv
Replied: on Mar 15, 2022 04:42 AM
Report

RichardSa says:
protected void Recsearch_Click(object sender, EventArgs e)

   {

       if (ddlTables.SelectedIndex > 0)

       {

           string query = "";

           if (ddlTables.SelectedValue.ToLower() == "Team")

           {

               query = "SELECT Uid,email,Role,Name,CreateDate FROM Users WHERE (CreatedBy=@CreatedBy OR CreatedBy = '')";

               if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))

               {

                   query += " AND CreateDate BETWEEN @From AND @To";

               }

           }

           if (ddlTables.SelectedValue.ToLower() == "cards")

           {

               query = "SELECT Uid,image,CreatedBy,Role,CreatedDate FROM CardTbl WHERE (CreatedBy=@CreatedBy OR CreatedBy = '')";

               if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))

               {

                   query += " AND CreatedDate BETWEEN @From AND @To";

               }

           }

           if (ddlTables.SelectedValue.ToLower() == "certificates")

           {

               query = "SELECT Uid,image,CreatedBy,Role,CreatedDate FROM CertTbl WHERE (CreatedBy=@CreatedBy OR CreatedBy = '')";

               if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))

               {

                   query += " AND CreatedDate BETWEEN @From AND @To";

               }

           }

           if (ddlTables.SelectedValue.ToLower() == "reciepts")

           {

               query = "SELECT Uid,image,CreatedBy,Name,CreatedDate FROM tablereceipt WHERE (CreatedBy=@CreatedBy OR CreatedBy = '')";

               if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))

               {

                   query += " AND CreatedDate BETWEEN @From AND @To";

               }

           }

           DataTable dt = new DataTable();

           using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True"))

           {

               using (SqlCommand cmd = new SqlCommand(query))

               {

                   using (SqlDataAdapter sda = new SqlDataAdapter())

                   {

                       cmd.Connection = con;

                       cmd.Parameters.AddWithValue("@CreatedBy", createby.Text);

                       if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))

                       {

                           cmd.Parameters.AddWithValue("@From", TextBox1.Text);

                           cmd.Parameters.AddWithValue("@To", Dat.Text);

                       }

                       sda.SelectCommand = cmd;

                       sda.Fill(dt);

                   }

               }

           }

           GridView1.DataSource = dt;

           GridView1.DataBind();

       }

Please refer below modified code.

protected void Recsearch_Click(object sender, EventArgs e)
{
    if (ddlTables.SelectedIndex > 0)
    {
        string query = "";
        if (ddlTables.SelectedValue.ToLower() == "Team")
        {
            query = "SELECT Uid,email,Role,Name,CreateDate FROM Users WHERE (CreatedBy=@CreatedBy OR @CreatedBy = '')";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " AND CreateDate BETWEEN @From AND @To";
            }
        }
        if (ddlTables.SelectedValue.ToLower() == "cards")
        {
            query = "SELECT Uid,image,CreatedBy,Role,CreatedDate FROM CardTbl WHERE (CreatedBy=@CreatedBy OR @CreatedBy = '')";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " AND CreatedDate BETWEEN @From AND @To";
            }
        }
        if (ddlTables.SelectedValue.ToLower() == "certificates")
        {
            query = "SELECT Uid,image,CreatedBy,Role,CreatedDate FROM CertTbl WHERE (CreatedBy=@CreatedBy OR @CreatedBy = '')";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " AND CreatedDate BETWEEN @From AND @To";
            }
        }
        if (ddlTables.SelectedValue.ToLower() == "reciepts")
        {
            query = "SELECT Uid,image,CreatedBy,Name,CreatedDate FROM tablereceipt WHERE (CreatedBy=@CreatedBy OR @CreatedBy = '')";
            if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
            {
                query += " AND CreatedDate BETWEEN @From AND @To";
            }
        }
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\Dataregister.mdf;Integrated Security=True"))
        {
            using (SqlCommand cmd = new SqlCommand(query))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    cmd.Parameters.AddWithValue("@CreatedBy", createby.Text);
                    if (!string.IsNullOrEmpty(TextBox1.Text) && !string.IsNullOrEmpty(Dat.Text))
                    {
                        cmd.Parameters.AddWithValue("@From", TextBox1.Text);
                        cmd.Parameters.AddWithValue("@To", Dat.Text);
                    }
                    sda.SelectCommand = cmd;
                    sda.Fill(dt);
                }
            }
        }
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
}

dharmend...

0 5

Reply
ANSWER
User: dharmendr
Replied: on Mar 15, 2022 06:25 AM
Report

arjunv says:

cmd.Parameters.AddWithValue("@CreatedBy", createby.Text);

Uncomment the above line.

[Solved] ASP.Net GridView not displaying records based on DropDownList selection using C#

Company

Explore

Follow us

Contact Us