Select Query not filling my DataSet ASP.NET C#

Question

When I execute this SELECT query directly, it works. However, while debugging, I see my dataset is empty. What can the issue possibly be?

protected void Grid_ItemList_RowDataBound(object sender, GridViewRowEventArgs e)
        {

        if (e.Row.RowType == DataControlRowType.DataRow)
            {
            Connection con = new Connection();
            con.con = new SqlConnection(con.str);

            try
                {
                con.con.Open();
                con.cmd = new SqlCommand("Select Item_Code,Item_Name from Pharmacy_Item_M", con.con);
                var ddl = (DropDownList)e.Row.FindControl("ddlnames");
                SqlDataAdapter da = new SqlDataAdapter(con.cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                con.con.Close();
                ddl.DataSource = ds;    
                ddl.DataTextField = "ItemName";
                ddl.DataValueField = "ItemCode";
                ddl.DataBind();

                }
            catch (Exception ex)
                {
                log.Warn("Unable to open connection");
                }
            }
        }

I am following this tutorial.

@RajeevKumar No Exception. Its just empty after it fills the dataset. — divinediu, Apr 02 '14 at 11:30
@markpsmith True. But There it is empty before it reaches there even. — divinediu, Apr 02 '14 at 11:31
@sna2stha The con.str is pulling the connection string from my connection object. That is populated perfectly. — divinediu, Apr 02 '14 at 11:33
@divinediu, maybe obvious question, but still - how do you know it is empty? — Andrei, Apr 02 '14 at 11:33
@markpsmith Your edit worked for me. Please post it as an answer so I can mark it. — divinediu, Apr 02 '14 at 11:35
@Andrei It is working fine but my data table is still empty. Strange. I checked like [this](http://stackoverflow.com/questions/1337084/how-to-view-a-datatable-while-debugging) — divinediu, Apr 02 '14 at 11:36

Amitesh · Accepted Answer · 2014-04-03T06:51:38.827

May be this will help you

protected void GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        //Find the DropDownList in the Row
        DropDownList ddlnames = (e.Row.FindControl("ddlnames") as DropDownList);
        ddlRole.DataSource = GetData("Select Item_Code,Item_Name from Pharmacy_Item_M");
        ddlRole.DataTextField = "Item_Name";
        ddlRole.DataValueField = "Item_Code";
        ddlRole.DataBind();

        //Add Default Item in the DropDownList
        ddlnames.Items.Insert(0, new ListItem("Please select"));

        //Select the role of user in DropDownList
        string x = (e.Row.FindControl("lblnames") as Label).Text;
        ddlnames.Items.FindByValue(x).Selected = true;
    }        
}

and in GetData Function

 private DataSet GetData(string query)
{
    string conString = ConfigurationManager.ConnectionStrings["xxxx"].ConnectionString;
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection(conString))
    {
        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            cmd.Connection = con;
            da.SelectCommand = cmd;
            using (DataSet ds = new DataSet())
            {
                da.Fill(ds);
                return ds;
            }
        }
    }
}

Thanks a lot! On SelectedIndexChanged of my dropdown itemName, I want it to populate the _itemCode_ textbox in the same gridview row. How Do I go about this? — divinediu, Apr 03 '14 at 04:22
Your solution present in that link http://dotnetsolutionsbyvenkat.blogspot.in/2013/11/how-to-handle-dropdown-list-selected.html hope this will help you — Amitesh, Apr 03 '14 at 06:50

score 2 · Answer 2 · answered Apr 02 '14 at 12:20

2

Your value & text fields:

ddl.DataTextField = "ItemName";
ddl.DataValueField = "ItemCode";

should match the field names in the table select statement:

"Select Item_Code,Item_Name from...

answered Apr 02 '14 at 12:20

markpsmith

4,860
2
33
62

Select Query not filling my DataSet ASP.NET C#

2 Answers2