By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,728 Members | 2,462 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,728 IT Pros & Developers. It's quick & easy.

SqlDataReader showing Empty columns until after error.

P: n/a
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.

Dec 7 '06 #1
Share this Question
Share on Google+
13 Replies


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

"lithoman" <li******@gmail.comwrote in message
news:11*********************@j44g2000cwa.googlegro ups.com...
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.

Dec 7 '06 #2

P: n/a
"Marina Levit [MVP]" <so*****@nospam.comwrote in message
news:eP*************@TK2MSFTNGP06.phx.gbl...
That's a key part of this you didn't tell us.
That never ceases to amaze me...!
Dec 7 '06 #3

P: n/a

I would try a different syntax.

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


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" <li******@gmail.comwrote in message
news:11*********************@j44g2000cwa.googlegro ups.com...
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.

Dec 7 '06 #4

P: n/a
Me too, I'm still surprised every time. I just don't get it....

"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
"Marina Levit [MVP]" <so*****@nospam.comwrote in message
news:eP*************@TK2MSFTNGP06.phx.gbl...
>That's a key part of this you didn't tell us.

That never ceases to amaze me...!

Dec 7 '06 #5

P: n/a
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?

Marina Levit [MVP] wrote:
Me too, I'm still surprised every time. I just don't get it....

"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
"Marina Levit [MVP]" <so*****@nospam.comwrote in message
news:eP*************@TK2MSFTNGP06.phx.gbl...
That's a key part of this you didn't tell us.
That never ceases to amaze me...!
Dec 7 '06 #6

P: n/a
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" <li******@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...
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?

Marina Levit [MVP] wrote:
>Me too, I'm still surprised every time. I just don't get it....

"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
"Marina Levit [MVP]" <so*****@nospam.comwrote in message
news:eP*************@TK2MSFTNGP06.phx.gbl...

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

That never ceases to amaze me...!

Dec 7 '06 #7

P: n/a
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];
}
}
----------------------------------------------------------------------------------------------------------
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?

Dec 7 '06 #8

P: n/a
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 H wrote:
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];
}
}
----------------------------------------------------------------------------------------------------------

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?
Dec 7 '06 #9

P: n/a
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?

Marina Levit [MVP] wrote:
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" <li******@gmail.comwrote in message
news:11**********************@80g2000cwy.googlegro ups.com...
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?

Marina Levit [MVP] wrote:
Me too, I'm still surprised every time. I just don't get it....

"Mark Rae" <ma**@markNOSPAMrae.comwrote in message
news:%2****************@TK2MSFTNGP03.phx.gbl...
"Marina Levit [MVP]" <so*****@nospam.comwrote in message
news:eP*************@TK2MSFTNGP06.phx.gbl...

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

That never ceases to amaze me...!
Dec 7 '06 #10

P: n/a
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 H wrote:
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 H wrote:
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];
}
}
----------------------------------------------------------------------------------------------------------
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?
Dec 7 '06 #11

P: n/a
Excuse my typo... It is supposed to contain the value 18...

Dec 7 '06 #12

P: n/a

lithoman wrote:
Does return (int) reader.GetInt32(columnNum); handle the DBNull issue?
No.

Dec 7 '06 #13

P: n/a
So a google search for

"class SafeDataReader"

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


"lithoman" <li******@gmail.comwrote in message
news:11*********************@j44g2000cwa.googlegro ups.com...
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.

Dec 8 '06 #14

This discussion thread is closed

Replies have been disabled for this discussion.