Solved

Handling SQL Server Fields with Null Values on VB .Net Windows Forms

Posted on 2004-10-29
735 Views
Last Modified: 2012-06-27
I have a SQL Server 2000 database which contains tables with nullable fields.  One such field is [Date Complete].  In my code (VB .Net 2003), I bind the field to a textbox control using the code below.

----
txtDateComplete.DataBindings.Add(New Binding("Text", drProject, "Date_Complete"))
----

When the application runs, I receive the following error as a result.

----
Additional information: Property accessor 'Date_Complete' on object 'Project_Tracker.sqldsProject+ProjectRow' threw the following exception:'Cast from type 'DBNull' to type 'Date' is not valid.'
----

I do not have any formatting attributes applied to the textbox control as that doesn't appear to be an option in the properties window for the control.

Does anyone know how I can get controls on my Windows forms to accept NULL values from fields?  Any help would be greatly appreciated.  Hopefully this is a simple question for soem of you.  I am learning the .Net technology and for me, this issue has been a stumbling block for several days now.
0
Question by:StevenED1964
    6 Comments
     
    LVL 28

    Expert Comment

    by:iboutchkine
    what is drProject?
    I fthis is datarow you can determine if this is null or not

    something like that

    If IsDBNull(drProject("colname")) Then
        lblWhatever.text="nill"
    Else
        lblWhatever.text=drProject("colname")
    End If
    0
     

    Author Comment

    by:StevenED1964
    Yes, drProject is a datarow.  From your example - it sounds like I would have to manually set the text value of the control and then manually set the column value before any updates took place on the dataset.

    Isn't there a way to directly "bind" the field to the control so that this is done automatically?

    I have even tried using the Format event of the Binding class to handle this but that did not work either.  Below is my code for my attempt with the Format event.

    ----
                dbDataBinding = New Binding("Text", drProject, "Date_Complete")
                AddHandler dbDataBinding.Format, AddressOf DateReFormat
                txtDateComplete.DataBindings.Add(dbDataBinding)



          Private Sub DateReFormat(ByVal Sender As Object, ByVal CEvent As ConvertEventArgs)
                If Not CEvent.DesiredType Is GetType(String) Then
                      Exit Sub
                End If
                CEvent.Value = DatePart(DateInterval.Month, CEvent.Value).ToString("00") + _
                "/" + DatePart(DateInterval.Day, CEvent.Value).ToString("00") + _
                "/" + DatePart(DateInterval.Year, CEvent.Value).ToString("0000")
          End Sub
    ----

    The problem I have with the above code is that I do not know how to test for a DBNull value.  The code blows up just as the binding does in the previous example when it encounters a null value in the field.

    How do you ask if CEvent.Value is DBNull?
    0
     

    Author Comment

    by:StevenED1964
    iboutchkine

    Never mind.  I see how to ask about DBNull in the example you gave.  I am a dummy.  Sorry.
    0
     

    Author Comment

    by:StevenED1964
    iboutchkine

    I am going to try my Format code with the IsDBNull logic to see if I can use that to resolve my problem.  I'll post another response here shortly with the outcome of that test.  Thanks
    0
     

    Author Comment

    by:StevenED1964
    iboutchkine

    I modified my code as shown below and I still get an error at run-time when the application tries to bind the field to the control.  The Format event is not doing the trick.  I guess I'll have to manually set the control's value and then manually push the value back to the record.

    ----
          Private Sub DateReFormat(ByVal Sender As Object, ByVal CEvent As ConvertEventArgs)
                If Not CEvent.DesiredType Is GetType(String) Then
                      Exit Sub
                End If
                If IsDBNull(CEvent.Value) Then
                      CEvent.Value = " "
                Else
                      CEvent.Value = DatePart(DateInterval.Month, CEvent.Value).ToString("00") + _
                      "/" + DatePart(DateInterval.Day, CEvent.Value).ToString("00") + _
                      "/" + DatePart(DateInterval.Year, CEvent.Value).ToString("0000")
                End If
          End Sub
    ----
    0
     
    LVL 28

    Accepted Solution

    by:
    I am afraid so
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.
    Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now