Datareader will not populate dropdownlist

Topics: Data Access Application Block, General discussion
Jun 29, 2010 at 9:12 AM

Hi All,

hopefully someone can help me out with this one - I am trying to incorporate the Data Access Block into a new application I am creating. This is my first encounter with a code block.

I want to use the datareader to populate my drop down - which is something I am able to do in ADO.NET ..

private Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>("ApplicationDB");

    protected void Page_Load(object sender, EventArgs e)
    {
        //Get Location for drop down
        using (IDataReader dr = _db.ExecuteReader("sp_getLocations"))
        {
            while (dr.Read())
            {
                ddlLocation.DataSource = dr;
                ddlLocation.Text = dr["Location"].ToString();
                ddlLocation.SelectedValue = dr["LocationID"].ToString();
                ddlLocation.DataBind();
            }
        }
    }
But I am getting the following error - InvalidOperationException was unhandled by user code: Data source is an invalid type. It must be either an IListSource, IEnumerable or IDataSource. 
Can someone please tell me how to get this working or if this is even possible ?
Thanks
Jun 29, 2010 at 10:28 AM

Hi,

This has already been answered in this thread http://entlib.codeplex.com/Thread/View.aspx?ThreadId=215171.

To re-iterate the resolution here's the following.


This is due to a fix made for a bug in EntLib 3.0.  If you look at the source code for entlib 5.0, the ExecuteReader now wraps first the datareader instance to an instance of RefCountingDataReader.  More info on this can be found in this thread.

 

So, to sum up the workaround, you would create an extension method like this:

<System.Runtime.CompilerServices.Extension()> _
 Public Function AsDataReader(ByVal reader As IDataReader) As IDataReader
        Return DirectCast(reader, RefCountingDataReader).InnerReader
End Function

Your code now  will look like this:

Dim strSQL As String = "SELECT ..."
Dim db As Database = DatabaseFactory.CreateDatabase() 
DropDownList1.DataSource = db.ExecuteReader(db.GetSqlStringCommand(strSQL)).AsDataReader()
DropDownList1.DataBind()

Gino Terrado
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Jun 29, 2010 at 11:07 AM

Hi Gino,

thankyou for your reply.

I had found some other posts on this issue and I tried to create an extension method in c# without much success.

However, going by what I've read I've come up with ...

private Database _db = EnterpriseLibraryContainer.Current.GetInstance<Database>("EMSDB");

    protected void Page_Load(object sender, EventArgs e)
    {
        //Get Location for drop down
        RefCountingDataReader reader = (RefCountingDataReader)_db.ExecuteReader("sp_getLocations");
        if (((SqlDataReader)reader.InnerReader).HasRows)
        {
            SqlDataReader dr = (SqlDataReader)reader.InnerReader;
            while (dr.Read())
            {
                ddlLocation.DataSource = dr;
                //ddlLocation.SelectedValue = dr["LocationID"].ToString();
                //ddlLocation.Text = dr["Location"].ToString();
                ddlLocation.DataBind();
            }
        }
    }

But now when I look at my location drop down list it is populated with the text 'system.data.common.datarecordinternal' - where am I going wrong?

Jun 29, 2010 at 11:57 AM

Try setting the DataTextField/DataValueField property of your dropdownlist control with the column name from your DataReader object.

Jun 29, 2010 at 12:32 PM

Thankyou, this is what worked for me in the end ....

protected void Page_Load(object sender, EventArgs e)
    {
        //Get Location for drop down
        RefCountingDataReader reader = (RefCountingDataReader)_db.ExecuteReader("sp_getLocations");

        if (((SqlDataReader)reader.InnerReader).HasRows)
        {
            SqlDataReader dr = (SqlDataReader)reader.InnerReader;

            do
            {
                ddlLocation.DataSource = dr;
                ddlLocation.DataValueField = "Location";
                ddlLocation.DataTextField = "LocationID";
                ddlLocation.DataBind();
            } 
            while (dr.Read());
        }
    }

Normally I just use a a 'while (dr.Read())' - but I was only getting 3 listItems in the dropdownlist even though there are 4 records in the DB.

Do you know what would affect this?

Jun 29, 2010 at 1:07 PM

Unfortunately, I'm unable to reproduce your problem, It works fine in my end.

Jun 29, 2010 at 2:07 PM

I think its related to something on my setup here as I got the same results through ADO.NET.

At least its working. Thanks again for all your help.

Jul 1, 2010 at 2:08 AM

One very important thing you should add in your code is to close your datareader or enclose it in a using statement to avoid screwing up your connection.

 

Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@Avanade.com