Data conversion Error

[Reggie]

Hi and TIA. I have a dataset that I fill from an Access table. Some
integer and date fields are null. I'm iterating through the records and
sending them to an SQL database table. The problem I'm having is I get an
error: (FormatException: Input string was not in a correct format.]) when it
comes to a null value in a field when I try placing it into the variable. I
have tried converting and everything I can't find a fix. Any advice on
where I can find the proper method would be appreciated. My code snippets
are below. I have my date variable declared as a string cause I was getting
an error when declared as a DateTime. Thanks for your time.

Dim intCVN_w_DMD As Integer
Dim intCVN_2Yr_Freq As Integer
Dim dtord_dttm As String

For Each row As DataRow In dst_MDB.Tables("1_NC_Analysis").Rows
intCVN_w_DMD = row("CVN_w_DMD").ToString 'here's where the error is
for null values
intCVN_2Yr_Freq = row("CVN_2Yr_Freq").ToString 'here's where the error
is for null values
dtord_dttm = row("ord_dttm").ToString 'here's where the error is for
null values

cmd = New SqlCommand("sp_NC_Analysis", con)
Try
With cmd
.CommandTimeout =
Convert.ToInt32(ConfigurationSettings.AppSettings("conTimeOut"))
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CVN_w_DMD", SqlDbType.Int).Value = intCVN_w_DMD
.Parameters.Add("@CVN_2Yr_Freq", SqlDbType.Int).Value =
intCVN_2Yr_Freq
.Parameters.Add("@ord_dttm", SqlDbType.NVarChar).Value = dtord_dttm
End With

cmd.ExecuteNonQuery()

Catch sqlExc As SqlException
Label4.Text = sqlExc.ToString
End Try

 

[Teemu Keiski]

Hi,

you can use Convert.IsDbNull to first check if value is null. If it is you
cannot convert it to Integer or String, but instead you need to set default
values for variables in this case (or something else to indicate they are
nulls). if you use .NET 2.0, you could utilize nullable types (although null
in that case means null reference not DbNull which is the value when null
comes from a database)

You could build your own helper library for checking nulls, and setting a
default value in case DbNull i tried to convert.

For example
=========

Public Class NZ
Public Shared Function GetInteger(obj As Object, def As Integer) As
Integer

If Convert.IsDbNull(obj) OrElse obj Is Nothing Then
Return def
End If

Try
Return Convert.ToInt32(obj)
Catch
Return def
End try

End Function

End Class


Then using that:

intCVN_w_DMD = NZ.GetInteger(row("CVN_w_DMD"),0)

Now if intCVN_w_DMD is 0, you know DbNull was faced (assuming it cannot have
0 otherwise, if it can, you need to have another default value like
Int32.MinValue)

Same thing concerns DateTimes and Strings etc

--
Teemu Keiski
ASP.NET MVP, AspInsider
Finland, EU
http://blogs.aspadvice.com/joteke

Hi and TIA. I have a dataset that I fill from an Access table. Some
integer and date fields are null. I'm iterating through the records and
sending them to an SQL database table. The problem I'm having is I get an
error: (FormatException: Input string was not in a correct format.]) when
it comes to a null value in a field when I try placing it into the
variable. I have tried converting and everything I can't find a fix. Any
advice on where I can find the proper method would be appreciated. My
code snippets are below. I have my date variable declared as a string
cause I was getting an error when declared as a DateTime. Thanks for your
time.

Dim intCVN_w_DMD As Integer
Dim intCVN_2Yr_Freq As Integer
Dim dtord_dttm As String

For Each row As DataRow In dst_MDB.Tables("1_NC_Analysis").Rows
intCVN_w_DMD = row("CVN_w_DMD").ToString 'here's where the error is
for null values
intCVN_2Yr_Freq = row("CVN_2Yr_Freq").ToString 'here's where the
error is for null values
dtord_dttm = row("ord_dttm").ToString 'here's where the error is for
null values

cmd = New SqlCommand("sp_NC_Analysis", con)
Try
With cmd
.CommandTimeout =
Convert.ToInt32(ConfigurationSettings.AppSettings("conTimeOut"))
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CVN_w_DMD", SqlDbType.Int).Value = intCVN_w_DMD
.Parameters.Add("@CVN_2Yr_Freq", SqlDbType.Int).Value =
intCVN_2Yr_Freq
.Parameters.Add("@ord_dttm", SqlDbType.NVarChar).Value = dtord_dttm
End With

cmd.ExecuteNonQuery()

Catch sqlExc As SqlException
Label4.Text = sqlExc.ToString
End Try

 

[Reggie]

Awesome! Exactly what I was looking for. Thanks very much for your
knowledge and time.

--

******************
Reggie

Hi,

you can use Convert.IsDbNull to first check if value is null. If it is you
cannot convert it to Integer or String, but instead you need to set
default values for variables in this case (or something else to indicate
they are nulls). if you use .NET 2.0, you could utilize nullable types
(although null in that case means null reference not DbNull which is the
value when null comes from a database)

You could build your own helper library for checking nulls, and setting a
default value in case DbNull i tried to convert.

For example
=========

Public Class NZ
Public Shared Function GetInteger(obj As Object, def As Integer) As
Integer

If Convert.IsDbNull(obj) OrElse obj Is Nothing Then
Return def
End If

Try
Return Convert.ToInt32(obj)
Catch
Return def
End try

End Function

End Class


Then using that:

intCVN_w_DMD = NZ.GetInteger(row("CVN_w_DMD"),0)

Now if intCVN_w_DMD is 0, you know DbNull was faced (assuming it cannot
have 0 otherwise, if it can, you need to have another default value like
Int32.MinValue)

Same thing concerns DateTimes and Strings etc

--
Teemu Keiski
ASP.NET MVP, AspInsider
Finland, EU
http://blogs.aspadvice.com/joteke

Hi and TIA. I have a dataset that I fill from an Access table. Some
integer and date fields are null. I'm iterating through the records and
sending them to an SQL database table. The problem I'm having is I get
an error: (FormatException: Input string was not in a correct format.])
when it comes to a null value in a field when I try placing it into the
variable. I have tried converting and everything I can't find a fix. Any
advice on where I can find the proper method would be appreciated. My
code snippets are below. I have my date variable declared as a string
cause I was getting an error when declared as a DateTime. Thanks for
your time.

Dim intCVN_w_DMD As Integer
Dim intCVN_2Yr_Freq As Integer
Dim dtord_dttm As String

For Each row As DataRow In dst_MDB.Tables("1_NC_Analysis").Rows
intCVN_w_DMD = row("CVN_w_DMD").ToString 'here's where the error is
for null values
intCVN_2Yr_Freq = row("CVN_2Yr_Freq").ToString 'here's where the
error is for null values
dtord_dttm = row("ord_dttm").ToString 'here's where the error is
for null values

cmd = New SqlCommand("sp_NC_Analysis", con)
Try
With cmd
.CommandTimeout =
Convert.ToInt32(ConfigurationSettings.AppSettings("conTimeOut"))
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@CVN_w_DMD", SqlDbType.Int).Value = intCVN_w_DMD
.Parameters.Add("@CVN_2Yr_Freq", SqlDbType.Int).Value =
intCVN_2Yr_Freq
.Parameters.Add("@ord_dttm", SqlDbType.NVarChar).Value =
dtord_dttm
End With

cmd.ExecuteNonQuery()

Catch sqlExc As SqlException
Label4.Text = sqlExc.ToString
End Try