Hi all, I was using VB to write the following code and got the "Specified cast is not valid. " error. Any idea to solve that? Thanks in advance.
Protected
Overridable Function GetUserFromReader(ByVal reader
As IDataReader)
As UserDetailsReturn
New UserDetails(CInt(reader("UserID")), IIf((reader("FirstName")
Is DBNull.Value), [String].Empty, reader("FirstName").ToString()), IIf((reader("LastName")
Is DBNull.Value), [String].Empty, reader("LastName").ToString()), reader("Email").ToString(),
reader("Password").ToString(), IIf((reader("Title")
Is DBNull.Value), [String].Empty, reader("Title").ToString()), _reader("MobileNumber").ToString(), IIf((reader("HomeAddressID")
Is DBNull.Value), -1,
CInt(reader("HomeAddressID"))), IIf((reader("WorkAddressID")
Is DBNull.Value), -1,
CInt(reader("WorkAddressID"))),
CInt(reader("TextMiles")),
DirectCast(reader("RegistrationDate"), DateTime),
CInt(reader("UserProfileId")), _CBool(reader("AccountActivated")), IIf((reader("LastChange")
Is DBNull.Value),
DirectCast(reader("RegistrationDate"), DateTime),
DirectCast(reader("LastChange"), DateTime)))End
Function
For the user you are trying to get have you checked that the SQL returns non NULL values and values that can be converted to the types you are converting to? I.e. run the SQL in Management Studio and check that...
<div mce_keep="true">Column UserID can be converted to Type Int and is not Null</div>
<div mce_keep="true">Colum TextMiles can be converted to type int and is not Null</div>
Yeah you could use an In Line IF or you could do what nepster_from_india suggests or you could even check in the SQL like so:
SELECT
ISNULL(UserProfileId, 0)
as UserProfileId
Run the SQL in your management studio to check to see if any of the fields are returning NULL or if they are returning values that cannot be cast as the types you want, i.e. if the column UserProfileId stores a value of 'Dave' it won't be
able to be converted into an integer.
Are you positive the columns RegistrationDate / LastChange if not null are of type DateTime? Can you run your SQL for the user you are trying to get and post the results i.e. run it from SQL management studio.
The value of LastChange field is normally null,So I used inline if statement to check it. If it's null then I used
RegistrationDate value which was not null value,else use Now() to get current time.Then it works. Thanks.
IIf((reader(
"LastChange")
Is DBNull.Value),
DirectCast(reader("RegistrationDate"), DateTime),
Now())))
NingIE
Member
183 Points
167 Posts
Why "Specified cast is not valid"?
Mar 19, 2008 11:22 AM|LINK
Hi all, I was using VB to write the following code and got the "Specified cast is not valid. " error. Any idea to solve that? Thanks in advance.
Protected Overridable Function GetUserFromReader(ByVal reader As IDataReader) As UserDetails Return New UserDetails(CInt(reader("UserID")), IIf((reader("FirstName") Is DBNull.Value), [String].Empty, reader("FirstName").ToString()), IIf((reader("LastName") Is DBNull.Value), [String].Empty, reader("LastName").ToString()), reader("Email").ToString(), reader("Password").ToString(), IIf((reader("Title") Is DBNull.Value), [String].Empty, reader("Title").ToString()), _ reader("MobileNumber").ToString(), IIf((reader("HomeAddressID") Is DBNull.Value), -1, CInt(reader("HomeAddressID"))), IIf((reader("WorkAddressID") Is DBNull.Value), -1, CInt(reader("WorkAddressID"))), CInt(reader("TextMiles")), DirectCast(reader("RegistrationDate"), DateTime), CInt(reader("UserProfileId")), _ CBool(reader("AccountActivated")), IIf((reader("LastChange") Is DBNull.Value), DirectCast(reader("RegistrationDate"), DateTime), DirectCast(reader("LastChange"), DateTime))) End Functionscott@elband...
Star
11346 Points
1865 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 12:10 PM|LINK
For the user you are trying to get have you checked that the SQL returns non NULL values and values that can be converted to the types you are converting to? I.e. run the SQL in Management Studio and check that...
</div>
NingIE
Member
183 Points
167 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 01:22 PM|LINK
nepster_from...
Member
334 Points
63 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 01:30 PM|LINK
Hi NBingIE!
Scott is right. You should check if the value is null or not.
Also you can use Convert.ToInt32() or .ToString() functions if the problem is only because of casting and not the null value.
Hope this will help you!
Nepster from India! [Yes]
Please click "Mark as Answer" to this post if you get any help.
Nepster from India
Note: Please mark this post as answer if it help you.
scott@elband...
Star
11346 Points
1865 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 01:42 PM|LINK
Yeah you could use an In Line IF or you could do what nepster_from_india suggests or you could even check in the SQL like so:
SELECT
ISNULL(UserProfileId, 0) as UserProfileId
Run the SQL in your management studio to check to see if any of the fields are returning NULL or if they are returning values that cannot be cast as the types you want, i.e. if the column UserProfileId stores a value of 'Dave' it won't be able to be converted into an integer.
NingIE
Member
183 Points
167 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 02:33 PM|LINK
Hi all, I changed the code as below and still got the same error, anything wrong with my code? Thanks.
New UserDetails(IIf((reader("UserID") Is DBNull.Value), -1, CInt(reader("UserID"))), _IIf((reader(
"FirstName") Is DBNull.Value), [String].Empty, reader("FirstName").ToString()), _ IIf((reader("LastName") Is DBNull.Value), [String].Empty, reader("LastName").ToString()), _IIf((reader(
"Email") Is DBNull.Value), [String].Empty, reader("Email").ToString()), _ IIf((reader("Password") Is DBNull.Value), [String].Empty, reader("Password").ToString()), _IIf((reader(
"Title") Is DBNull.Value), [String].Empty, reader("Title").ToString()), _ IIf((reader("MobileNumber") Is DBNull.Value), [String].Empty, reader("MobileNumber").ToString()), _IIf((reader(
"HomeAddressID") Is DBNull.Value), -1, CInt(reader("HomeAddressID"))), _ IIf((reader("WorkAddressID") Is DBNull.Value), -1, CInt(reader("WorkAddressID"))), _IIf((reader(
"TextMiles") Is DBNull.Value), 50, CInt(reader("TextMiles"))), _ IIf((reader("RegistrationDate") Is DBNull.Value), Now(), DirectCast(reader("RegistrationDate"), DateTime)), _IIf((reader(
"UserProfileId") Is DBNull.Value), -1, CInt(reader("UserProfileId"))), _ IIf((reader("AccountActivated") Is DBNull.Value), False, CBool(reader("AccountActivated"))), _IIf((reader(
"LastChange") Is DBNull.Value), DirectCast(reader("RegistrationDate"), DateTime), DirectCast(reader("LastChange"), DateTime)))scott@elband...
Star
11346 Points
1865 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 02:45 PM|LINK
Are you positive the columns RegistrationDate / LastChange if not null are of type DateTime? Can you run your SQL for the user you are trying to get and post the results i.e. run it from SQL management studio.
BHendry
Participant
1644 Points
297 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 02:52 PM|LINK
Immediate If (IIF) will not work in this context because it ALWAYS evaluates both sides of the expression :(
Please remember to click "Mark as Answer" on the posts that helped solve your issue.
NingIE
Member
183 Points
167 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 03:06 PM|LINK
Hi all,
The value of LastChange field is normally null,So I used inline if statement to check it. If it's null then I used RegistrationDate value which was not null value,else use Now() to get current time.Then it works. Thanks.
IIf((reader(
"LastChange") Is DBNull.Value), DirectCast(reader("RegistrationDate"), DateTime), Now())))NingIE
Member
183 Points
167 Posts
Re: Why "Specified cast is not valid"?
Mar 19, 2008 03:10 PM|LINK