Seems like a simple thing, but the fact that you need to subtract a year if the person hasn’t had their birthday yet in the current year made it more involved. There were a lot of different ways to do it online, some of them more clunky than others. I cobbled this together from the various posts I found (assume a Person object with a DOB field).
public int Age {
}
T-SQL’s UNIQUE constraint allows for NULL values, but only one null value is allowed per column. There is, however, a nifty little way around this, via the WHERE clause.
For example, here’s a UNIQUE constraint that makes sure an ID is unique but allows for both multiple NULL values and multiple empty strings:
CREATE UNIQUE NONCLUSTERED INDEX [UNIQUE_ID] ON [dbo].[tblParticipant]
(
[ID] ASC
)
WHERE ([ID] IS NOT NULL AND [ID] <> ”)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
Recently I did a presentation on the basic information that users, web developers, and IT people should know about HTML5, and I decided it might be a good idea to post the slides I used in the presentation here.
For years, I have depended on some handy code called sp_search_code to search for text in stored procedures, views, and functions. It was written by Narayana Vyas Kondreddi who has a website at http://vyaskn.tripod.com. However, I just used it in SQL Server 2012 and it didn’t work. So I came across some code at Code Project, which will find text in a stored procedure. I’ve written a simple stored procedure using that code and am providing it here. Since right now it only searches stored procedures it is not as useful as sp_search_code, but it’s a start:
create procedure dbo.SearchCode
@Search varchar(100)
as
SELECT Name as [Stored Procedure Name]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%’ + @Search + ‘%’
This is just a link to a blog posting from Soheib Blog that perfectly addresses this problem I have run up against with new SQL Server Reporting Services installations (SQL Server 2012 or 2008 R2). If you are getting an error message about a user not having the required permissions, this blog post should do the trick.
There are all kinds of very helpful articles online, like this one, explaining how to create a dynamic WHERE clause using COALESCE or ISNULL or CASE. Generally, they describe something like this:
declare @FirstName NVarchar(50) = null
declare @LastName NVarchar(50) = null
declare @StreetAddress NVarchar(100) = null
declare @City NVarchar(50) = null
declare @JobNumber NVarchar(50) = ‘543216’
select e.FirstName, e.LastName, e.DeptID, a.StreetAddress, a.City,
a.StateID, a.ZipCode, j.JobName, j.JobNumber
from tblEmployee e
left join tblAddress a on e.EmployeeID = a.EmployeeID
left join tblJobs j on e.EmployeeID = j.EmployeeID
where e.FirstName = COALESCE(@FirstName, e.FirstName) AND
e.LastName = COALESCE(@LastName, e.LastName) AND
a.StreetAddress = COALESCE(@StreetAddress, a.StreetAddress) AND
a.City = COALESCE(@City, a.City) AND
j.JobNumber = COALESCE(@JobNumber, j.JobNumber)
And this works great as long as the columns in our WHERE clause all contain data. However, any column with a NULL value will not be returned. For instance, in the above example we are searching only by Job Number, so any record with a Job Number of 543216 should be returned. But, if the StreetAddress field for any of those records is NULL, then they won’t be returned, even though there is a value of 543216 for the Job Number.
Why is this? Because in our WHERE clause we are using the equal operator, and NULL can’t equal anything. NULL isn’t a value – it just means UNKNOWN. So NULL = NULL won’t return true, because what you’re really asking is “Does this unknown thing equal that unknown thing?” And, of course, the answer to that is “Who knows?” It can’t evaluate to true, so it must return false.
The solution, then, is to adjust the WHERE clause so that, if the values are NULL, we convert them to something known that we can compare. If we add an ISNULL function to the column side of the equal sign, and add the same replacement value as the final value in the COALESCE function on the other side, as below, then the query should be able to evaluate and return all rows, even ones with NULL values.
declare @FirstName NVarchar(50) = null
declare @LastName NVarchar(50) = null
declare @StreetAddress NVarchar(100) = null
declare @City NVarchar(50) = null
declare @JobNumber NVarchar(50) = ‘543216’
select e.FirstName, e.LastName, e.DeptID, a.StreetAddress, a.City,
a.StateID, a.ZipCode, j.JobName, j.JobNumber
from tblEmployee e
left join tblAddress a on e.EmployeeID = a.EmployeeID
left join tblJobs j on e.EmployeeID = j.EmployeeID
where ISNULL(e.FirstName, ”) = COALESCE(@FirstName, e.FirstName, ”) AND
ISNULL(e.LastName, ”) = COALESCE(@LastName, e.LastName, ”) AND
ISNULL(a.StreetAddress, ”) = COALESCE(@StreetAddress, a.StreetAddress, ”) AND
ISNULL(a.City, ”) = COALESCE(@City, a.City, ”) AND
ISNULL(j.JobNumber, ”) = COALESCE(@JobNumber, j.JobNumber, ”)
Usually, when you set a control’s Enabled property to False, the control will appear grayed out in the form. However, a DataGridView control whose Enabled property is False looks the same as one whose Enabled property is True. Once you try to click in to the DataGridView to edit or add something, you’ll find that it is not enabled, but it doesn’t look any different – which can be confusing to users, especially if it is on a form with other disabled controls that are grayed out.
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
So here’s the scenario – you’ve got some kind of code that you want to run whenever the value of a cell changes in a DataGridView. You write some code in the DataGridView’s CellValueChanged event, but you notice that it doesn’t fire if you are editing a cell and then you close the form without first tabbing/clicking out of the DataGridView (I noticed this when working on my IsDirty class, described in my last post).
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.
Here is a ChangeTracker class I developed for use in WinForms scenarios – it is adapted from an article I found at The Code Project site. In testing it, I also came across a problem and work-around for DataGridView controls – see the next blog post for details.
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
Here are a couple of ways to use T-SQL to create a comma separated list of column values in SQL Server. The first uses FOR XML PATH, and returns a csv for each ID in the table – this could be used in a table function.
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
TechKnowSolve Blog 