am loading a combo Box with values from sql db

Question

1.00/5 (4 votes)

See more:

I am loading a combo Box with staff ids from a sql db but am not getting it right.
This is what I did:
i really dont know how to do that, i made a research and tried my hands on it but didnot worked. pls how do i load only
staff id into a combo box

public void loadID()
        {
            SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
            conn.ConnectionString = "Data Source=MICKY-PC;Initial Catalog=SMS;User ID=sa;Password=mike";
            conn.Open();

            SqlCommand cmd = new SqlCommand();
            string sqlQuery = null;
            sqlQuery = "select * from tblstaffpersonaldetails";

            cmd.Connection = conn;
            cmd.CommandText = sqlQuery;
            cmd.CommandType = System.Data.CommandType.Text;

            SqlDataReader dr = null;

            dr = cmd.ExecuteReader();

            this.cboStaffid5.Items.Clear();
            while (dr.Read())
            {
                this.cboStaffid5.Items.Add(dr["staffid"]);
            }
            conn.Close();        
        }

        private void cboStaffid5_SelectedIndexChanged(object sender, EventArgs e)
        {
            loadID();
        }

Posted 29-May-12 9:33am

mikeoabban

Updated 29-May-12 11:37am

v3

Add a Solution

Comments

R. Giskard Reventlov 29-May-12 15:39pm

So what's your question?

mikeoabban 30-May-12 4:12am

how do i load only staff id into a combo box cbostaffid5 after i have created
the loadID method

Sergey Alexandrovich Kryukov 29-May-12 15:43pm

So what? Loading a combo box with values? Would be strange if you loaded it without values... :-)
"Not getting it right" is not informative...
--SA

mikeoabban 30-May-12 4:10am

how do i load only staff id into a combo box cbostaffid5 after i have created
the loadID method

Sergey Alexandrovich Kryukov 29-May-12 15:45pm

Are you sure you really want to select ALL from a table? Why would you ever need it?
--SA

mikeoabban 30-May-12 4:11am

sorry am only selecting staffid from the table

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

MugiwaraUK · Answer 1 · 2012-05-30T01:47:00

Ok the following code should work fine. but there are some queries
1. Why are you populating the combo box on selected index change on the same object?
2. You also need to call the LoadID() method to get the data first otherwise the selected index method wont invoke.

edit: The code snippets on here don't support the method XML comments(///) so i have added them as basic comments

C#

// Populates the combo box with the staffID from the database
private void LoadID()
{
    System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection("Data Source=MICKY-PC;Initial Catalog=SMS;User ID=sa;Password=mike");
    connection.Open();
    System.Data.SqlClient.SqlCommand command = connection.CreateCommand();
    command.CommandText = "select staffid from tblstaffpersonaldetails";
    command.CommandType = CommandType.StoredProcedure;
    System.Data.SqlClient.SqlDataReader reader = null;


    cboStaffid5.Items.Clear();

    try
    {
        reader = command.ExecuteReader();

        if (!reader.HasRows)
        {
            MessageBox.Show("No records to add");
            return;
        }

        while (reader.Read())
        {
            if (!reader["staffid"].Equals(DBNull.Value))
            {
                cboStaffid5.Items.Add(reader["staffid"].ToString());
            }
        }
        MessageBox.Show("Import Completed Successfully Woot!!!");
    }
    catch (Exception ex)
    {
        // shows error message obv
        MessageBox.Show("Error: " + ex.Message);
    }
    finally
    {
        // this is to ensure that the reader and connection are closed properly (even on errors)
        if (reader != null)
        {
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            reader = null;
        }
        if (connection.State != ConnectionState.Closed)
        {
            connection.Close();
        }
    }
}

//reloads the data from the database upon selection change (not sure why this is done)
private void cboStaffID_SelectedIndexChanged(object sender, EventArgs e)
{
    int currentSelectedIndex = (sender as ComboBox).SelectedIndex;
    LoadID();
    (sender as ComboBox).SelectedIndex = currentSelectedIndex;
}

And for those who prefer OleDB, just change the SQLClient Objects in the LoadID method to the following

C#

System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection("Data Source=MICKY-PC;Initial Catalog=SMS;User ID=sa;Password=mike");
connection.Open();

System.Data.OleDb.OleDbCommand command = connection.CreateCommand();
command.CommandText = "select * from tblstaffpersonaldetails";
command.CommandType = CommandType.StoredProcedure;
System.Data.OleDb.OleDbDataReader reader = null;

mohdniyaz169 · Answer 2 · 2012-05-29T09:41:00

only just use instead oledb use SqlClient

C#

try
{
   OleDbConnection con = new OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0;Data Source=D:\\EID.mdb");
   string str = "select * from membersamount";
   con.Open();
   OleDbCommand cmd = new OleDbCommand(str, con);
   OleDbDataReader dr = cmd.ExecuteReader();
   while (dr.Read())
   {
      comboBox3.Items.Add(dr[6]);
   }
   dr.Close();
}
catch (Exception es)
{
   MessageBox.Show(es.Message);
}

mohdniyaz169 · Answer 3 · 2012-05-29T10:04:00

this is for sql db your Q for sql db

C#

con = new SqlConnection(GlobalClass.conn);
con.Open();
string sql = "SELECT EmpID FROM EmployeeSalaryAgreement WHERE EmpID NOT IN('HR001')";
SqlCommand1 = new SqlCommand(sql, con);
SqlDataReader dr = SqlCommand1.ExecuteReader();
while (dr.Read())
{
   cmbEmpID.Items.Add(dr[0]);
   count++;
}
dr.Close();
}

am loading a combo Box with values from sql db

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0