DataReader not getting all recs

[slatet]

I'm using a datareader for the first time. I'm using an example that I found online to export values to an excel spreadsheet. It seems to be skipping a row. If it is supposed to return 1 row, it is empty. If it is supposed to return 2 rows, it skips the first row and only displays the second row.

This is the call to the database:
//Get the sql string

string SqlString = "SELECT * FROM MASTER_NODE";

//Create a new command object
SqlCommand sc = new SqlCommand();
//Set the connection
sc.Connection = new SqlConnection("...");

//Open the connection
sc.Connection.Open();

//Set the command type
sc.CommandType = CommandType.Text;

//Set the SQL String
sc.CommandText = SqlString;

//Execute the SQL Statement/Procedure
SqlDataReader RS = sc.ExecuteReader();


This is where the data is output:
MemoryStream ReturnStream = new MemoryStream();
try
{
//make the update to the table changing assessors
SqlDataReader RS = ExecuteReaderCommand(CmdGetActiveCurrent);

//Create a streamwriter to write to the memory stream
StreamWriter sw = new StreamWriter(ReturnStream);

if (RS.HasRows)
{

//Create a header for the file from the names of the
//columns that we are retrieving
RS.Read();
string tempstring = "";
for (int counter = 0; counter < RS.FieldCount;counter++)
{
tempstring += RS.IsDBNull(counter)?"":RS.GetName(counter) + "\t";
}
//Write the row of data to the Memory Stream.
sw.WriteLine(tempstring);

//Loop through the data row and write the contents to the memory
//stream.
while (RS.Read())
{
//Put each columns' data value into a string separated by a tab.
tempstring = "";
for (int counter = 0; counter < RS.FieldCount;counter++)
{
tempstring += RS.IsDBNull(counter)?"":RS.GetValue(counter).ToString() + "\t";
}
//Write the row of data to the Memory Stream.
sw.WriteLine(tempstring);
}

}
//Clean up the stream writer
sw.Flush();
sw.Close();
//Clean up the data reader
RS.Close();

If anyone has any ideas, it would be appreciated.

 

[brd24gor]

Which row are you not getting: the first or the last one?

 

[slatet]

The first row. It will get the columns, then skip the first row, then get the second row.

 

[brd24gor]

It looks to me like you are reading two records from your DataReader before you start processing values. When you do your first RS.Read(), you read the first record returned by your SQL statement. You get your column info, but then you don't process the values in that record. When you do your second RS.Read at the beginning of your while loop, you advance to the next record and then process values.

What you need to do is make sure to process the values of that first record before you read another one. I would just move your while loop to the first to you .Read() and set a flag to determine the first time you loop so you can process columns.

//Get the sql string
string SqlString = "SELECT * FROM MASTER_NODE";

//Create a new command object
SqlCommand sc = new SqlCommand();
//Set the connection
sc.Connection = new SqlConnection("...");

//Open the connection
sc.Connection.Open();
//Set the command type
sc.CommandType = CommandType.Text;

//Set the SQL String
sc.CommandText = SqlString;

//Execute the SQL Statement/Procedure
SqlDataReader RS = sc.ExecuteReader();


MemoryStream ReturnStream = new MemoryStream();
try
{
	//make the update to the table changing assessors
	SqlDataReader RS = ExecuteReaderCommand(CmdGetActiveCurrent);

	//Create a streamwriter to write to the memory stream
	StreamWriter sw = new StreamWriter(ReturnStream);

	if (RS.HasRows)
	{
		//Loop through the data row and write the contents to the memory
		//stream.
		bool firstLoop = true;
		while (RS.Read())
		{
			if(firstLoop)
			{
				//Create a header for the file from the names of the
				//columns that we are retrieving
				string tempstring = "";
				for (int counter = 0; counter < RS.FieldCount;counter++)
				{
					tempstring += RS.IsDBNull(counter)?"":RS.GetName(counter) + "\t";
				}
				//Write the row of data to the Memory Stream.
				sw.WriteLine(tempstring);
				firstLoop = false;
			}

			//Put each columns' data value into a string separated by a tab.
			tempstring = "";
			for (int counter = 0; counter < RS.FieldCount;counter++)
			{
				tempstring += RS.IsDBNull(counter)?"":RS.GetValue(counter).ToString() + "\t";
			}
			//Write the row of data to the Memory Stream.
			sw.WriteLine(tempstring);
		}

	}
	//Clean up the stream writer
	sw.Flush();
	sw.Close();
	//Clean up the data reader
	RS.Close();

Hope this helps,
--Brad

 

[slatet]

That did it! Thanks for the help!

 

[obislavu]

You only have to remove the first RS.Read(). That is all!
To retrieve the column names e.g. call RS.GetName() from the DataReader, there is no need to call Read().
The call to Read() is required when you want to access the data from a row.
Also, use a StringBuilder instead of "tempstring" variable.
The string is not recommended in such cases.
obislavu