While Populating Dropdown Inside Gridview I Am Getting Error Below Error

Question

0.00/5 (No votes)

See more:

SQL

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The connection was not closed. The connection's current state is open.

Where i am wrong?

C#

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
  if (e.Row.RowType == DataControlRowType.DataRow)
  {
    conn.Open();

    var ddl = (DropDownList)e.Row.FindControl("ddlCompany1");

    SqlCommand cmd = new SqlCommand("SELECT CompanyName FROM  Company", conn);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);

    conn.Close();

    ddl.DataSource = ds;
    ddl.DataTextField = "CompanyName";
    ddl.DataValueField = "ID";
    ddl.DataBind();
    ddl.Items.Insert(0, new ListItem("Select", "0"));
  }
}

Posted 13-Dec-14 23:38pm

alok0000

Updated 13-Dec-14 23:41pm

Kornfeld Eliyahu Peter

v2

Add a Solution

Comments

Maciej Los 14-Dec-14 8:03am

Please, carefully read error message. ;)

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2014-12-14T02:07:00

Solution 1

The error message says that connection is still opened.

C#

//below line causes error message:
conn.Open();

You need to check connection state[^].

C#

if (conn.State==ConnectionState.Closed) {conn.Open();}

Posted 14-Dec-14 2:07am

Maciej Los

Comments

DamithSL 14-Dec-14 8:27am

Maciej, what if the connection status is one of this "Broken, Connnecting, Executing, Fetching" :)

Maciej Los 14-Dec-14 9:20am

In this case - nothing ;)

DamithSL 14-Dec-14 10:58am

5wd

Maciej Los 14-Dec-14 11:01am

Thank you, Damith (i hope it's your name) ;)

DamithSL 14-Dec-14 11:02am

Yes :)

Maciej Los 14-Dec-14 11:06am

I can't find its meaning. Do you know it?

DamithSL 14-Dec-14 11:12am

It means treasure.

Maciej Los 14-Dec-14 11:17am

Maciej name meaning ;)

DamithSL · Answer 2 · 2014-12-14T02:26:00

I would do as below,

C#

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
  if (e.Row.RowType == DataControlRowType.DataRow)
  {
      var ddl = (DropDownList)e.Row.FindControl("ddlCompany1");
      if(ddl !=null)
      {
          DataSet ds = GetData("SELECT CompanyName, ID FROM  Company");
          ddl.DataSource = ds.Tables[0];
          ddl.DataTextField = "CompanyName";
          ddl.DataValueField = "ID";
          ddl.DataBind();
          ddl.Items.Insert(0, new ListItem("Select", "0"));
      }
   }
}

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

Note that,
1. try to separate your UI code and the data access codes
2. Reusable methods
3. you haven't select ID column in your select statement but you set it as value field!
4. Connections handling is not easy when you have class level or global shared connection object, try to use "using blocks" and create the connection when you need and it will close the connection automatically.