SqlDataReader is not returning all rows
-
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
-
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);
}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
-
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);
}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.
-
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
-
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.
-
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);
}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 :) 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 :suss: If you can't read my code, try converting it here[^]
-
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.
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.
-
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 :) 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 :suss: If you can't read my code, try converting it here[^]
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
-
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);
}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 -
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 easierOne 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()) {}
-
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()) {}
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
