Re: SqlDataReader is not returning all rows - C# Discussion Boards

2-Sep-13 0:57

My SQL command returns 3 rows which is verified in a SQL GUI. I run the same code and SqlDataReader returns only 2 of them. The same sql command returns 3 rows with SqlDataAdapter. Here is my code.
ds has 3 rows.

using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["VPO"].ConnectionString))
        {
            string sql="SELECT DISTINCT A.account_id, A.fname, A.lname, 
            FROM T_Test1 A WITH (NOLOCK) JOIN T_Test2 AF WITH (NOLOCK) ON A.Account_id=AF.Account_id
            WHERE account_status = 'A' AND A.card IS NOT NULL
            AND A.dateFrom >= '09-02-2013 00:00:00' AND A.dateFrom <= '09-30-2013 00:00:00' AND  AF.code = 'INE'";
            SqlCommand command = new SqlCommand(sql.ToString(), connection);
            command.CommandTimeout = 3600;
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            { 
               while (reader.Read())
               {}
            }
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(command.CommandText, connection);
            da.Fill(ds);
        }

Simon_Whale2-Sep-13 1:08

Simon_Whale

2-Sep-13 1:08

I know that isn't an answer to your question but why are you executing this query twice?

NarVish wrote:
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{}
}

and

NarVish wrote:
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command.CommandText, connection);
da.Fill(ds);

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

NarVish2-Sep-13 1:37

2-Sep-13 1:37

Just to show the difference, I added the SqlDataAdapter code. It will be removed.

Simon_Whale2-Sep-13 3:11

Simon_Whale

2-Sep-13 3:11

what happens if the you run the query direct on your database?

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

NarVish2-Sep-13 5:35

2-Sep-13 5:35

If I run the query on database (SQL GUI), it is showing three records

OriginalGriff2-Sep-13 3:46

2-Sep-13 3:46

What are the three rows, and which one is not being returned?

This message is manufactured from fully recyclable noughts and ones. To recycle this message, please separate into two tidy piles, and take them to your nearest local recycling centre.
Please note that in some areas noughts are always replaced with zeros by law, and many facilities cannot recycle zeroes - in this case, please bury them in your back garden and water frequently.

NarVish2-Sep-13 5:36

2-Sep-13 5:36

DataReader didn't read first record.

OriginalGriff2-Sep-13 8:04

2-Sep-13 8:04

Hm. Unlikely - but suspicious.

I use code very similar to yours every day - and it has never missed out a row (particularly not the first row). I notice that the code you show a fragment of isn't the code you are actually using - can you post that bit? It seems unlikely that a "missing the first row" problem would be that specific is it was an SQlReader bug. Oh, and can you show the three rows worth of data and I'll try it here, see what happens.

Eddy Vluggen2-Sep-13 6:23

Eddy Vluggen

2-Sep-13 6:23

NarVish wrote:
I run the same code and SqlDataReader returns only 2 of them.

The DataAdapter does not remove records from the resultset that it receives.

NarVish wrote:
AND A.dateFrom >= '09-02-2013 00:00:00' AND A.dateFrom <= '09-30-2013 00:00:00'

Are you sure those dates are correctly interpreted?

Use a parameterized query to prevent questions like those Smile | :)

Further, "sql" is already a string (no need to call .ToString) and SqlCommand is a disposable.

NarVish wrote:
My SQL command returns 3 rows which is verified in a SQL GUI.

Also do verify that you're pointing to the same database in both environments.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Manfred Rudolf Bihy2-Sep-13 8:42

Manfred Rudolf Bihy

2-Sep-13 8:42

Eddy Vluggen wrote:
Also do verify that you're pointing to the same database in both environments

Good thing to point out! I've been bitten by that one before.

Cheers!

"I had the right to remain silent, but I didn't have the ability!"

Ron White, Comedian

Pete O'Hanlon2-Sep-13 8:45

Pete O'Hanlon

2-Sep-13 8:45

I'm suspicious that you're missing the code inside reader.Read(). I have the feeling that the problem you are looking at lies in that block. As a test, replace your reader.Read line with this

int count = 0;
while (reader.Read())
{
  Debug.WriteLine("Count is {0}", ++count);

Then, when you are debugging your app, take a look at what appears in the output window in Visual Studio.

Chill _Maxxx_

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

NarVish2-Sep-13 19:40

2-Sep-13 19:40

One of the line in using section is reading the first record. In while loop, it is reading from second record. I removed the below if condition and it worked fine. Thank you all for your replies. Sorry for not posting that line, as I thought that line is handling only exception.

if (!reader.Read())
throw new ApplicationException("MISSING Transaction Returned By Financial Institution. Transaction was not found in the database.");
while (reader.Read())
{}

Question about using an if in a string

Pete O'Hanlon2-Sep-13 20:22

Pete O'Hanlon

2-Sep-13 20:22

At least the mystery has been solved. The effect you were after here could have been achieved by using the following instead:

if (!reader.HasRows)
{
  throw new ApplicationException("MISSING Transaction Returned By Financial Institution. Transaction was not found in the database.");
}
while (reader.Read())
{
}

For future reference, the act of calling Read automatically moves the record to the next available record (if one's present) when it completes. It implements a firehose cursor, whereby data is effectively squirted at you.

Chill _Maxxx_

CodeStash - Online Snippet Management | My blog | MoXAML PowerToys | Mole 2010 - debugging made easier

skeeterz711-Sep-13 17:35

skeeterz71

1-Sep-13 17:35

Hi,

New to coding. Trying to teach myself. I am wanting to test an input against a name and if it is true then proceed. If it is not true display something different. Here is what I wrote along with the error. Obviously it's wrong. Any help would be appreciated. Thanks

 Console.WriteLine(name + " What is her name?");
String herName = Console.ReadLine();
            if (herName == ashley)
                 {
                   do this stuff...
                 }

 Error	1  The name 'ashley' does not exist in the current context

modified 1-Sep-13 23:52pm.

Dave Kreskowiak1-Sep-13 18:05

Dave Kreskowiak

1-Sep-13 18:05

You're comparing against a string literal, but what you wrote is comparing against the contents of a variable called ashley, which you never defined. That's why you're getting the error message.

If you're going to specify a string literal, the string must be enclosed in double-quotes:

if (herName == "ashley")

But, this poses a bit of a problem. The comparison is case sensitive. "Ashley" does not equal "ashley". You need something closer to this to fix that:

if (string.Compare(herName, "ashley", StringCompare.CurrentCultureIgnoreCase))

If the Compare method returns 0, the strings are equal according to the rules of the current culture and ignoring the case of any letters.

You REALLY need to pickup a beginners book on C# and work through it. Trying to learn a language through nothing but trial and error and forum posts will only slow you down and frustrate you.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

skeeterz711-Sep-13 18:31

skeeterz71

1-Sep-13 18:31

Ok just going with the first example you gave me:

Console.WriteLine(name + " What is her name?");
String herName = Console.ReadLine();
            if(herName == "ashley")
               {
                   do this stuff..
               }
Error	1	Use of unassigned local variable 'ashley'	
             {

I used the 2nd method as well and still get errors. The variable Ashley doesn't com into play until user input so I don't understand why I am getting the error.

Dave Kreskowiak1-Sep-13 18:37

Dave Kreskowiak

1-Sep-13 18:37

Go back and read what I said again. Somewhere you have the string ashley without double-quotes around it.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

skeeterz711-Sep-13 18:46

skeeterz71

1-Sep-13 18:46

I thought when the user input ashley here

String herName = Console.ReadLine();

The string with the name ashley would be created at that time. Anyhow I'm just not getting it. The code I have written works until I put an if statement into it Frown | :(

FYI I do not have the word Ashley anywhere else in the code. If I remove the if statement, the program works fine.

modified 2-Sep-13 1:02am.

BillWoodruff1-Sep-13 19:42

BillWoodruff

1-Sep-13 19:42

I strongly agree with Dave's feedback that you need to get a good basic book on .NET, and go over the fundamentals: you are "spinning your wheels here."

Try this free book from the renowned educator Charles Petzold, ".NET Book Zero:" [^]. From that site you can download either a .pdf file, or an .xps version of the book, and a separate .zip file is available there for download that contains all the code examples in the book.

good luck, Bill

Google CEO, Erich Schmidt: "I keep asking for a product called Serendipity. This product would have access to everything ever written or recorded, know everything the user ever worked on and saved to his or her personal hard drive, and know a whole lot about the user's tastes, friends and predilections." 2004, USA Today interview

OriginalGriff2-Sep-13 3:58

2-Sep-13 3:58

No, no, it just doesn't work like that!

Forget computers for a moment, and suppose you are on the phone, talking to a company. The lady at the other end asks you a question "What is your name?"
You reply "Skeeterz".

Does that move you physically into her company, or automatically create a file in her computer?
Of course not! That would be silly...

What it does do is transfer a small amount of information from one place (your home) to another (her office) - and she will make a note of it by jotting it on a piece of paper, perhaps, or typing it into her computer to see if there is any information on you allready.

The computer program you wrote does the same thing: It asks "What is your name?" and the user replies "ashley" - that transfers the information from the user space into the program space, and stores it in memory, putting a "reminder" for what her name is into the variable "herName". In future, whenever you want to use the users name, you just use the variable "herName":

Console.WriteLine("Hello " + herName + " and welcome!");

UserDetails herInfo = LookupInTheDatabase(herName);

AmitGajjar2-Sep-13 20:10

AmitGajjar

2-Sep-13 20:10

Quote:
Does that move you physically into her company, or automatically create a file in her computer?
Of course not! That would be silly...

Hahaha that would be awesome if person can physically move... kind of Mass transfer of human being!!! You will see in sci fi movies.

Thanks
-Amit Gajjar

OriginalGriff2-Sep-13 21:24

2-Sep-13 21:24

Um. Might have side effects[^]