473,395 Members | 2,713 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

SqlDataReader showing Empty columns until after error.

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
13 2803
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
"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

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
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
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
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
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
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
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
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
Excuse my typo... It is supposed to contain the value 18...

Dec 7 '06 #12

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

Dec 7 '06 #13
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Wayno | last post by:
My php logs are coming up empty. I have done all I can think of, and all that made sense to me. Can someone take a look at my php.ini please and tell me what you think may be the problem. I...
3
by: tornado | last post by:
Hi all, I am pretty new to PHP. I was reading PHP manual and trying out the example from 2nd chapter (A simple Tutorial). When i try to print the variable as given in the example it returns...
4
by: mimi | last post by:
Hi Please help me out, I can't find a way to close a sqldatareader when error occur at statement cmd.ExecuteReader(). I can't close it in catch because it is local in try scope and I can't...
2
by: tshad | last post by:
I have a Datagrid that has no rows in it, but I want the title to show, and they aren't. Is there a way to make them visible even if the Datagrid is empty? Thanks, Tom
5
by: Tom Edelbrok | last post by:
I notice that using the SqlDataReader requires the use of ordinal field references rather than by name. For example, do while (myDataReader.Read())...
7
by: Web learner | last post by:
I am trying to create a method GetDataFor(string column) becaues I have to repeat the same statements for several columns but I get an error as follows: The name 'dr' does not exist in the current...
3
by: rn5a | last post by:
A SqlDataReader is populated with the records from a SQL Server 2005 DB table. The records retrieved depends upon 2 conditions (the conditions depend on what a user selects in an ASPX page). If...
1
by: mastershake | last post by:
I'm trying to make some c#. I have a sqldatareader to get the logical file names from *.BAK files. It works on the default instance but if I add a named instance it fails. It gives me this: ...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.