SqlDataReader showing Empty columns until after error.

L

lithoman

I'm stumped here. I run the procedure Batch_Select against the database
with @ID=18 and I get the expected data.
When it loads into a SqlDataReader, it gets messed up somehow.
Initially, after the reader.Read(), it has a row with 13 data columns,
but they're all empty. So, my GetInt() function throws an error. When
it jumps to the catch(), reader's columns then show the proper data.
What gives?

I have reader._data[0] (which is the ID column) in my variable Watch
and it should be an Int32 and contain the value 18.
After reader.Read() it has _type=Empty; Value=;
The next line, int tid, throws an error {"Specified cast is not
valid."} and jumps to catch()
After catch(), however, it has _type=Int32; Value=18;

----------------------------------------------------------------------------------------------------------
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Batch_Select", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
cmd.Parameters["@ID"].Value = 18;
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
reader.Read();
int tid = GetInt(reader, "ID");
int tpartid = GetInt(reader, "PartID");
string tpartnum = GetString(reader, "Part#");
string tpartname = GetString(reader, "PartName");
string tdesc = GetString(reader, "Description");
string trev = GetString(reader, "Revision");
string tserial = GetString(reader, "SerialNumber");
int tqty = GetInt(reader, "Quantity");
string ttdr = GetString(reader, "TDR");
string trm = GetString(reader, "RM");
string trmlot = GetString(reader, "RMlot");
int tstatus = GetInt(reader, "StatusID");
DateTime tdate = GetDate(reader, "DateBorn");
}
else
{
throw new ApplicationException("Retrieval Error:<br />
Unable to load Part: " + id);
}
}
catch (Exception err)
{
throw new ApplicationException("Data Error:<br />" +
err.Message);
}
finally
{
con.Close();
}
----------------------------------------------------------------------------------------------------------

I'm sure someone is going to ask, so here's the GetInt()

----------------------------------------------------------------------------------------------------------
public static int GetInt(IDataReader reader, string columnName)
{
int columnNum = reader.GetOrdinal(columnName);
if (reader.IsDBNull(columnNum))
{
return 0;
}
else
{
return (int)reader[columnName];
}
}
----------------------------------------------------------------------------------------------------------

And this exact same code snippet format is working fine in another
function for another Stored Procedure, thus my confusion. This code is
in a different class, however. I'm trying to figure out if there is
anything in the class that could interfere.
 
M

Marina Levit [MVP]

What is the error that gets thrown exactly? That's a key part of this you
didn't tell us.

lithoman said:
I'm stumped here. I run the procedure Batch_Select against the database
with @ID=18 and I get the expected data.
When it loads into a SqlDataReader, it gets messed up somehow.
Initially, after the reader.Read(), it has a row with 13 data columns,
but they're all empty. So, my GetInt() function throws an error. When
it jumps to the catch(), reader's columns then show the proper data.
What gives?

I have reader._data[0] (which is the ID column) in my variable Watch
and it should be an Int32 and contain the value 18.
After reader.Read() it has _type=Empty; Value=;
The next line, int tid, throws an error {"Specified cast is not
valid."} and jumps to catch()
After catch(), however, it has _type=Int32; Value=18;

----------------------------------------------------------------------------------------------------------
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Batch_Select", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
cmd.Parameters["@ID"].Value = 18;
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
reader.Read();
int tid = GetInt(reader, "ID");
int tpartid = GetInt(reader, "PartID");
string tpartnum = GetString(reader, "Part#");
string tpartname = GetString(reader, "PartName");
string tdesc = GetString(reader, "Description");
string trev = GetString(reader, "Revision");
string tserial = GetString(reader, "SerialNumber");
int tqty = GetInt(reader, "Quantity");
string ttdr = GetString(reader, "TDR");
string trm = GetString(reader, "RM");
string trmlot = GetString(reader, "RMlot");
int tstatus = GetInt(reader, "StatusID");
DateTime tdate = GetDate(reader, "DateBorn");
}
else
{
throw new ApplicationException("Retrieval Error:<br />
Unable to load Part: " + id);
}
}
catch (Exception err)
{
throw new ApplicationException("Data Error:<br />" +
err.Message);
}
finally
{
con.Close();
}
----------------------------------------------------------------------------------------------------------

I'm sure someone is going to ask, so here's the GetInt()

----------------------------------------------------------------------------------------------------------
public static int GetInt(IDataReader reader, string columnName)
{
int columnNum = reader.GetOrdinal(columnName);
if (reader.IsDBNull(columnNum))
{
return 0;
}
else
{
return (int)reader[columnName];
}
}
----------------------------------------------------------------------------------------------------------

And this exact same code snippet format is working fine in another
function for another Stored Procedure, thus my confusion. This code is
in a different class, however. I'm trying to figure out if there is
anything in the class that could interfere.
Click to expand...
 
S

sloan

I would try a different syntax.

reader.Read();
int tid = GetInt(reader, "ID");
Click to expand...



reader.Read();
object o = reader.GetValue(0); // 0 is the ordinal column value
int tid = reader.GetInt32(0);

Why use object? Just in case the type is not what youre expecting, you can
figure out the type that way.
Don't use it for production code (the .GetValue that is) .... just til you
get the kinks out.




lithoman said:
I'm stumped here. I run the procedure Batch_Select against the database
with @ID=18 and I get the expected data.
When it loads into a SqlDataReader, it gets messed up somehow.
Initially, after the reader.Read(), it has a row with 13 data columns,
but they're all empty. So, my GetInt() function throws an error. When
it jumps to the catch(), reader's columns then show the proper data.
What gives?

I have reader._data[0] (which is the ID column) in my variable Watch
and it should be an Int32 and contain the value 18.
After reader.Read() it has _type=Empty; Value=;
The next line, int tid, throws an error {"Specified cast is not
valid."} and jumps to catch()
After catch(), however, it has _type=Int32; Value=18;

-------------------------------------------------------------------------- --------------------------------
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Batch_Select", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
cmd.Parameters["@ID"].Value = 18;
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
reader.Read();
int tid = GetInt(reader, "ID");
int tpartid = GetInt(reader, "PartID");
string tpartnum = GetString(reader, "Part#");
string tpartname = GetString(reader, "PartName");
string tdesc = GetString(reader, "Description");
string trev = GetString(reader, "Revision");
string tserial = GetString(reader, "SerialNumber");
int tqty = GetInt(reader, "Quantity");
string ttdr = GetString(reader, "TDR");
string trm = GetString(reader, "RM");
string trmlot = GetString(reader, "RMlot");
int tstatus = GetInt(reader, "StatusID");
DateTime tdate = GetDate(reader, "DateBorn");
}
else
{
throw new ApplicationException("Retrieval Error:<br />
Unable to load Part: " + id);
}
}
catch (Exception err)
{
throw new ApplicationException("Data Error:<br />" +
err.Message);
}
finally
{
con.Close();
}
-------------------------------------------------------------------------- --------------------------------

I'm sure someone is going to ask, so here's the GetInt()

-------------------------------------------------------------------------- --------------------------------
public static int GetInt(IDataReader reader, string columnName)
{
int columnNum = reader.GetOrdinal(columnName);
if (reader.IsDBNull(columnNum))
{
return 0;
}
else
{
return (int)reader[columnName];
}
}
-------------------------------------------------------------------------- --------------------------------

And this exact same code snippet format is working fine in another
function for another Stored Procedure, thus my confusion. This code is
in a different class, however. I'm trying to figure out if there is
anything in the class that could interfere.
Click to expand...
 
L

lithoman

The specific error I mentioned is "Specified cast is not valid." which
is thrown by the line
return (int)reader[columnName]; (in the GetInt() function)
because it is trying to cast an Empty sql column as an int. I uderstand
the error, and I can certainly fix that by checking for Empty. What I
am concerned with, however, is why is the data not there?

The Stored Procedure is returning one row with data. I have confirmed
that. The code isn't seeing that row at first, however. When I am
debugging, after reader.Read() all the columns are Empty, any one of
which causes an error condition. As soon as processing passes to the
catch() statement, the reader's columns are no longer empty, but show
exactly the data I am expecting.

What is causing reader to return empty after the Read() statement, but
show me the data after an error is thrown?
 
M

Marina Levit [MVP]

You know, I read your message several times, and I couldn't see the error
message as it was burried in with a whole bunch of other stuff. Sorry I
missed it, but it was very hard to spot and didn't stand out.

I think the issue is - what value does the column have at the moment? All
that means, is that whatever it is, it cannot be converted into an integer.
It doesn't mean it's empty - how do you define empty anyway? It's not
DBNull, since you are checking for that. A numeric can only have a value of
DBNull or a number - so it looks to me like your stored procedure is
returning a non-numeric data type for that column. Perhaps it is returning a
string - or the empty string, don't know. Maybe it's not an integer and has
decimal places, and so the conversion is failing somehow?

You should try retrieving the value of this column and see exactly what it
is. Try calling GetFieldType on the datareader for that column to see which
datatype that column is.

lithoman said:
The specific error I mentioned is "Specified cast is not valid." which
is thrown by the line
return (int)reader[columnName]; (in the GetInt() function)
because it is trying to cast an Empty sql column as an int. I uderstand
the error, and I can certainly fix that by checking for Empty. What I
am concerned with, however, is why is the data not there?

The Stored Procedure is returning one row with data. I have confirmed
that. The code isn't seeing that row at first, however. When I am
debugging, after reader.Read() all the columns are Empty, any one of
which causes an error condition. As soon as processing passes to the
catch() statement, the reader's columns are no longer empty, but show
exactly the data I am expecting.

What is causing reader to return empty after the Read() statement, but
show me the data after an error is thrown?
Me too, I'm still surprised every time. I just don't get it....
Click to expand...
Click to expand...
 
P

Phil H

I'm sure someone is going to ask, so here's the GetInt()
----------------------------------------------------------------------------------------------------------
public static int GetInt(IDataReader reader, string columnName)
{
int columnNum = reader.GetOrdinal(columnName);
if (reader.IsDBNull(columnNum))
{
return 0;
}
else
{
return (int)reader[columnName];
}
}
----------------------------------------------------------------------------------------------------------
Click to expand...

Please forgive me if this is a dumb question but is there any
particular reason why you don't use:

return (int) reader.GetInt32(columnNum)

to read off what you expect to be a 4 byte integer?
 
P

Phil H

Just had another thought

The return statement of your GetInt() function should be:

return reader[ColumnNum]

NOT Reader[ColumnName] as you have it above!

The casting error you are getting which is throwing the exception may
be the attempt to cast the string 'ColumnName' to an integer for
indexing (which should be *ColumnNum*).

That at least would explain why the Catch() doesn't show anything wrong
with the actual data in reader.

Am I right?

Phil said:
I'm sure someone is going to ask, so here's the GetInt()

----------------------------------------------------------------------------------------------------------
public static int GetInt(IDataReader reader, string columnName)
{
int columnNum = reader.GetOrdinal(columnName);
if (reader.IsDBNull(columnNum))
{
return 0;
}
else
{
return (int)reader[columnName];
}
}
----------------------------------------------------------------------------------------------------------
Click to expand...

Please forgive me if this is a dumb question but is there any
particular reason why you don't use:

return (int) reader.GetInt32(columnNum)

to read off what you expect to be a 4 byte integer?
Click to expand...
 
L

lithoman

Let's take the first column for instance. It is supposed to contain the
value 8. When I run the Procedure in SQL Manager, I get exactly that.
When I debug the app and step through the code, I watch the contents of
reader._data[0]. Right after reader.Read(), here are a sample of
relevant values:

_isNull : false
_type: Empty
IsEmpty: true
IsNull: false
Int32: 'reader._data[0].Int32' threw an exception of type
System.InvalidCastException'
Value: {}

Now as far as I know (I am quite new to .Net, however) Read() makes the
next record available, and that record's content is in reader until
Read() is invoked again or the reader is destroyed. But after the error
is thrown that same object reads:

_isNull : false
_type: Int32
IsEmpty: false
IsNull: false
Int32: 18
Value: 18

The same is happening with all columns of that first record. Any idea
why the change in content?
You know, I read your message several times, and I couldn't see the error
message as it was burried in with a whole bunch of other stuff. Sorry I
missed it, but it was very hard to spot and didn't stand out.

I think the issue is - what value does the column have at the moment? All
that means, is that whatever it is, it cannot be converted into an integer.
It doesn't mean it's empty - how do you define empty anyway? It's not
DBNull, since you are checking for that. A numeric can only have a value of
DBNull or a number - so it looks to me like your stored procedure is
returning a non-numeric data type for that column. Perhaps it is returning a
string - or the empty string, don't know. Maybe it's not an integer and has
decimal places, and so the conversion is failing somehow?

You should try retrieving the value of this column and see exactly what it
is. Try calling GetFieldType on the datareader for that column to see which
datatype that column is.

lithoman said:
The specific error I mentioned is "Specified cast is not valid." which
is thrown by the line
return (int)reader[columnName]; (in the GetInt() function)
because it is trying to cast an Empty sql column as an int. I uderstand
the error, and I can certainly fix that by checking for Empty. What I
am concerned with, however, is why is the data not there?

The Stored Procedure is returning one row with data. I have confirmed
that. The code isn't seeing that row at first, however. When I am
debugging, after reader.Read() all the columns are Empty, any one of
which causes an error condition. As soon as processing passes to the
catch() statement, the reader's columns are no longer empty, but show
exactly the data I am expecting.

What is causing reader to return empty after the Read() statement, but
show me the data after an error is thrown?
Me too, I'm still surprised every time. I just don't get it....


That's a key part of this you didn't tell us.

That never ceases to amaze me...!
Click to expand...
Click to expand...
Click to expand...
 
L

lithoman

reader[ColumnNum] and reader[ColumnName] are both valid methods to get
at the value of the reader column.

As for the previous question, this was the work-around I found for
dealing with DBNulls which don't convert directly to .Net nulls,
throwing a cast error instead. It's been working very well for me so I
haven't bothered to look into a different method.

Does return (int) reader.GetInt32(columnNum); handle the DBNull issue?

Phil said:
Just had another thought

The return statement of your GetInt() function should be:

return reader[ColumnNum]

NOT Reader[ColumnName] as you have it above!

The casting error you are getting which is throwing the exception may
be the attempt to cast the string 'ColumnName' to an integer for
indexing (which should be *ColumnNum*).

That at least would explain why the Catch() doesn't show anything wrong
with the actual data in reader.

Am I right?

Phil said:
I'm sure someone is going to ask, so here's the GetInt()

----------------------------------------------------------------------------------------------------------
public static int GetInt(IDataReader reader, string columnName)
{
int columnNum = reader.GetOrdinal(columnName);
if (reader.IsDBNull(columnNum))
{
return 0;
}
else
{
return (int)reader[columnName];
}
}
----------------------------------------------------------------------------------------------------------
Click to expand...

Please forgive me if this is a dumb question but is there any
particular reason why you don't use:

return (int) reader.GetInt32(columnNum)

to read off what you expect to be a 4 byte integer?
Click to expand...
Click to expand...
 
S

sloan

So a google search for

"class SafeDataReader"

or go here
http://www.lhotka.net/cslanet/download10.aspx




lithoman said:
I'm stumped here. I run the procedure Batch_Select against the database
with @ID=18 and I get the expected data.
When it loads into a SqlDataReader, it gets messed up somehow.
Initially, after the reader.Read(), it has a row with 13 data columns,
but they're all empty. So, my GetInt() function throws an error. When
it jumps to the catch(), reader's columns then show the proper data.
What gives?

I have reader._data[0] (which is the ID column) in my variable Watch
and it should be an Int32 and contain the value 18.
After reader.Read() it has _type=Empty; Value=;
The next line, int tid, throws an error {"Specified cast is not
valid."} and jumps to catch()
After catch(), however, it has _type=Int32; Value=18;

-------------------------------------------------------------------------- --------------------------------
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Batch_Select", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));
cmd.Parameters["@ID"].Value = 18;
SqlDataReader reader;
try
{
con.Open();
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
reader.Read();
int tid = GetInt(reader, "ID");
int tpartid = GetInt(reader, "PartID");
string tpartnum = GetString(reader, "Part#");
string tpartname = GetString(reader, "PartName");
string tdesc = GetString(reader, "Description");
string trev = GetString(reader, "Revision");
string tserial = GetString(reader, "SerialNumber");
int tqty = GetInt(reader, "Quantity");
string ttdr = GetString(reader, "TDR");
string trm = GetString(reader, "RM");
string trmlot = GetString(reader, "RMlot");
int tstatus = GetInt(reader, "StatusID");
DateTime tdate = GetDate(reader, "DateBorn");
}
else
{
throw new ApplicationException("Retrieval Error:<br />
Unable to load Part: " + id);
}
}
catch (Exception err)
{
throw new ApplicationException("Data Error:<br />" +
err.Message);
}
finally
{
con.Close();
}
-------------------------------------------------------------------------- --------------------------------

I'm sure someone is going to ask, so here's the GetInt()

-------------------------------------------------------------------------- --------------------------------
public static int GetInt(IDataReader reader, string columnName)
{
int columnNum = reader.GetOrdinal(columnName);
if (reader.IsDBNull(columnNum))
{
return 0;
}
else
{
return (int)reader[columnName];
}
}
-------------------------------------------------------------------------- --------------------------------

And this exact same code snippet format is working fine in another
function for another Stored Procedure, thus my confusion. This code is
in a different class, however. I'm trying to figure out if there is
anything in the class that could interfere.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQLDataReader stops responding after reading. 0
System.ApplicationError in Data access class 4
*.csv to *.txt after adding columns 7
how to close sqldatareader when error occur 4
Dropdownlist not showing all from datasource and default is not bl 1
Image Not Showing 0
instance of sqlDataReader - is not available to an exteranl method 7
Databinding a SQLDataReader to a DataGrid control 8

Members online

No members online now.
Total: 32 (members: 0, guests: 32)
Robots: 427

Forum statistics

Threads
473,769
Messages
2,569,580
Members
45,055
Latest member
SlimSparkKetoACVReview

Latest Threads

Top