0

I have created a code to take data from one database and place it into a second database, the problem i am having currently is when a field in the database is null it returns an error "Conversion type 'DBNULL' to type 'String' is not valid"

The code I am using is: 

Dim ITN As String = ResultSet.Item( "ItemNumber")
Dim QN As String = ResultSet.Item( "QuoteNumber")
Dim ITD As String = ResultSet.Item( "ItemDescription")
Dim DET As String = ResultSet.Item( "Details")
Dim PR As String = ResultSet.Item("Price")

Hoping someone can assist with this one!!

youritlink
  • 1

2 Answers2

0

You can use a ternary statement and check if the Item is null before casting it to a string.

Also in c# you could do something like this:

String value = ResultSet.Item("Price") as String;

The as will convert an invalid reference type to null automatically. For VB.NET:

VB.Net equivalent of C# "As"

Community
  • 1
  • 1
Newse
  • 2,330
  • 1
  • 12
  • 7
0

I got tired of checking for DBNULL so i wrote a function for that. Depending on the type of database you are working with it really depends but, For efficiency, you'd probably want to use the StringBuilder class rather than string concatenation.

If you use a parameterized query see this link for a very basic introduction to using parameterized queries with Access, you would be able to directly insert the special DBNull value:

Dim myConnection As New OleDbConnection(DBConnection)
Dim Cmd As OleDbCommand = New OleDbCommand()
Cmd.CommandText = "INSERT INTO dbTable (ItemNumber, QuoteNumber, ItemDescription, Details, Price) VALUES (@ITN, @QN, @ITD, @DET, @PR)"
Cmd.Parameters.Add(New OleDbParameter("@ITN", OleDbType.VarChar)).Value = CheckDBNull(ITN)
Cmd.Parameters.Add(New OleDbParameter("@QN", OleDbType.VarChar)).Value = CheckDBNull(QN)
Cmd.Parameters.Add(New OleDbParameter("@ITD", OleDbType.VarChar)).Value = CheckDBNull(ITD)
Cmd.Parameters.Add(New OleDbParameter("@DET", OleDbType.VarChar)).Value = CheckDBNull(DET)
Cmd.Parameters.Add(New OleDbParameter("@PR", OleDbType.VarChar)).Value = CheckDBNull(PR)
DBConnection.Open()
Cmd.ExecuteNonQuery()

this is also good for avoiding nasty SQL Injection. Like I mentioned, depending on the database you are using you might have to use SqlParameter & SqlDbType vs. OleDbParameter & OleDbType but The CheckDBNull function could be a simple as the following:

Private Function CheckDBNull(ByVal s As String) As Object
    If Not s Is Nothing And s.Length > 0 Then
        Return s
    Else
        Return System.DBNull.Value
    End If
End Function

I hope this helps. please note some of these parameters were just used as an example (myConnection, Cmd, dbTable) as you did not provide db info:

Oddacon
  • 309
  • 6
  • 12