Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 2.0 > ASP.NET 2.0 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 2.0 Basics If you are new to ASP or ASP.NET programming with version 2.0, this is the forum to begin asking questions. Please also see the Visual Web Developer 2005 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 2.0 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old December 10th, 2006, 02:13 AM
Friend of Wrox
 
Join Date: Apr 2004
Location: San Francisco, CA, USA.
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default Question about Nullable Types

I'm trying to learn how to use Nullable types in my applications.

I have a table in a database, with a column called FacilityID declared as type Int. Some of the rows will have a NULL value in this column.

I wrote this code to test with:

Code:
        Dim cnxn As New SqlConnection
        Dim cmd As New SqlCommand
        Dim reader As SqlDataReader
        Dim FacID As Nullable(Of Integer)

        cnxn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("EmployeeDBConnectionString").ConnectionString
        cnxn.Open()
        cmd.Connection = cnxn
        cmd.CommandText = "Select * from tbl_MyTable"
        reader = cmd.ExecuteReader

        While reader.Read
            FacID = reader("FacilityID")
        End While
The first time it gets to a NULL value in that column, I get an error message "The Specified Cast is Invalid".

What am I doing wrong here?

Thanks.

Aaron

Reply With Quote
  #2 (permalink)  
Old December 10th, 2006, 11:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Since DataReaders don't provide any methods to deal with nullable types, you need to populate the type yourself with code like:

Code:
while (dr.Read())
    if (dr.IsDBNull(dr.GetOrdinal("FacilityID")))
        FacID = null;
    else
        FacID = dr.GetInt32(dr.GetOrdinal("FacilityID"));
Sorry 'bout the C# but I think the VB is virtually identical.

Bob

Reply With Quote
  #3 (permalink)  
Old December 10th, 2006, 12:23 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: San Francisco, CA, USA.
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No problem, Bob. Thanks for the response. I think the VB equivalent for the null expression is vbNull.

Just as an editorial aside: it occurs as kind of silly to define a nullable type, then not let it take on the value of DBNull directly, considering that the main reason people might want a nullable type in the first place is to populate data from a database. Anyone have an opinion about that? You know I tried this same code but defined FacID as SqlInt32, and it got the same results. You can't cast a SqlInt32 as DBNull either.

Okay, so my second question then is: Populating controls on a page, e.g. textboxes: do I have to do the same thing:

Code:
If FacID.GetType is VBNull.Value Then
   Textbox1.Text = ""
Else
   Textbox1.Text = FacID
End If
And for that matter, do I need to do the same thing when updating my data store:

Code:
If FacId.GetType is vbNull.Value then
   cmd.Parameters.Insert (New SqlParameter ("@FacID", DbNull.Value))
Else
   cmd.Parameters.Insert (New SqlParameter ("@FacID", FacID))
End If
Thanks.

Aaron

Reply With Quote
  #4 (permalink)  
Old December 10th, 2006, 04:10 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Aaron,

In the first case, assigning the value of FacID to the text property, no need for the null check. Simply call FacID.ToString() in your assignment statement. If FaqID is null, an empty string will be assigned to the control.

In the second case, parameterizing a stored procedure, yes, you need the additional null checks:
Code:
cmd.Parameters.Add("@FacilityID", SqlDbType.Int);
if (FacID != null)
{
    cmd.Parameters["@FacilityID"].Value = FacID;
}
else
{
    cmd.Parameters["@FacilityID"].Value = DBNull.Value;
}
This will allow you to insert a null value in your database if FacID is null.

HTH,

Bob

Reply With Quote
  #5 (permalink)  
Old December 10th, 2006, 11:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Aaron,

Hold the presses on the paramterized stored procedure bit. In C#, the new nullable type operator - ?? - lets you rewrite the if...else null check as:

Code:
cmd.Parameters.Add("@FacilityID", FacID ?? (object)System.DBNull.Value);
Using ?? and casting DBNull to an object lets you have DBNull inserted for nullable types that have a null value.

Problem is, the ?? operator only exists in C#, and I'm clueless 'bout what the VB version of the above would look like. Hopefully, someone else can get you that snippet.

Bob





Reply With Quote
  #6 (permalink)  
Old December 10th, 2006, 11:57 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: San Francisco, CA, USA.
Posts: 204
Thanks: 0
Thanked 0 Times in 0 Posts
Default

My understanding from the websites I've checked out is that VB doesn't have this construct available in the current version, but will in the next version of .NET (I read that they ran out of time or something).

It's a slick construct, although I don't know how VB will reconcile it's terseness. Seems so anti-VB.

Thanks.

Aaron

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
nullable datetimepicker.value Anar Visual Studio 2005 2 January 4th, 2010 04:29 PM
Nullable Type Question Aaron Edwards ASP.NET 2.0 Professional 1 August 30th, 2007 07:37 AM
Nullable Datetime shivamnshah BOOK: ASP.NET 2.0 Instant Results ISBN: 978-0-471-74951-6 3 April 26th, 2007 12:46 PM
Nullable DateTime formatting sajan C# 2005 1 March 19th, 2007 12:43 PM
Keycode is non-nullable salaikumar VS.NET 2002/2003 0 May 22nd, 2006 12:47 AM



All times are GMT -4. The time now is 03:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.