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 upgraded my ASUS x202e notebook from Windows 8.1 to Windows 10. The upgrade went smoothly and I liked the look of the new OS, but I noticed right away that my touchpad no longer worked. I found some online instructions that restored the cursor, but the click and right-click didn’t work, so what’s the point, right?
I contacted ASUS support, and they said I’d need to reinstall the Windows 8 drivers to fix the problem. And since this did fix my problem, I thought I’d post a how-to, as there were several steps. Keep in mind that this is what fixed my problem as of this writing, which is shortly after win10 rolled out. Things may change over the next few weeks and months.
OK, so here’s what I did. By the way, you’ll need to have either a touch screen or an external mouse to do this, since there is a fair amount of selecting and clicking, and you don’t have a TouchPad yet!
I’ve been working with Murach Publishing to revise their JavaScript book, and it’s almost ready to go to press (yay!). While I was finishing up the final Intro to jQuery chapter, though, I found myself puzzled by something. But first, let me give you some JavaScript background to explain my puzzlement.
In JavaScript, you can use constructor functions to make multiple instances of an object. When you do, you should put the object’s methods on the object prototype. This saves memory and makes the methods available to all instances of the object.
By contrast, when you’re working with a singleton, like an object literal or an object created with the module pattern, there’s only one instance of the object. In this case, you should put the methods directly on the object.
The jQuery library uses the module pattern to create a global singleton object named jQuery. That means there’s only one instance of jQuery. But when you create a jQuery plugin, you’re supposed to add your method to the jQuery.fn (or $.fn) object, which is an alias for the jQuery object’s prototype. But why? If the jQuery object is a singleton, why are we adding methods to it’s prototype?
Further puzzling me, the jQuery plugin tutorial says “Whenever you use the $ function to select elements, it returns a jQuery object”. Hmm. If jQuery is a singleton created by the module pattern, how is the $ function returning jQuery objects?
Now, you should know that you don’t need to have the answers to these questions to use jQuery effectively and write your own plugins. You don’t even need to have them to write an effective Intro to jQuery chapter in a JavaScript technical book. And so I set this all aside and went on with my work.
Yesterday, though, I couldn’t take not knowing the answers for one more second. So I read some articles online and studied the uncompressed jQuery code (luckily the library isn’t too massive), and I think I’ve figured it out. Bear with me, though – things get a little circular.
The jQuery object is indeed a singleton created with the module pattern, and the methods that jQuery uses are indeed on this singleton object’s prototype. But, crucially, one of the objects on that prototype is a constructor function named init. And, also crucially, the prototype of the init constructor function points to the prototype of the singleton jQuery object. Finally, most crucially, an instance of this init object is what is returned when you call the $ function.
Another way to look at this is that the singleton object’s $ function is a factory that creates and returns instances of the jQuery.fn.init constructor function. And both the singleton and the instances of the init object are referred to as jQuery objects. And they have the same prototype, so anything I add to the singleton’s prototype is also available to the prototype of all the instances of the init object.
Phew! No wonder I was puzzled. I’m not enough of a jQuery maven to know why this structure was chosen. It might be that there’s some really advanced JavaScript benefit to coding it this way. Or it might be that it’s a holdover from earlier versions that they wish they could change but it would cause too many problems. But at least I think I’ve got a grip on why I’d want to add my plugin code to the singleton jQuery object’s prototype.
Here are some code examples that illustrate:
I recently ran into a problem in a report I was developing. The report was a Sales Invoice, and if there were enough line items to move the Totals section to a second page, the tablix which contained the totals moved from the right side of the report, where I had set the location, to the middle. Eventually I learned that SSRS attempts to maintain relative spacing between report items, so that if an item to the left of another item changes in size, or is hidden, the item to its right is moved.
I had a number of items in my report that had an expression for the Hidden property: ( =iif(Fields!InvoiceType.Value = 2, false, true). I fixed my report by changing the Hidden property for all items to False. Then, to achieve the same effect as hiding, I worked with the Color property: (=iif(Fields!InvoiceType.Value = 2, “Black”,”White”). Changing the font color to white makes it invisible and my Totals tablix stayed where I wanted it, on the right side of the report.
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
TechKnowSolve Blog 