Why does my Select * T-SQL statement not return all the records?

I've built a web application in ASP .NET 3.5 [C#]. I've written a query pull all of the records from a table. Its not doing it and for the life of me I don't know why. The query is straight forward and pulls back all the records when I'm in SQL studio. I may have just noticed the problem but it doesn't seem as if it were doing this all along. The data that its not pulling back is in the table so I don't understand why its not pulling back all of the data. Any thoughts?
LVL 1
Michael SterlingWeb Applications DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just for kicks and giggles, it might help if you could copy-paste the query SQL into this question.
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
no problem. i've tried a few different ways.

SELECT * FROM utathleteinfo ORDER BY AthleteLastName

and

SELECT * FROM dbo.utathleteinfo ORDER BY AthleteLastName

and

SELECT TOP 100 PERCENT * FROM dbo.utathleteinfo ORDER BY AthleteLastName
0
Fernando SotoRetiredCommented:
Can you post the code from setting up the connection string to executing the query and what you are using to display the results.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Michael SterlingWeb Applications DeveloperAuthor Commented:
i can post some of that. let me tell you what i just did, and i'm thinking that some of that isn't needed. i just ran, in code, a query to pull back a count of the records that should be there. the count came back correct. so that tells me that something is happening in how i'm processing the records. what my code does (and i'll include it), is that it pulls each record (row) into and object and then adds that object to an <List> of those kinds of objects. I'm thinking that something is going on with the adding of the records to the objects or the adding of the objects to the array but i'm not sure what it could be right now. as i said earlier, this didn't seem to be a problem before now. i don't recall at this point what i would've done to alter this part of the code as this lives in separate projects that have been "stable" for a while now or have maybe always been broken, as I guess that's possible too. anyway below is the code that adds the records to the objects and then the objects to a <List>. and then the class that defines the object itself.

        public static StudentListBaseList GetAllStudents()
        {
            StudentListBaseList sbl = new StudentListBaseList();
            StudentListBase studentListBase = new StudentListBase();
            try
            {
                STTDatabase db = new STTDatabase("SQLExpress");
                SqlCommand command1 = StudentCommandList.GetAllStudentsCount;
                SqlCommand command = StudentCommandList.GetAllStudentsII;
                command.CommandTimeout = 60;

                db.ExecuteNonQuery(command1);
                using (SqlDataReader dr1 = db.ExecuteReader(command1))
                {
                    while (dr1.Read())
                    {
                        int cnt = Convert.ToInt32(dr1["CNT"]);
                    }
                }

                db.ExecuteNonQuery(command);
                using (SqlDataReader dr = db.ExecuteReader(command))
                {
                    while (dr.Read())
                    {
                        studentListBase = new StudentListBase();
                        studentListBase.AthleteInfoUid = Convert.ToInt32(dr["AthleteInfoUid"]);
                        studentListBase.AthleteUid = dr["AthleteUid"].ToString();
                        studentListBase.AthleteSSN = dr["AthleteSSN"].ToString();
                        studentListBase.LastName = dr["AthleteLastName"].ToString();
                        studentListBase.FirstName = dr["AthleteFirstName"].ToString();
                        studentListBase.AthleteName = dr["AthleteName"].ToString();
                        studentListBase.SemesterTotal = Convert.ToInt32(dr["SemesterTotal"]);
                        studentListBase.SemesterTime = dr["SemesterTime"].ToString();
                        studentListBase.WarningCount = Convert.ToInt32(dr["WarningCount"]);
                        studentListBase.Notes = dr["Notes"].ToString();
                        studentListBase.WeekTotal = dr["WeekTotal"].ToString();
                        //studentListBase.RequiredHours = Convert.ToInt32(dr["RequiredHours"]);
                        studentListBase.RequiredHours = Convert.ToDecimal(dr["RequiredHours"]);
                        studentListBase.BankedMinutes = Convert.ToInt32(dr["BankedMinutes"]);
                        studentListBase.BankedTime = dr["BankedTime"].ToString();
                        studentListBase.SportUid = Convert.ToInt32(dr["SportUid"]);
                        studentListBase.TotalMinutes = Convert.ToInt32(dr["TotalMinutes"]);
                        studentListBase.SignedIn = Convert.ToBoolean(dr["SignedIn"]);
                        studentListBase.UsedBankedMinutes = Convert.ToInt32(dr["UsedBankedMins"]);
                        studentListBase.GPA = dr["GPA"].ToString();
                        studentListBase.Major = dr["Major"].ToString();
                        studentListBase.Active = Convert.ToBoolean(dr["Active"]);
                        studentListBase.Classification = dr["Classification"].ToString();
                        studentListBase.StartTerm = dr["StartTerm"].ToString();

                        sbl.Add(studentListBase);
                    }
                    dr.Close();
                }
            }
            catch (Exception ex)
            {
                STTExceptionHandler.HandleCriticalException(ex);
            }

            return sbl;
        }
    }

Open in new window



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StudyTimeTracker.Common.Domain;

namespace StudyTimeTracker.Common.Domain
{
    /// <summary>
    /// represents the information about a student
    /// </summary>
    public class StudentListBase : IComparable<StudentListBase>
    {
        private enum DateComparisonResult
        {
            Earlier = -1,
            Later = 1,
            TheSame = 0
        };

        private enum SportUidEnum
        {
            MBA = 1,
            MBB,
            MXC,
            MGF,
            MSC,
            MTN,
            WBB,
            WXC,
            WRW,
            WSC,
            WSB,
            WTN,
            WCR,
            WGF,
            WVB,
            DAN
        };

        #region Private Members

        private int p_athleteinfouid;
        private string p_athleteuid;
        private string p_athletessn;
        private string p_lastname;
        private string p_firstname;
        private string p_athletename;
        private int p_semestertotal;
        private string p_semestertime;
        private int p_warningcount;
        private string p_notes;
        private string p_weektotal;
        //private int p_requiredhours;
        private decimal p_requiredhours;
        private int p_bankedminutes;
        private string p_bankedtime;
        private int p_sportuid;
        private int p_totalminutes;
        private bool p_signedin;
        private int p_usedbankedminutes;
        private string p_gpa;
        private string p_major;
        private bool p_active;
        private string p_classification;
        private string p_startterm;

        private int p_inoutuid;
        private DateTime p_timein;
        private DateTime p_timeout;
        private string p_teamname;

        #endregion

        #region Public Members
        public int AthleteInfoUid { get { return p_athleteinfouid; } set { p_athleteinfouid = value; } }
        public string AthleteUid { get { return p_athleteuid; } set { p_athleteuid = value; } }
        public string AthleteSSN { get { return p_athletessn; } set { p_athletessn = value; } }
        public string LastName { get {return p_lastname;} set {p_lastname = value;} }
        public string FirstName { get { return p_firstname; } set { p_firstname = value; } }
        public string AthleteName { get { return p_athletename; } set { p_athletename = value; } }
        public int SemesterTotal { get { return p_semestertotal; } set { p_semestertotal = value; } }
        public string SemesterTime { get { return p_semestertime; } set { p_semestertime = value; } }
        public int WarningCount { get { return p_warningcount; } set { p_warningcount = value; } }
        public string Notes { get { return p_notes; } set { p_notes = value; } }
        public string WeekTotal { get { return p_weektotal; } set { p_weektotal = value; } }
        //public int RequiredHours { get { return p_requiredhours; } set { p_requiredhours = value; } }
        public decimal RequiredHours { get { return p_requiredhours; } set { p_requiredhours = value; } }
        public int BankedMinutes { get { return p_bankedminutes; } set { p_bankedminutes = value; } }
        public string BankedTime { get { return p_bankedtime; } set { p_bankedtime = value; } }
        public int SportUid { get { return p_sportuid; } set { p_sportuid = value; } }
        public int TotalMinutes { get { return p_totalminutes; } set { p_totalminutes = value; } }
        public bool SignedIn { get { return p_signedin; } set { p_signedin = value; } }
        public int UsedBankedMinutes { get { return p_usedbankedminutes; } set { p_usedbankedminutes = value; } }
        public string GPA { get { return p_gpa; } set { p_gpa = value; } }
        public string Major { get { return p_major; } set { p_major = value; } }
        public bool Active { get { return p_active; } set { p_active = value; } }
        public int InOutUid { get { return p_inoutuid; } set { p_inoutuid = value; } }
        public DateTime TimeIn { get { return p_timein; } set { p_timein = value; } }
        public DateTime TimeOut { get { return p_timeout; } set { p_timeout = value; } }
        public string TeamName { get { return p_teamname; } set { p_teamname = value; } }
        public string Classification { get { return p_classification; } set { p_classification = value; } }
        public string StartTerm { get { return p_startterm; } set { p_startterm = value; } }

        #endregion

        public int CompareTo(StudentListBase otherStudentList)
        {
            SortBase.SortDirection sortDir = SortBase.GetStudentListSortDirection();
            if (sortDir == SortBase.SortDirection.Ascending)
                return AthleteUid.CompareTo(otherStudentList.AthleteUid);
            else
                return otherStudentList.AthleteUid.CompareTo(AthleteUid);
        }

        #region Comparers

        public static Comparison<StudentListBase> LastNameComparison = delegate(StudentListBase q1, StudentListBase q2)
        {
            SortBase.SortDirection sortDir = SortBase.GetStudentListSortDirection();

            if (sortDir == SortBase.SortDirection.Ascending)
                return q1.LastName.CompareTo(q2.LastName);
            else
                return q2.LastName.CompareTo(q1.LastName);
        };

        public static Comparison<StudentListBase> AthleteUidComparison = delegate(StudentListBase q1, StudentListBase q2)
        {
            SortBase.SortDirection sortDir = SortBase.GetStudentListSortDirection();

            if (sortDir == SortBase.SortDirection.Ascending)
                return q1.AthleteUid.CompareTo(q2.AthleteUid);
            else
                return q2.AthleteUid.CompareTo(q1.AthleteUid);
        };

        public static Comparison<StudentListBase> TimeInComparison = delegate(StudentListBase q1, StudentListBase q2)
        {
            SortBase.SortDirection sortDir = SortBase.GetStudentListSortDirection();

            if (sortDir == SortBase.SortDirection.Ascending)
                return q1.TimeIn.CompareTo(q2.TimeIn);
            else
                return q2.TimeIn.CompareTo(q1.TimeIn);
        };

        public static Comparison<StudentListBase> SportTeamComparison = delegate(StudentListBase q1, StudentListBase q2)
        {
            SortBase.SortDirection sortDir = SortBase.GetStudentListSortDirection();

            if (sortDir == SortBase.SortDirection.Ascending)
                return q1.SportUid.CompareTo(q2.SportUid);
            else
                return q2.SportUid.CompareTo(q1.SportUid);
        };

        #endregion
    }

    [Serializable]
    public class StudentListSearchArgs
    {
        #region Private Members

        private string studentuid;
        private string team;
        private bool signedin;
        private string lastname;
        private string firstname;
        private int sportuid;

        #endregion

        #region Public Properties

        public string StudentUid
        {
            get { return studentuid; }
            set { studentuid = value; }
        }

        public string Team
        {
            get { return team; }
            set { team = value; }
        }

        public bool SignedIn
        {
            get { return signedin; }
            set { signedin = value; }
        }

        public string LastName
        {
            get { return lastname; }
            set { lastname = value; }
        }

        public string FirstName
        {
            get { return firstname; }
            set { firstname = value; }
        }

        public int SportUid
        {
            get { return sportuid; }
            set { sportuid = value; }
        }

        #endregion
    }

    public class StudentSignInSignOutRec
    {
        #region Private members

        private int p_id;
        private string p_sid;
        private string p_name;
        private string p_timein;
        private string p_notes;
        private string p_programattended;

        #endregion

        #region Public members

        public int ID { get { return p_id; } set { p_id = value; } }
        public string SID { get { return p_sid; } set { p_sid = value; } }
        public string Name { get { return p_name; } set { p_name = value; } }
        public string TimeIn { get { return p_timein; } set { p_timein = value; } }
        public string Notes { get { return p_notes; } set { p_notes = value; } }
        public string ProgramAttended { get { return p_programattended; } set { p_programattended = value; } }

        #endregion
    }

    public class ApplicationControls
    {

        #region Private Members

        private bool p_lockmonitorpassword;
        private string p_monitorpassword;
        private DateTime p_startweekdate;
        private DateTime p_endweekdate;

        #endregion

        #region Public Members

        public bool LockMonitorPassword { get { return p_lockmonitorpassword; } set { p_lockmonitorpassword = value; } }
        public string MonitorPassword { get { return p_monitorpassword; } set { p_monitorpassword = value; } }
        public DateTime StartWeekDate { get { return p_startweekdate; } set { p_startweekdate = value; } }
        public DateTime EndWeekDate { get { return p_endweekdate; } set { p_endweekdate = value; } }

        #endregion

    }

    public class StudentListBaseList : List<StudentListBase> {}

    public class StudentSignInSignOutRecList : List<StudentSignInSignOutRec> { }

}

Open in new window

0
Michael SterlingWeb Applications DeveloperAuthor Commented:
supplemental information: there are 285 records in the table, and i'm only getting 258. for what its worth just thought I'd include that. it doesn't appear to be skipping any of them records, just stopping at 258.
0
Fernando SotoRetiredCommented:
Hi Mike;

The only issue I see is the following code which I think you might of added to get a count of the records. You go through the records one by one but you don't keep a grand total of records you just discard the record and read the next record.
db.ExecuteNonQuery(command1);
using (SqlDataReader dr1 = db.ExecuteReader(command1))
{
    while (dr1.Read())
    {
        int cnt = Convert.ToInt32(dr1["CNT"]);
    }
}

Open in new window

Otherwise it looks like it is filling the list from the code I can see.
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
yes. the code segment you included was just to get a count. that segment will be removed. with what you said about goin through the records and not keeping a total, do you think that's causing a problem or its fine?
0
PortletPaulfreelancerCommented:
I would suggest you do this as the benchmark for number of rows:

in sql

SELECT count(*) FROM utathleteinfo

btw: "TOP 100 PERCENT" should not make any difference, the optimizer most likely will ignore it.
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
yes that is what "command1" does. I execute that 1st to get my count, then set a break point there and check that value. that's how I knew I was getting the correct number of records. so, then , after that I run the query to actually pull the records. for  some reason , while looping through the actual records it stops short of getting all the records. that's what confuses me. also I notice that the number of records varies. sometimes its more records sometimes its less but always fewer than 285. for example as I leave the page and come back some times I would get 60. sometimes maybe 200 etc.
0
Fernando SotoRetiredCommented:
Hi Mike;

To your question, "do you think that's causing a problem or its fine?", I don't believe that is causing an issue. What happens if you were to use a SQL statement in your code as follows

SELECT * FROM utathleteinfo

What do you get in the results?

How are you binding the results to the control?
0
PaulHewsCommented:
The first question I have to ask:  if you put a breakpoint on

STTExceptionHandler.HandleCriticalException(ex);

is an exception being thrown and handled?
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
@PaulHews: no exception is being thrown.

@FernandoSoto: Once I pull my records in to a List object that holds all of the records, I bind to my control (a GridView control) like this:

        gdvAllStudentInfo.DataSource = slbl;
        gdvAllStudentInfo.DataBind();
0
Fernando SotoRetiredCommented:
Hi Mike;

In your function GetAllStudents at the line, return sbl;, can you set a breakpoint and in the locals Window see if you have 285 records in the list please.
0
PaulHewsCommented:
I confess I don't see why you call ExecuteNonQuery.  At the same time, I don't see how that's causing a problem.

It sounds like a re-entrant type of problem.  I would try not using a static method for data access.  At the same time, the static method doesn't seem to have external dependencies, so I don't have any real confidence that's the problem...  Just something to rule out.
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
@PaulHews: Fair enough, I'm open to that idea. How should I declare my method? Also I don't have anything against executing the query another way, what do you suggest?

@FernandoSoto: Currently, in my test environment, yes I get all 285 records at that break point.
0
PaulHewsCommented:
How should I declare my method?
Basically remove the "static" keyword from the method.  Then you have to declare an instance of the containing class in order to call the GetAllStudents method.

Also I don't have anything against executing the query another way, what do you suggest?

Delete the ExecuteNonQuery calls.  Just use ExecuteReader to return your result set.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fernando SotoRetiredCommented:
What happens if you use this to bind to the grid?

gdvAllStudentInfo.DataSource = slbl.ToList();
gdvAllStudentInfo.DataBind();
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
@FernandoSoto and @PaulHews: I tried all suggestions and while nothing broke, it didn't change the outcome. Still getting less records than are in the table.
0
Fernando SotoRetiredCommented:
I thought you're not getting any results out to your grid view.

You stated the following, "@FernandoSoto: Currently, in my test environment, yes I get all 285 records at that break point.", are you filtering it between querying the database and displaying it in the grid?
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
@FernandoSoto: My apologies for my miscommunication. In my test environment I get all of the records. In my production I only get a varying fraction of the total records. I do not have any filtering in place in between the query and the display.
0
Fernando SotoRetiredCommented:
Hi Mike;

Can you try this on your Production code, thanks.

In your function GetAllStudents at the line, return sbl;, can you set a breakpoint and in the locals Window see if you have 285 records in the list please.
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
@FernandoSoto: I will have to send a message up through my code with that value and post it some where. the server where the code lives in production does not have VS on it. So I will do that. Just will take me just a bit to write the necessary code and move it over to the production machine.
0
Fernando SotoRetiredCommented:
You don't have the code on a development machine with a test database?
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
right now the code lives on a web server. that server talks to the database. i do my application development on my own machine and upload it to the development machine.
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
@FernandoSoto: I wound up bringing the count back and posted it into the url. it came back with the correct count, this time, and the grid populated with all of the records. As you suggested  (PaulHews), I commented out the "db.ExecuteNonQuery(command);" lines of code and left them commented out. Not sure if they helped or not. I will remove them after this has consistently given me the correct number of rows / records in my grid. For now I will leave it as it is to see if it breaks again.
0
Fernando SotoRetiredCommented:
OK
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
this is still functioning correctly, to my knowledge. I will close this question today. Thank you.
0
Michael SterlingWeb Applications DeveloperAuthor Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.