Help with first time Dropdownlist from database. RSS

• • Reply
  

  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,

• • Reply
  

  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

  Please remember to click “Mark as Answer” on my response that helps you. This can be beneficial to other community members reading the thread.
• • Reply
  

  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();
      }

  Please 'Mark as Answer' if my post helped you
• • Reply
  

  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?

• • Reply
  

  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();
      }
  }

  Please 'Mark as Answer' if my post helped you
  • Edited by AsadShahs on Mar 14, 2012 07:59 AM
• • Reply
  

  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>&nbsp;&nbsp;<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>&nbsp;&nbsp;<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>&nbsp;&nbsp;<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>&nbsp;&nbsp;<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,
  
  

• • Reply
  

  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();
      }
  }

• • Reply
  

  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>&nbsp;&nbsp;<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>&nbsp;&nbsp;<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,

• • Reply
  

  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：）

  snteran

  void BindDropDown1()

  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……

• • Reply
  

  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,