Inserting NULL integer using VB.Net and EF5

Question

Working on an application that relies on an older version of entity, and I'm trying to insert a NULL into an int field. The field in SQL Server is (int, null).

Here's the definition of the object in EF:

<EdmScalarPropertyAttribute(EntityKeyProperty:=false, IsNullable:=true)>
<DataMemberAttribute()>
Public Property application_id() As Nullable(Of Global.System.Int32)

...and here is where I'm trying to set it:

applications.application_id = IIf(IsNumeric(txtAppID.Text), CInt(txtAppID.Text), Nothing)

The error thrown in response is:

An exception of type 'System.InvalidCastException' occurred in ... but was not handled in user code

Additional information: Specified cast is not valid.

I can confirm that this issue is being thrown due to the Nothing portion because previously it was applications.application_id = CInt(txtAppID.Text) and all was fine.

I've tried DBNull.Value instead of Nothing, though the error reads the same. Done a fair bit of research though most issues relate to ES6 or datetime fields, and as such I felt my issue was specific enough to warrant its own question.

Thanks.

Change the code so you only add a value to the record if it is numeric, i.e.: If IsNumeric(txtAppID.Text) Then applications.application_id = CInt(txtAppID.Text) — Laughing Vergil
– Laughing Vergil, Commented Apr 6, 2017 at 15:05
@LaughingVergil I should've mentioned this is used for updates as well. Using that method would mean that the use has no way to remove an application ID, because if they blank out the field and update, applications.application_id will not get set. — Tyler Roper
– Tyler Roper, Commented Apr 6, 2017 at 15:40

Mark · Accepted Answer · 2017-04-06 15:08:27Z

1

The IIf function doesn't short circuit, and therefore always evaluates both the true and false parts, so it's not going to work in that situation. The If keyword does short circuit, but you will probably run into issues with the return type and nullable value types (e.g. Dim x As Integer? = If(False, 1, Nothing) results in x = 0 because the If is returning Integer and not Integer?).

So, I would recommend either using a regular If statement:

If IsNumeric(txtAppID.Text) Then
    applications.application_id = CInt(txtAppID.Text)
Else
    applications.application_id = Nothing
End If

or you could create a helper function:

Function NullableCInt(value As String) As Integer?
    If IsNumeric(value) Then Return CInt(value)
    Return Nothing
End Function

and use that:

applications.application_id = NullableCInt(txtAppID.Text)

answered Apr 6, 2017 at 15:08

Mark

8,1701 gold badge16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tyler Roper Over a year ago

Appreciate it, what a nasty oversight! I fixed it by simply doing Dim AppID as Integer? = IIf(IsNumeric(txtAppID.Text), txtAppID.Text, Nothing) and applications.application_id = AppID and it seems to be working just fine.

Mark Over a year ago

You may want to turn Option Strict On, which wouldn't allow implicit casting like that, but would result in more robust code.

Tyler Roper Over a year ago

Correct, I was quick to say my method worked prior to testing all cases. I actually really like Fabio's solution, however yours is no less correct. I appreciate your input and leading me in the right direction here.

Mark Over a year ago

@Santi Yes, especially @Fabio's performance optimization with TryParse is nice, so I'm going to borrow that one for my own code! :-)

Fabio · Accepted Answer · 2017-04-06 16:02:34Z

1

You can get working If method with casting

Dim temp As Integer
applications.application_id = If(Integer.TryParse(value, temp), temp, DirectCast(Nothing, Integer?))

For better readability you can introduce "default" value

Static DEFAULT_VALUE As Integer? = Nothing    
Dim temp As Integer
applications.application_id = If(Integer.TryParse(value, temp), temp, DEFAULT_VALUE)

With Integer.TryParse you need "check/convert" string to integer only once.

edited Apr 6, 2017 at 16:02

answered Apr 6, 2017 at 15:54

Fabio

32.5k4 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Inserting NULL integer using VB.Net and EF5

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related