How to connect to a SQL Server Named Instance

This is something I just don’t remember, so I thought this would be a good place to post it. If you are trying to connect to a named instance of SQL Server, for example TECHKNOWSOLVETECHKNOWSOLVE2000, you might get an error in your code that says this server doesn’t exist. The quick and easy fix is to refer to the server by its port number, ie TECHKNOWSOLVE, 444. In this instance TECHKNOWSOLVE is the name of the server and the port number is 444. Note that the server name is followed by a comma, not the backslash that is used in named instances.

How do you find out what port number your named instance is using? In SQL Server 2000, use the Server Network Utility. Choose your named instance. Under “Enabled Protocols”, click on “TCP/IP”. Click on the “Properties” button. There you will see a number for “Default Port”. This is the number that will follow your server name.

I haven’t tried this with SQL Server 2005, but this article tells you how to find the port for SQL Server 2005 (and SQL Server 2000, for that matter.)

Get Max Number from Datatable using the Compute method

This is a nifty little thing I came across recently.  I wanted to get the maximum value of a number field from a datatable, and learned about the Datatable’s Compute method.  It’s really easy to use.  The method takes two arguments – an expression argument, which is the aggregate expression you want to use (Count, Max, Sum, etc), and a filter expression, which functions like a SQL WHERE clause.  If you don’t want to filter by anything, you can send Nothing for the second argument (Null in C#).

An example:

Dim expression As String
Dim filter as string
Dim maxNum As Integer

‘get maximum job number from table
expression = “Max(JobNumber)”
filter = “JobDate > 12/31/06 AND JobDate < 1/1/08”
maxNum = CInt(tblJobInfo.Compute(expression, filter))

If you don’t want to filter, it would look like this:

expression = “Max(JobNumber)”
maxNum = CInt(tblJobInfo.Compute(expression, Nothing))

Gridview cell text values all Empty Strings

I spent a good part of today butting up against this problem – puzzled and Googled and experimented and Googled some more, but could not for the life of me figure it out – I kept doing exactly what the examples said I should do.  But then I finally found the answer, and so thought I would post it in the hopes of saving someone else the same hassle.

First, what I was trying to do.  I have an updatable gridview of a set size – it always displays 5 rows, and all 4 columns for all 5 rows will need to contain data when the user saves the page.  So, on page load, I want to loop through the gridview, inspect each cell to see whether it contains a value, and set its backcolor to yellow if it doesn’t to remind the user to fill it in.

The articles I read said that there were basically two ways to see if the cell has a value.  For a regular boundfield, you just check the cell’s text property directly, like so (All these examples are in the Gridview RowDataBound event, using that event’s System.Web.UI.WebControls.GridViewRowEventArgs argument.  This event fires on every postback.):

Dim c As Integer
Dim cell As TableCell

If e.Row.RowType = DataControlRowType.DataRow Then
   For c = 0 To e.Row.Cells.Count – 1

      ‘get cell
      cell = e.Row.Cells(c)

      ‘if cell is empty, make cell yellow
      If String.IsNullOrEmpty(cell.Text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
   Next
End If

And for a named control in a Template field, you use the cell’s FindControl method to get the control, and then check the control’s text property.  So if you had a Template field with a label named lblValue, you’d do this:

Dim cell As TableCell
Dim lbl As Label

If e.Row.RowType = DataControlRowType.DataRow Then

      ‘get cell
      cell = e.Row.Cells(0)

      ‘get control
      lbl = CType(cell.FindControl(“lblValue”), Label)

      ‘if label text is empty, make cell yellow
      If String.IsNullOrEmpty(lbl.Text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
End If

Turned out my problem was that I was using Template fields, but not using named controls (at least not in the item template) – so I couldn’t use the FindControl method because I didn’t have a control name to feed it, but just checking the cell’s text property returned an empty string.  Because, I discovered, when you use a Template field without a named control, like this:

<asp:TemplateField HeaderText=”Value”>
    <ItemTemplate>
        <%#Me.FormatCell(Eval(“Value”))%>
    </ItemTemplate>
    <EditItemTemplate>
        <asp:TextBox ID=”txtValue” runat=”server” Text='<%# Bind(“Value”) %>’>
        </asp:TextBox>
    </EditItemTemplate>
</asp:TemplateField>

even though you don’t specifically put the Item Template value in a control, ASP.NET does.  You can’t tell this from looking at the source code – the value just appears to be in a regular <td> cell.  But if you look at the cell in the Command Window during Debugging (as I finally thought to do), you’ll see that there is a control in the cell’s control collection – a DataBoundLiteralControl.

So I needed to do a variation of the second get-the-value-from-the-control method.  Plus I had the added difficulty that I wanted the cell background to stay yellow even while the user was editing it.  But of course, when the row was in edit mode, the cell had 3 controls, not one – a LiteralControl, a Textbox, and another LiteralControl.  And, I didn’t want to have to name every control in order to use the cell’s FindControl method – I wanted to look at every cell.

So I worked out a procedure that found the controls I needed based on their position in the cell’s control collection, rather than their name – and adjusted for rows in edit mode.  My final code ended up looking like this:

Dim c As Integer
Dim cell As TableCell
Dim td As DataBoundLiteralControl
Dim txtBox As TextBox
Dim text As String

If e.Row.RowType = DataControlRowType.DataRow Then

   For c = 0 To e.Row.Cells.Count – 1

      ‘get cell
      cell = e.Row.Cells(c)

      ‘if row is in edit mode, get textbox control’s text value
      If e.Row.RowIndex = Me.Gridview1.EditIndex Then 
          txtBox = CType(cell.Controls(1), TextBox)
          text = txtBox.Text.Trim
      Else ‘otherwise, get DataBoundLiteralControl’s text value
          td = CType(cell.Controls(0), DataBoundLiteralControl)
          text = td.Text.Trim
      End If

      ‘if text is blank, set cell backcolor to yellow
      If String.IsNullOrEmpty(text) Then
          cell.BackColor = Drawing.Color.Yellow
      End If
   Next

End If

Formatting percentages with Javascript

My current project has several pages where I need to grab values from textboxes, calculate a percentage, and then display the percentage in a label.  So I needed to write a Javascript function to do that.

As always, I relied on Google, as I’m not a Javascript expert, and find it easier to start with other people’s code than figure out all the details myself.  So I found a nifty little bit of code that formats a decimal as a percentage using Math.round.  All well and good, except my client wants some percentages to always have two decimals, even when the calculated value is a whole number.  And, of course, Math.round rounds to the nearest integer – no remainders.

So I found another nifty little bit of code that allows you to round to however many decimal places you want, rather than just to the nearest integer.   Better, but this code will return a whole number with no decimals when the calculated value is an integer, and I need two decimal places no matter what.

And so I found another neat bit of code, which uses the modulus operator to determine whether the value is an integer – if it is, then I’ll add the “.00” manually.

Just a couple more wrinkles – in this application, when the values in the texboxes are negative numbers, they will be formatted as (100), rather than -100.  And, sometimes the client wants two decimals, and sometimes he wants no decimals.

So I put it all together as follows (a previous function – not shown – does the calculation, and sends the calculated value to the function below):

function formatPercent(value, noDecimal)
{
//set default flag value
var isNegNum = false;

//set default multiplier value (will produce result with 2 decimal places)
var mult = 100;

//if neg number (begins with paren)…
if (value.substring(0,1) == “(“)
{
//set flag to true
isNegNum = true;

//remove parens from value
value = value.replace(/(/,”);
value = value.replace(/)/,”);
}

//if  no decimals wanted (‘noDecimal’ param has a value), set multiplier to 1
//(will produce result with no decimals)
if (noDecimal != null) {mult = 1;}

//format percent (‘mult’ variable determines whether has 2 or 0 decimals)
var result = Number(value)*100;
result = Math.round(result*mult)/mult;

//if decimals wanted (‘noDecimal’ param doesn’t have a value) and result
//is a whole number, manually add decimals
if (noDecimal === undefined && isInt(result) == true)
{
result = result + “.00”
}

//add minus sign to neg number
if (isNegNum == true)
{
result = “-” + result;
}

//add percent sign
result = result + “%”

//return formatted percent
return result;
}

function isInt(num)
{
// get the modulus
var mod = num % 1;

//if it’s 0, then it’s an integer
if (mod == 0) {
return true;
} else {
return false;
}
}

To use this function, send in the value that you want to be formatted as the first argument.  If you want the formatting to contain two decimal places, then don’t send anything in for the second (‘noDecimal’) argument.  If you want no decimals, then send in something for this second argument (doesn’t really matter what, as the function only checks to see if the parameter is null or not – I’ve been sending the string “true”).

Using “Execute As” in a Stored Procedure To Access A Separate Database

Here is a scenario that I often run across. A client wants me to create an add-on to a pre-existing application that runs on SQL Server. I don’t want to touch the existing application and database (let’s call this database PROPIETARY) so I create a separate database (we’ll call it TECHKNOWSOLVE) that queries the existing database. Now, in the PROPRIETARY database is a table (often an employees table) that contains all sorts of confidential information (like salary, age, social security number, etc) that I don’t need. In the TECHKNOWSOLVE database I just need a few columns from this table, like LastName, FirstName, and EmployeeID.

Here’s what I do. I create a job that runs a stored procedure once a day. In the stored procedure I delete the data in the TECHKNOWSOLVE Employees table and then repopulate it with the data I need from the PROPRIETARY Employees table. All works well since the job runs under my login and I am a system administrator.

But what if users need to update the TECHKNOWSOLVE Employees table before the next job runs? If I create a button for them within my application that runs the stored procedure, they will get an error unless I give them select permission on the Employees table in PROPRIETARY and that’s a bad idea. I could grant column permissions within the Employees table in PROPRIETARY but I’d really like to touch the PROPRIETARY database as little as possible. So instead, I make a change in my stored procedure.

Here’s the relevant part of the original procedure:

Create procedure uspGetEmployees
as
insert into tblEmployees select employee_no, first_name, last_name
from PROPRIETARY.dbo.employees

Here’s the change:

Create procedure uspGetEmployees
with execute as ‘DOMAINNAMEAllPowerfulUser’
as
insert into tblEmployees select employee_no, first_name, last_name
from PROPRIETARY.dbo.employees

Now if you run this you will get an error: The Server principal “User” is not able to access the database “model” under the current security context. That’s because you’re not done yet. There are a few steps you’re going to have to take before you can get the stored procedure to work. This MSDN article gives some excellent information about extending database impersonation but I thought it also left a few things out, thus my tutorial here.
 

The first thing you have to do is make sure the OWNER of your database, in this case TECHKNOWSOLVE, is also a user in the PROPRIETARY database. Once you’ve done that, you have to grant that user authenticate permissions in the PROPRIETARY database. Here’s how you do that:

• In the PROPRIETARY database, under Security and then users, right-click on the user who is the owner of TECHKNOWSOLVE. Choose Properties
• Click on Securables. Click Add
• Choose Specific Objects and Click OK
• Click on the Object Types button, click Databases and Click OK
• Click on the Browse button
• Select the applicable database (PROPRIETARY) and click OK.
  Down below you will see all the permissions. Scroll down until you see Authenticate.
  Check Grant and click OK

Ah, but we’re not done yet! Now we have to tell the SQL Server that our TECHKNOWSOLVE database is TRUSTWORTHY.

In the Master Database, run this query:

ALTER DATABASE TECHKNOWSOLVE SET TRUSTWORTHY ON;

Now you should be ready to go, but you could still run into trouble. If you don’t have Service Pack 1 or later installed and you use this with a stored procedure that includes transactions and rollbacks, you could get the following error: A severe error occurred on the current command. The results, if any, should be discarded. This error will be fixed by installing the latest service pack.

You may also get this error: The database owner SID recorded in the master database differs from the database owner SID recorded in database ‘YOURDATABASENAME’. You should correct this situation by resetting the owner of database ‘YOURDATABASENAME’ using the ALTER AUTHORIZATION statement.

This blog post gives good information about this error. The easiest solution I found was to change the database owner of TECHKNOWSOLVE to sa, using this command:
EXEC dbo.sp_changedbowner @loginame = N’sa’, @map = false

 

Correct for 2-digit date entry in Javascript

In working with the function described in the last post, I realized that I needed some way to correct the year when a user entered a 2-digit year as opposed to a 4-digit year. This is because Javascript interprets ‘3/1/09’ as ‘March 1, 1909’ — throwing off my calculations quite alarmingly. There are a variety of fixes for this posted online, but most of them are focused on 19xx and 20xx. And even though it seems unlikely that anyone will be using anything I’ve written in 21xx, I still wanted to plan for it – wanted to learn from my y2k forebears.

So I worked out a script that subtracts the first two numbers of the entered year from the first two numbers of the current one, multiplies the result by 100 and then adds it to the entered year. Thus, if the current year is 2008 and the entered year is 1909, the script does this:

• 20 – 19 = 1
• 1 * 100 = 100
• 1909 + 100 = 2009

If the current year is 2008 and the entered year is 2009, the script does this:

• 20 – 20 = 0
• 0 * 100 = 0
• 2009 + 0 = 2009

And, if someone is still using my script in 2108, and javascript still interprets a 2-digit year of ’09 as 1909, the script would work like this:

• 21 – 19 = 2
• 2 * 100 = 200
• 1909 + 200 = 2109

Here’s the script:

function correctYear(dt)
{
//get current date
var currDate = new Date();

//get full year for current date and arg date and convert to string
var dtYear = dt.getFullYear() + “”;
var currYear = currDate.getFullYear() + “”;

//subtract first two numbers of arg year from first two numbers of current year
var diff = (Number(currYear.substring(0,2)) – Number(dtYear.substring(0,2)));

//multiply difference by 100 and add to arg year
dtYear = Number(dtYear) + (diff * 100);

//set corrected year as arg date year, and return corrected date object
dt.setFullYear(dtYear);
return dt;
}

It’s not perfect – if the someone in 2108 really meant 2009, not 2109, then they’ll have to type that out. Same for someone in 2098 referring to 2102 – if they type in ’02, my script will interpret that as 2002 (of course, once the century turns, it’s all good again). But I took my shot. You can see the function in action in my last post.

Get the difference between two dates with Javascript

This is nothing earthshaking; in fact, it’s almost a complete rip off of another blog post, where I initially found what I needed. But I thought I’d put it here, so it would be easier for me to find it again when I need it again:-)

function daysDiff(dayStart, daySubtract)
{

//if either date is empty, return empty string
if ((dayStart == ”||dayStart == null)||(daySubtract == ”||daySubtract == null)){
return ”;
}
else {
//convert values to dates
var dateStart = new Date(dayStart);
var dateSubtract = new Date(daySubtract);

//correct for 2-digit year entry
var dateStart = correctYear(dateStart);
var dateSubtract = correctYear(dateSubtract);

//one day in milliseconds
var oneDay=1000*60*60*24;

//use the getTime() method of the javascript date object, which converts
//date to milliseconds. Subtract dates, then divide by oneDay variable
//to convert milliseconds to days. Round using the Math.round()* method.
var days = Math.round((dateStart.getTime()-dateSubtract.getTime())/(oneDay));

//return variable
return days;
}
}

*Originally, I used Math.ceil() here (because the blog I ripped off used Math.ceil).  However, I found in practice that Math.ceil did not give me the results I wanted.  Math.ceil always rounds upwards to the nearest integer, whereas Math.round will round up or down to the nearest integer.

Reporting Services – Report Manager won’t work after renaming web server

It took me a while to find out where I had to make a change when one of our clients changed the name of their web server. The error message I got when I tried to browse the Report Manager from IIS said “The remote name could not be resolved:” and displayed the old name of the web server.

There is a config file called RSWebApplication.config. You will find this file in your Reports Virtual Directory. The default location for this file on my development machine is C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportManager.

In this file, you need to change the value of the ReportServerURL to reflect the new name of your web server.

Use Generic Methods to create a Session Wrapper class

Getting values out of session in ASP.NET can be a bit of a pain for two reasons:

1. You have to remember what you named the value when you put it in
2. You have to type cast the value, as session stores it in an Object

One nice way to handle this is to build a wrapper class to handle getting values into and out of session. And an even nicer way is to use generic methods in your wrapper class.

Here’s how I implement this in my projects. First, I have a class called SessionBase that contains two shared Generic methods, LoadValue and RetrieveValue:

Public Class SessionBase

Public Shared Function RetrieveValue(Of T)(ByVal keyName As String) As T

If HttpContext.Current.Session(keyName) IsNot Nothing Then
Return DirectCast(HttpContext.Current.Session(keyName), T)
End If

End Function

Public Shared Sub LoadValue(Of T)(ByVal keyName As String, ByVal value As T)

HttpContext.Current.Session(keyName) = value

End Sub

End Class

The beauty of these methods is that they can handle any data type – I don’t have to have separate methods for integers, collections, custom types, etc. It’s all handled here in this generic and reusable class (I usually keep it in a .dll along with other base classes, and add a reference to it in my project). I use DirectCast rather than CType in my retrieve method because it is supposed to have less of a performance penalty.

Then, in my project, I create a class called sess that inherits the base class (because the base class contains shared methods, it doesn’t really need to be inherited, but I prefer to do it this way so there is less typing – I can type sess.LoadValue instead of TKS.SessionBase.LoadValue). In the project-specific class, I create constants for the session key names, and shared properties to load and retrieve values from session:

Public Class sess
Inherits TKS.Web.SessionBase

Private Const cProjID As String = "ProjID"
Private Const cProjName As String = "ProjName"

Public Shared Property ProjID() As Integer
Get
Return sess.RetrieveValue(Of Integer)(cProjID)
End Get
Set(ByVal value As Integer)
sess.LoadValue(of Integer)(cProjID, value)
End Set
End Property

Public Shared Property ProjName() As String
Get
Return sess.RetrieveValue(Of String)(cProjName)
End Get
Set(ByVal value As String)
sess.LoadValue(Of String)(cProjName, value)
End Set
End Property

End Class

Note how the generic methods are made specific at this point – Integer for ProjID, String for ProjName.

Now in my project, I can use the sess properties to easily access the session object:

Protected Sub btnLoad_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoad.Click

sess.ProjID = Integer.Parse(Me.txtProjectID.Text)
sess.ProjName = Me.txtProjectName.Text

End Sub

Protected Sub btnRetrieve_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnRetrieve.Click

Me.txtProjectID.Text = sess.ProjID.ToString
Me.txtProjectName.Text = sess.ProjName

End Sub

Dynamically change Master Page body tag

Today I spent several hours butting up against this, and finally, after much gnashing of teeth, found this great blog entry explaining what I needed to know.

Basically, the problem is that I needed to call a javascript function onload in the body tag of the page, but of course in an ASP.NET Content page there is no body tag – it only occurs once in the Master Page. But I didn’t want to put my javascript call in the Master page body tag, because then it would be called on every page.

I tried putting it in window.onload in a script tag at the bottom of the page, but this then screwed up some ASP.NET-generated javascript from a validation control, and also some of my other javascript.

The solution put forth in the blog entry is to dynamically put the onload function call in the Master Pages body tag from code in the Content page. Don’t have time to show the code myself (it’s quittin’ time), but please feel free to follow the above link.

By the way, this still didn’t solve my problem in Mozilla, but it works in IE, and as this particular project is for a company intranet that only uses IE, I’m happy (at least for now).