Hi All, I have a thread working now that consist of all I need to help build a web page that I am converting from Classic ASP to .Net C#. I am very new to programming and VS 2010 C#.
My need is to fill a Dropdownlist from items in a sql 2005 Database.
DB Table - Listoptions - Table
Column1 = STable (This refers to a Table within the Database, i.e - Server, ServerCheck,Administrative,Network)
Column2 = SColumn (This refers to a specific Column within a table, i.e. - Server_name, server_owner, software_version,device_function)
Column3-column23 = Options1-Option21 (Here is where we add items that will appear in the dropdownlist, listitem)
So if in the sql query, it would be to populate information regarding what the Device Function would be, i would have a statement:
select * from listoptions where stable = 'server' and scolumn = 'device_function'
Then starting with the 3 column, I would use those to fill my listitem option. I also need to make sure if the column is NULL, that is not added. That way there are no blanks in the dropdownlist.
Again, I’m new to C# and VS 2010, so any and all help with the code behind and aspx code is greatly appreciated.
Sorry for the simple question, but the code you guys have demonstrated, does that go in the code behind in the Page Load?
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection sqlcon = new SqlConnection("Data Source=ServerName\\SQLEXPRESS; Initial Catalog=DataBaseName; Integrated Security=True");
sqlcon.Open();
SqlCommand cmd = new SqlCommand("select columnname from tablename");
SqlDataReader dr = cmd.ExecuteReader();
While(dr.read())
{
drowdownlist1.datasource=dr;
dropdownlist1.dataTextField="columnname";
dropdownlist1.dataValueField="columnnameId";
dropdownlist1.DataBind();
sqlcon.close();
}
Also, I will have about 6 different dropdownlist, will I be OK if I just have SqlCommand1 and so on as I need them depending on the exact column?
I will also need to make the select query to grab everything but start on the 3 column, is there a correct way in C# to designate?
The String vm_host updates fine right now. Then I tried to add the dropdownlist and there is where I'm totally lost. Again, I'm very new so I need to test over and over to learn. The one other thing as you notice of the sqlcommand, I am selecting "*" from server
where stable = 'server' and scolumn = 'status'. When I get the query result back, I need to make sure that the first two columns are not part of the dropdownlist. So I would skip result column1 and column2.
Hope this helps explain it better so I can help other understand my situation.
Thank you for the suggestion, however it is still not working. I get a compilation error:
CS1061: 'System.Web.UI.WebControls.DataList' does not contain a definition for 'DataTextField'
I also have an issue with the “DataValueField”.
Here is the Code behind: (I changed it a little bit, I would like to stay away from keeping the connection string in the code since I want to be able to use the same code for my Dev/Test and Prod sites)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
public partial class info : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindDropDown1();
}
}
void BindDropDown1()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlDataAdapter adapter = new SqlDataAdapter("select * from listoptions where stable = 'server' and scolumn = 'status'", "conn"))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
DataList1.DataSource = dt;
DataList1.DataTextField = "Status";
DataList1.DataValueField = "regStatus";
DataList1.DataBind();
}
}
}
Now the Web.aspx page:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
public partial class info : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindDropDown1();
}
}
void BindDropDown1()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlDataAdapter adapter = new SqlDataAdapter("select * from listoptions where stable = 'server' and scolumn = 'status'", "conn"))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
DataList1.DataSource = dt;
DataList1.DataTextField = "Status";
DataList1.DataValueField = "regStatus";
DataList1.DataBind();
}
}
}
<asp:DataList ID="DataList1" runat="server" DataKeyField="Server_Name" CellPadding="4" DataSourceID="SqlDataSource1"
onselectedindexchanged="DataList1_SelectedIndexChanged" OnEditCommand="DataList1_EditCommand" OnCancelCommand="DataList1_CancelCommand"
OnUpdateCommand="DataList1_UpdateCommand">
<HeaderTemplate>
<table border="1" cellpadding="5" cellspacing="0">
</HeaderTemplate>
<EditItemTemplate>
<tr>
<td>Server Name:</td><td colspan="2"><asp:Label ID="Label1" runat="server" Text='<%# Eval("server_name") %>'></asp:Label></td>
</tr>
<tr>
<td>VM_Host:</td><td><asp:TextBox ID="txtVM_Host" runat="server" Text='<%# Eval("VM_Host") %>'></asp:TextBox></td>
<td><asp:LinkButton ID="LinkButton1" runat="server" CommandName="update" >Save</asp:LinkButton> <asp:LinkButton CommandName="cancel" ID="LinkButton2" runat="server">Cancel</asp:LinkButton></td>
</tr>
<tr>
<td>Status:</td><td><asp:DropDownList ID="ddlStatus" runat="server" AppendDataBoundItems="true" DataTextField='<%# Eval("Status") %>' DataValueField="regStatus"> <asp:ListItem Text="<Select Subject>" Value="0" /></asp:DropDownList></td>
<td><asp:LinkButton ID="LinkButton3" runat="server" CommandName="update" >Save</asp:LinkButton> <asp:LinkButton CommandName="cancel" ID="LinkButton4" runat="server">Cancel</asp:LinkButton></td>
</tr>
</EditItemTemplate>
Again, I might have done this completely wrong. I am trying to read my textbook (Pro C# 2008 and the .Net 3.5 Platform, search the web and use this forum to build my new ASP.Net site)
Of course DataList hasn't DataTextField and DataValueField……But why do you name binding a DataList as a "BindDropDown"? I thought it was binding to a Dropdownlist……
Plz change from "DataList" to "Dropdownlist" and have a try……
I appreciate your assistance. I apologize if I’m not explaining myself very well. I guess I’m still at the point of “I don’t know, what I don’t know to ask good questions”.
As the title states, I need help creating a Dropdownlist that is populated from a Database table. The table holds many types of option list, it depends what the first two columns hold as to what the remainder of the columns will hold. I have the setup
of the table in my opening question(as to not repeat it again).
I don’t understand, replace datalist with dropdownlist? I don’t see datalist being used in the code behind. I tried to replace the DataTable to a Dropdownlist but that did not work either.
Again, I apologize for the silly questions and not understanding your request, but I’m very new to this process and need all the help I can get.
snteran
Member
45 Points
43 Posts
Help with first time Dropdownlist from database.
Mar 11, 2012 08:54 PM|LINK
Hi All, I have a thread working now that consist of all I need to help build a web page that I am converting from Classic ASP to .Net C#. I am very new to programming and VS 2010 C#.
My need is to fill a Dropdownlist from items in a sql 2005 Database.
DB Table - Listoptions - Table
Column1 = STable (This refers to a Table within the Database, i.e - Server, ServerCheck,Administrative,Network)
Column2 = SColumn (This refers to a specific Column within a table, i.e. - Server_name, server_owner, software_version,device_function)
Column3-column23 = Options1-Option21 (Here is where we add items that will appear in the dropdownlist, listitem)
So if in the sql query, it would be to populate information regarding what the Device Function would be, i would have a statement:
select * from listoptions where stable = 'server' and scolumn = 'device_function'
Then starting with the 3 column, I would use those to fill my listitem option. I also need to make sure if the column is NULL, that is not added. That way there are no blanks in the dropdownlist.
Again, I’m new to C# and VS 2010, so any and all help with the code behind and aspx code is greatly appreciated.
Thanks,
naveenchandr...
Member
469 Points
232 Posts
Re: Help with first time Dropdownlist from database.
Mar 12, 2012 03:13 AM|LINK
Hi,
As I understand you wish to fatch the data from the database and want to pop up on the drop downlist for that follow the below procedure:
1:- stablish the connection with the connection string
like
SqlConnection conn = new SqlConnection("Data Source=ADOBE1-PC\\SQLEXPRESS; Initial Catalog=emp_details; Integrated Security=True");
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = ("select columnname from tablename '");
SqlDataReader adr = cmd.ExecuteReader();
drowdownlist1.datasource=adr;
dropdownlist1.datafield="columnname";
dropdownlist1.databind();
conn.close();
once you are done with this coding you will be able to fatch the data from database to the dropdownlist
once done let me know
AsadShahs
Member
314 Points
73 Posts
Re: Help with first time Dropdownlist from database.
Mar 12, 2012 05:39 AM|LINK
SqlConnection sqlcon = new SqlConnection("Data Source=ServerName\\SQLEXPRESS; Initial Catalog=DataBaseName; Integrated Security=True"); sqlcon.Open(); SqlCommand cmd = new SqlCommand("select columnname from tablename"); SqlDataReader dr = cmd.ExecuteReader(); While(dr.read()) { drowdownlist1.datasource=dr; dropdownlist1.dataTextField="columnname"; dropdownlist1.dataValueField="columnnameId"; dropdownlist1.DataBind(); sqlcon.close(); }snteran
Member
45 Points
43 Posts
Re: Help with first time Dropdownlist from database.
Mar 13, 2012 02:28 AM|LINK
Sorry for the simple question, but the code you guys have demonstrated, does that go in the code behind in the Page Load?
protected void Page_Load(object sender, EventArgs e) { SqlConnection sqlcon = new SqlConnection("Data Source=ServerName\\SQLEXPRESS; Initial Catalog=DataBaseName; Integrated Security=True"); sqlcon.Open(); SqlCommand cmd = new SqlCommand("select columnname from tablename"); SqlDataReader dr = cmd.ExecuteReader(); While(dr.read()) { drowdownlist1.datasource=dr; dropdownlist1.dataTextField="columnname"; dropdownlist1.dataValueField="columnnameId"; dropdownlist1.DataBind(); sqlcon.close(); } Also, I will have about 6 different dropdownlist, will I be OK if I just have SqlCommand1 and so on as I need them depending on the exact column? I will also need to make the select query to grab everything but start on the 3 column, is there a correct way in C# to designate?AsadShahs
Member
314 Points
73 Posts
Re: Help with first time Dropdownlist from database.
Mar 14, 2012 07:58 AM|LINK
SqlConnection sqlcon = new SqlConnection("Data Source=ServerName\\SQLEXPRESS; Initial Catalog=DataBaseName; Integrated Security=True"); protected void Page_Load(object sender, EventArgs e) { if(!IsPostback) { BindDropDown1(); BindDropDown2(); } } void BindDropDown1() { sqlcon.Open(); SqlCommand cmd = new SqlCommand("select columnname from tablename"); SqlDataReader dr = cmd.ExecuteReader(); While(dr.read()) { drowdownlist1.datasource=dr; dropdownlist1.dataTextField="columnname"; dropdownlist1.dataValueField="columnnameId"; dropdownlist1.DataBind(); sqlcon.close(); } } void BindDropDown2() { sqlcon.Open(); SqlCommand cmd = new SqlCommand("select columnname from tablename"); SqlDataReader dr = cmd.ExecuteReader(); While(dr.read()) { drowdownlist1.datasource=dr; dropdownlist1.dataTextField="columnname"; dropdownlist1.dataValueField="columnnameId"; dropdownlist1.DataBind(); sqlcon.close(); } }snteran
Member
45 Points
43 Posts
Re: Help with first time Dropdownlist from database.
Mar 15, 2012 01:46 AM|LINK
Thank you for the response. I think it is starting to make a little since.
I wanted to put some of my Datalist control with the dropdownlist for better understandings.
<asp:DataList ID="DataList1" runat="server" DataKeyField="Server_Name" CellPadding="4" DataSourceID="SqlDataSource1" onselectedindexchanged="DataList1_SelectedIndexChanged" OnEditCommand="DataList1_EditCommand" OnCancelCommand="DataList1_CancelCommand" OnUpdateCommand="DataList1_UpdateCommand"> <EditItemTemplate> <tr> <td>Server Name:</td><td colspan="2"><asp:Label ID="Label1" runat="server" Text='<%# Eval("server_name") %>'></asp:Label></td> </tr> <tr> <td>VM_Host:</td><td><asp:TextBox ID="txtVM_Host" runat="server" Text='<%# Eval("VM_Host") %>'></asp:TextBox></td> <td><asp:LinkButton ID="LinkButton1" runat="server" CommandName="update" >Save</asp:LinkButton> <asp:LinkButton CommandName="cancel" ID="LinkButton2" runat="server">Cancel</asp:LinkButton></td> </tr> <tr> <td>Status:</td><td><asp:DropDownList ID="ddlStatus" runat="server" AppendDataBoundItems="true" DataTextField='<%# Eval("Status") %>'><asp:ListItem >Select</asp:ListItem></asp:DropDownList></td> <td><asp:LinkButton ID="LinkButton3" runat="server" CommandName="update" >Save</asp:LinkButton> <asp:LinkButton CommandName="cancel" ID="LinkButton4" runat="server">Cancel</asp:LinkButton></td> </tr> <tr> </EditItemTemplate>Then the associated code behind, including your edits:
<asp:DataList ID="DataList1" runat="server" DataKeyField="Server_Name" CellPadding="4" DataSourceID="SqlDataSource1" onselectedindexchanged="DataList1_SelectedIndexChanged" OnEditCommand="DataList1_EditCommand" OnCancelCommand="DataList1_CancelCommand" OnUpdateCommand="DataList1_UpdateCommand"> <EditItemTemplate> <tr> <td>Server Name:</td><td colspan="2"><asp:Label ID="Label1" runat="server" Text='<%# Eval("server_name") %>'></asp:Label></td> </tr> <tr> <td>VM_Host:</td><td><asp:TextBox ID="txtVM_Host" runat="server" Text='<%# Eval("VM_Host") %>'></asp:TextBox></td> <td><asp:LinkButton ID="LinkButton1" runat="server" CommandName="update" >Save</asp:LinkButton> <asp:LinkButton CommandName="cancel" ID="LinkButton2" runat="server">Cancel</asp:LinkButton></td> </tr> <tr> <td>Status:</td><td><asp:DropDownList ID="ddlStatus" runat="server" AppendDataBoundItems="true" DataTextField='<%# Eval("Status") %>'><asp:ListItem >Select</asp:ListItem></asp:DropDownList></td> <td><asp:LinkButton ID="LinkButton3" runat="server" CommandName="update" >Save</asp:LinkButton> <asp:LinkButton CommandName="cancel" ID="LinkButton4" runat="server">Cancel</asp:LinkButton></td> </tr> <tr> </EditItemTemplate> SqlConnection sqlcon = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\BegASPNET\\App_Data\\servers.mdf;Integrated Security=True;User Instance=True"); protected void Page_Load(object sender, EventArgs e) { if (!IsPostback) { BindDropDown1(); } } void BindDropDown1() { sqlcon.Open(); SqlCommand cmd = new SqlCommand("select * from listoptions where stable = 'server' and scolumn = 'status'"); SqlDataReader dr = cmd.ExecuteReader(); While(dr.read()) { DataList1.DataSource = dr; DataList1.dataTextField="Status"; DataList1.dataValueField="Sever_name"; DataList1.DataBind(); sqlcon.close(); } } protected void DataList1_SelectedIndexChanged(object sender, EventArgs e) { } protected void DataList1_EditCommand(object source, DataListCommandEventArgs e) { DataList1.EditItemIndex = e.Item.ItemIndex; DataList1.DataBind(); } protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e) { DataList1.EditItemIndex = -1; DataList1.DataBind(); } protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e) { String server_name = DataList1.DataKeys[e.Item.ItemIndex].ToString(); String vm_host = ((TextBox)e.Item.FindControl("txtVM_Host")).Text;
The String vm_host updates fine right now. Then I tried to add the dropdownlist and there is where I'm totally lost. Again, I'm very new so I need to test over and over to learn. The one other thing as you notice of the sqlcommand, I am selecting "*" from server where stable = 'server' and scolumn = 'status'. When I get the query result back, I need to make sure that the first two columns are not part of the dropdownlist. So I would skip result column1 and column2.
Hope this helps explain it better so I can help other understand my situation.
Thanks,
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Help with first time Dropdownlist from database.
Mar 17, 2012 01:44 AM|LINK
Hello snteran:)
Plz change to DataTable instead of Reader,and this will reset your DataSource each time when binding——
void BindDropDown1() { using(SqlDataAdapter adapter = new SqlDataAdapter("select * from listoptions where stable = 'server' and scolumn = 'status'","Your connection string") { DataTable dt = new DataTable(); adapter.Fill(dt); DataList1.DataSource = dt; DataList1.dataTextField="Status"; DataList1.dataValueField="Sever_name"; DataList1.DataBind(); } }snteran
Member
45 Points
43 Posts
Re: Help with first time Dropdownlist from database.
Mar 17, 2012 10:40 PM|LINK
Thank you for the suggestion, however it is still not working. I get a compilation error:
CS1061: 'System.Web.UI.WebControls.DataList' does not contain a definition for 'DataTextField'
I also have an issue with the “DataValueField”.
Here is the Code behind: (I changed it a little bit, I would like to stay away from keeping the connection string in the code since I want to be able to use the same code for my Dev/Test and Prod sites)
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Configuration; using System.Data; using System.Data.Sql; using System.Data.SqlClient; public partial class info : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { BindDropDown1(); } } void BindDropDown1() { using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlDataAdapter adapter = new SqlDataAdapter("select * from listoptions where stable = 'server' and scolumn = 'status'", "conn")) { DataTable dt = new DataTable(); adapter.Fill(dt); DataList1.DataSource = dt; DataList1.DataTextField = "Status"; DataList1.DataValueField = "regStatus"; DataList1.DataBind(); } } }Now the Web.aspx page:
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Configuration; using System.Data; using System.Data.Sql; using System.Data.SqlClient; public partial class info : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { BindDropDown1(); } } void BindDropDown1() { using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlDataAdapter adapter = new SqlDataAdapter("select * from listoptions where stable = 'server' and scolumn = 'status'", "conn")) { DataTable dt = new DataTable(); adapter.Fill(dt); DataList1.DataSource = dt; DataList1.DataTextField = "Status"; DataList1.DataValueField = "regStatus"; DataList1.DataBind(); } } } <asp:DataList ID="DataList1" runat="server" DataKeyField="Server_Name" CellPadding="4" DataSourceID="SqlDataSource1" onselectedindexchanged="DataList1_SelectedIndexChanged" OnEditCommand="DataList1_EditCommand" OnCancelCommand="DataList1_CancelCommand" OnUpdateCommand="DataList1_UpdateCommand"> <HeaderTemplate> <table border="1" cellpadding="5" cellspacing="0"> </HeaderTemplate> <EditItemTemplate> <tr> <td>Server Name:</td><td colspan="2"><asp:Label ID="Label1" runat="server" Text='<%# Eval("server_name") %>'></asp:Label></td> </tr> <tr> <td>VM_Host:</td><td><asp:TextBox ID="txtVM_Host" runat="server" Text='<%# Eval("VM_Host") %>'></asp:TextBox></td> <td><asp:LinkButton ID="LinkButton1" runat="server" CommandName="update" >Save</asp:LinkButton> <asp:LinkButton CommandName="cancel" ID="LinkButton2" runat="server">Cancel</asp:LinkButton></td> </tr> <tr> <td>Status:</td><td><asp:DropDownList ID="ddlStatus" runat="server" AppendDataBoundItems="true" DataTextField='<%# Eval("Status") %>' DataValueField="regStatus"> <asp:ListItem Text="<Select Subject>" Value="0" /></asp:DropDownList></td> <td><asp:LinkButton ID="LinkButton3" runat="server" CommandName="update" >Save</asp:LinkButton> <asp:LinkButton CommandName="cancel" ID="LinkButton4" runat="server">Cancel</asp:LinkButton></td> </tr> </EditItemTemplate>
Again, I might have done this completely wrong. I am trying to read my textbook (Pro C# 2008 and the .Net 3.5 Platform, search the web and use this forum to build my new ASP.Net site)
Thanks for the help,
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Help with first time Dropdownlist from database.
Mar 18, 2012 12:42 AM|LINK
Hello snteran:)
Of course DataList hasn't DataTextField and DataValueField……But why do you name binding a DataList as a "BindDropDown"? I thought it was binding to a Dropdownlist……
Plz change from "DataList" to "Dropdownlist" and have a try……
snteran
Member
45 Points
43 Posts
Re: Help with first time Dropdownlist from database.
Mar 18, 2012 01:51 AM|LINK
Hello Decker,
I appreciate your assistance. I apologize if I’m not explaining myself very well. I guess I’m still at the point of “I don’t know, what I don’t know to ask good questions”.
As the title states, I need help creating a Dropdownlist that is populated from a Database table. The table holds many types of option list, it depends what the first two columns hold as to what the remainder of the columns will hold. I have the setup of the table in my opening question(as to not repeat it again).
I don’t understand, replace datalist with dropdownlist? I don’t see datalist being used in the code behind. I tried to replace the DataTable to a Dropdownlist but that did not work either.
Again, I apologize for the silly questions and not understanding your request, but I’m very new to this process and need all the help I can get.
Thanks,