none
SQLDataReader return 1 less row than actual RRS feed

  • Question

  • Newbie here. Trying to learn.

    I have written a successful query against a table with 21 records. 3 of the records have identical data in the column I'm selecting. (I have the table displayed on another monitor so I know the records exist).

    However, when I do:

    If (reader.Read())

    and cycle thru the results with:

    Console.WriteLine((reader["PrmtTNum"]));

    but it always misses the first matching record.

    It does give me the second and third record.

    Any ideas on why it's skipping the first matching record?

    Saturday, January 18, 2020 10:09 PM

Answers

  • If (reader.Read())

    Every time you call the Read() method, it pulls-in one row from the table. Ths row will no longer be readable in the next read operations.

    Therefore, if you first call if (reader.Read()) and then later you do while (reader.Read()) to read all the rows, then the "while" will not be able to see the first row, because it will have been already "swallowed" by the "if".

    Solution: Don't use the "if" statement. It is not needed; if there are no rows, the "while" loop will receive "false" the first time and the whole loop will be bypassed. You will know that there were no rows because nothing will be stored in whatever place your loop would have stored it if it had performed any iterations.

    • Marked as answer by J Vallee Sunday, January 19, 2020 2:37 PM
    Sunday, January 19, 2020 9:43 AM
    Moderator

All replies

  • Newbie here. Trying to learn.

    I have written a successful query against a table with 21 records. 3 of the records have identical data in the column I'm selecting. (I have the table displayed on another monitor so I know the records exist).

    However, when I do:

    If (reader.Read())

    and cycle thru the results with:

    Console.WriteLine((reader["PrmtTNum"]));

    but it always misses the first matching record.

    It does give me the second and third record.

    Any ideas on why it's skipping the first matching record?

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-data-using-a-datareader
    Saturday, January 18, 2020 11:04 PM
  • In the following example I show iterating rows returned from a SELECT/WHERE statement which iterates rows, note I'm strong typing columns via reader.Get methods rather than reader["columnName"]. If you are doing this then there is a problem with the WHERE condition. You should write the SQL not in code but in the database for MS-Access, SSMS for SQL-Server, Toad for Oracle etc. If it works in those tools and fails in code your code is the issue not the query.

    https://github.com/karenpayneoregon/ListViewWindowsForms/blob/master/SqlServerOperations/SqlInformation.cs#L237


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, January 18, 2020 11:41 PM
    Moderator
  • If (reader.Read())

    Every time you call the Read() method, it pulls-in one row from the table. Ths row will no longer be readable in the next read operations.

    Therefore, if you first call if (reader.Read()) and then later you do while (reader.Read()) to read all the rows, then the "while" will not be able to see the first row, because it will have been already "swallowed" by the "if".

    Solution: Don't use the "if" statement. It is not needed; if there are no rows, the "while" loop will receive "false" the first time and the whole loop will be bypassed. You will know that there were no rows because nothing will be stored in whatever place your loop would have stored it if it had performed any iterations.

    • Marked as answer by J Vallee Sunday, January 19, 2020 2:37 PM
    Sunday, January 19, 2020 9:43 AM
    Moderator
  • Oh that makes soooo much sense!!! Thank you!!!
    Sunday, January 19, 2020 2:36 PM