Fill data into combobox from datatable

Question

I have searched and and tried but I was unable o find an proper answer to my need.

in my application I have combobox I want to add data into it from database table column leavetype.

 table hsettings
 hsysid
 leavetype
 martialstatus

Right now I am using mouseclick event,

private void txtleavetype_MouseClick(object sender, MouseEventArgs e)
{
    try
    {
        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);
    }
}

whenever I click on combobox it shows data from table column but on each click same column data being loaded multiple times.

1
2
3
4
1
2
3
4

I want it to load once on each click or if there is any other better way to sort it out.

Answer 1

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!

Answer 2

You need to remove all the items from the combobox before refilling it.

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

The best thing would be to add items to combobox at the time of form_load.

private void Form1_Load(object sender, EventArgs e)
{
     try
    {
        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);
    }
}