Stone Coast Web Design Blog

SQL Server PIVOT Function

Eric — Fri, 03 Feb 2012 14:28:40 GMT

http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

http://blogs.msdn.com/b/spike/archive/2009/03/03/pivot-tables-in-sql-server-a-simple-sample.aspx

http://www.calsql.com/2009/10/pivot-with-out-aggregate.html

FamFamFam Icon Sets

Eric — Fri, 27 Jan 2012 16:07:03 GMT

This is a great freeware site for icons for sites http://famfamfam.com/lab/icons/

2 of the sets are available here:

Full Set

Mini Icons

Installing Sharepoint Foundation 2010 On Windows 7

Eric — Wed, 25 Jan 2012 12:05:47 GMT

Unlike Sharepoint 2007 which required that it be installed on a Windows Server product (2003 or 2008), Sharepoint 2010 can be installed on Windows Vista or Windows 7 as long as they are x64 versions. This is great for developers and for those who just want to learn more about the product.

It takes a few steps but I got it working on Windows 7 using these instructions:

http://msdn.microsoft.com/library/ee554869(office.14).aspx

Sharepoint Web Parts

Eric — Mon, 23 Jan 2012 20:18:13 GMT

Here is a huge list of free Sharepoint Web parts

http://www.amrein.com/apps/page.asp?Q=5794

ASP.NET Controls Suite–oBout Software

Eric — Mon, 23 Jan 2012 14:31:10 GMT

This is a really nice (and free) suite of ASP.NET controls

http://www.obout.com/

Fonts

Eric — Fri, 20 Jan 2012 15:49:45 GMT

This is a nice site for free fonts: http://www.fontsquirrel.com/

Creating and Using a VB Class

Eric — Wed, 28 Dec 2011 19:06:29 GMT

This is kind of a basic overview of how to create a class file in VB.NET and use it in your Web application.

Here is the code for the CallHistory class. Notice where the file needs to be located in the application.

Imports Microsoft.VisualBasic Imports System.Data Imports System.Data.SqlClient Public Class CallHistory Public Sub AddCallHistory(ByVal ReviewID As Integer, ByVal UserID As Integer, ByVal EventName As String) Dim strSQLConn As SqlConnection Dim cmd As SqlCommand
strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("CCMConnectionString").ConnectionString)
cmd = New SqlCommand("p_AddCallHistory",
strSQLConn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@ReviewID",
ReviewID) cmd.Parameters.AddWithValue("@UserID",
UserID) cmd.Parameters.AddWithValue("@DateChanged",
Now()) cmd.Parameters.AddWithValue("@EventName",
EventName) Try strSQLConn.Open() cmd.Connection = strSQLConn
cmd.ExecuteNonQuery() Finally strSQLConn.Close() End Try End Sub End Class

Now we can use this class anywhere in our web application using the following technique.

First import our CallHistory namespace

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="CallHistory" %>

Next, we create an instance of the class:

'Add the call to the CallHistory table Dim newCallHistory As New CallHistory

then we call the method

newCallHistory.AddCallHistory(ReviewID, lblUserID.Text, "Call
Added")

Microsoft Ajax Content Delivery Network

Eric — Sat, 24 Dec 2011 03:45:14 GMT

I’ve been using the Microsoft Ajax CDN for a while but didn’t know that they had an official page for it. Includes not only links to jQuery but also to jQuery UI, jQuery Validate, jQuery Mobile and Modernizr.

http://www.asp.net/ajaxlibrary/cdn.ashx#Modernizr_Releases_on_the_CDN_6

Here is an example of how to call these files from the CDN:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="TestjQueryUICDN.WebForm1" %>
<!DOCTYPE html > <html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server"> <title>Using jQuery UI
from the CDN</title> <link rel="Stylesheet" href="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.10/themes/redmond/jquery-ui.css" />
</head> <body> <form id="form1" runat="server">
<div> <asp:TextBox ID="txtStartDate" ClientIDMode="Static" runat="server" />
</div> </form> <script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.5.js"></script>
<script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.10/jquery-ui.js"></script>
<script> $("#txtStartDate").datepicker(); </script>
</body> </html>

SQL Server: Handling Multiple Result sets in a Procedure

Eric — Wed, 16 Nov 2011 14:36:36 GMT

Suppose you have a SQL Server stored procedure that returns multiple result sets and you want to store all these results into another table.

CREATE procedure [dbo].[mytest] as select 1 as id, 'test1' as myname select 2 as id, 'test2' as myname

When you execute this procedure, it returns two result sets. The following code will copy these two result sets in a table variable.

declare @t table(id int, names varchar(100))
insert into @t exec mytest select * from @t

SQL Server–CSVExpress

Eric — Wed, 16 Nov 2011 14:01:55 GMT

http://csvexpress.expressor-software.com/

Some cool software to move CSV data around to different databases.

ASP.NET Browser Definition Files

Eric — Tue, 15 Nov 2011 21:15:28 GMT

Microsoft has updated their browser definition files http://stephenwalther.com/blog/archive/2010/03/05/use-asp.net-4-browser-definitions-with-asp.net-3.5.aspx

Another related article:

http://visualstudiomagazine.com/articles/2011/11/14/recognizing-mobile-browsers.aspx

Scan to PDF

Eric — Tue, 15 Nov 2011 13:26:01 GMT

I work from home full-time and I often need to print and sign documents and then scan them back to as a PDF. There are many solutions you can pay for but I found a free one which involves iCopy and CutePDF.

iCopy: http://icopy.sourceforge.net/?page_id=40

CutePDF: http://www.cutepdf.com/

SQL Server - Unmatched Query Record

Eric — Thu, 10 Nov 2011 20:00:00 GMT

SELECT * FROM Employees
SELECT * FROM EmployeeHistory

If table Employees has a one to many relationship with EmployeeHistory and you need to find any missing records from EmployeeHistory, then you could run the two queries above and compare them side-by-side but this pretty tedious and kind of dumb. A better way is to run an unmatched query. A left outer join of the Employee table to the EmployeeHistory table on the EmpID field will get all matching and non-matching records. To look for every record where the Employee table does not have a matching record in the EmployeeHistory table, we look for a null in the EmployeeHistory table.

SELECT E.* FROM Employees LEFT OUTER JOIN EmployeeHistory
EH ON E.EmpID = EH.EmpID WHERE EH.EmpID IS NULL

Similarly, to insert the missing record into the EmployeeHistory table, you could do this:

INSERT INTO EmployeeHistory SELECT E.* FROM Employee
E LEFT OUTER JOIN EmployeeHistory
EH ON E.EmpID = EH.EmpID WHERE EH.EmpID IS NULL