none
How to DataBind TextBox to a Typed DataRow Field that allows Nulls? RRS feed

  • Question

  • I'm trying to bind textboxes and dropdown controls to a strongly typed datarow.  Some of the rows fields allow nulls which is throwing an exception because the Binding class seems to try to access TypeDataRow.PropertyName directly and not testing for NULL using the IsPropertyNameNull() method.

    How do I two-way bind my controls to a typed datarow that allows nulls in some of its properties?

    Me.TextBox.DataBindings.Add("Text", TypedDataRow, PropertyName, True, DataSourceUpdateMode.OnPropertyChanged, Nothing)
    
    
    ' typed datarow field
            Public Property PropertyName() As Decimal
                Get
                    Try 
                        Return CType(Me(Me.TypedTable.PropertyNameColumn),Decimal)
                    Catch e As Global.System.InvalidCastException
                        Throw New Global.System.Data.StrongTypingException("The value for column 'PropertyName' in table 'Lists' is DBNull.", e)
                    End Try
                End Get
                Set
                    Me(Me.TypedTable.PropertyNameColumn) = value
                End Set
            End Property


    Ryan

    Saturday, June 22, 2019 12:18 AM

Answers

  • Hello,

    You could go a slightly different route using the Binding class e.g.

    Public Class Form1
        WithEvents Binding1 As Binding
        Private _bs As BindingSource = New BindingSource()
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '
            ' mock up data
            '
            Dim dt As New DataTable()
            dt.Columns.Add("DecimalOne", GetType(Decimal))
            dt.Rows.Add(New Object() {10.4})
            dt.Rows.Add(New Object() {Nothing})
            dt.Rows.Add(New Object() {20.3})
    
            _bs.DataSource = dt
            BindingNavigator1.BindingSource = _bs
    
            ' set up binding and events
            txtDecimalOne.DataBindings.Add("Text", _bs, "DecimalOne", False)
            Binding1 = txtDecimalOne.DataBindings("Text")
            AddHandler Binding1.Format, AddressOf DecimalPlaces_Format
    
            ' read current value
            Binding1.ReadValue()
        End Sub
    
        Private Sub DecimalPlaces_Format(sender As Object, e As ConvertEventArgs)
            If Not IsDBNull(e.Value) Then
                e.Value = CDec(e.Value).ToString("C2")
            End If
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) _
            Handles Button1.Click
    
            Dim row = CType(_bs.Current, DataRowView).Row
    
            If Not IsDBNull(row("DecimalOne")) Then
                row.SetField("DecimalOne", 12.99D)
            Else
                row.SetField("DecimalOne", 0D)
            End If
    
            Binding1.ReadValue()
    
        End Sub
    End Class

    Or

    Public Class Form1
        Private _bs As BindingSource = New BindingSource()
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '
            ' mock up data
            '
            Dim dt As New DataTable()
            dt.Columns.Add("DecimalOne", GetType(Decimal))
            dt.Rows.Add(New Object() {10.4})
            dt.Rows.Add(New Object() {20.3})
    
            _bs.DataSource = dt
            BindingNavigator1.BindingSource = _bs
    
    
            Dim binding1 As Binding = txtDecimalOne.DataBindings.
                    Add("Text", _bs, "DecimalOne", False)
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Saturday, June 22, 2019 1:03 AM
    Moderator
  • Hi Ryan,
    try the following little demo. That's the same approach as Karen: binding object with Format event for displaying value and Parse event for writing value back. If the value of the field in the strongly typed row is null "<empty>" will be shown. After navigation in DataGridView the text from the TextBox will be write in the field. If the value in the TextBox is "<empty>" or "" a Null willl be write in the field of the strongly typed row.

    Imports System.Data.OleDb
    
    Public Class Form26
    
      Private tb As New TextBox With {.Dock = DockStyle.Bottom}
      Private dgv As New DataGridView With {.Dock = DockStyle.Fill}
      Private bs As New BindingSource
      Private dt As New Form26DataSet1.Tab1DataTable
      Private TypedDataRow As Form26DataSet1.Tab1Row
    
      Private Sub Form26_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' simulate designer 
        Me.Controls.AddRange(New Control() {dgv, tb})
        ' load data
        Call (New Form26DataSet1TableAdapters.Tab1TableAdapter).Fill(dt)
        ' bind data to DataGridView
        bs.DataSource = dt
        dgv.DataSource = bs
        ' bind CurrentRow to TextBox
        Dim b1 As New Binding("Text", bs, "Field1")
        tb.DataBindings.Add(b1)
        AddHandler b1.Format, AddressOf b1_Format
        AddHandler b1.Parse, AddressOf b1_Parse
      End Sub
    
      Private Sub b1_Format(sender As Object, e As ConvertEventArgs)
        If e.Value Is DBNull.Value Then e.Value = "<empty>"
      End Sub
    
      Private Sub b1_Parse(sender As Object, e As ConvertEventArgs)
        If e.Value Is Nothing _
          OrElse String.IsNullOrEmpty(e.Value.ToString) _
          OrElse e.Value.ToString = "<empty>" Then e.Value = DBNull.Value
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by Ryan0827 Monday, June 24, 2019 9:42 PM
    Saturday, June 22, 2019 5:03 AM

All replies

  • Hello,

    You could go a slightly different route using the Binding class e.g.

    Public Class Form1
        WithEvents Binding1 As Binding
        Private _bs As BindingSource = New BindingSource()
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '
            ' mock up data
            '
            Dim dt As New DataTable()
            dt.Columns.Add("DecimalOne", GetType(Decimal))
            dt.Rows.Add(New Object() {10.4})
            dt.Rows.Add(New Object() {Nothing})
            dt.Rows.Add(New Object() {20.3})
    
            _bs.DataSource = dt
            BindingNavigator1.BindingSource = _bs
    
            ' set up binding and events
            txtDecimalOne.DataBindings.Add("Text", _bs, "DecimalOne", False)
            Binding1 = txtDecimalOne.DataBindings("Text")
            AddHandler Binding1.Format, AddressOf DecimalPlaces_Format
    
            ' read current value
            Binding1.ReadValue()
        End Sub
    
        Private Sub DecimalPlaces_Format(sender As Object, e As ConvertEventArgs)
            If Not IsDBNull(e.Value) Then
                e.Value = CDec(e.Value).ToString("C2")
            End If
        End Sub
        Private Sub Button1_Click(sender As Object, e As EventArgs) _
            Handles Button1.Click
    
            Dim row = CType(_bs.Current, DataRowView).Row
    
            If Not IsDBNull(row("DecimalOne")) Then
                row.SetField("DecimalOne", 12.99D)
            Else
                row.SetField("DecimalOne", 0D)
            End If
    
            Binding1.ReadValue()
    
        End Sub
    End Class

    Or

    Public Class Form1
        Private _bs As BindingSource = New BindingSource()
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            '
            ' mock up data
            '
            Dim dt As New DataTable()
            dt.Columns.Add("DecimalOne", GetType(Decimal))
            dt.Rows.Add(New Object() {10.4})
            dt.Rows.Add(New Object() {20.3})
    
            _bs.DataSource = dt
            BindingNavigator1.BindingSource = _bs
    
    
            Dim binding1 As Binding = txtDecimalOne.DataBindings.
                    Add("Text", _bs, "DecimalOne", False)
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Saturday, June 22, 2019 1:03 AM
    Moderator
  • That did it.  Thank you!

    But for some reason the controls don't reflect what's in the data source sometimes after clicking around on the controls.  I have to call BindingSource.ResetCurrentItem().  Any idea why?  I can't find any explicit answers.


    Ryan

    Saturday, June 22, 2019 3:06 AM
  • Hi Ryan,
    try the following little demo. That's the same approach as Karen: binding object with Format event for displaying value and Parse event for writing value back. If the value of the field in the strongly typed row is null "<empty>" will be shown. After navigation in DataGridView the text from the TextBox will be write in the field. If the value in the TextBox is "<empty>" or "" a Null willl be write in the field of the strongly typed row.

    Imports System.Data.OleDb
    
    Public Class Form26
    
      Private tb As New TextBox With {.Dock = DockStyle.Bottom}
      Private dgv As New DataGridView With {.Dock = DockStyle.Fill}
      Private bs As New BindingSource
      Private dt As New Form26DataSet1.Tab1DataTable
      Private TypedDataRow As Form26DataSet1.Tab1Row
    
      Private Sub Form26_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' simulate designer 
        Me.Controls.AddRange(New Control() {dgv, tb})
        ' load data
        Call (New Form26DataSet1TableAdapters.Tab1TableAdapter).Fill(dt)
        ' bind data to DataGridView
        bs.DataSource = dt
        dgv.DataSource = bs
        ' bind CurrentRow to TextBox
        Dim b1 As New Binding("Text", bs, "Field1")
        tb.DataBindings.Add(b1)
        AddHandler b1.Format, AddressOf b1_Format
        AddHandler b1.Parse, AddressOf b1_Parse
      End Sub
    
      Private Sub b1_Format(sender As Object, e As ConvertEventArgs)
        If e.Value Is DBNull.Value Then e.Value = "<empty>"
      End Sub
    
      Private Sub b1_Parse(sender As Object, e As ConvertEventArgs)
        If e.Value Is Nothing _
          OrElse String.IsNullOrEmpty(e.Value.ToString) _
          OrElse e.Value.ToString = "<empty>" Then e.Value = DBNull.Value
      End Sub
    
    End Class


    --
    Best Regards / Viele Grüße
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    • Marked as answer by Ryan0827 Monday, June 24, 2019 9:42 PM
    Saturday, June 22, 2019 5:03 AM
  • Hello,

    Unsure which version has been used to say why as in both code samples. I will be gone most of today so I can't reply until later on it you have more information.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, June 22, 2019 9:37 AM
    Moderator