1

I can't seem to find a working solution.

I have a custom datagridview cell based on the DataGridViewTextboxColumn. I'm using VB 2005 for this.

I'm trying to implement a design-time property I can use to store a SQL statement to be used by the cells in the column. The lookup is the same for each row but the returned value is different.

I have a version which requires I set the statement during the CellFormatting and RowsAdded events , but I want to make it so all I have to do is provide a SQL statement (i.e. Select JobName From JobList where JobNo = {0}) as a property in the column class. I want to bind the cell and use the the value it gets as the replacement value for the statement. I can not get the SQL statement property to persist (right word?)

Part of my problem is that I'm not sure if the property should go in the custom COLUMN or the custom CELL definition. I've been trying to add the property to the column, which seems logical since I want the same value to be used for each instance if the cell without having to set the statement value each time the cell format event is fired.

I found and tried these attributes:

<Browsable(True), _
    EditorBrowsable(EditorBrowsableState.Always), _
    Category("Data"), _
    Description("The SQL Query to use for lookup. Make sure it will work with string.format"), _
    DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)> _
Public Property ScalarSQLStatement() As String
End Property

It doesn't work. I can enter the values in the column editor, but if I exit the editor and go back in the property is reset to it's default value.

EDIT ----

I have created subclass for the DataGridTextboxCell and put this in it:

Imports System.ComponentModel

Public Class DataGridViewScalarValue2TextboxCell
    Inherits DataGridViewTextBoxCell

    Private _scalarStatement As String = String.Empty
    Private _ReturnValue As String = String.Empty

    Private _LookupValue As Object

      Public Property LookupValue() As Object
        Get
            Return _LookupValue
        End Get
        Set(ByVal value As Object)
            _LookupValue = value
        End Set
    End Property


    Public Sub New()

        Me._LookupValue = Nothing

        If Me.OwningColumn Is Nothing Then Return

        Me.ScalarStatement = CType(Me.OwningColumn, DataGridViewScalarValue2TextboxColumn).ScalarSQLStatement

    End Sub

    Public Property ScalarStatement() As String
        Get
            Return _scalarStatement
        End Get
        Set(ByVal Value As String)
            _scalarStatement = Value
        End Set
    End Property

    ' Override the Clone method so that the Enabled property is copied. 

    Public Overrides Function Clone() As Object

        Dim Cell As DataGridViewScalarValue2TextboxCell = CType(MyBase.Clone(), _
                DataGridViewScalarValue2TextboxCell)

        Cell._scalarStatement = Me._scalarStatement
        Return Cell

    End Function

    Protected Overrides Function GetFormattedValue(ByVal value As Object, _
                                                   ByVal rowIndex As Integer, _
                                                   ByRef cellStyle As DataGridViewCellStyle, _
                                                   ByVal valueTypeConverter As TypeConverter, _
                                                   ByVal formattedValueTypeConverter As TypeConverter, _
                                                   ByVal context As DataGridViewDataErrorContexts) As Object

        If _LookupValue <> value Then
            _LookupValue = value
            GetReturnValueFromLookupValue()
        End If

        Return MyBase.GetFormattedValue(_ReturnValue, rowIndex, cellStyle, _
                valueTypeConverter, formattedValueTypeConverter, context)

    End Function

    Protected Overrides Sub Paint(ByVal graphics As Graphics, _
                                  ByVal clipBounds As Rectangle, _
                                  ByVal cellBounds As Rectangle, _
                                  ByVal rowIndex As Integer, _
                                  ByVal cellState As DataGridViewElementStates, _
                                  ByVal value As Object, _
                                  ByVal formattedValue As Object, _
                                  ByVal errorText As String, _
                                  ByVal cellStyle As DataGridViewCellStyle, _
                                  ByVal advancedBorderStyle As DataGridViewAdvancedBorderStyle, _
                                  ByVal paintParts As DataGridViewPaintParts)

        If value IsNot Nothing AndAlso _
            (TypeOf value Is String AndAlso Not String.IsNullOrEmpty(value)) OrElse _
            (TypeOf value Is Integer AndAlso Integer.TryParse(value, Nothing)) OrElse _
            (TypeOf value Is Decimal AndAlso Decimal.TryParse(value, 0)) OrElse _
            (TypeOf value Is Date) AndAlso IsDate(value) Then
            _LookupValue = value
            GetReturnValueFromLookupValue()

        Else
            _ReturnValue = String.Empty

        End If

        MyBase.Paint(graphics, clipBounds, cellBounds, rowIndex, cellState, value, _
                _ReturnValue, errorText, cellStyle, advancedBorderStyle, paintParts)

    End Sub

    Private Sub GetReturnValueFromLookupValue()

        If _LookupValue Is Nothing _
         Or OwningColumn Is Nothing _
         Or String.IsNullOrEmpty(_scalarStatement) Then
            _ReturnValue = Nothing
            Return
        End If
        Using conn As New SqlClient.SqlConnection(ConnUtils.MyGCPTableConnectionString)
            conn.Open()

            Dim cmd As SqlClient.SqlCommand = conn.CreateCommand

            With cmd
                .CommandText = String.Format(_scalarStatement, _LookupValue)

                .CommandType = CommandType.Text

                Dim objResult = .ExecuteScalar

                If objResult IsNot Nothing Then
                    _ReturnValue = objResult

                End If

            End With

            conn.Close()
        End Using

    End Sub

    Public Overloads Property Value() As Object

        Get
            Return _ReturnValue
        End Get

        Set(ByVal value)

            If TypeOf value Is Integer AndAlso value > 0 Then
                If _LookupValue <> value Then
                    _LookupValue = value
                    GetReturnValueFromLookupValue()

                End If
            End If

        End Set

    End Property
End Class

I did the same for the DataGridViewTextboxColumn class. Ireferences its main property (for this usage) in my original post. This subclass has this code in it:

Imports System.ComponentModel


Public Class DataGridViewScalarValue2TextboxColumn
    Inherits DataGridViewTextBoxColumn

    Public Sub New()
        Me.CellTemplate = New DataGridViewScalarValue2TextboxCell()

    End Sub

    '' Fields...
    Private _scalarSQLStatement As String = String.Empty

    <Browsable(True), _
        EditorBrowsable(EditorBrowsableState.Always), _
        Category("Data"), _
        Description("The SQL Query to use for lookup. Make sure it will work with string.format"), _
        DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)> _
    Public Property ScalarSQLStatement() As String
        Get
            Return _scalarSQLStatement
        End Get
        Set(ByVal Value As String)
            _scalarSQLStatement = Value
        End Set
    End Property

In the form I use the CellFormatting and RowsAdded events like below:

Private Sub dgvCustomCellTypes_CellFormatting(ByVal sender As Object, ByVal e As .DataGridViewCellFormattingEventArgs) Handles dgvCustomCellTypes.CellFormatting

    If e.ColumnIndex = colHaulID2.Index Then
        Dim dgvr As DataGridViewRow = CType(sender, DataGridView).Rows(e.RowIndex)
        Dim HaulCell As DataGridViewScalarValue2TextboxCell = CType(dgvr.Cells(colHaulID2.Index), DataGridViewScalarValue2TextboxCell)
        HaulCell.ScalarStatement = colHaulID2.ScalarSQLStatement
    End If

End Sub

Private Sub dgvCustomCellTypes_RowsAdded(ByVal sender As Object, ByVal e As DataGridViewRowsAddedEventArgs) Handles dgvCustomCellTypes.RowsAdded

    For i As Integer = e.RowIndex To e.RowCount - 1
        Dim dgvr As DataGridViewRow = CType(sender, DataGridView).Rows(i)
        Dim HaulCell As DataGridViewScalarValue2TextboxCell = CType(dgvr.Cells(colHaulID2.Index), DataGridViewScalarValue2TextboxCell)
        HaulCell.ScalarStatement = colHaulID2.ScalarSQLStatement

    Next
End Sub

And added this to the form initialization:

    colHaulID2.ScalarSQLStatement = "Select CompanyName From HaulCompany Where HaulID = {0}"

The column is databound and gets its lookup value there. It works fine, but is not the solution I'm looking for.

I am trying to rewrite the cell/column so that I can just add the SQL statement to the column using the column editor at design time and rely on the databound value to provide a lookup key.

The only thing, in my mind, that is stopping me from achieving my goal, is getting the control to save it's SQL statement after I close the column editor at design time. Or prevent it from getting cleared when I reopen the column editor. I'm not sure which is happening. But I'm sure there is a solution. I've made a few attempts at persistence, including a VB6-like property bag example I found. I am ignoring saving the property to disk. Seems there should be a way to let the application handle this task. So far, nothing has worked for me

END EDIT

Any help would be greatly appreciated.

Thanks.

Marshall

SSS
  • 4,807
  • 1
  • 23
  • 44
Marshall
  • 648
  • 1
  • 8
  • 16

2 Answers2

1

Double-click a cell in the first column for the demo.

Public Class Form1
  Sub New()

    ' This call is required by the designer.'
    InitializeComponent()

    ' Add any initialization after the InitializeComponent() call.'
    Dim dtb As New DataTable
    dtb.Columns.Add("C1")
    dtb.Columns.Add("C2")
    dtb.Columns.Add("C3")
    dtb.Rows.Add("1", "2", "3")
    dtb.Rows.Add("2", "3", "4")
    dtb.Rows.Add("3", "4", "5")
    dtb.Rows.Add("4", "5", "6")
    DataGridView1.DataSource = dtb
    DataGridView1.Columns(0).Tag = "Select JobName From JobList where JobNo = {0}"
  End Sub

  Private Sub DataGridView1_CellMouseDoubleClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseDoubleClick
    Dim strSql As String = String.Format(DataGridView1.Columns(e.ColumnIndex).Tag.ToString, DataGridView1.Rows(e.RowIndex).Cells(e.ColumnIndex).Value.ToString)
    MsgBox(strSql)
  End Sub
End Class
SSS
  • 4,807
  • 1
  • 23
  • 44
  • So basically, do this at runtime instead of doing it at design time? This is a control I plan on using in a few dozen applications and was hoping not to have to add any more manual code than necessary. I wanted to capsulate everything within the column or cell controls themselves. – Marshall Dec 11 '14 at 14:54
  • I've added the rest of the code for my subclassed column and cell, and tried to explain a little better exactly what I'm attempting to do. – Marshall Dec 11 '14 at 15:28
  • Yes, I would recommend this approach rather than a custom control. Custom controls are quite hard to debug since the IDE needs a compiled control to work - so there can be version issues if you are debugging your custom control and the business application that uses it at the same time (particularly if there is a breaking bug in the custom control). It's a huge shame that the .Tag property for the column is not available from the IDE. – SSS Dec 12 '14 at 02:26
  • Another option might be a hidden column that contains the SQL – SSS Dec 12 '14 at 02:27
1

I found the solution I was looking for.

I needed to override the Clone method in the column (not the cell), and ensure the property was getting passed to the cell at runtime. Below I'm posting my final solution.

(I found my Solution here on Stack Overflow.)

Imports System.ComponentModel
Imports System.ComponentModel.Design

<Serializable()> _
Public Class DataGridViewScalarValueTextboxColumn
    Inherits DataGridViewTextBoxColumn

    Public Sub New()

        Me.CellTemplate = New DataGridViewScalarValueTextboxCell()
    End Sub

    ' Fields...'
    Private _scalarSQLStatement As String = String.Empty

    <Browsable(True), _
        Category("Data"), _
        Description("The SQL Query to use for lookup. Must work with the string.format function if lookup value is used."), _
        DesignerSerializationVisibility(DesignerSerializationVisibility.Visible)> _
    Public Property ScalarSQLStatementCol() As String
        Get
            Return _scalarSQLStatement
        End Get

        Set(ByVal Value As String)
            _scalarSQLStatement = Value
        End Set
    End Property

    Public Overrides Function Clone() As Object

        Dim myClone As DataGridViewScalarValueTextboxColumn = CType(MyBase.Clone, DataGridViewScalarValueTextboxColumn)
        myClone.ScalarSQLStatementCol = ScalarSQLStatementCol
        Return myClone

    End Function

End Class

Public Class DataGridViewScalarValueTextboxCell
    Inherits DataGridViewTextBoxCell

    Private _ReturnValue As String = String.Empty

    Private _LookupValue As Object

    Public Property LookupValue() As Object
        Get
            Return _LookupValue
        End Get
        Set(ByVal value As Object)
            _LookupValue = value
        End Set

        End Property

    Public Sub New()

        Me._LookupValue = Nothing

    End Sub

    Public Overrides Function Clone() As Object
        'Method may not be needed now'
        Dim Cell As DataGridViewScalarValueTextboxCell = CType(MyBase.Clone(), _
                DataGridViewScalarValueTextboxCell)
        Return Cell

    End Function

    Protected Overrides Function GetFormattedValue(ByVal value As Object, _
                                                   ByVal rowIndex As Integer, _
                                                   ByRef cellStyle As DataGridViewCellStyle, _
                                                   ByVal valueTypeConverter As TypeConverter, _
                                                   ByVal formattedValueTypeConverter As TypeConverter, _
                                                   ByVal context As DataGridViewDataErrorContexts) As Object

        If _LookupValue <> value Then
            _LookupValue = value
            GetReturnValueFromLookupValue()
        End If

        Return MyBase.GetFormattedValue(_ReturnValue, rowIndex, cellStyle, _
                valueTypeConverter, formattedValueTypeConverter, context)

    End Function

    Protected Overrides Sub Paint(ByVal graphics As Graphics, _
                                  ByVal clipBounds As Rectangle, _
                                  ByVal cellBounds As Rectangle, _
                                  ByVal rowIndex As Integer, _
                                  ByVal cellState As DataGridViewElementStates, _
                                  ByVal value As Object, _
                                  ByVal formattedValue As Object, _
                                  ByVal errorText As String, _
                                  ByVal cellStyle As DataGridViewCellStyle, _
                                  ByVal advancedBorderStyle As DataGridViewAdvancedBorderStyle, _
                                  ByVal paintParts As DataGridViewPaintParts)

        If value IsNot Nothing AndAlso _
            (TypeOf value Is String AndAlso Not String.IsNullOrEmpty(value)) OrElse _
            (TypeOf value Is Integer AndAlso Integer.TryParse(value, Nothing)) OrElse _
            (TypeOf value Is Decimal AndAlso Decimal.TryParse(value, 0)) OrElse _
            (TypeOf value Is Date) AndAlso IsDate(value) Then
            _LookupValue = value
            GetReturnValueFromLookupValue()

        Else
            _ReturnValue = String.Empty

        End If

        MyBase.Paint(graphics, clipBounds, cellBounds, rowIndex, cellState, value, _
                _ReturnValue, errorText, cellStyle, advancedBorderStyle, paintParts)

    End Sub

    Private Sub GetReturnValueFromLookupValue()

        If _LookupValue Is Nothing _
         Or (OwningColumn Is Nothing _
         OrElse CType(Me.OwningColumn, DataGridViewScalarValue2TextboxColumn).ScalarSQLStatementCol.Length = 0) Then
            _ReturnValue = Nothing
            Return

        End If

        Using conn As New SqlClient.SqlConnection(ConnUtils.MyGCPTableConnectionString)
            conn.Open()

            Dim cmd As SqlClient.SqlCommand = conn.CreateCommand

            With cmd
                .CommandText = String.Format(CType(Me.OwningColumn, DataGridViewScalarValue2TextboxColumn).ScalarSQLStatementCol, _LookupValue)
                .CommandType = CommandType.Text

                Dim objResult = .ExecuteScalar
                If objResult IsNot Nothing Then
                    _ReturnValue = objResult

                End If
            End With

            conn.Close()
        End Using

    End Sub

    Public Overloads Property Value() As Object

        Get
            Return _ReturnValue
        End Get

        Set(ByVal value)

            If TypeOf value Is Integer AndAlso value > 0 Then
                If _LookupValue <> value Then
                    _LookupValue = value
                    GetReturnValueFromLookupValue()

                End If
            End If

        End Set

    End Property

End Class

I'm sure there is more refactoring that can be done, but it works. Just add the column to your grid, set the bind property for lookup and set the SQL Statement (must return just one value) and run it. Only had to load the source data.

Thanks all.

Community
  • 1
  • 1
Marshall
  • 648
  • 1
  • 8
  • 16