Here is a little utility function that will manually gray out the headers and cells of a DataGridView.  It doesn’t gray out check box columns, but then the checkboxes aren’t grayed out in a disabled CheckedListBox, either, so I figure this is close enough:

    Private Sub DisableGrid(ByVal grid As DataGridView)
        With grid
            .Enabled = False
            .ForeColor = Color.Gray
            For Each col As DataGridViewColumn In .Columns
                col.HeaderCell.Style.ForeColor = Color.Gray
            Next
        End With
    End Sub

There is a very quick and dirty solution to this problem – in the FormClosing event, set focus on another form element, like a textbox (for example, txtFirstName.Focus()).  This will cause the focus to leave the DataGridView, which will cause the CellValueChanged event to fire, which will cause your CellValueChanged code to run.

To use this class, download the class file and add it to your project.  In the form in which you want to track changes, add a form level variable:

Private changeTracker As TKS.ChangeTracker

In the form’s Load event, put the following code:

'set up change tracking
Me.changeTracker = New TKS.ChangeTracker(Me)

In the form’s FormClosing event, put the following code (you can see a clue as to the next blog entry in the first line):

'set focus on a textbox control, to make sure that datagrid CellChangeValue event fires if necessary
Me.txtFirstName.Focus()

'check if form is dirty
If Me.changeTracker.IsDirty Then
      Dim result = MessageBox.Show("Would you like to save changes before exiting?", "Save Changes", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question)
      Select Case result
           Case Windows.Forms.DialogResult.Yes
                Me.Save()
                Me.changeTracker.SetAsClean()
           Case Windows.Forms.DialogResult.No
'no action needed, unless form is hidden instead of closed. Then, you might want to re-set the change tracker
                Me.changeTracker.SetAsClean()
           Case Windows.Forms.DialogResult.Cancel
                e.Cancel = True
      End Select
End If

And here is the code for the class itself.  It is written to check TextBoxes, CheckBoxes, ComboBoxes, CheckedListBoxes and DataGridViews, but you can adapt it to check whatever you want. (You can also download the code.):

Namespace TKS
    Public Class ChangeTracker

#Region "Data Members"
        Private _frmTracked As Form
        Private _ctlData As Generic.Dictionary(Of String, TKS.ChangeTracker.ControlInfo)
#End Region

#Region "Constructor"
        Public Sub New(ByVal frm As Form)
            'initialize data members
            _frmTracked = frm
            _ctlData = New Generic.Dictionary(Of String, TKS.ChangeTracker.ControlInfo)

            'get initial control values, and hook up tracking events
            Me.SetUpControlTracking(_frmTracked.Controls, isEventsAssigned:=False)
        End Sub
#End Region

#Region "Properties"
        Public ReadOnly Property IsDirty As Boolean
            Get
                'loop through the change-tracker object for each control.  If any is dirty, exit loop and return true
                For Each kvPair As KeyValuePair(Of String, ControlInfo) In _ctlData
                    If kvPair.Value.IsDirty Then Return True
                Next
                'if we've gotten here, then nothing is dirty - return false
                Return False
            End Get
        End Property
#End Region

#Region "Methods"
        Public Sub SetAsClean()
            'clear previous data
            _ctlData.Clear()
            'get new control values - don't need to hook up events as they are already hooked up
            Me.SetUpControlTracking(_frmTracked.Controls, isEventsAssigned:=True)
        End Sub

        Private Sub SetUpControlTracking(ByVal col As Control.ControlCollection, ByVal isEventsAssigned As Boolean)
            'loop control collection, add control value to info collection, add event handler to designated controls if it hasn't been done already
            For Each ctl As Control In col
                If TypeOf ctl Is TextBox Then
                    Dim txtbox = CType(ctl, TextBox)
                    _ctlData.Add(txtbox.Name, New ControlInfo(txtbox.Text, False))
                    If Not isEventsAssigned Then AddHandler txtbox.TextChanged, AddressOf TextBox_TextChanged

                ElseIf TypeOf ctl Is CheckBox Then
                    Dim chkbox = CType(ctl, CheckBox)
                    _ctlData.Add(chkbox.Name, New ControlInfo(chkbox.Checked.ToString, False))
                    If Not isEventsAssigned Then AddHandler chkbox.CheckedChanged, AddressOf CheckBox_CheckedChanged

                ElseIf TypeOf ctl Is ComboBox Then
                    Dim cbobox = CType(ctl, ComboBox)
                    _ctlData.Add(cbobox.Name, New ControlInfo(cbobox.SelectedValue.ToString, False))
                    If Not isEventsAssigned Then AddHandler cbobox.SelectedIndexChanged, AddressOf ComboBox_SelectedIndexChanged

                ElseIf TypeOf ctl Is CheckedListBox Then
                    Dim lstbox = CType(ctl, CheckedListBox)
                    _ctlData.Add(lstbox.Name, New ControlInfo(Me.GetAllChecked(lstbox), False))
                    If Not isEventsAssigned Then AddHandler lstbox.SelectedIndexChanged, AddressOf CheckedListBox_SelectedIndexChanged

                ElseIf TypeOf ctl Is DataGridView Then
                    Dim dg = CType(ctl, DataGridView)
                    Dim key As String
                    'will need to loop datagrid and get each cell value.  Concatenate grid name, row index, cell index to create unique key for each cell.  Skip rows with no values.
                    For Each row As DataGridViewRow In dg.Rows
                        If Not row.IsNewRow Then
                            For Each cell As DataGridViewCell In row.Cells
                                key = dg.Name & row.Index.ToString & cell.ColumnIndex.ToString
                                _ctlData.Add(key, New ControlInfo(cell.Value.ToString, False))
                            Next
                        End If
                    Next
                    If Not isEventsAssigned Then AddHandler dg.CellValueChanged, AddressOf DataGridView_CellValueChanged
                End If

                'recursively get values from, add event handlers to, child controls
                If ctl.HasChildren Then
                    SetUpControlTracking(ctl.Controls, isEventsAssigned)
                End If
            Next
        End Sub
#End Region

#Region "Event Handlers"
        'Event handlers will:
        '  - get control's change-tracker object from collection
        '  - get control's new value and pass to 'CheckIfDirty' method of change-tracker object
        '  - if new value is different from saved value, 'CheckIfDirty' will mark control as dirty
        '  - if new value restores saved value, 'CheckIfDirty' will change control back to clean
        Private Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim txtbox = CType(sender, TextBox)
            If _ctlData.ContainsKey(txtbox.Name) Then
                _ctlData(txtbox.Name).CheckIfDirty(txtbox.Text)
            End If
        End Sub

        Private Sub CheckBox_CheckedChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim chkbox = CType(sender, CheckBox)
            If _ctlData.ContainsKey(chkbox.Name) Then
                _ctlData(chkbox.Name).CheckIfDirty(chkbox.Checked.ToString)
            End If
        End Sub

        Private Sub ComboBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim cbobox = CType(sender, ComboBox)
            If _ctlData.ContainsKey(cbobox.Name) Then
                _ctlData(cbobox.Name).CheckIfDirty(cbobox.SelectedValue.ToString)
            End If
        End Sub

        Private Sub CheckedListBox_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)
            Dim lstbox = CType(sender, CheckedListBox)

            If _ctlData.ContainsKey(lstbox.Name) Then
                _ctlData(lstbox.Name).CheckIfDirty(Me.GetAllChecked(lstbox))
            End If
        End Sub

        Private Sub DataGridView_CellValueChanged(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)
            Dim dg = CType(sender, DataGridView)
            Dim row = dg.Rows(e.RowIndex)
            Dim cell = row.Cells(e.ColumnIndex)
            Dim key As String = dg.Name & e.RowIndex.ToString & e.ColumnIndex.ToString

            'if key exists, check against initial value
            If _ctlData.ContainsKey(key) Then
                _ctlData(key).CheckIfDirty(cell.Value.ToString)
            Else
                'if key doesn't exist, this is a new row - add cell, and set as dirty  (set value to something unlikely so control will always evaluate as dirty)
                _ctlData.Add(key, New ControlInfo("-TKS", True))
            End If
        End Sub
#End Region

#Region "Get All Selected Values in List"
        Private Function GetAllChecked(ByVal lstBox As CheckedListBox) As String
            'loop through 'CheckedIndices' collection and concatenate
            Dim sb As New System.Text.StringBuilder
            For Each i As Integer In lstBox.CheckedIndices
                sb.Append(i.ToString)
            Next
            Return sb.ToString
        End Function
#End Region

#Region "ControlInfo Class"
        'change-tracker object - stores control's initial value and evaluates new values
        Private Class ControlInfo
            Sub New(ByVal v As String, ByVal dirty As Boolean)
                Value = v
                IsDirty = dirty
            End Sub

                Public Property Value As String
            Public Property IsDirty As Boolean

                Public Sub CheckIfDirty(ByVal newValue As String)
                If Me.Value.Equals(newValue) Then
                    Me.IsDirty = False
                Else
                    Me.IsDirty = True
                End If
            End Sub
        End Class
#End Region

    End Class
End Namespace

The second uses COALESCE, and returns a csv for a specified ID – this could be used in a scalar function.  Also, since FOR XML PATH is not available prior to SQL Server 2005, the COALESCE version can be used in SQL Server 2000.

First, let’s build a table variable to play with:

DECLARE @tblA TABLE(ID INT, Value INT)

INSERT INTO @tblA VALUES(1000,1)
INSERT INTO @tblA VALUES(1000,2)
INSERT INTO @tblA VALUES(1000,3)
INSERT INTO @tblA VALUES(1001,2)
INSERT INTO @tblA VALUES(1001,3)
INSERT INTO @tblA VALUES(1001,4)
INSERT INTO @tblA VALUES(1001,5)

Here’s the FOR XML PATH version:

SELECT ID,
STUFF(
 (
 select ‘, ‘+ CAST(value AS VARCHAR)
 from @tblA b
 WHERE a.ID = b.ID
 FOR XML PATH(”)
 )
,1,1,”) AS Value
FROM @tblA a
GROUP BY a.ID

Result of query:

ID          Value
———————–
1000         1, 2, 3
1001         2, 3, 4, 5

And here’s the COALESCE version:

declare @List varchar(500);
select @List = COALESCE(@List + ‘, ‘, ”) + cast(Value as varchar(5))
from @tblA
where ID = 1000;
select @List;

Results of query:

Value
——-
1, 2, 3

This snippet looks for textareas that have a maxlength attribute, and limits the number of characters to the number in the attribute.  This could use a bit of validation code, perhaps – making sure that the maxlength value is numeric, for example.  But you get the idea…

$(document).ready(function(){

    $(‘textarea[maxlength]‘).keyup(function() {

        //get textarea text and maxlength attribute value
        var t = $(this);
        var text = t.val();
        var limit = t.attr(‘maxlength’);

        //if textarea text is greater than maxlength limit, truncate and re-set text
        if (text.length > limit) {
            text = text.substring(0, limit);
            t.val(text);
        }
    });

});

http://aspnetcoe.wordpress.com/2007/02/06/how-to-remove-recent-projects-from-visual-studio-start-page/

http://mvantzet.wordpress.com/2009/09/15/remove-recent-projects-from-visual-studio-2008/

When I removed some items from my version of VS 2008, I found that I also needed to rename the remaining registry keys so that there were no gaps in the order (ie, File1, File2, etc with no gaps)

Private Sub LinkLabel1_LinkClicked(ByVal sender As System.Object, ByVal e As System.Windows.Forms.LinkLabelLinkClickedEventArgs) Handles LinkLabel1.LinkClicked
    Using player As New PlayAudioFile(“C:\Misc\WS400001.WMA”)
         player.PlaySnippet(5)
    End Using
End Sub

Public Class PlayAudioFile
    Implements IDisposable

    ‘Private data member – file path
    Private fileToPlay As String

    ‘Private function to send commands to the Windows OS MultiMedia API
    Private Declare Function mciSendString Lib “winmm.dll” Alias “mciSendStringA” _
    (ByVal lpstrCommand As String, ByVal lpstrReturnString As String, _
    ByVal uReturnLength As Integer, ByVal hwndCallback As Integer) As Integer

    ‘Private wrapper method for API function
    Private Sub SendCommand(ByVal command As String)
        mciSendString(String.Format(“{0} {1}”, command, fileToPlay), Nothing, 0, 0)
    End Sub

    ”’ <summary>
    ”’ Class Constructor – accepts an audio file (must be MP3, WAV or WMA file)
    ”’ </summary>
    ”’ <param name=”soundFile”>Full path of audio file to be played</param>
    Public Sub New(ByVal soundFile As String)
        Dim ext As String = IO.Path.GetExtension(soundFile)
        Select Case ext.ToLower
            Case “.mp3″, “.wav”, “.wma”
                fileToPlay = Chr(34) + soundFile + Chr(34)
            Case Else
                Throw New ArgumentException(“File must be an .MP3, .wav or .WMA file”)
        End Select
    End Sub

    ”’ <summary>
    ”’ Play audio file
    ”’ </summary>
    Public Sub Play()
        Me.SendCommand(“open”)
        Me.SendCommand(“play”)
    End Sub

    ”’ <summary>
    ”’ Stop audio file playback
    ”’ </summary>
    Public Sub StopPlay()
        Me.SendCommand(“stop”)
    End Sub

    ”’ <summary>
    ”’ Pause audio file playback
    ”’ </summary>
    Public Sub PausePlay()
        Me.SendCommand(“pause”)
    End Sub

    ”’ <summary>
    ”’ Resume playback of paused audio file
    ”’ </summary>
    Public Sub ResumePlay()
        Me.SendCommand(“resume”)
    End Sub

    ”’ <summary>
    ”’ Close audio file
    ”’ </summary>
    Public Sub CloseFile()
        Me.SendCommand(“close”)
    End Sub

    ”’ <summary>
    ”’ Play the first part of the sound file (default 15 seconds)
    ”’ </summary>
    Public Sub PlaySnippet()
        PlaySnippet(15)
    End Sub

    ”’ <summary>
    ”’ Play the first part of the sound file
    ”’ </summary>
    ”’ <param name=”snippetLength”>Length of snippet (in seconds)</param>
    Public Sub PlaySnippet(ByVal snippetLength As Integer)
        Me.Play()
        System.Threading.Thread.Sleep(snippetLength * 1000)
        Me.StopPlay()
    End Sub

#Region ” IDisposable Support ”

    Private disposedValue As Boolean = False        ‘ To detect redundant calls

    ‘ IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ‘ TODO: free unmanaged resources when explicitly called
                Me.CloseFile()
            End If

            ‘ TODO: free shared unmanaged resources
            Me.CloseFile()
        End If
        Me.disposedValue = True
    End Sub

    ‘ This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        ‘ Do not change this code.  Put cleanup code in Dispose(ByVal disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class

There should probably be more error handling in the class (for example, to make sure that the file is opened before calling ResumePlay, that sort of thing), but I thought this was a good start.

The DataGridView itself has a DataSource property that determines what columns are in it. The DataGridViewComboBox column also has a DataSource property, but that isn’t the same as the DataSource of the DataGridView. It is the DataSource that is used to populate the ComboBox in each cell. The DataMember and ValueMember properties are the column names of the columns in the ComboBox’s DataSource that define what is displayed in the ComboBox, and the underlying value for that item. It is the DataPropertyName property of the DataGridViewComboBoxColumn that determines the column name in the DataGridView’s DataSource that the ataGridViewComboBoxColumn is associated with. The ValueMember determines the value that will be set for that column in the DataGridView’s DataSource.

Here’s the original link. Thanks, Kevin Spencer.

Basically, you need to make sure that the user has permissions on both the file in which the .csv file resides, and also the file in which the Jet Engine will create temporary files. It is this last piece that I didn’t know, and lead to days of heartache.

So I thought I’d post, in the hopes of minimizing the heartache for others…