Help with first time Dropdownlist from database.

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, Im new to C# and VS 2010, so any and all help with the code behind and aspx code is greatly appreciated.

Thanks,

Re: Help with first time Dropdownlist from database.

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

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

Re: Help with first time Dropdownlist from database.

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

Re: Help with first time Dropdownlist from database.

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?

Re: Help with first time Dropdownlist from database.

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

Re: Help with first time Dropdownlist from database.

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,

Re: Help with first time Dropdownlist from database.

Hello snteran

Plz change to DataTable instead of Readerand 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();
    }
}

Re: Help with first time Dropdownlist from database.

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,

Re: Help with first time Dropdownlist from database.

Hello snteran

snteran
void BindDropDown1()

Of course DataList hasn't DataTextField and DataValueFieldBut 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

Re: Help with first time Dropdownlist from database.

Hello Decker,

I appreciate your assistance. I apologize if Im not explaining myself very well. I guess Im still at the point of I dont know, what I dont 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 dont understand, replace datalist with dropdownlist? I dont 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 Im very new to this process and need all the help I can get.

Thanks,

Re: Help with first time Dropdownlist from database.

Hello again

I don't understandeitherSince you are dealling with Dropdownlistand what does it have any relationship with Dropdownlist

Reguards and Thanks

Re: Help with first time Dropdownlist from database.

OK, so I'm still trying to get what I would think would be a simple step done in C#/aspx.

My aspx Edittemplate:

<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>
                <td>
             <asp:DropDownList ID="ddlStatus" runat="server" DataSourceID="SqlDataSource1" DataTextField="Status" DataValueField="Status" SelectedValue='<%# Bind("Status") %>' AppendDataBoundItems="True">
              </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>

As you can see in the EditItemTemplate, the dropdownlist control is added and needs to show up as one of the edit items with a populated dropdownlist.

Here is the code behind:

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            BindDropDown1();
        }
    }

   

    private void BindDropDown1()
    {
        
        SqlConnection cs = new SqlConnection(ConfigurationManager.ConnectionStrings["sCheckEDConnectionString"].ToString());
        string query = "select * from wwwlistoptions where stable = 'server' and scolumn = 'status'";
        SqlDataAdapter da = new SqlDataAdapter(query, cs);
        DataSet ds = new DataSet();
        da.Fill(ds, "status");
        DataTable dt = new DataTable();
      
        da.Fill(dt);

        for (int i = 0; i < dt.Rows.Count; i++)
        {
           Code Needed?
        }

    }

I have been looking through the net to find a method to process my update. I hope I can get some help so I can move forward. Thanks,

Re: Help with first time Dropdownlist from database.

Hello

Since you are using a Dropdownlist inside the DataListyou should change your UI to design modethen choose the "EditTemplate" and then directly bind the Dropdownlist with the DataSourceset DataTextField and DataValueField and set SelectedValue to itMake sure that your SelectedValue must be one of the items bound to the Dropdownlist as a datasourceor error will be thrown out

Reguards