Converting null string to date

Question

I have searched high and low to no avail, and this is last step before completing my project so please help! Thanks in advance!

The user will select an entry in a gridview, which then redirects them to a form that is populated with the data from the selected row (thus making the gridview editable in a more user friendly way). Null values are accepted by the DB and I would like to show null date values as blank (or " ") in the corresponding text boxes. Instead I get the error:

Conversion from type 'DBNull' to type 'Date' is not valid.

Here is my code:

'preceded by connection code     
Dim sqlcmd As String = "SELECT * from Master WHERE RecNum = @recnum"
        'Dim sqlCmd As New OleDb.OleDbCommand("SELECT * from Master WHERE RecNum = @recnum", connection)
        Dim FileCommand3 As New OleDb.OleDbCommand(sqlcmd, connection)
        FileCommand3.Parameters.AddWithValue("@recnum", user)
        Dim Reader3 As OleDb.OleDbDataReader = FileCommand3.ExecuteReader()
        If Reader3.Read Then

            stock = myCStr(Reader3("StockNum"))
            make = myCStr(Reader3("Make"))
            color = myCStr(Reader3("Color"))
            stockin = myCStr(Reader3("Stockin"))
            ucistart = myCStr(Reader3("UCIStartDate"))
            repairs = Reader3("Repairs")
            tires = Reader3("tiresneeded")
            onlot = Reader3("onlot")
            sold = Reader3("sold")
            year = myCStr(Reader3("year"))
            model = myCStr(Reader3("model"))
            location = Reader3("location")
            srvcRO = myCStr(Reader3("svcROnum"))
            ucicompldate = myCStr(Reader3("uciestcompletedate"))
            collRO = myCStr(Reader3("collisionROnum"))
            other = myCStr(Reader3("other"))
            offprop = Reader3("offProperty")
            detail = (Reader3("detail")
        End If
        connection.Close()

        SoldCheckBX.Checked = sold
        DetailTXTbox.Text = detail
        'etc, etc
    End Sub

I used the function mycstr to fix the dbnull to string error but it does not seem as simple to adapt to "date" data type

Function myCStr(ByVal test As Object) As String
    If isdbnull(test) Then
        Return ("")
    Else
        Return CStr(test)
    End If
End Function

TBohnen.jnr · Accepted Answer · 2011-06-24T05:19:38.390

2

try this when you read the values from the reader with all your dates, this will first test to see if the date is dbnull, if it is then it will assign a nothing value and you should get your desired empty cell, otherwise it will show the date:

ucistart = IIf(reader3("UCIStartDate") Is DBNull.Value, Nothing, reader3("UCIStartDate"))

edited Jun 24 '11 at 05:19

answered Jun 24 '11 at 05:13

TBohnen.jnr

5,117
1
19
26

Don't forget to compensate the other end when saving if the date doesn't get set in code. @ucistart = IIf (myObj.date Is null ,DBNull.Value, myObj.Date); – Fellmeister Jun 24 '11 at 05:30
Thanks!!! that did it!....the only thing is that I am still getting the time in the cell. I declared ucistart as Date... how can i set it as a date without the the time? – JDV590 Jun 24 '11 at 05:38
Cool, You can format the datagrid to only display the date, in the gridview column set the dataformat string to: dataformatstring="{0:M-dd-yyyy}" where the string is the format you want it to be in – TBohnen.jnr Jun 24 '11 at 06:53
just to be clear I am filling a asp text box with the value from the datareader, not a cell in a table. I am no longer seeing the date but i still get a time (12:00 AM). I have tried using dataformatstring="{0:M-dd-yyyy}". ALso i have tried {0:d}, {0:MM-dd-yyyy}. none of these seem to work. Any other ideas on getting a completely blank textbox??? Thanks!! – JDV590 Jun 28 '11 at 04:17
Ok, so you can do a check before setting the textbox value i.e. If dt <> Nothing Then textbox1.Text = dt.ToString("M-dd-yyyy") Else textbox1.Text = "" End If – TBohnen.jnr Jun 28 '11 at 05:23

score 0 · Answer 2 · answered Jun 24 '11 at 05:10

0

Have you tried using the Convert.IsDBNull function?

Here is the official documentation.

answered Jun 24 '11 at 05:10

tzup

3,566
3
26
34

Converting null string to date

2 Answers2