CODEWITHSUNDEEP
c#winformsstored-procedurescomboboxpopulate
johncroc
johncroc

Reputation: 37

ComboBoxes not getting populated from stored procedure

I want to populate a ComboBox on a form. I have stored procedures that get the data for my ComboBoxes. My code is below. The problem is my comboboxes don't get populated. When I step through the code, it gets to the "reader = sc.ExecuteReader();" line and does not execute any of the subsequent lines in Pop_ComboBox(). The stored procedure returns "ID" and "Description" (only). What am I doing wrong?

    private void frmInput_Load(object sender, EventArgs e)
    {
        //Connect to the db
        this.sqlConn = new SqlConnection("Server=\"our_server";Database=\"Astra\";Trusted_Connection=yes;");

        //Populate the Equipment Type Listbox
        Pop_ComboBox("exec uspASTRA_GetEquipTypeList;", cboEquipType);


    }

    private void Pop_ComboBox(string sQuery, ComboBox NameOfcbo)
    {
        SqlCommand sc = new SqlCommand(sQuery, this.sqlConn);
        SqlDataReader reader;

        reader = sc.ExecuteReader();
        DataTable dt = new DataTable();

        dt.Columns.Add("ID", typeof(int));
        dt.Columns.Add("Description", typeof(string));
        dt.Load(reader);

        NameOfcbo.ValueMember = "ID";
        NameOfcbo.DisplayMember = "Description";
        NameOfcbo.DataSource = dt;

    }

Upvotes: 0

Views: 1310

Answers (3)

johncroc
johncroc

Reputation: 37

So I wanted to sum this up into a cogent answer to my own question. I need to give credit where it is due, however, because I could not be answering were it not for some great thoughts from others.

First of all, I needed a Try-Catch block around my code. If I'd had that, I would have gotten the information that I did not have a good connection to the db. Thank you Dario-Ramos!

Second, I needed a good connection string and to explicitly connect to the db. Obviously. Thank you, Azhar Khorasany!

Finally, as suggested by Eslam Gamal, I needed to set the command type to "stored procedure," and execute as follows:

sc.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = sc.ExecuteReader();

Thank you all for your suggestions. I learned some valuable lessons and my combo boxes get populated!

Upvotes: 0

Eslam Gamal
Eslam Gamal

Reputation: 74

Explicitly set the command type to StoredProcedure

sc.CommandType = CommandType.StoredProcedure;

and dont Use exec just use the StoredProc name

Upvotes: 0

Azhar Khorasany
Azhar Khorasany

Reputation: 2709

Use a SqlDataAdapter and fill your datatable. e.g:

http://www.dotnetperls.com/sqldataadapter

Then bind your combo boxes datasource with datatable.

Upvotes: 1

Related Questions