[8100 Power]

Hello,

I've been practicing with C# SQL coding and pulling/inserting data into a database. Now, I'm to the point where I want to edit what's in my tables.

My simple solution was (for testing purposes) was to have a combobox populate with EMP_LNAME, EMP_FNAME (successfully working) and when selected to fill in a series of textboxes with the rest of the data in that row.

My problem is actually getting the EMP_ID from that combobox selection so I can correct use my WHERE EMP_ID = "Combobox Value".

SqlCommand cmdEmpDataFIll = mySQLConn.CreateCommand();
            cmdEmpDataFIll.CommandText = "SELECT EMP_ID FROM EMPLOYEE WHERE EMP_ID=" + this.cmbEmpList.SelectedText;

Works great.. now, for those empty textboxes in my forum. How can I associate the primary key (or emp_id) to put back in my where clause for a different command to populate those textboxes?

This post has been edited by 8100 Power: 17 June 2012 - 02:54 PM

[Momerath]

Create a simple class to hold the ID and full name. Add these classes to the combobox and set the DataMember to the full name. You'll get the class from the selection and you'll have the ID

[8100 Power]

I'm not really getting what you mean...

Would that be the simplest solution to this?

[Momerath]

Create a form and add a label and combo box to it. Set the DataMember to "Name". Add this class to your project

public class Data {
    public String Name { get; private set; }
    public int ID { get; private set; }
    public Data(String name, int id) {
        Name = name;
        ID = id;
    }
}

In the Form_Load event, add this

private void Form1_Load(object sender, EventArgs e) {
    Data d;

    d = new Data("one", 1);
    comboBox1.Items.Add(d);
    d = new Data("two", 2);
    comboBox1.Items.Add(d);
    d = new Data("three", 3);
    comboBox1.Items.Add(d);
}

Add a selected index changed event to the combo box that looks something like this

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) {
    Data d = (Data)comboBox1.SelectedItem;
    label1.Text = d.ID.ToString();
}

Run the project and select different items. You'll see the label change as you select them. Now this isn't the best code (no error checking, should use sender in the event rather than the hard coded combobox1, etc.) but it gives you enough to create what you need for your project.

Yes, IMHO this is the simplest method to use.

[h4nnib4l]

Also, you should read this post on parameterizing your queries (instead of concatenating variables into your queries). Reasons you should do this are listed in the post, so I won't list any here, but suffice it to say that this is a better, more secure way of interacting with the DB.

[8100 Power]

Momerath,

I'm not really following where this data pulls the EMP_ID columns information?

h4nnib4l, on 18 June 2012 - 06:58 AM, said:

Also, you should read this post on parameterizing your queries (instead of concatenating variables into your queries). Reasons you should do this are listed in the post, so I won't list any here, but suffice it to say that this is a better, more secure way of interacting with the DB.

Yes, I've already done that for my insert queries. Just haven't moved over to my SELECT queries yet. Thanks for the link, very helpful.

[Momerath]

You pull the EMP_ID and the first and last names. You put the data into a class, set the combobox to the property that displays the name.

[8100 Power]

Momerath, on 18 June 2012 - 09:09 PM, said:

You pull the EMP_ID and the first and last names. You put the data into a class, set the combobox to the property that displays the name.

Ok, for example..

I have the combox box as follows:

//THIS IS FORM_LOAD EVENT
SqlCommand sqlCmdFillEmpCMBOX = mySQLConn.CreateCommand();
            sqlCmdFillEmpCMBOX.CommandText = "SELECT EMP_ID, EMP_LNAME, EMP_FNAME FROM EMPLOYEE ORDER BY EMP_LNAME";
            mySQLConn.Open();

            SqlDataReader drFillEmp = sqlCmdFillEmpCMBOX.ExecuteReader(Commandbehavior.Default);
            while (drFillEmp.Read())
            {
                cmbEmpList.Items.Add(drFillEmp["EMP_LNAME"] + ", " + drFillEmp["EMP_FNAME"]);
            }

I'm filling my combobox from this form load event.

My class is made..

public class Data
    {
        public String Fname { get; private set; }
        public String Lname { get; private set; }
        public int ID { get; private set; }
        public Data(int id, String fname, String lname)
        {
            ID = id;
            Fname = fname;
            Lname = lname;
        }

    }

Now, where I need help:

So, I select a employee from the combobox, I'm not following how to "get" the data from what is virtually in that listing. Do I just pull the data and place into variables, then populate the combo box from there?

Sorry for so much trouble, I've only had college courses on C# and SQL within C# is totally new (as you can see!).

Thanks again for help so far, it's been a great.

[Momerath]

No, don't fill the combo box from the data reader. You need to put the data into the class, then add the class.

[8100 Power]

Momerath, on 19 June 2012 - 08:57 PM, said:

No, don't fill the combo box from the data reader. You need to put the data into the class, then add the class.

I'm just not getting this to work..

Maybe I should use a dataset with a data adapter?

[Momerath]

No, you do this

//THIS IS FORM_LOAD EVENT
SqlCommand sqlCmdFillEmpCMBOX = mySQLConn.CreateCommand();
sqlCmdFillEmpCMBOX.CommandText = "SELECT EMP_ID, EMP_LNAME, EMP_FNAME FROM EMPLOYEE ORDER BY EMP_LNAME";
mySQLConn.Open();

SqlDataReader drFillEmp = sqlCmdFillEmpCMBOX.ExecuteReader(Commandbehavior.Default);
while (drFillEmp.Read()) {
    String name = String.Format("{0}, {1}", drFillEmp["EMP_LNAME"], drFillEmp["EMP_FNAME"]);

    Data d = new Data(name, drFillEmp["EMP_ID"]);
    
    cmbEmpList.Items.Add(d);
}

You are filling the combo box with Data objects. Data objects will have the name and id. You will have set the property mentioned above to "Name", then you'll get back a Data object from SelectedItem. Use the ID property from the Data object and you'll have the EMP_ID of the name that was selected.

This post has been edited by Momerath: 20 June 2012 - 12:32 AM

[baavgai]

Momerath, on 18 June 2012 - 01:58 AM, said:

Yes, IMHO this is the simplest method to use.

I agree with this to a point. However, I have two nits to pick.

First, if you don't define a ToString for that class, you aren't going to get a very helpful listing. Second, never add values one at a time. Even in a simple example, it teaches bad habits.

Rather:

class Data {
	public String Name { get; private set; }
	public int ID { get; private set; }
	public Data(String name, int id) {
		this.Name = name;
		this.ID = id;
	}
	// this is important, this it what will show in the combo box
	public override string ToString() { return this.Name; }

}

public Form1() {
	InitializeComponent();
	this.comboBox1.DataSource = new Data[] {
		new Data("one", 1), new Data("two", 2), new Data("three", 3)
	};
	// note, this will often be the result of a data pull
	// usually a List of object or a DataTable
}

[baavgai]

I should add, doing your own looping is also wasteful. You should be using data adapters and data tables.

e.g.

public Form1() {
	InitializeComponent();
	LoadEmpComboBox(cmbEmpList);
}

private void LoadEmpComboBox(ComboBox cb) {
	SqlCommand cmd = getConnection().CreateCommand();
	cmd.CommandText = "SELECT EMP_ID, EMP_LNAME, EMP_FNAME, EMP_LNAME + ', ' + EMP_FNAME as DisplayValue FROM EMPLOYEE ORDER BY EMP_LNAME";
	DataTable dt = new DataTable();
	new SqlDataAdapter(cmd).Fill(dt);
	cb.DataSource = dt;
	cb.DisplayMember = "DisplayValue";
}

private SqlConnection getConnection() { /* your code here */ }

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) {
	DataRow row = ((DataRowView)cmbEmpList.SelectedItem).Row;
	this.label1.Text = row["EMP_ID"].ToString();
}

Here, we are binding to a DataTable, which is a collection of DataRows. Each row has a ton of properties; you can access the fields by name as objects.

You can also use typed DataSets, which are cleaner. Visual Studio will actually write the code for you...

Hope this helps.

[Momerath]

baavgai, on 20 June 2012 - 04:29 AM, said:

First, if you don't define a ToString for that class, you aren't going to get a very helpful listing.

No need to define ToString as setting the DataMember to "Name" will display the Name property of the class.

[baavgai]

Ah. I see this in you explanation, not in code. I believe this is DisplayMember, not DataMember.

If you're going to roll you own class, knowing that ToString is used by default allows for more complex display like the OP was looking for. e.g.

public override string ToString() { return "(" + this.ID + ") " + this.Name; }