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();

Why bother looping here? Just build your query with some grouping and get the whole dataset in a single query. — Sean Lange
– Sean Lange, Commented Apr 27, 2015 at 15:30
It only picks up the teachers on a specific subject... But when I try to select another subject it doesn't show up... — bluffer
– bluffer, Commented Apr 27, 2015 at 15:34
Well your sql is hard coded to only return "Teacher". That should be parameterized if you want to return data for a given position. — Sean Lange
– Sean Lange, Commented Apr 27, 2015 at 15:43
Could you please clarify what the problem is and what you are expecting? It is a bit unclear to me on what you are trying to do. — John Odom
– John Odom, Commented Apr 27, 2015 at 15:44

simon at rcl · Accepted Answer · 2015-04-27 15:55:50Z

4

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.

answered Apr 27, 2015 at 15:55

simon at rcl

7,3691 gold badge21 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bluffer Over a year ago

Yes, I'm sorry for the confusion it is a combobox... I'm actually helping out a friend... and I didn't revised it.Thanks a lot @simon at rcl

simon at rcl Over a year ago

No problem, but do you understand why it isn't working?

bluffer Over a year ago

I was confused... we needed to clear the combobox first and then the loop began to work... And it is working now... Thanks a lot!!!

Yuri · Accepted Answer · 2015-04-27 16:01:41Z

0

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

answered Apr 27, 2015 at 16:01

Yuri

2,9084 gold badges31 silver badges41 bronze badges

Comments

jdweng · Accepted Answer · 2015-04-27 17:07:04Z

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; }
    }
}

Collectives™ on Stack Overflow

How to loop value in C# using SQL Server

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related