binding a dropdownlist to a database

Question

I want to bind a dropdownlist to a database. I did the following coding but it isn't working.

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.Odbc;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

            rebind();

        }
    }

    public void rebind()
    {

        try
        {
       OdbcConnection myConn = new OdbcConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);     
       string sql = "select casename,casecode from casetype";
       myConn.Open();
       OdbcCommand cmd = new OdbcCommand(sql, myConn);
       OdbcDataReader MyReader = cmd.ExecuteReader();
                    {
                        DropDownList3.DataSource= sql;
                        DropDownList3.DataTextField = "casename";
                        DropDownList3.DataValueField = "casetype";
                        DropDownList3.DataBind();
                    }
                }

        catch (Exception ex)
        {

            Response.Write(ex.StackTrace);

        }   
    }
}

I am getting the error as

at _Default.rebind() in c:\Documents and Settings\a\My Documents\Visual Studio 2008\WebSites\toolbar1\Default.aspx.cs:line 32 

Please help me to solve my problem and bind the dropdownlist to a datasource.I want my dropdownlist to display text from a database column and use the value field for some other purpose later on in code. I am getting the page displayed when i run the project but not able to get the data in dropdownlist

Answer 1

You are setting the string of sql to the datasource, not a data structure.

read out your objects into a list or Ilist sub type.

Then bind that to the drop down.

 List<CaseType> ct = new List<CaseType>();
    try
    {
   OdbcConnection myConn = new OdbcConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);     
   string sql = "select casename,casecode from casetype";
   myConn.Open();
   OdbcCommand cmd = new OdbcCommand(sql, myConn);
   OdbcDataReader MyReader = cmd.ExecuteReader();
   while(MyReader.ReadNext()){
                   ct.Add(new CaseType(){Name = MyReader.Read("casename").ToString(), Type = Convert.ToInt32(MyReader.Read("casetype"))});
            }
 }
    catch (Exception ex)
    {

        Response.Write(ex.StackTrace);

    }   

Its been a long time since I've done anything this metal with ado. However it should be more like that in the reader part.

Then the binding afterwards.

                  DropDownList3.DataSource= ct;
                    DropDownList3.DataTextField = "Name";
                    DropDownList3.DataValueField = "Type";
                    DropDownList3.DataBind();

Answer 2

Your query is querying columns casename,casecode

string sql = "select casename,casecode from casetype";

Edit -

But, while binding you are binding different columns to your datatextfield and datavaluefields.

You are using the sql string variable as the datasource. You should be using your datareader instead.

Try using -

DropDownList3.DataSource= MyReader; 
DropDownList3.DataTextField = "casename";
DropDownList3.DataValueField = "casecode";

Answer 3

try following code....it will surely work.... first define ConnectionString in web.config inside

protected void Page_Load(object sender, EventArgs e)
    {
        string strconnection = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
        SqlConnection con = new SqlConnection(strconnection);
        con.Open();

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "select ename,ecompany from example";

        SqlDataAdapter adp = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();
        adp.Fill(ds, "example");

        DropDownList1.DataSource = ds;
        DropDownList1.DataTextField = "ename";
        DropDownList1.DataValueField = "ecompany";
        DropDownList1.DataBind();
    }

Answer 4

Did you try using DataAdapter like this?

public void rebind()
    {

        try
        {
            OdbcConnection myConn = new OdbcConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);
            string sql = "select casename,casecode from casetype";
            myConn.Open();
            OdbcCommand cmd = new OdbcCommand(sql, myConn);
            OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            DropDownList3.DataSource = dt;
            DropDownList3.DataTextField = "casename";
            DropDownList3.DataValueField = "casecode";
            DropDownList3.DataBind();
        }
        catch (Exception ex)
        {
            Response.Write(ex.StackTrace);
        }
    }