SQL Query returns 2 results but only 1 result showing in c# webpage

Question

I have a table for the hours a business is opened called Schedule with four columns: Id, Working, Commencing, and Finishing.

Monday     1/1/1900 8:00:00 AM    1/1/1900 5:00:00 PM
Tuesday    1/1/1900 8:00:00 AM    1/1/1900 5:00:00 PM
Wednesday  1/1/1900 8:00:00 AM    1/1/1900 5:00:00 PM
Thursday   1/1/1900 8:00:00 AM    1/1/1900 5:00:00 PM
Friday     1/1/1900 8:00:00 AM    1/1/1900 3:00:00 PM
Saturday   1/1/1900 12:00:00 AM   1/1/1900 12:00:00 AM
Sunday     1/1/1900 12:00:00 AM   1/1/1900 12:00:00 AM

I used this query to show the hours open in a gridview table and it works fine. I get a table with Monday through Friday showing and the hours open and close.

select * from Schedule where [Commencing] != [Finishing];

I then created a class called Scheduling.

public class Scheduling
{
public int Id { get; set; }
public string Working { get; set; }

public Scheduling(int Id, string Working)
{
    this.Id = Id;
    this.Working = Working;
}
}

And updated my ConnectionClass as follows:

public static ArrayList GetCloseSchedule(string Id)
{
    ArrayList list = new ArrayList();
    string query = string.Format("select * from Schedule where [Commencing] = [Finishing]", Id);

    try
    {
        conn.Open();
        command.CommandText = query;
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            int id = reader.GetInt32(0);
            string Working = reader.GetString(1);

            Scheduling schedules = new Scheduling(id, Working);
            list.Add(schedules);
        }
    }
    finally
    {
        conn.Close();
    }

    return list;
}

And then I have this in my default.aspx.cs file.

str = "select * from Schedule where [Commencing] = [Finishing]";
    com = new SqlCommand(str, con);
    ArrayList Scheduling = ConnectionClass.GetCloseSchedule(lblMsgO.Text);

    foreach (Scheduling schedules in Scheduling)
    {
        sb.Append(string.Format(@"{0}<br />",
           schedules.Working));

        lblMsgO.Text = sb.ToString();

        sb.Clear();
        reader.Close();
        con.Close();
    }

It is a very simple query and I have successfully completed several of them so I am not sure why this one is not working. It shows only Sunday and is skipping Saturday even though both rows appear when I test the SQL query in the database.

Yes, I know. I used that as an example to show that the query works perfectly fine in one scenario, i.e. giving all 5 results which matches the query in the database, but when I change it from != to =, instead of giving me 2 results, as the query does, it only gives me 1 on the c# webpage. — C. Fuller, Jul 17 '18 at 14:15
Did you mean `lblMsgO.Text += sb.ToString();` else you simply overwrite the property in the loop so its value will always be the last result from the rowset. (If so better to assign the sb to the property when the loop ends) — Alex K., Jul 17 '18 at 14:17
Alex K. adding the + sign worked. I am assuming I need the + sign because I am comparing two tables instead of just pulling data from one. — C. Fuller, Jul 17 '18 at 17:25

score 1 · Accepted Answer · answered Jul 18 '18 at 11:14

1

In your default.aspx.cs you are overwriting the value in lblMsg0 every time in a loop. You should pull out writing to label and clearing of StringBuilder outside of loop (if you clear StringBuilder value every tim, you are missing the point of using StringBuilder:

str = "select * from Schedule where [Commencing] = [Finishing]";
com = new SqlCommand(str, con);
ArrayList Scheduling = ConnectionClass.GetCloseSchedule(lblMsgO.Text);

foreach (Scheduling schedules in Scheduling)
{
    sb.Append(string.Format(@"{0}<br />",
       schedules.Working));
}

lblMsgO.Text = sb.ToString();

sb.Clear();
reader.Close();
con.Close();

Also, for connection you should use using block to create and dispose connetion, and not closing it on your own.

answered Jul 18 '18 at 11:14

Caldazar

2,801
13
21

Caldazar, I think that I have it set up right according to this thread: https://stackoverflow.com/questions/61092/close-and-dispose-which-to-call. However, I am very new to c#, so am obviously making mistakes along the way.The section that you saw on my default.aspx.cs page is only one of five (so far, and still building). So, does that make a difference in your recommendation? I did move the lblMsg reference on all of my code per your answer. – C. Fuller Jul 18 '18 at 15:36
When you say one of five, do you mean all using the same `lblMsg0`, or each section using different label? – Caldazar Jul 21 '18 at 11:58
Each section uses a different label. – C. Fuller Jul 21 '18 at 15:42
In that case, for each section this aproach can be used. Only difference would be if they all used same label that you would have to use `lblMsg0.Text += sb.ToString();` because in that case you would want to append text each time. I hope that my answer solved your issue ;) – Caldazar Jul 21 '18 at 17:51

SQL Query returns 2 results but only 1 result showing in c# webpage

1 Answers1