ASP.NET and data binding a dropdown list with SQL Server and C# code

Hey guys I am wondering if u can help me out with something.
I can't seem to fill the dropdown list on my .aspx file =S
=============================================
Let just say i have createnewaccount.aspx in it is a aspx form and this dropdown:
=============================================
<aspropDownList id="dpdProvinces" runat="server"></aspropDownList>


==============================================
Then in my createnewaccount.cs file I have the following function
==============================================
//Fill a dropdown list Parameters: Dropdown id, database table name
public static bool FillDropDownList(DropDownList dDl,string tblName)
{
const string strServer = "TestServer";
const string strDBId = "SA";
const string strDBPwd = "";
const string strDBName = "dotNetDemo";
//Connection string for SQL Server DB
string ConnectionString =
"server=" + strServer +
";uid=" + strDBId +
";pwd=" + strDBPwd +
";database=" + strDBName + ";";
//Set the Data Value and Text fields to the db column
dDl.DataValueField = "Id";
dDl.DataTextField = "Name";

//SQL string to execute
string strSQL = "SELECT * FROM " + tblName;
//Create a connection
SqlConnection conn = new SqlConnection(ConnectionString);
try
{
conn.Open();
//Execute the SQL String
SqlCommand cmd = new SqlCommand(strSQL, conn);
SqlDataReader reader = cmd.ExecuteReader();
dDl.DataSource = reader;
dDl.DataBind();
conn.Close();
//Insert a item at the begining of the list
dDl.Items.Insert(0, "<--- Select --->");

}
catch // (Exception e) // Exception Removed
{
return false;

}
finally
{
conn.Close();
}
return true;
}


private void Page_Load(object sender, System.EventArgs e)
{
FillDropDownList(dpdProvinces, "Province");
}


Now should that work already by the databind i did with the function or do i have to loop through the table data and fill in the function? i tried both ways and doesn't seem to work. I kno my connection string works because I tried it somewhere else.. and I have data in the Province table and the fields are named correctly.. any suggestions would be appreciated.. thanks!!!

Hi,

i hope it will helps you. please place this code after reader is excuted.


while (reader.Read())
{
dDl.Items.Add(new ListItem(reader.GetString(1),reader.GetString(0))) ;
}

//dDl.DataSource = reader;
//dDl.DataBind();


Regards,
Nagesh

Here's a useful link for you:

http://www.akadia.com/services/dotnet_databinding.html

For ur problem u need to add one more line in ur code
dDl.DataTextField="xyz" after data sorce.


dDl.DataSource = reader;
here................
dDl.DataBind();

in place of "xyz" u need to specify the field name u want to fill the dropdown list with.

Try this sample code

#region BindCountry
private void BindCountry()
{
SqlConnection Con = new SqlConnection();
string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlCommand SqlCmd = new SqlCommand();
string Str="select * from dbo.Country";
SqlCmd.CommandText = Str;
Con.ConnectionString = ConnectionString;



DataSet DS = new DataSet();
SqlDataAdapter SqlAdp = new SqlDataAdapter(Str,Con);
SqlAdp.Fill(DS,"Country");

//ddlCountry.DataSource = DS;

if (DS.Tables[0].Rows.Count > 0)
{
foreach (DataRow Dr in DS.Tables[0].Rows)
{
ddlCountry.Items.Add(new ListItem(Dr["countryName"].ToString(), Dr["countryID"].ToString()));
}
}
}
#endregion

and call function in pageload