• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 776
  • Last Modified:

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

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
Edward Stevens
Asked:
Edward Stevens
  • 4
  • 2
1 Solution
 
iboutchkineCommented:
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
 
Edward StevensIT ConsultantAuthor Commented:
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
 
Edward StevensIT ConsultantAuthor Commented:
iboutchkine

Never mind.  I see how to ask about DBNull in the example you gave.  I am a dummy.  Sorry.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Edward StevensIT ConsultantAuthor Commented:
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
 
Edward StevensIT ConsultantAuthor Commented:
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
 
iboutchkineCommented:
I am afraid so
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now