Share This!

Wednesday, December 16, 2009

Using MSSQL to Calculate Map Distances

This procedure will show you how to create a SQL Server function that accepts 2 map coordinates in and calculates the surface distance between them.

First we will create the function.

-- =============================================
-- Author:  Bryan Valencia
-- Create date: 12/16/2009
-- Description: takes latitude/longitude for 2 
--   places on Earth and gives the spherical distance.
-- =============================================

 @lat1 float,
 @long1 float,
 @lat2 float,
 @long2 float
 DECLARE @r float 
 --radius of the Earth
 --select @r=3437.74677 --(nautical miles)
 --select @r=6378.7 --(kilometers)
 SELECT @r=3963.0 --(statute miles) 
 DECLARE @radlat1 float
 DECLARE @radlong1 float
 DECLARE @radlat2 float
 DECLARE @radlong2 float
 SELECT @radlat1 = RADIANS(@lat1)
 SELECT @radlong1 = RADIANS(@long1)
 SELECT @radlat2 = RADIANS(@lat2)
 SELECT @radlong2 = RADIANS(@long2)
 --calculate answer (from
 -- and
 DECLARE @answer float
 SELECT @answer = @r * ACOS(SIN(@radlat1)*SIN(@radlat2)+COS(@radlat1)*COS(@radlat2)*cos(@radlong2 - @radlong1))
 RETURN @answer



So now we have a function to accept two lat/long coords and return the distance.  To use it in a select, (assuming you have a data table of geodata organized by zip code, like this... Access Zip Code Database.

Import this data to SQL Server and then use this select statement.

Select distinct Z1.[ZIP Code], Z1.City, Z1.[State Code], 
  cast(Z2.Latitude as float),cast(Z2.Longitude as float),
  cast(Z1.Latitude as float),cast(Z1.Longitude as float)) as Distance 
from [ZIP Codes] Z1
left outer join [ZIP Codes] Z2 on (Z2.[ZIP Code]='94558')
order by 4

Note here that the data as presented in the Access table stores the latitudes and longitudes as text, so we need to use cast to force it to floats. This gives us a result set that looks a lot like this...

Monday, November 2, 2009

Using a Legacy Windows DLL in ASP.NET

Recently I had to use a very old DLL in an web site. The dll has one function (that I care about) and I went down one rabbit trail after another looking for how to get this done. This is NOT a .NET managed dll, it's an old-style dll where there is a function that accepts various parameters and returns a horrible coded string.

Without much ado, let me show you what worked in this case. Note that Visual Web Developer was of NO HELP AT ALL when it came to figuring out the setup for this.

Place the dll. I found that it was nearly impossible to place the dll in my /bin/ directory under my website. Likewise I found that it was impossible to create a reference to it in the /bin/ directory. I placed the dll in my C:\Windows\System32\ directory. I have seen some chatter that this is the only place the IIS server has enough permissions for, so even if you think someplace else might be better, I'm telling you that this worked for me.

Wrap the function. You need to create a wrapper in your c# code to call the function in the dll. Note that you MUST have the function call specs - VWD will not find them for you like it will for a .NET managed dll.
Here's what the setup looks like... anything highlighted will have to be replaced with the info for your dll call.

using System;
using System.Runtime.InteropServices;

/// Wrapper for a function in the CodeGen DLL
public class CodeGen
 public static extern string CreateCode2(
  int level,
  string name,
  string encrypt_template,
  Int64 hardwareID,
  int otherinfo1,
  int otherinfo2,
  int otherinfo3,
  int otherinfo4,
  int otherinfo5

Now you just add a call to your wrapper function from wherever it's appropriate.

 protected void Page_Load(object sender, EventArgs e)
  string code = CodeGen.CreateCode2(1, 
     "This is an encrypting template", 
     0xface0fff, 0, 0, 0, 0, 0);

Wednesday, October 28, 2009

Using GenericIdentity for Cross Platform Authentication in the .NET framework

Let me say from the beginning that this should be a lot easier.

Basic Authentication

When designing a WinForms application, the most straightforward way to authenticate a user is using NTLM or Active Directory... It's built right into the OS and you don't need to deal with password dialogs and lost password questions at all - just ask Windows who the user is, like this...

using System.Security.Principal;
WindowsIdentity windowsIdentity = WindowsIdentity.GetCurrent();

That was easy... now how about using a web application?  That gets a little more complicated.  If the user is coming in over the intranet, then IIS knows who it is, but if they are accessing your site over the internet, it uses the whole aspnet security setup to create users and roles and permissions.  This is very easy to use as well, and for the most part requires no coding at all - but if you need to retrieve the name of the authenticated user, you would use something like this...

using System.Web.Security;
MembershipUser User = Membership.GetUser();
roles = System.Web.Security.Roles.GetRolesForUser();

Rolling Your Own

What if you need a robust set of libraries that can access identity information regardless of the data source?  Thats where the GenericIdentity and GenericPrincipal objects come into play.
For deployment into a mixed web/winforms environment, these components are very useful as they can migrate without regard to the source of the user and role data.

Creating a Generic Identity


string userName = "somebody";
GenericIdentity authenticatedGenericIdentity = new GenericIdentity(userName, "Database");

This creates a generic identity named somebody who was validated using an authenticacationType of "Database".

...from Windows

WindowsIdentity windowsIdentity = WindowsIdentity.GetCurrent();
string authenticationType = windowsIdentity.AuthenticationType;
string userName = windowsIdentity.Name;
GenericIdentity authenticatedGenericIdentity = new GenericIdentity(userName, authenticationType);

...from a database

string connectionstring = @"Data Source=.\SQLExpress;Initial Catalog=PermsLib;Integrated Security=True";
SqlConnection MyConn = new SqlConnection(connectionstring);

SqlCommand Query1 = new SqlCommand(@"Select * from logins where username=@user and password=@pwd and account_locked=0;", MyConn);
Query1.Parameters.AddWithValue("@user", username);
Query1.Parameters.AddWithValue("@pwd", password);

SqlDataReader myReader = Query1.ExecuteReader();

if (myReader.HasRows)
string userName = username;
GenericIdentity authenticatedGenericIdentity = new GenericIdentity(userName, "Database");
return authenticatedGenericIdentity;
} else {
throw new System.Security.SecurityException("invalid user");

The great thing is that you can toss these objects around in a mixed application, and they will travel nicely from place to place.

What is a GenericPrincipal?

That explains the identity object, but what is a GenericPrincipal?
As far as I can tell the Generic principal's only use is to contain both a GenericIdentity object, and a list of roles assigned to that identity.  So think of it as a baggie with an ID card and a ring of keys (roles/permissions).
Loading a GenericPrincipal  object is easy, all you need is a GenericIdentity, and a string array of roles.
string[] userRoles = { "Administrator", "Manager" }; GenericPrincipal MyPrincipal = new GenericPrincipal(userIdentity, userRoles);
The source of the roles data is unimportant, it can be hard coded, from a database, XML, or even read directly from ActiveDirectory sources.

Thursday, September 17, 2009

Failed to access the metabase, error code is 80070422

Event Type: Error
Event Source: MSExchangeMU
Event Category: General 
Event ID: 1009
Date:  9/17/2009
Time:  9:51:51 PM
User:  N/A
Computer: WEB1
Failed to access the metabase, error code is 80070422 (The service cannot be started, either because it is disabled or because it has no enabled devices associated with it.). 

For more information, click

It seems that the latest Windows 2003 SBS patch (which I think was a IMF Patch) causes the IIS Admin Service to switch from Automatic Startup to DISABLED. Turn it back to automatic, and start it if you ever want to check your email again.

Thursday, July 16, 2009



Monday, February 23, 2009

Connection Strings, Web.Config, and the Development Environment

Ok, admittedly it's not as enticing as Sex, Lies, and Video Tape, but this is an awesome way of helping you with ASP.NET website development.

The Problem

You create a site on your local machine(s) and get it working perfectly. Now it comes time to publish your changes to the live website, and you run into a giant problem whenever there are any changes to your web.config file. All your connectionstrings (Data Source=localhost\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True) and configurations get promoted to the live site, where they fail until they are hand-edited to reflect the web site's configuration. Alternately, you can forbid the publishing of web.config, forcing all changes to this one file to be done by hand.

Following is a procedure showing how to remove the appSettings and connectionStrings sections into a separate file, enabling the publishing of web.config whenever without hand-editing.

  1. with your project open, add a new item (control-shift-A).
  2. choose Web Configuration File and name it appsettings.config Ignore the text that Visual Web Developer automatically puts in there.
  3. create another one named connectionstrings.config.
  4. wipe the text from these 2 new files.
  5. copy the appSettings section from your web.config appSettings section into the appsettings.config file. It should look like this:
    <add key="connectionstring" value="Data Source=localhost\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True"/>
  6. now do the same for connectionstrings:
    <connectionStrings >
    <add name="ConnectionString" connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True"   providerName="System.Data.SqlClient"/>
  7. Now replace these sections only in your web.config file as follows:
    <appSettings configSource="appsettings.config"> </appSettings>
    <connectionStrings configSource="connectionstrings.config"> </connectionStrings>
  8. Note: if anyone knows how to tell the Copy Web Site dialog to NEVER publish these files, I am all ears.

Now you can hand-edit the web server's copy of appSettings.config and connectionstrings.config to give them the correct environment for the webserver. If you can't edit these remotely, then you can edit them on the development server, publish them and then change them back for your development environment.

Monday, February 2, 2009

"Server Application Unavailable"

I got this error message while trying to set up my on line site to use a different database from the default crap-tastic AspNetSqlProvider that takes 3 minutes to boot up with each web hit.
Here's the entire (incorrect) error message as IIS sends it.

Server Application Unavailable

The web application you are attempting to access on this web server is currently unavailable.  Please hit the "Refresh" button in your web browser to retry your request.
Administrator Note: An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.
The first clue I got that something was Micro-softy was that there was no event logged in the event logger. I think I may have caused this problem when I logged in to the server with Remote Desktop and with Visual Web Developer at the same time, trying to make it select the correct database.

The Solution

  1. go to the webserver (or remote-desktop in)
  2. open IIS Manager (Microsoft Internet Information Services)
  3. browse to the server and website that is broken
  4. stop the website
  5. open the site's properties page (right click the name of the site and pick "properties")
  6. select the ASP.NET tab and change the .net version. Click Apply.
  7. change the version back to the right (latest) version. Click Apply.
  8. close the properties page and restart the website.

Thursday, January 22, 2009

Oracle Retentive

Oracle Retentive: adj. 1. The inability to write even the most trivial software function without making it access a SQL database in general, or an Oracle database in particular. Sundar is so Oracle retentive I bet he couldn't write "Hello, World" without a database connection. 2. The inability to understand any database other than Oracle, say MSSQL or MySQL. 3. The propensity to hand large sums of cash to Oracle because they asked you for it.

Thursday, January 15, 2009

View. Print. Handheld. Make your site work on any media.

There is no need to manually control the rendering of your site to alter layout. There is an easy CSS based feature that can be used to format your entire site for whatever options you need.

In this article we will see how to make the same site render automatically for screen, print, and hand held. There are other media types that can be used, but these will be the most common for daily use.

Wouldn't it be great if you could just alter the stylesheet for your site to adjust it slightly for printing or hand held devices? Then you could just create a single site, and use a modified stylesheet based on the media the site is being viewed on. Well, wishes do come true... observe!

<link href = "mysite.css" rel="stylesheet" type="text/css" media="screen" />
<link href = "mysite.print.css" rel="stylesheet" type="text/css" media="print"/>
<link href = "mysite.pda.css" rel="stylesheet" type="text/css" media="handheld"/>

All 3 lines (or ones like them) are placed in the <head> section of the master page, or in every html page if you are not using a master page. Note the normal html link to the stylesheet, with the addition of the media parameter. These media types are pre-defined and can be selected from a drop down in Visual Web Developer. The current list of supported types is here.

Now we can load a different stylesheet based on our needs! The next thing to do is to make a copy of our existing full stylesheet. If you do a lot of in-page formatting, you are going to rue the day you decided to do that.

Making the Menu Disappear

To make any div simply cease to exist on a printed page, simply do this in the printer version of the stylesheet:


The display:none; means that this div or td (tablecell) will not be rendered when the browser is rendering to that media type - print in this case. so our entire graybar object is hidden.needless to say, your layout can be dramatically altered for smaller handheld displays.

The most brilliant thing is that the browser manages all this. So you never have to do anything conditional in your code that eats processor power. For instance, you don't have to have a "printer-friendly" version, the print engine will automatically choose the stylesheet intended for paper printouts. What's really cool is that you needn't worry what happens if you have a printer-friendly version of your site and people start navigating around - or linking to the version not intended for screen.

Layout for paper

OK, in word processors we taught ourselves to think in "points". then we started doing websites, and points make it all unmanageable, we opt instead for "pixels". But now we're actually designing websites for a paper media, so in the copy of the stylesheet, wherever you see the letters px, it's a good idea to re-arrange your thinking back into points. It can be a lot of work but you'll thank me later.

Tuesday, January 6, 2009

Don't Overlook Robots.txt

In the good old days, web spiders would crawl your sites once you registered them with a search engine. Today, they are a lot more proactive, crawling sites when the domain names are registered. For this reason, it is not optional during the development phase to add a robots.txt file to all projects that instructs robots not to crawl the site.

It's super easy. Just create a text file in the website root directory named robots.txt. Put the following text in it.

User-agent: *
Disallow: /

That's it. Now your temporary website is safe from most webcrawlers. Note that all subdomains must have one of these in the root path.


There is no need to put a robots.txt in subdirectories or virtual paths, such as

More info on Robots.txt here

More in-depth info here

A tutorial for when you want your site to be crawled.

Please note that without a robots.txt file, a web spider will attempt to crawl every file, every path in your website. It is rare that you would actually want a webcrawler to do this. For instance, do you really want all your button images and background images indexed?

Contact Us


Email *

Message *