populate dropdown items via stored procedure

[ellen89]

Hi All,

I am new to this field, and is using VS.2005, language is C#.

1) created a database "Test" in sql 2005, had a table "NameList", a stored proc "GetAllName"
2) created a new website, on .aspx page has a dropdown control "ddlName"

Wish could have help at following:

Now I want to all the names from NameList will be populated at 'ddlName" when page loaded.

There is no problem for me to complete this task using data adapter, but I want to use stored procedure.

Thank you in advance. Your time is great appreciated.

ellen89

[nateraaaa]

Please see the following article in our Howtos section.

http://www.thescripts.com/forum/thread635615.html

Nathan

[ellen89]

nateraaaa,

Thank you very much for your time.

Now I had another problem, my code is:

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string cnString = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
SqlConnection cnn = new SqlConnection(cnString);
SqlCommand cmd = new SqlCommand("GetAllName", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetAllName";
cnn.Open();

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "NameList");
DropDownList1.DataSource = ds;
DropDownList1.DataMember = "NameList";
DropDownList1.DataBind();

cnn.Close();
}
}

But the at Dropdown list will have "System.Data.DataRowView" instead of real data.

[saran23]

nateraaaa,

Thank you very much for your time.

Now I had another problem, my code is:

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string cnString = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
SqlConnection cnn = new SqlConnection(cnString);
SqlCommand cmd = new SqlCommand("GetAllName", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "GetAllName";
cnn.Open();

SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds, "NameList");
DropDownList1.DataSource = ds;
DropDownList1.DataMember = "NameList";
DropDownList1.DataBind();

cnn.Close();
}
}

But the at Dropdown list will have "System.Data.DataRowView" instead of real data.

Hi, The problem is in this line,

DropDownList1.DataSource = ds;

ur binding a dataset to a dropdownlist, a dataset may contain more than one table also, so there comes the problem, even if u hv used Datamember,the table name to which the "NameList" column belongs is not specified,So u hv to do this,

DropDownList1.DataSource = ds.Tables[0]

The '[0]' denotes the index of the datatable in the dataset.

Im sure this works...

Thanks
Saran

[ellen89]

Sarah,

Thank you very much. I had tried, unfortunately, I have no lucky.

I am not sure the .DataMember property, NameList is my table name, with 2 columns (firstName, lastName).

Gusee I should not have the column name in my code because I am using stored procedure to get the result. This stored procedure is very simple and tested in sql server 2005.

Thanks again,

ellen89

[kunal pawar]

Hi, ur code correct but to display text in drop down control u have to set column name, same for value

DropDownList1.DataSource = ds;
DropDownList1.DataValueField = "Field name for selected item value"
DropDownList1.DataTextField = "Field name for selected item Text"
DropDownList1.DataBind();

[ellen89]

Thank you!

Now the problem is I want to show the full name on the dropdown list, but the Name is stored in databas as FirstName & LastName (2 columns).

What should I put for DropDownList1.DataTextField ?

I had tried DropDownList1.DataTextField = "FirstName+' '+LastName", it gave run time error.

ellen89

[ellen89]

I had solved this problem by using a VIEW table at SQL server to have FirstName and LastName at same column.

Thank you for all help.

ellen89

[kunal pawar]

You not need to create view for this single reason, just add column in ur sql query
as FirstName + ' '+LastName as Name.

and

DropDownList1.DataTextField = "Name"