DataBind to DropDownList Problem "The IListSource does not contain any data sources"

  • Thread starter Thread starter Assimalyst
  • Start date Start date
A

Assimalyst

Hi,

I'm attempting to use a data reader to load data from a single table,
tblCountry, with two columns, countryNo (the Key) and countryName, into
a DropDownList box. Here's the code:


protected System.Data.SqlClient.SqlConnection conn;
protected System.Data.SqlClient.SqlDataReader drCountry;

private void Page_Load(object sender, System.EventArgs e)
{
string SQL = "SELECT * FROM tblCountry";

SqlConnection conn = new
SqlConnection("Server=(local);Database=YLCdbSQL;Integrated
Security=SSPI");
SqlCommand countryLoad = new SqlCommand (SQL, conn);
SqlDataReader drCountry = null;

try
{
conn.Open();
drCountry = countryLoad.ExecuteReader();

countryDropLst.DataSource = drCountry;
countryDropLst.DataMember = "countryName";
countryDropLst.DataTextField = "countryName";
countryDropLst.DataValueField = "countryNo";
countryDropLst.DataBind();
}
finally
{
drCountry.Close();
conn.Close();
}
}



But when I run it I get the error "The IListSource does not contain any
data sources", and the DataBind line is highlighted.

I have checked the table using Enterprise Manager, it exists and has
numerous entries in both columns.

Any idea what the problem might be?

Thanks.
 
Thanks for the ideas,

Removed the datamember, made no difference. Still getting the same
error.

Any more?
 
protected System.Data.SqlClient.SqlConnection conn;
protected System.Data.SqlClient.SqlDataReader drCountry;

private void Page_Load(object sender, System.EventArgs e)
Click to expand...

Be sure to add the following

if (!this.IsPostBack)
{

{
string SQL = "SELECT * FROM tblCountry";

SqlConnection conn = new
SqlConnection("Server=(local);Database=YLCdbSQL;Integrated
Security=SSPI");
SqlCommand countryLoad = new SqlCommand (SQL, conn);


try
{
conn.Open();
drCountry = countryLoad.ExecuteReader();
Click to expand...
//Try the following:
while(drCountry.Read())
{
ListItem newListItem = new ListItem();

newListItem.Value = drCountry.GetValue(0).ToString();
newListItem.Text = drCountry.GetValue(2).ToString();
// Add Items to DropDownAwardType DropDownList Web Control
countryDropLst.Items.Add(newListItem);
}

//countryDropLst.DataSource = drCountry;
//countryDropLst.DataMember = "countryName";
//countryDropLst.DataTextField = "countryName";
//countryDropLst.DataValueField = "countryNo";
//countryDropLst.DataBind();
}
finally
{
drCountry.Close();
conn.Close();
}
}
Click to expand...

}
 
Moojjoo, thankyou so much. I have been struggling with this for far too
long now, at last it works!! :)
 
Hi Assimalyst, I have just noticed that you have been given a solution
creating ListItems manually, if you still want to use databinding you can try
this,
HTH jd


================= into aspx==================
<body>
<form id="Form1" method="post" runat="server">
<asp:dropdownlist id="ddlCountry" runat="server"></asp:dropdownlist></form>
</body>

================ end aspx ======================
================begin code behind=================
using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient ;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace csharp
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DropDownList ddlCountry;

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion


private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here

if(!Page.IsPostBack )
{
bindDropDown();
}
}

private void bindDropDown()
{
//
// get our datareader
//
IDataReader dr = DataAccess.CountryDB.GetCountryReader();

//
// bind our drop down
//
ddlCountry.DataSource = dr;
ddlCountry.DataTextField = "CountryName";
ddlCountry.DataValueField = "CountryNo";
ddlCountry.DataBind ();

//
// close our datareader - note this will also close the database
connection
// because we used ocmd.ExecuteReader(CommandBehavior.CloseConnection)
// when we opened it
//

dr.Close();

}

}

//
// ideally move these classes into a different .cs file / assembly
// so the functionality can be reused in different apps / pages
//
namespace DataAccess
{


//
//class for accessing data relating to country
//

public class CountryDB
{
public static IDataReader GetCountryReader()
{
//should ideally use a stored procedure here
SqlCommand ocmd = new SqlCommand
("Your Select Query Here",
DBConnection.GetDBConnection());
return ocmd.ExecuteReader(CommandBehavior.CloseConnection) ;
}

}

//
// helper class to return a database connection
//

public class DBConnection
{

public static SqlConnection GetDBConnection()
{
//
//ideally use configuration or other more secure store for
connectionstring
//

//SqlConnection oconn = new
SqlConnection(ConfigurationSettings.AppSettings("DBConnectionString")) ;
SqlConnection oconn = new SqlConnection("Your Connection String Here");
oconn.Open();
return oconn;
}
}
}

}
=================end codebehind======================
 
No problem. Now if I could just get an answer to my problem... AGH!!!

Getting values from a user control that was added to a datagrid. This is
driving me batty...
 
Thank you too London Calling, you obviously put quite an effort into
forming your reply, it's much appreciated.

I have implemented your method too, just to see how it works, and i
think i will use it as i will be needing to use the same method on a
number of pages, which your answer allows easily. (no offense Moojjoo!)

Thank you both again, without your suggestions i think i would have
caused my self a serious injury by now banging my head against that
brick wall!! :)
 
Back
Top