Don't Understand Error Message (Database)

[Jonathan Wood]

The following code raises the error "Specified cast is not valid."

MembershipUser user = Membership.GetUser(userId);
DataSet ds = DataLayer.ExecQueryData("SELECT * FROM mc_Clients WHERE
UserId='" + userId.ToString() + "'");
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
return null;
ClientEx client = new ClientEx();
client.ClientData = new Client();
client.ClientData.Email = user.UserName;
client.ClientData.UserID = user.ProviderUserKey;
client.ClientData.LastLogin = user.LastLoginDate;
DataRow dr = ds.Tables[0].Rows[0];

// ...

if (!Convert.IsDBNull(dr["Sex"]))
client.Sex = (int)dr["Sex"]; <<== ERROR HERE!

// ...

In the database table, Sex has a data type=smallint and nullable=True, and
client.Sex has a data type of int.

If I attempt to examine the contents of dr["Sex"] in the debugger, it shows
a value of 0x0000. If I attempt to examine the contents of (int)dr["Sex"] in
the debugger, it shows an error about not being able to unbox (sorry I no
longer have the exact text).

Any suggestions?

 

[Mark Rae [MVP]]

The following code raises the error "Specified cast is not valid."

Yes, it would do...

if (!Convert.IsDBNull(dr["Sex"]))

if (dr["Sex"] != DBNull.Value)

In the database table, Sex has a data type=smallint

So the Sex field can have a range of values from -32,768 to 32,767...?
http://msdn2.microsoft.com/en-us/library/aa933198(SQL.80).aspx

 

[Peter Bromberg [C# MVP]]

What type is client.Sex? Looks like whatever it is, it cannot accept int as a
value. Unless the Convert of the (int)dr["Sex"] is failing because it
itself is not an integer.

-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com

 

[Jonathan Wood]

As stated, client.Sex is an int.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

What type is client.Sex? Looks like whatever it is, it cannot accept int
as a
value. Unless the Convert of the (int)dr["Sex"] is failing because it
itself is not an integer.

-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com

The following code raises the error "Specified cast is not valid."

MembershipUser user = Membership.GetUser(userId);
DataSet ds = DataLayer.ExecQueryData("SELECT * FROM mc_Clients WHERE
UserId='" + userId.ToString() + "'");
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count ==
0)
return null;
ClientEx client = new ClientEx();
client.ClientData = new Client();
client.ClientData.Email = user.UserName;
client.ClientData.UserID = user.ProviderUserKey;
client.ClientData.LastLogin = user.LastLoginDate;
DataRow dr = ds.Tables[0].Rows[0];

// ...

if (!Convert.IsDBNull(dr["Sex"]))
client.Sex = (int)dr["Sex"]; <<== ERROR HERE!

// ...

In the database table, Sex has a data type=smallint and nullable=True,
and
client.Sex has a data type of int.

If I attempt to examine the contents of dr["Sex"] in the debugger, it
shows
a value of 0x0000. If I attempt to examine the contents of (int)dr["Sex"]
in
the debugger, it shows an error about not being able to unbox (sorry I no
longer have the exact text).

Any suggestions?

 

[Jonathan Wood]

Mark,

The following code raises the error "Specified cast is not valid."

Yes, it would do...

if (!Convert.IsDBNull(dr["Sex"]))

if (dr["Sex"] != DBNull.Value)

So what does that mean? Does that mean dr["Sex"] was, in fact, null but my
check was wrong? And what the heck does Convert.IsDBNull() do? This seemed
to work on some other fields.

Actually, I just tried this:

if (dr["Sex"] != DBNull.Value)
client.Sex = (int)dr["Sex"];

And I seem to get exactly the same error on the same line.

So the Sex field can have a range of values from -32,768 to 32,767...?

Hey, we like our site to support all types. ;-) Seriously, it's coming from
a RadioButtonList and just seemed to make more sense to use an int. Besides,
I don't see how a database can store a bit field using less than a byte
anyway.

 

[Jonathan Wood]

Actually, it looks like dr["Sex"] was NULL. But I thought I was testing for
that.

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

What type is client.Sex? Looks like whatever it is, it cannot accept int
as a
value. Unless the Convert of the (int)dr["Sex"] is failing because it
itself is not an integer.

-- Peter
Site: http://www.eggheadcafe.com
UnBlog: http://petesbloggerama.blogspot.com
MetaFinder: http://www.blogmetafinder.com

The following code raises the error "Specified cast is not valid."

MembershipUser user = Membership.GetUser(userId);
DataSet ds = DataLayer.ExecQueryData("SELECT * FROM mc_Clients WHERE
UserId='" + userId.ToString() + "'");
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count ==
0)
return null;
ClientEx client = new ClientEx();
client.ClientData = new Client();
client.ClientData.Email = user.UserName;
client.ClientData.UserID = user.ProviderUserKey;
client.ClientData.LastLogin = user.LastLoginDate;
DataRow dr = ds.Tables[0].Rows[0];

// ...

if (!Convert.IsDBNull(dr["Sex"]))
client.Sex = (int)dr["Sex"]; <<== ERROR HERE!

// ...

In the database table, Sex has a data type=smallint and nullable=True,
and
client.Sex has a data type of int.

If I attempt to examine the contents of dr["Sex"] in the debugger, it
shows
a value of 0x0000. If I attempt to examine the contents of (int)dr["Sex"]
in
the debugger, it shows an error about not being able to unbox (sorry I no
longer have the exact text).

Any suggestions?

 

[Mark Rae [MVP]]

The following code raises the error "Specified cast is not valid."

Yes, it would do...

if (!Convert.IsDBNull(dr["Sex"]))

if (dr["Sex"] != DBNull.Value)

So what does that mean? Does that mean dr["Sex"] was, in fact, null but my
check was wrong?

I believe so...

And what the heck does Convert.IsDBNull() do? This seemed to work on some
other fields.

It (supposedly) does exactly the same thing:
http://msdn2.microsoft.com/en-us/library/system.convert.isdbnull.aspx

but I've found it to be an extremely unreliable way of checking for a null
value in a database field...

Actually, I just tried this:

if (dr["Sex"] != DBNull.Value)
client.Sex = (int)dr["Sex"];

And I seem to get exactly the same error on the same line.

Hmm - OK... Set a breakpoint on the first line above and, in the Immediate
window, inspect dr["Sex"]

Hey, we like our site to support all types. ;-) Seriously, it's coming
from a RadioButtonList and just seemed to make more sense to use an int.

I don't understand the thinking behind that at all...

Besides, I don't see how a database can store a bit field using less than
a byte anyway.

SQL Server (and several other RDBMS) can:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.datatype.bit.aspx

My slightly tongue-in-cheek point was that you're using a larger datatype
than is necessary... For gender, I tend to use char(1)...

 

[Jonathan Wood]

I figured this out. It only works if I cast using (short) insteat of (int).
I have no idea why that is--converting from a 16-bit integer to a 32-bit
integer is a trivial task, in fact one that the compiler still does after my
change.

 

[Jonathan Wood]

Mark,

And I seem to get exactly the same error on the same line.

Hmm - OK... Set a breakpoint on the first line above and, in the Immediate
window, inspect dr["Sex"]

I described that in my original post. At any rate, the fix was to type cast
to a short instead of an int, which doesn't seem like it should be the case.

SQL Server (and several other RDBMS) can:

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.datatype.bit.aspx
My slightly tongue-in-cheek point was that you're using a larger datatype
than is necessary... For gender, I tend to use char(1)...

I didn't see where that link talked about the actual amount of storage used
to store bits in the database, I suppose it's possible to optimize bit
fields if they are stored contiguously, rather than with the other fields in
the same row, but I wouldn't have thought that's how it's done. At any rate,
I've used both char(1) and bit but I'm more comfortable using smallint in
this particular case.

 

[Mark Rae [MVP]]

I didn't see where that link talked about the actual amount of storage
used to store bits in the database,
OK.

I suppose it's possible to optimize bit fields if they are stored
contiguously, rather than with the other fields in the same row, but I
wouldn't have thought that's how it's done.

Not sure what you're talking about there...

At any rate, I've used both char(1) and bit but I'm more comfortable using
smallint in this particular case.

I simply can't fathom that at all...

Struggling to work out how the entity of 'gender' can ever be considered as
a numeric entity but, even if you consider that 'male' is somehow 1 and
'female' is somehow 2, then surely you'd use a tinyint rather than a
smallint, otherwise you're using twice as much storage space as you need...

 

[Jonathan Wood]

Mark,

Struggling to work out how the entity of 'gender' can ever be considered
as a numeric entity but, even if you consider that 'male' is somehow 1 and
'female' is somehow 2, then surely you'd use a tinyint rather than a
smallint, otherwise you're using twice as much storage space as you
need...

As I pointed out, the data is collected via a RadioButtonList. This control
returns an integer to indicate the selection, and I'm simply storing this
value as an integer.

Perhaps a byte would be better--isn't that a tinyint? But I see no reason to
deal with conversions to another data type here, particularly when I think a
single byte is the least amount of storage space that most fields could
occupy.

 

[Mark Rae [MVP]]

Perhaps a byte would be better

Yes it would - or a char(1)...

isn't that a tinyint?

Yes - a tinyint and a char(1) both occupy one byte...

a single byte is the least amount of storage space that most fields could
occupy.

Correct, apart from bit fields for boolean values, but that doesn't apply
here...

 

[Jonathan Wood]

Changed to a tinyint, along with several other fields based on CheckBoxList
controls (some with several different options).

Thanks.

 

[Mark Rae [MVP]]

Changed to a tinyint, along with several other fields based on
CheckBoxList controls (some with several different options).

So, how do you tell the difference between the two genders when you're using
a tinyint...?

 

[Jonathan Wood]

Mark,

So, how do you tell the difference between the two genders when you're
using a tinyint...?

Not sure I understand the question. As it is, Male is the first option so
Male == 0 and Female == 1. I tried creating enums for this (and the other
options) but stupid C# has problems with if (i == sexesMale). Oh well, I can
write code that simply tests the value for being 0 or 1.

 

[Mark Rae [MVP]]

Not sure I understand the question. As it is, Male is the first option so
Male == 0 and Female == 1.

That seems totally unnatural and unintuitive to me...

When you use char(1), male is 'M' and female is 'F'...

A query like SELECT * FROM MyTable WHERE Sex = 0 just seems really weird...

 

[Jonathan Wood]

Hmm... I'm not sure how long you've been programming but it doesn't seem at
all odd to me. At any rate, it's simply used for a couple of calculations
and on a report. There will be no queries based on it.

 

[ThatsIT.net.au]

Hmm... I'm not sure how long you've been programming but it doesn't seem
at all odd to me. At any rate, it's simply used for a couple of
calculations and on a report. There will be no queries based on it.

if the values are 0 and 1 then you should use bit
0 = false
1 = true

entred as you would a int

INSERT INTO dataTypes (aBit)Values(1)

 

[Mark Rae [MVP]]

I'm not sure how long you've been programming

LOL! About 30 years or so...

 

[Mark Rae [MVP]]

if the values are 0 and 1 then you should use bit

Unless the field is used in any query criteria, as bit fields can't be
indexed...