Search data in Sql and display them in C # ComboBox

Question

Search data in Sql and display them in C # ComboBox

Navigation

#1 by (4 votes)
#2 by (2 votes)

4

Good afternoon.

I would like to know how I can show data in ComboBox . This data I look for in SQL and I show them in a DataGridView but I would like to show it in a ComboBox , this is what I have of code.

public DataTable heater()
    {
        //conectar con la base de datos para extraer el hater segun el manufacturing order que le corresponda
        SqlConnection con = new SqlConnection(consql);
        con.Open();
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();

        da.SelectCommand = new SqlCommand("SELECT 
        spmh.[Samtec_Part_Master_Description] 
        FROM[production].[Pockets].[PocketHeater] as ph 
        inner join[production].[dbo].[SAMTEC_PART_MASTER] as spm 
        on ph.[PocketPartNumberId] = spm.[Samtec_Part_Master_ID] 
        inner join[production].[dbo].[SAMTEC_PART_MASTER] as spmh 
        on ph.[HeaterPartNumberId] = spmh.[Samtec_Part_Master_ID] 
        WHERE spm.[Samtec_Part_Master_Description] = '" + extraer() + "' 
        or spm.[Samtec_Part_Master_Description] = '" + prt + "' ", con);

        da.Fill(ds);

        dt = ds.Tables[0];

        return dt;
    }

and this is my formaplication.

 private void txtbuscar_Click(object sender, EventArgs e)
    {
        clas.ordr = txtorder.Text;
        clas.prt = txtpart.Text;

        txtorder.Text = clas.limpio();
        txtpart.Text = clas.extraer();

        //txtheater.Text = clas.heater();
        //lbheaters.Text = clas.heater();
        //upheater.Text = clas.heater();

        cbheater.DataSource = clas.heater();
        dgvheater.DataSource = clas.heater();
    }

And this is what is shown in ComboBox .

Thank you.

sql c# combobox

asked by use2105 25.11.2016 в 21:54

source

2 answers

Javascript - Find similar words in an Array () query to obtain maximum date between several tables

score 4 · Answer 1

@ use2105 said:

I would like to know how I can show data in a ComboBox. This data I look for them in SQL and I show them in a DataGridView but I would like show it on a ComboBox

To load a ComboBox you can use DataTable or Listas strongly typed to obtain a data collection, you can do the following:

private DataTable CategoryAll()
    {
        using (var cn = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
        {
            using (var da = new SqlDataAdapter())
            {
                using (var cmd = cn.CreateCommand())
                {
                    cmd.CommandText = "SELECT CategoryID, CategoryName FROM Categories";
                    da.SelectCommand = cmd;
                    var dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
            }
        }
    }

Load your form

private void Form1_Load(object sender, EventArgs e)
    {
        DataTable dt = CategoryAll();
        comboBox1.DataSource = dt;
        comboBox1.ValueMember = "CategoryID";
        comboBox1.DisplayMember = "CategoryName";

    }

Demonstration:

Note: It is an example code that you should adapt to your needs.

Recommendation: By enclosing the declaration of the object Connection between a block Using ... End Using , we will be sure to close the connection and destroy the resources used by the object , at the end of that block (when End Using is executed). With this we do not have to be aware of calling the method Close (to close the connection), nor the method Dispose (to destroy the object), therefore, it is not necessary to have an external procedure for such tasks.

And as for calling the Open method to open the connection, it is not necessary to make this call if what we want is to fill in a DataTable object, that is, when we call the method Fill of object SqlDataAdapter , because this object will take care of doing it automatically, and to close the connection when it finishes its work.

score 2 · Answer 2

I have a method to fill any combo just passing parameters, here I share it:

    public static void LlenarCombo(string query, string displayMember, string valueMember, ComboBox comboBox1, string connectionString1)
    {
        using (SqlConnection sqlConnection1 = new SqlConnection(connectionString1))
        {
            sqlConnection1.Open();
            DataTable dataTable1 = new DataTable();
            using (SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter(query, sqlConnection1))
            {
                sqlDataAdapter1.Fill(dataTable1);
                comboBox1.DataSource = dataTable1;
                comboBox1.DisplayMember = displayMember;
                comboBox1.ValueMember = valueMember; //identificador
                comboBox1.SelectedIndex = -1; //opcional
            }
        }
    }

According to the example of @ pedro-Ávila, you send it as:


private void Form1_Load(object sender, EventArgs e)
    {
        LlenarCombo("SELECT CategoryID, CategoryName FROM Categories","CategoryName","CategoryID",comboBox1, ConfigurationManager.ConnectionStrings["default"].ToString());
    }

Note: Do not forget to handle exceptions, you can generate syntax errors in the query, in the fields or connection fields.