Convert DBNull to Date

[leo88]

Hi, I wants to ask how to convert DBNull to Date type?

For Each row As DataRow In dt.Rows
                i = 0
                Dim array As Object = row.ItemArray
 Dim a As String
                If array(i + 10) IsNot Nothing Then

                    a = CDate(array(i + 10)).ToString("yyyy-MM-dd hh:mm:ss")

                Else
                    array(i + 10) = DBNull.Value
                    a = array(i + 10)

                End If

                SQLStr2 = "INSERT INTO ScannedPartNoTbl (ScannedTime)" & _
                        " VALUES ('" & _
                        SQLStrCmd.ReplaceStr(a) & "')"
            Next

array(i + 10) is datetime type, in the program above, if the Datetime column contains items, it still can run. However, when it loops to a row where the Datetime column does not have value of datetime (or the column is null), then an error " Conversion from type 'DBNull' to type 'Date' is not valid" is occured. I tried to use the if statements as program above but it seems like cannot works.
Anyone have solution for this problem?

[Stefan_Lam88]

Means you want to insert null value to the database if the table doesnt consists of data(date)?

[leo88]

Yes,for the column Datetime of the table, some rows contains data, some rows is empty. Actually I also not really sure the empty field is null or means " ", the datable is generated after I joins many table and selected using sql command, the rows are empty when the joined table didnt matched

[Stefan_Lam88]

Dim getDate As SqlDateTime
getDate = SqlDateTime.Null

Try this in ur else statement

[leo88]

Do I needs to import class for the SqlDateTime?

[Stefan_Lam88]

ya import System.Data.SqlTypes if u didnt declare manually in ur code

[leo88]

The problem is still same," Conversion from type 'DBNull' to type 'Date' is not valid"
After I change "If array(i + 10) IsNot Nothing" to "If array(i + 10) <> " " ", the error becomes " Conversion from string " " to type 'Date' is not valid", seems like it doesn't goes to else statement.

[Stefan_Lam88]

how come still gt DB NULL? I thought replace d?

[leo88]

Never mine, I would check my previous coding to see if there got mistakes, Thanks.

[leo88]

how come still gt DB NULL? I thought replace d?

I thinks should be datatable insides my program appeared error, I would check there, now :)