How to loop value in C# using SQL Server

Question

What I want to do is to add items in my comboSubDep wherein it compares what subject the teacher has. For example: if I select Math for my subject only teachers of that subject should appear. But I don't want to compare it directly to a specific subject. I just want it to loop to every subject that the database has. In which the subject is not constant.

con.Open();
cmd = new SqlCommand("SELECT Emp_FName, Emp_LName, T_Subject FROM Employee WHERE Emp_Position = 'Teacher'", con);
rdr = cmd.ExecuteReader();

while (rdr.Read())
{
    string sub = rdr["T_Subject"].ToString();
    string fname = rdr["Emp_FName"].ToString();
    string lname = rdr["Emp_LName"].ToString();
    string fulname = fname + ' ' + lname;

    if (ComboSubDep.Text != sub)
    {
        txtTeacher.Text = "";
        txtTeacher.Items.Clear();
    } 
    else
    {
        txtTeacher.Text = "";
        txtTeacher.Items.Clear();
        txtTeacher.Items.Add(fulname);
    }
}

rdr.Close();
con.Close(); 

Answer 1

You can create a dictionary by subject. See code below

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;


namespace WindowsFormsApplication4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();

        }

        List<Employee> employees = new List<Employee>();
        private void button1_Click(object sender, EventArgs e)
        {
            con.Open();
            cmd = new SqlCommand("SELECT Emp_FName, Emp_LName, T_Subject FROM Employee WHERE Emp_Position = 'Teacher'", con);
            rdr = cmd.ExecuteReader();

            while (rdr.Read())
            {
                Employee newEmployee = new Employee();
                employees.Add(newEmployee);
                newEmployee.sub = rdr["T_Subject"].ToString();
                newEmployee.fname = rdr["Emp_FName"].ToString();
                newEmployee.lname = rdr["Emp_LName"].ToString();
                newEmployee.fulname = fname + ' ' + lname;
            }
            Dictionary<string, List<Employee>> dict = employees.AsEnumerable()
                .GroupBy(x => x.sub, y => y)
                .ToDictionary(x => x.Key, y => y.ToList());

            rdr.Close();
            con.Close(); 
        }


    }
    public class Employee
    {
        public string sub {get; set;}
        public string fname { get; set; }
        public string lname { get; set; }
        public string fulname { get; set; }
    }
}

Answer 2

Not sure what are you trying to complete, but if I understud you correctly you can do this

    txtTeacher.Items.Clear();
    while (rdr.Read())
   {
       string sub = rdr["T_Subject"].ToString();
       string fname = rdr["Emp_FName"].ToString();
       string lname = rdr["Emp_LName"].ToString();
       string fulname = fname + ' ' + lname;

    if(sub=="Math")
    {

        txtTeacher.Items.Add(fulname);
    }
}

This will add to your combo only teachers with subject Math

Answer 3

Your code is horribly confused. You get your reader and then loop through each row. For each row you:

• Get the column contents (fine)
• If the teacher's subject isn't the one you want
  • Clear all items from txtTeacher - ALL OF THEM!
• else (If the teacher's subject is the one you want)
  • Clear all items from the txtTeacher
  • Add the current as a new item to txtTeacher

First point: I assume that txtTeacher is a ComboBox. Usually, when people are giving variable names type prefixes a prefix of txt would signal a TextBox: this makes the code look wierd and is not very supportable. 'cbo' is the more common prefix for a ComboBox. This, however won't stop your code working.

What does is this. Imagine you get 4 teachers in all. The first isn't in the correct subject so you clear the combo box. The second is so you clear the combo and add the teacher. The third is also, so you clear the combo (getting rid of the second) and add the third teacher. The fourth isn't so you clear the combo.

At the end of processing you have an empty combo box. Can you see why? It's a simple thing but the code should look like this:

con.Open();
cmd = new SqlCommand("SELECT Emp_FName,Emp_LName,T_Subject from Employee where Emp_Position = 'Teacher'", con);
rdr = cmd.ExecuteReader();

txtTeacher.Items.Clear;
    while (rdr.Read())
    {
        string sub = rdr["T_Subject"].ToString();
        string fname = rdr["Emp_FName"].ToString();
        string lname = rdr["Emp_LName"].ToString();
        string fulname = fname + ' ' + lname;

    if (ComboSubDep.Text == sub)
    {
        txtTeacher.Items.Add(fulname);
    }
}

You can catch this sort of thing by using the debugger to step through the code line by line. That should show you why this is happening.