Software Works

Dynamic Pivot with SQL in SQL Server

2024-12-07T02:40:00.000-08:00

In our example we have a Dynamic Survey form in the database which we would use to take survey of employees across an agency. Now when we have collected the survey we need to generate a report in pivot format which would give u one row per employee and in the columns we would get answers to each question. Interesting!

Lets look at the options available in SQL Server.

To pivot your query and transform it into a format where each row represents an employee and the columns contain answers for each question, you can use a PIVOT operation.

Steps:

Aggregate the Data:
- Use the base query to gather the employee data, question texts, and their corresponding answers.
Prepare the Pivot:

Use the PIVOT operator to dynamically transform rows (questions and answers) into columns for each question.

If the questions are dynamic and you don't know them in advance, you need to dynamically generate the column list using dynamic SQL.

Handling Dynamic Questions:

If the list of questions is not fixed and changes frequently, you can use dynamic SQL to generate the column list automatically.

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- Step 1: Generate the Column List Using FOR XML PATH
SELECT @Columns = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(QUESTION)
    FROM QUIZ_QUESTIONS
    WHERE QUESTIONID IN (
        SELECT DISTINCT QUESTIONID
        FROM QUIZ_ATTEMPT_RESULT
        WHERE QUIZASSIGNID IN (SELECT QUIZASSIGNID FROM QUIZ_ASSIGNMENT WHERE QUIZID = 1)
    )
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Step 2: Construct the Dynamic Pivot Query
SET @SQL = '
WITH BaseData AS (
    SELECT 
        EMPLOYEES.EMPLOYEEID,
        EMPLOYEES.FIRSTNAME + '' '' + EMPLOYEES.LASTNAME AS EmployeeName,
        QUIZ_QUESTIONS.QUESTION AS QuestionText,
        QUIZ_QUESTION_ANSWERS.ANSWER AS Answer
    FROM [dbo].[QUIZ_ASSIGNMENT]
    INNER JOIN EMPLOYEES
        ON EMPLOYEES.EMPLOYEEID = [QUIZ_ASSIGNMENT].EMPLOYEEID
    INNER JOIN [dbo].[QUIZ_ATTEMPT_RESULT]
        ON QUIZ_ATTEMPT_RESULT.QUIZASSIGNID = QUIZ_ASSIGNMENT.QUIZASSIGNID
    INNER JOIN [dbo].[QUIZ_QUESTION_ANSWERS]
        ON QUIZ_QUESTION_ANSWERS.ANSWERID = QUIZ_ATTEMPT_RESULT.ANSWERID
    INNER JOIN [dbo].[QUIZ_QUESTIONS]
        ON QUIZ_QUESTIONS.QUESTIONID = QUIZ_ATTEMPT_RESULT.QUESTIONID
    WHERE QUIZ_ASSIGNMENT.QUIZID = 1
)
SELECT *
FROM (
    SELECT 
        EmployeeName,
        QuestionText,
        Answer
    FROM BaseData
) AS SourceTable
PIVOT (
    MAX(Answer)
    FOR QuestionText IN (' + @Columns + ')
) AS PivotTable;
';

-- Step 3: Execute the Dynamic SQL
EXEC sp_executesql @SQL;

Sample Input Data:

Name	Question	Answer
John Smith	What is your name?	John
John Smith	What is your age?	30
Jane Doe	What is your name?	Jane
Jane Doe	What is your age?	25

Sample Output Data:

Name	What is your name?	What is your age?
John Smith	John	30
Jane Doe	Jane	25

HTML5, Draw Rounded Rectangle on Canvas

2012-09-23T01:46:00.001-07:00

Example below will draw rounded rectangle:

function doRoundedRectangle(c,sx,sy,ex,ey,r) {
 var ctx = c.getContext("2d");
 var r2d = Math.PI/180;
 if( ( ex - sx ) - ( 2 * r ) < 0 ) { r = ( ( ex - sx ) / 2 ); } //ensure that the radius isn't too large for x
 if( ( ey - sy ) - ( 2 * r ) < 0 ) { r = ( ( ey - sy ) / 2 ); } //ensure that the radius isn't too large for y
 ctx.beginPath();
 ctx.moveTo(sx+r,sy);
 ctx.lineTo(ex-r,sy);
 ctx.arc(ex-r,sy+r,r,r2d*270,r2d*360,false);
 ctx.lineTo(ex,ey-r);
 ctx.arc(ex-r,ey-r,r,r2d*0,r2d*90,false);
 ctx.lineTo(sx+r,ey);
 ctx.arc(sx+r,ey-r,r,r2d*90,r2d*180,false);
 ctx.lineTo(sx,sy+r);
 ctx.arc(sx+r,sy+r,r,r2d*180,r2d*270,false);
 ctx.closePath();
};

Parameters:
c = Canvas object
sx = Start x position
sy = Start y position
ex = End x position
ey = End y position
r = radius for rounded corners

HttpException: The URL-encoded form data is not valid

2012-03-13T00:01:00.006-07:00

An httpexception might be poping up in most of the ASP.NET 2.0 applications where there are lot of user controls on your view. Microsoft security update MS11-100 limits the maximum number of form keys, files, and JSON members to 1000 in an HTTP request. Because of this change, ASP.NET applications reject requests that have more than 1000 of these elements.
HTTP clients that make these kinds of requests will be denied, and an error message will appear in the web browser. The error message will usually have an HTTP 500 status code.

Exception information in Windows event log will contain:
Exception type: HttpException
Exception message: The URL-encoded form data is not valid

ASP.NET 2.0
If your application view reaches over 1000 elements, than you will need to configure this value in your web.config file. And following lines need to be added:

<configuration>
  <appSettings>
    <add key="aspnet:MaxHttpCollectionKeys" value="1000" />
  </appSettings>
</configuration>

You should set the value to your need, and it should be any value over 1000. You can give value under 1000 as well if your application works on it.

ASP.NET 1.1
For .NET 1.1 web applications you will have to set a DWORD in registry. Following is the key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ASP.NET\1.1.4322.0\MaxHttpCollectionKeys

JSON Limit
Applications that hit this limit for JSON payloads can configure as follows:

<configuration>
  <appSettings>
    <add key="aspnet:MaxJsonDeserializerMembers" value="1000" />
  </appSettings>
</configuration>

Reference:
http://support.microsoft.com/kb/2661403

Commodo Antivirus, Manually update virus definition

2012-01-11T01:19:00.000-08:00

Download the latest full AV database:

For respective CIS version, we have different complete bases.cav:

1. For CIS 4.x
Following link points to latest complete bases.cav always
http://download.Comodo.com/av/updates40/sigs/bases/bases.cav

2. For CIS 5.0
Following link points to latest complete bases.cav always
http://download.Comodo.com/av/updates50/sigs/bases/bases.cav

3. For CIS 5.3 & 5.4
i) Download
http://download.Comodo.com/av/updates51/sigs/bases/bases.cav.z
ii)
Use 7-zip from http://www.7-zip.org/download.html , or your archiver program of choice that can handle 7z archives and unpack it. After unpacking you will have to rename the file to bases.cav.

4. For CIS 5.5
i) Download
http://download.Comodo.com/av/updates55/sigs/bases/bases.cav.z
ii)
Use 7-zip from http://www.7-zip.org/download.html , or your archiver program of choice that can handle 7z archives and unpack it. After unpacking you will have to rename the file to bases.cav.

5. For CIS 5.8
i) Download
http://download.Comodo.com/av/updates58/sigs/bases/bases.cav.z
ii)
Use 7-zip from http://www.7-zip.org/download.html , or your archiver program of choice that can handle 7z archives and unpack it. After unpacking you will have to rename the file to bases.cav.

Activating the download:

1. Save the downloaded file on your computer

2. Reboot in Safe-Mode

3. Open explorer and go to C:\Program files\Comodo\Comodo internet security\scanners and copy the downloaded bases.cav here

4. Reboot your system in normal mode

The 'microsoft.jet.oledb.4.0' provider is not registered on the local machine

2011-11-17T21:20:00.000-08:00

On Windows 7 64bit and Windows 2008 64bit system a common error may arise when using Microsoft.jet.oledb.4.0 driver. Applications running in 64bit mode may not be able to access this driver. This is because 64bit version of this driver does not exist.

Solution to this problem is as follows:

1. We use 32bit application, 32bit application will be able to access the ODBC drivers

2. If you are a application developer and need to develop a 64bit application which can connect to csv, excel files, etc using ADO.NET. You should use Microsoft Access Database Engine 2010 which is a small redistributable and available in both 32bit and 64bit versions.

Summary:

Issue:
The 'microsoft.jet.oledb.4.0' provider is not registered on the local machine.
Issue is that Application pool is running in 64bit mode, therefore 32bit Jet drivers not accessible.

Solution:
Microsoft Access Database Engine 2010:
http://www.microsoft.com/download/en/details.aspx?id=13255
Use this provider Microsoft.ACE.OLEDB.12.0 to connect with csv, excel, etc files.

How to sort Rows/Data in a DataTable

2011-09-21T01:56:00.000-07:00

We can sort binding source, data view and data table default view to sort data in a data table. There are following three methods which can be applied:


Method 1:
 bindingSource.DataSource = table
 dataGridView.DataSource = bindingSource
 bindingSource.Sort = "Column_Name"

Method 2:
Alternatively, you can just use a DataView:

 Dim view as DataView = new DataView(table)
 view.Sort = "Column_Name"
 dataGridView.DataSource = view

Method 3:
or change the DataTable's DefaultView:

 table.DefaultView.Sort = "Column_Name"

Column_Name is the name of one or more columns on which a sort is required. So a Column_Name can have following values like: "COUNTRY" or "COUNTRY, POPULATION" or "COUNTRY, POPULATION DESC".

SQL Server Management, error 29506

2011-04-08T20:42:00.000-07:00

When installing SQL Server Management studio on Windows 7 64bit it gives an error with error code 29506. The problem is we need to start this setup as Administrator. Unfortunately when we right click on the setup file, there is no option to Run as Administrator in the context menu.

A work around to this problem is we start a Command prompt (Console) as Administrator, set directory path to the location of setup file, and type the setup file name and press enter to execute.

Now the setup will start as Administrator and will successfully complete.

Steps:

1. Downloaded setup file SQLServer2005_SSMSEE_x64.msi from Microsoft and saved on my local disk, in my case it is D:\Softwares\SSMSEE\

2. Open start menu and type "cmd" in the search box, you will see "cmd.exe" program

3. Now right click it and select Run as Administrator

4. First change drive by typing D: and press enter

5. Now change directory to setup location, type "cd D:\Softwares\SSMSEE\" and press enter

6. Now type setup file name "SQLServer2005_SSMSEE_x64.msi" and press enter

7. The setup will start and complete successfully. That's all

This might also be the case with Windows 7 with 32bit version, in that case same solution should work.

A canvas globalCompositeOperation example

2011-01-05T23:43:00.000-08:00

var compositeTypes = [
  'source-over','source-in','source-out','source-atop',
  'destination-over','destination-in','destination-out',
  'destination-atop','lighter','darker','copy','xor'
];
function draw(){
  for (i=0;i<compositeTypes.length;i++){
    var label = document.createTextNode(compositeTypes[i]);
    document.getElementById('lab'+i).appendChild(label);
    var ctx = document.getElementById('tut'+i).getContext('2d');

    // draw rectangle
    ctx.fillStyle = "#09f";
    ctx.fillRect(15,15,70,70);

    // set composite property
    ctx.globalCompositeOperation = compositeTypes[i];
    
    // draw circle
    ctx.fillStyle = "#f30";
    ctx.beginPath();
    ctx.arc(75,75,35,0,Math.PI*2,true);
    ctx.fill();
  }
}

Reference: developer.mozilla.org/samples/canvas-tutorial/canvas_composite

Check if a browser supports HTML5

2011-01-02T09:44:00.000-08:00

Check if a browser supports HTML5

HTML5 is a new way of developing interactive websites, HTML5 is still a emerging technology but we can already see lot of work being done and browsers supporting this new technology.

Anyone who will develop HTML5 will need to know how he can detect the browser is capable of supporting HTML5? or How he can detect which features the browser is compatible with.

The simplest way to do that is creating a canvas object using document.createElement method. After creating the object we can check if 2D context can be created, here is an example below:

<script type="text/javascript">
function supports_canvas() {
 //Check if browser supports canvas
 return !!document.createElement('canvas').getContext;
}
</script>

We have a open source solution as well, with help of which we can all the features supported by a browser, this is a javascript include file which can be obtained from Modernizr. We simply need to include this file in the head section, no call to any function, on its include it executes and initialize few boolean properties which can be accessed to check different HTML5 feature support.

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Dive Into HTML5</title>
  <script src="modernizr.min.js"></script>
</head>
<body>
  ...
</body>
</html>

The Modernizr.canvas property will return false if your browser does not support the HTML5 canvas API, following check will be used:

if (Modernizr.canvas) {
  // let's perform some 2d graphics!
} else {
  // no canvas support available!
}

HTML5 Hello World Application

2011-01-01T02:57:00.000-08:00

I consider HTML5 canvas the backbone of all the features available in HTML5, i recently got chance to work on a HTML5 application and really enjoyed learning new things and was really impressed with myself. Actually anyone who already knows some JavaScript and traditional HTML can kick start working on it.

For those who have just started working on it, i have created a Hello world application, this is a simple application, but will give overall view of how it works. Like in our graduation we always create a Hello world application to get start learning things, so here how it works:

First of all we should know whether the browser supports HTML5 canvas, for this you can view my post at Check if browser supports HTML.

I will use following method:

function supports_canvas() {
 //Check if browser supports canvas
 return !!document.createElement('canvas').getContext;
}

Next we need a Canvas element which supports 2D graphic drawings, it is the canvas on which we can draw different shapes like circle, rectangle, arcs, circle, etc also we can write text, and draw images from some source files.

This sample focuses on creating a canvas and writing hello world on it. For this i will create a DIV element in my document, than add a Canvas element in the DIV using JavaScript:

function initializeCanvas(containerid, canvasWidth, canvasHeight) {
  var container = document.getElementById(containerid);
  if ( supports_canvas() ) {
    //Create canvas element
    var canvas = document.createElement('canvas');
    canvas.setAttribute('width', canvasWidth);
    canvas.setAttribute('height', canvasHeight);
    container.appendChild(canvas);
    //Context of canvas for performing 2d graphics operations
    context = canvas.getContext('2d');
    return context;
  } else {
    container.innerHTML = 'This is a HTML5 app,
    you need a HTML5 capable browser like Firefox,
    Chrome, Safari or Internet Explorer 9.'
    
    return null;
  }
}

Here is the working sample: Sample Hello World

Crystal Reports .NET Error: Load Report Failed

2010-12-31T22:14:00.000-08:00

Most of the ASP.NET developers might be familiar with this problem and faced it too. When we Google for it, most of the solutions indicate that the C:\Windows\Temp folder needs permission for NETWORK SERVICE account in Windows Server and ASP NET account in Windows Client.

On my web server i can clearly see temporary files of crystal reports being created when a report is called from ASP.NET page. It is recommended to set a special permission no C:\Windows\Temp folder as follows:

Add permission to List folder and Read
Add permission to Create files, Append files
Add permission to Delete files

This should fix the problem in 80% of the cases. Although there are other scenarios as well which cause this problem. But i have here indicated the one common problem, i hope it works for most of you and save you a day.

Another issue might be in the coding section, if report documents are not properly closed and disposed off, this will trigger the error as well. Whenever a report is called in a web application a copy in C:\Windows\Temp folder is created and than served to the client response.

You should inspect the C:\Windows\Temp folder to see if these temp files are not hanging around, if so that means the report documents are not properly close and disposed after processing. Crystal report document need to be closed by calling the Close() method and than the Dispose() method to clean.

There is a recommendation for this in SAP Crystal reports document, and the code should look similar to the following:

private void Page_Unload(object sender, EventArgs e)
{
  if (boReportDocument != null)
  {
    boReportDocument.Close();
    boReportDocument.Dispose();
    GC.Collect();
  }
}

Reference: Troubleshooting the “Load Report Failed” Error

SQL Database Snapshot

2010-12-31T04:15:00.000-08:00

Create Database Snapshot on Current database:

USE TEST

GO

DECLARE @name VARCHAR(1000),
@filename VARCHAR(1000),
@dbname VARCHAR(1000),
@dbssname VARCHAR(1000),
@dbssfilename VARCHAR(1000)

DECLARE @hour VARCHAR(2),
@minute VARCHAR(2)

SET @hour = DATENAME(HOUR,GETDATE())
IF LEN(@hour) = 1 SET @hour = '0'+@hour
SET @minute = DATENAME(MINUTE,GETDATE())
IF LEN(@minute) = 1 SET @minute = '0'+@minute

SET @dbname = Db_Name();
SET @dbssname = @dbname+'_data_'+@hour+@minute
SET @dbssfilename = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\'+@dbssname+'.ss'

SELECT @name = [name], @filename = [filename]
FROM sys.sysfiles
WHERE groupid = 1;

--CREATE SNAPSHOT
EXEC( 'CREATE DATABASE ' + @dbname + '_dbss' +@hour+@minute + ' ON
( NAME =' + @name + ', FILENAME = '''+@dbssfilename +''')
AS SNAPSHOT OF '+@dbname);

Restore Database to a Database Snapshot:
USE master;
GO
-- Reverting TEST to TEST_dbss1717
RESTORE DATABASE TEST from
DATABASE_SNAPSHOT = 'TEST_dbss1717';
GO

Drop Database Snapshot:
USE master;
GO
DROP DATABASE TEST_dbss1717;

ASP.NET Change Master page on Run time

2010-11-26T03:09:00.000-08:00

As in my earlier post we looked at how we can change Theme of a page on click of a button, this similar principle is applied when we try to change master page on click of a button.

We again have the same question:

Can in Change master page on button click?
How to change master page on click of button?

The answer is again simple after reading the documentation of System.Web.UI.Page.MasterPageFile() property:

Property: Public Overridable Property MasterPageFile() As String
Member of: System.Web.UI.Page
Summary: Gets or sets the file name of the master page.
Exceptions:
System.InvalidOperationException: The System.Web.UI.Page.MasterPageFile
property is set after the System.Web.UI.Page.PreInit event is complete.
System.Web.HttpException: The file specified in does not exist or The page does not have a System.Web.UI.WebControls.Content control as the top level control.

The 'MasterPageFile' property can only be set in or before the 'Page_PreInit' event.

Same work around to change the MasterPageFile on run time on click of a button:

Partial Class Default
    Inherits System.Web.UI.Page

    Protected Sub btnChange_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnChange.Click
        'Cannot change MasterPageFile on Click event
    End Sub

    Protected Sub Page_PreInit(ByVal sender As Object, _
            ByVal e As System.EventArgs) Handles Me.PreInit
        'If Page is postback
        If Me.IsPostBack = True Then
            If Not Request(btnChange.UniqueID) Is Nothing Then
                Me.MasterPageFile = "~/NewMasterpage.master"
            End If
        End If

    End Sub

End Class

We have changed the MasterPageFile in 'Page_PreInit' event but on click of a button.

UniqueID of a control gets the unique, hierarchically qualified identifier for the server control. The fully qualified identifier for the server control, this is the ID which is received as key when value is post back.

The output of button is like this:
<input type="submit" name="btnChange" value="Change Theme" id="btnChange" />

Here name is what we call btnChange.UniqueID and id is what we call btnChane.ClientID, in HTML input controls "id" is used for client side validation and other java script things, and "name" attribute is used to identify the field when it is post back to server.

ASP.NET Change Page Theme on Run time

2010-11-23T08:59:00.000-08:00

A simple question about setting themes in ASP.NET would be of in which event I can set the or change the Theme of my page. Like if i want to give an option for user to select from list of available themes and the site on run time could be changed to a specific theme.

Can in Change theme on button click?
How to change theme on click of button?

The answer was simple after reading the documentation of System.Web.UI.Page.Theme() property:

Property: Public Overridable Property Theme() As String
Member of: System.Web.UI.Page
Summary: Gets or sets the name of the page theme.
Exceptions:
System.InvalidOperationException: An attempt was made to set System.Web.UI.Page.Theme after the System.Web.UI.Page.PreInit event has occurred.
System.ArgumentException: System.Web.UI.Page.Theme is set to an invalid theme name.

The 'Theme' property can only be set in or before the 'Page_PreInit' event.

I have made a work around to change the theme on run time on click of a button:

Partial Class Default
    Inherits System.Web.UI.Page

    Protected Sub btnChange_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) Handles btnChange.Click
        'Cannot change theme on Click event
    End Sub

    Protected Sub Page_PreInit(ByVal sender As Object, _
            ByVal e As System.EventArgs) Handles Me.PreInit
        'If Page is postback
        If Me.IsPostBack = True Then
            If Not Request(btnChange.UniqueID) Is Nothing Then
                Me.Page.Theme = "NewTheme"
            End If
        End If

    End Sub

End Class

We have still changed the theme in 'Page_PreInit' event but on click of a button.

UniqueID of a control gets the unique, hierarchically qualified identifier for the server control. The fully qualified identifier for the server control, this is ID which is received as key when value is post back.

The output of button is like this:
<input type="submit" name="btnChange" value="Change Theme" id="btnChange" />

Here name is what we call btnChange.UniqueID and id is what we call btnChane.ClientID, in HTML input controls "id" is used for client side validation and other java script things, and "name" attribute is used to identify the field when it is post back to server.

SQL Server 2005, Search string in Procedures and Functions

2010-11-12T10:06:00.000-08:00

Use following sql script to search some string inside Stored procedure or User defined function, this is handy when you have lot of stored procedures and functions and you are looking for particular one, like i want to list procedures in which a specific Table has been used.

This query search in procedures and functions script stored in database schema.

USE DATBASENAME

GO

-- SYS COMMENTS
SELECT distinct so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE charindex('Search', text) > 0

--INFORMATION_SCHEMA.ROUTINES
SELECT routine_name, routine_type
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%Search%'

ASP.NET Circular File Reference

2010-11-12T09:41:00.000-08:00

“Circular file references are not allowed” error occurs while building or deploying asp.net website, this can happen due to differnt reasons:

1. When one ASCX control references another which contains a reference back to the first one
2. When an ASCX control references another in a different directory that also contains other controls that reference back to the first one
3. When a ASPX page references ASCX control in a different folder, that also contain other pages that reference this control

There might be other reasons as well, ASP.NET website compiles into different libraries, which reference each other as well, .NET compiler bt default build libraries in batch, means files in one folder are batched together in a single library depending on the dependencies.

In "batch mode," the output of multiple source files is compiled into single assemblies according to the type of file, file dependencies, and other criteria. The result is a target site containing a set of assemblies with the executable code for the original source files.

<system.web>
    <compilation debug="true" batch="false">
    </compilation>
</system.web>

Here batch="false"tells the ASP.NET compiler to not batch assemblies, and create an assembly for each web form and user control. When it is set to true, then the compiler will batch assemblies.

Circular reference is a by-product of the ASP.NET compiler "batching" assemblies together for performance reasons. By default it will take the web forms and user controls in a folder and compile them into an assembly.

There are several ways to solve this issue, but we recommend moving the referenced pages (e.g. Pages2.aspx.vb and Pages3.aspx.vb) to their own folder. By default, the ASP.NET compiler will create a separate assembly containing these pages and the circular reference will be removed between Assembly A and B.

How to find Allocation Unit of your Disk Partition?

2010-11-10T23:45:00.000-08:00

Default allocation unit of any disk partition under Windows operation system is 4096 bytes for drives under 16 TB, this is the information as per Microsoft article 140365. The allocation unit is also referred as Cluster size.

Simple and fastest way to check what is the allocation unit size of any partition is as follows:

Command:
C:\Windows\system32>fsutil fsinfo ntfsinfo C:

Output:
C:\Windows\system32>fsutil fsinfo ntfsinfo C:
NTFS Volume Serial Number : 0x0000000000000000
Version : 3.1
Number Sectors : 0x0000000000000000
Total Clusters : 0x0000000000000000
Free Clusters : 0x0000000000000000
Total Reserved : 0x0000000000000000
Bytes Per Sector : 512
Bytes Per Cluster : 4096
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x0000000000000000
Mft Start Lcn : 0x0000000000000000
Mft2 Start Lcn : 0x0000000000000000
Mft Zone Start : 0x0000000000000000
Mft Zone End : 0x0000000000000000
RM Identifier: 00000000-0000-0000-0000-000000000000

In the above result "Bytes per Cluster" represents the allocation unit size.

Best Allocation unit Size:

Higher the allocation unit size better the performance, but lower the disk space available, because data is written in units, if you write 1 byte the allocation unit consumed will be 4096, with 4098 bytes wasted.

Therefore if you are going to use a drive for large files like videos, or other multimedia formats, than higher allocation will be better. And for partitions that will hold documents like text files, etc. default allocation unit size is enough.

Size and Size on Disk:

If you are a windows user, you may have noted that when we view properties of any file or folder, the general tab mentions two sizes. One is the "Size" which is the actual size of the file and other is the "Size on disk" which is the space consumed on the drive.

The difference in both size indicate how much space has been wasted.

Abstract Class vs Interface

2010-11-03T09:24:00.000-07:00

What is an Abstract Class?
An abstract class is a special kind of class that cannot be instantiated. So the question is why we need a class that cannot be instantiated? An abstract class is only to be sub-classed (inherited from). In other words, it only allows other classes to inherit from it but cannot be instantiated. The advantage is that it enforces certain hierarchies for all the subclasses. In simple words, it is a kind of contract that forces all the subclasses to carry on the same hierarchies or standards.

What is an Interface?
An interface has no implementation; it only has the signature or in other words, just the definition of the methods without the body. As one of the similarities to Abstract class, it is a contract that is used to define hierarchies for all subclasses or it defines specific set of methods and their arguments. The main difference between them is that a class can implement more than one interface but can only inherit from one abstract class. Interfaces are used to implement multiple inheritance.

Feature	Interface	Abstract class
Multiple inheritance	A class may inherit several interfaces.	A class may inherit only one abstract class.
Default implementation	An interface cannot provide any code, just the signature.	An abstract class can provide complete, default code and/or just the details that have to be overridden.
Access Modfiers	An interface cannot have access modifiers for the subs, functions, properties etc everything is assumed as public	An abstract class can contain access modifiers for the subs, functions, properties
Core VS Peripheral	Interfaces are used to define the peripheral abilities of a class. In other words both Human and Vehicle can inherit from a IMovable interface.	An abstract class defines the core identity of a class and there it is used for objects of the same type.
Homogeneity	If various implementations only share method signatures then it is better to use Interfaces.	If various implementations are of the same kind and use common behaviour or status then abstract class is better to use.
Speed	Requires more time to find the actual method in the corresponding classes.	Fast
Adding functionality (Versioning)	If we add a new method to an Interface then we have to track down all the implementations of the interface and define implementation for the new method.	If we add a new method to an abstract class then we have the option of providing default implementation and therefore all the existing code might work properly.
Fields and Constants	No fields can be defined in interfaces	An abstract class can have fields and constrants defined

Reference: Abstract Class versus Interface

Visual Basic .NET Data Type Summary

2010-11-03T08:42:00.000-07:00

The following table shows the Visual Basic data types, their supporting common language runtime types, their nominal storage allocation, and their value ranges.

Visual Basic type	Common language runtime type structure	Nominal storage allocation	Value range	System. Data.SqlDbType
Boolean	Boolean	Depends on implementing platform	True or False	Bit
Byte	Byte	1 byte	0 through 255 (unsigned)	TinyInt
Byte	Byte	Byte array	Array size	Binary [8000] Image [2147483647] Timestamp [8] VarBinary [8000]
Char (single character)	Char	2 bytes	0 through 65535 (unsigned)
Date	DateTime	8 bytes	0:00:00 (midnight) on January 1, 0001 through 11:59:59 PM on December 31, 9999	DateTime SmallDateTime
Decimal	Decimal	16 bytes	0 through +/-79,228,162,514,264,337,593,543,950,335 (+/-7.9...E+28) † with no decimal point; 0 through +/-7.9228162514264337593543950335 with 28 places to the right of the decimal; smallest nonzero number is +/-0.0000000000000000000000000001 (+/-1E-28) †	Decimal Money SmallMoney
Double (double-precision floating-point)	Double	8 bytes	-1.79769313486231570E+308 through -4.94065645841246544E-324 † for negative values; 4.94065645841246544E-324 through 1.79769313486231570E+308 † for positive values	Float
Integer	Int32	4 bytes	-2,147,483,648 through 2,147,483,647 (signed)	Int
Long (long integer)	Int64	8 bytes	-9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 (9.2...E+18 †) (signed)	BigInt
Object	Object (class)	4 bytes on 32-bit platform 8 bytes on 64-bit platform	Any type can be stored in a variable of type Object
SByte	SByte	1 byte	-128 through 127 (signed)
Short (short integer)	Int16	2 bytes	-32,768 through 32,767 (signed)	SmallInt
Single (single-precision floating-point)	Single	4 bytes	-3.4028235E+38 through -1.401298E-45 † for negative values; 1.401298E-45 through 3.4028235E+38 † for positive values	Real
String (variable-length)	String (class)	Depends on implementing platform	0 to approximately 2 billion Unicode characters	Char [8000] Nchar [4000] Ntext [1073741823] NVarChar [4000] Text [2147483647] VarChar [8000]
UInteger	UInt32	4 bytes	0 through 4,294,967,295 (unsigned)
ULong	UInt64	8 bytes	0 through 18,446,744,073,709,551,615 (1.8...E+19 †) (unsigned)
User-Defined (structure)	(inherits from ValueType)	Depends on implementing platform	Each member of the structure has a range determined by its data type and independent of the ranges of the other members
UShort	UInt16	2 bytes	0 through 65,535 (unsigned)

† In scientific notation, "E" refers to a power of 10. So 3.56E+2 signifies 3.56 x 102 or 356, and 3.56E-2 signifies 3.56 / 102 or 0.0356.

ASP.NET Control ClientID and UniqueID, Server control ID

2010-11-02T03:26:00.000-07:00

When ever a Server control is rendered in ASP.NET it has two different identifiers, one is named as ClientID which is the "id" attribute and second is the UniqueID which is the "name" attribute.

UniqueID of a control gets the unique, hierarchically qualified identifier for the server control. The fully qualified identifier for the server control, this is the ID which is received as key when value is post back.

The output of button and a text box is like this:
<input type="submit" name="btnLoad" value="Load" id="btnLoad" />
<input type="text" name="txtName" value="Hello" id="txtName" />

Here "name" is what we call btnLoad.UniqueID and "id" is what we call btnLoad.ClientID, in HTML input controls "id" is used for client side validation and other java script things, and "name" attribute is used to identify the field when it is post back to server.

These ID's are in a hierarchy, parent control id first and then child control id, like as follows:

<asp:Repeater ID="rptrNames" runat="server">
    <ItemTemplate>
        <asp:textbox runat="server" ID="txtName"></asp:textbox>
    </ItemTemplate>
</asp:Repeater>

The output of text box is like this:
<input name="rptrNames$ctl00$txtName" type="text" id="rptrNames_ctl00_txtName" />

<input name="rptrNames$ctl01$txtName" type="text" id="rptrNames_ctl01_txtName" />

and so on.

Here ctl00 and ctl01 is Id representing each item of the repeater, txtName is the id for control and rptrNames is the id of repeater control.
The hierarchy of controls is like this:
-Repeater control (Parent control)
--Repeater item (Child control of repeater and parent control of text box)
---TextBox inside each repeater item (Child control of repeater item)

Therefore the UniqueID of textbox is "rptrNames$ctl01$txtName" and ClientID of texbox is "rptrNames_ctl01_txtName".

Another thing noticeable here is the separator between the parent and child control ID's, in case of UniqueID it is "$" dollar sign and in case of ClientID it is "_" underscore. This are fixed control id separators and can be accessed using the read-only property ClientIDSeparator() As Char and read-only property IdSeparator() As Char.

The property ClientIDSeparator() As Char will return (_) underscore and the property IdSeparator() As Char will return ($).

Microsoft Windows, Briefcase

2010-11-01T21:43:00.000-07:00

This is a special folder which is used by mobile PC users, this folder provides synchronization feature, file synchronization can be done between briefcase and another folder. The briefcase is infact just a folder, but provides some additional features.

The briefcase folder contains two files one "desktop.ini" and another "Briefcase Database", both these files identify a briefcase folder.

The "desktop.ini" file will look like this:

[.ShellClassInfo]
CLSID={86BAC831-52A0-1069-A2E4-08002B30309D}
RunWizard=0
[Briefcase]
Database=Briefcase Database

Whereas the "Briefcase Database" is a binary file and is used for synchronization process.

Visual Studio 2010, Build error, Build (web): Object reference not set to an instance of an object

2010-05-31T21:56:00.000-07:00

When upgrading a from previous version, Crystal report build provider will cause builder error, in ASP.NET 4 crystal reports are also pre-compiled using build provider for (.rpt), when compiler tries to pre-compile reports build in previous version, a build error "Object reference not set to an instance of an object" is thrown and build fails.

Possible work arround is to remove the build provider for (*.rpt) in your web.config:

There will be following construct under section:

<buildProviders>
<add extension=".rpt" type="CrystalDecisions.Web.Compilation.RptBuildProvider, CrystalDecisions.Web, Version=14.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
</buildProviders>

We can change this to:

<buildProviders>
<add extension=".rpt" type="CrystalDecisions.Web.Compilation.RptBuildProvider, CrystalDecisions.Web, Version=14.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304"/>
<remove extension=".rpt"/>
</buildProviders>

What we have done is removed the build provider for (.rpt), in this way the crystal reports will remain as it is and no build error will show.

Object Oriented Javascript (Part-1)

2010-05-27T23:42:00.000-07:00

We can develop object oriented JavaScript using function method, in JavaScript functions are the main constructs that can be called or referenced. To write object oriented reusable JavaScript libraries we can utilize the similar function which use every day.

JavaScript function:
At this point i assume that we know what a function is, for quick example it is as follows:

<script type="text/javascript">
    //Declare a JavaScript function
    function helloWorld()
    {
        alert('This is my first function.');       
    }
   
    //calling a JavaScript function
    helloWorld();
</script>

JavaScript Class
There is no syntax to declare classes in javascript, but purpose of object class is to encapsulate data and provide some attributes or methods for outer world. This can still be achieved in JavaScript using the same function.
Functions in JavaScript can be instantiated, which means we can create multiple copies of a JavaScript function the same way as we can create instances of a class.

There are different ways to do that, the simplest way is as follows:

<script type="text/javascript">
    //Declare a JavaScript Class
    function Car(car_color)
    {
        //Initialize attributes with default values
        this.Color = car_color;
        this.Model = '';
        this.Make = '';
        //Methods
        this.getModel = function() {
            return this.Model;
        }
        this.setModel = function(car_model) {
            this.Model = car_model;
        }
    }
 
    //Instantiate
    var car1 = new Car('Red');
    var car2 = new Car('Black');
    //Set attributes
    //Car1
    car1.Make = 'Honda';
    car1.setModel('2009');
    //Car2
    car2.Make = 'Ford';
    car2.setModel('2008');
    //Call instance method
    alert(car1.getModel());
    alert(car2.getModel());
</script>

In the above example we can see how two instance of car were created, initialized and accessed in the JavaScript. This simple approach can help in creating reusable code which is encapsulated in a function, this way JavaScript is more manageable and help a lot when you are doing complex scripting.

WampServer 2.0 Port Configuration

2010-03-23T22:24:00.000-07:00

Now that you have WampServer 2.0 installed, you'll want to use it along side with other servers such as IIS. This is easily done by changing the port that WampServer 2.0 listens to. I suggest changing the port from 80 to 81.

Use Notepad or WordPad to open the files below.

Open the httpd.conf file for the Apache server. On my machine, it's located here:

C:\wamp\bin\apache\apache2.2.6\conf\httpd.conf
- Do a search for "Listen 80" and replace it with "Listen 81".
- Save and close the file.

Open the wampmanager.tpl file. On my machine, it's located here:

C:\wamp\wampmanager.tpl
- Do a search for "http://localhost/" and replace it with "http://localhost:81/" (There are three total).
- Do a search for "${w_testPort80}" and replace it with "${w_testPort81}".
- Save and close the file.

Open the testPort.php file. On my machine, it's located here:

C:\wamp\scripts\testPort.php
- Do a search for "80" and replace it with "81" (There are three total).
- Save and close the file.

Open the english.lang file. On my machine, it's located here:

C:\wamp\lang\english.lang
- Do a search for "$w_testPort80 = 'Test Port 80';" and replace it with "$w_testPort81 = 'Test Port 81';".
- Save and close the file.

Right click on the WampServer icon and click on "Exit".

Restart your WampServer.

SQL Server 2005 - Get Size of Databases

2010-03-12T21:47:00.000-08:00

EXEC DATABASE.dbo.sp_spaceused

EXEC DATABASE.dbo.sp_spaceused 'TABLE_NAME'

EXEC DATABASE.dbo.sp_spaceused '?'