C# Fill Combo Box from SQL Datatable

C# Fill combo box from SQL DataTable

I'm not yet sure what is the exact error in your code, but if you're ok with not using DataTable, you can do it this way:

using (SqlConnection sqlConnection = new SqlConnection("connstring"))
{
    SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Doctor", sqlConnection);
    sqlConnection.Open();
    SqlDataReader sqlReader = sqlCmd.ExecuteReader();

    while (sqlReader.Read())
    {
        cbDoctor.Items.Add(sqlReader["name"].ToString());
    }

    sqlReader.Close();
}

For more information take a look at SqlDataReader reference on MSDN.

In orer to find the issue in the original code you posted, please provide information in which line you get the exception (or is it an error that prevents application from compiling?) and what is its whole message.

Fill ComboBox with data from SQL Server

SqlDataAdapter da = new SqlDataAdapter("SELECT ItemName FROM TBItemList", oSqlConn);
DataTable dt = new DataTable();
da.Fill(dt);
cbxparameter.DataSource = dt;
cbxparameter.DisplayMember = "ItemName";
cbxparameter.ValueMember = "ItemName";

Fill ComboBox with DataTable result

Change your query like this

SELECT (Cast(id As varchar) + ' - ' + name + ' ' + last_name) As DisplayItem, Id FROM ...

then fill the table using that query.
To fill the blank item at the first row. use following code.

Dim row As DataRow = dtTable.NewRow()
row("DisplayItem") = "Enter Value or leave it blank"
row("ID") = -1
dtTable.Rows.InsertAt(row, 0)

Then bind the combobox with that datatable.

Combobox1.DisplayMember = "DisplayItem"
Combobox1.ValueMember = "Id"
Combobox1.DataSource = dtTable

Fill data into combobox from datatable

You can check if the combobox is already loaded or not.

private void txtleavetype_MouseClick(object sender, MouseEventArgs e)
{
    try
    {
       if(txtleavetype.Items.Count==0){
        con = new SqlConnection(cs.DBConn);
        con.Open();
        cmd = new SqlCommand(" select leavestype from hrsettings", con);
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            txtleavetype.Items.Add(dr["leavestype"]);
        }
        dr.Close();
      }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Or else you can clear the list items before adding.

private void txtleavetype_MouseClick(object sender, MouseEventArgs e)
    {
        try
        {
            txtleavetype.Items.Clear();
            con = new SqlConnection(cs.DBConn);
            con.Open();
            cmd = new SqlCommand(" select leavestype from hrsettings", con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                txtleavetype.Items.Add(dr["leavestype"]);
            }
            dr.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

As per the comment, You can also move the below code to Page_Load event and use if when it is not a post back.

protected void Page_Load(object sender, EventArgs e)
    {
         try
        {
          if (!IsPostBack)
           {
            txtleavetype.Items.Clear();
            con = new SqlConnection(cs.DBConn);
            con.Open();
            cmd = new SqlCommand(" select leavestype from hrsettings", con);
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                txtleavetype.Items.Add(dr["leavestype"]);
            }
            dr.Close();
          }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }

Hope This Helps!

Fill Combobox from database

You will have to completely re-write your code. DisplayMember and ValueMember point to columnNames! Furthermore you should really use a using block - so the connection gets disposed (and closed) after query execution.

Instead of using a dataReader to access the values I choosed a dataTable and bound it as dataSource onto the comboBox.

using (SqlConnection conn = new SqlConnection(@"Data Source=SHARKAWY;Initial Catalog=Booking;Persist Security Info=True;User ID=sa;Password=123456"))
{
    try
    {
        string query = "select FleetName, FleetID from fleets";
        SqlDataAdapter da = new SqlDataAdapter(query, conn);
        conn.Open();
        DataSet ds = new DataSet();
        da.Fill(ds, "Fleet");
        cmbTripName.DisplayMember =  "FleetName";
        cmbTripName.ValueMember = "FleetID";
        cmbTripName.DataSource = ds.Tables["Fleet"];
    }
    catch (Exception ex)
    {
        // write exception info to log or anything else
        MessageBox.Show("Error occured!");
    }               
}

Using a dataTable may be a little bit slower than a dataReader but I do not have to create my own class. If you really have to/want to make use of a DataReader you may choose @Nattrass approach. In any case you should write a using block!

EDIT

If you want to get the current Value of the combobox try this

private void cmbTripName_SelectedIndexChanged(object sender, EventArgs e)
{
    if (cmbTripName.SelectedItem != null)
    {
        DataRowView drv = cmbTripName.SelectedItem as DataRowView;

        Debug.WriteLine("Item: " + drv.Row["FleetName"].ToString());
        Debug.WriteLine("Value: " + drv.Row["FleetID"].ToString());
        Debug.WriteLine("Value: " + cmbTripName.SelectedValue.ToString());
    }
}

fill combobox with data in a stored procedure

//assume this that you have datatable filled from stored procedure
//This is sample for populating your combo
DataTable dt = new DataTable();
dt.Columns.Add("AuthorID", typeof(int));
dt.Columns.Add("FullName", typeof(string));

DataRow dr = null;
for (int i = 0; i < 10; i++)
{
   dr = dt.NewRow();
   dr[0] = i;
   dr[1] = "ABC" + i + 2 * i;

   dt.Rows.Add(dr);
}

comboBox1.DataSource = dt; // this will be populated from SP
comboBox1.ValueMember = "AuthorID";
comboBox1.DisplayMember = "FullName";

Populate TextBox with Text from DataTable - Combo Box

Looks like you might be declaring your datatables as local variables for that method.

Place the DataTable declaration in the space between Namespace declaration and the class constructor

Namespace MyProject
{
    public sealed partial class MyClass : Page
    {
        //Class wide variables go here

        DataTable mytable = new DataTable();

        class MyClass()
        {

        }
    }
}

After that you should be able to reference your DataTable within the ComboBox.SelectionChanged event to assign your value to the TextBox

Edit:

Within the ComboBox.SelectionChanged event use

TextBox.Text = ComboBox.SelectedValue.ToString();

where TextBox is the name of the TextBox to assign to, and ComboBox is the name of the ComboBox you want to get the value from.

or, if like in your case, its a third value the combobox can't handle directly

TextBox.Text = DataTable.Rows[ComboBox.SelectedIndex]["Note"].ToString()

Fill Combobox with Data from Database

You just need to set ValueMember and DisplayMember properties:

List<CD> myList = myDataAccess.GetCDList();
AlbumCombobox.DataSource = myList;
AlbumCombobox.DisplayMember = "Name";
AlbumCombobox.ValueMember = "ID";

The CD class should be defined as follows:

public class CD
{
    public string Name {get; set; }
    public int ID {get; private set;}

    public CD(int _ID)
    {
        ID = _ID;
    }
}

Fill comboBox with SELECT results

And what about the following way:

 string ChaineDeSelction = "SELECT fir_mdnt FROM -- WHERE fir_aktiv = 1 AND EXISTS(select firbpv_fir from --)";
        OdbcConnection MyConnec = new OdbcConnection(MyConnString);
        MyConnec.Open();
        OdbcCommand MyComm = new OdbcCommand(ChaineDeSelction, MyConnec);
        OdbcDataReader reader = MyComm.ExecuteReader();
        while (reader.Read())
        {
            _cb_Societe.Items.Add(reader[0]);
        };

Fill combobox with database, starting with blank item

So you want to insert a blank item in the combo box at index = 0, correct?

//rest of your code
comboBox1.DataSource = dt;    
comboBox1.Items.Insert(0, new ListItem(" ", "-1")); //After filling the DataSource, insert an item in the Combobox at Index 0

What I have done here is to insert an item after the datasource is filled with the data from the DB. What happening in your code seems obvious to me. For more info on this, have a quick read. The article shows in Windows Forms but you'll get the idea in case you are on asp.net

Adding and Removing Items from a Windows Forms ComboBox, ListBox, or CheckedListBox Control

Last time I worked on these controls was almost a decade ago. I'm typing a sample here in SO, taking a reference from my old Source Control repo.

Common Variables

DataRow rw;
public DataSet ds = new DataSet();
public SqlDataReader dr;
public SqlCommand cmd = new SqlCommand();
public SqlDataAdapter adp = new SqlDataAdapter();

Using DataTable

//If DataSet contains the table already, remove it first
if (ds.Tables.Contains(tbl)) 
      ds.Tables.Remove(tbl);

//Open connection here. Better with using
  cmd.CommandText = "YOUR_SQL_QUERY_GOES_HERE";
  adp.SelectCommand = cmd;
  adp.Fill(ds, tbl);
//close the connection here

rw = ds.Tables[tbl].NewRow();        //Add a new row
rw[0] = "-1";                        //Set it's value
rw[1] = "Itemtext";                  //Set it's text
ds.Tables[tbl].Rows.InsertAt(rw, 0); //Insert this row in the DataTable(DT) at Index 0
comboBox1.DataSource = ds.Tables[tbl]; //Assign the DT in the DataSource of the combobox
comboBox1.DataTextField = "Default Text";
comboBox1.DataValueField = "Default Value";
comboBox1.DataBind();

Using DataReader

comboBox1.Items.Clear(); //Clear the dropdown first
//Open the connection, set SqlCommandType and Text
using (SqlDataReader reader = sqlcmd.ExecuteReader())
  {
     comboBox1.DataSource = reader; //Assign DataReader to the DataSource of the Combobox
     comboBox1.DataValueField = "usr_entrada";
     comboBox1.DataTextField = "no_servicio";
     comboBox1.DataBind();
     //Here you can insert a new item at Index 0 of the Combobox.
     //For your case, keep the "Default Text" blank
     comboBox1.Items.Insert(0, new ListItem("--Default Text--", "-1"));
  }
//close the connection

Please note that the code is not optimized for production use and is only to give you more ideas! So do not penalize the answer because of the code quality. I have written it directly in SO and there might be some syntax errors!

---

Related Topics