Problems with using SQL Server instance by Windows remote desktop

'Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.'

This problem occurs when the user uses remote desktop and logs into the system for the first time. If the user logs in at first locally and then uses remote desktop the problem doesn't occur.

Microsoft describe the problem in the article
http://support.microsoft.com/?id=896613
It is enough to request them to send you a link for the fix (windows update) and wait a few hours. The fix cannot be downloaded directly because of need of additional testing. But it works fine for me.

A tip for avoiding null values in aggregate functions in sql

Sometimes the aggreagete functions in SQL returns null value - when the data doesn't meet selection criteria defined in the query.
We can return 0 instead NULL with (ISNULL (column, 0)).

For example:
SELECT
(ISNULL (SUM (salary), 0)) As Total
FROM Salaries
WHERE
UserId=@UserId

Using such statement avoids checking for null values in the code.

How to hibernate Windows.

What is Hibernate? This is a process of copying all RAM content to the hard disk and possibility to continue your work from the last state after shutting down the system.
The required things are:
1. Your motherboard must support this Function
2. Hard disk space at least your RAM size.
3. Enabled Hibernation in Windows
(Display Properties -> Screen Saver -> Monitor Power -> Hibernate -> Enable Hibernation check box)

Button "Hibernation" is shown after clicking Shut Down. If you dont have this button, you can show it by pointing "Stand By" button on the shutdown dialog box and hold Shift button. :-) I dont know why? It seems the newer versions of XP doesn't show Hibernate button.

ASP.NET How to access the EmptyDataTemplate controls from OnRowCommand in GridView

The way of getting access to the controls in EmptyDataTemplate is similar to the way of accessing controls in the same data row in the gridview.

For example - inserting value in the empty gridview:

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
 if (e.CommandName == "Insert") {
  TextBox txt  =
    (((Control)e.CommandSource).NamingContainer).FindControl("txtTest") as TextBox;

    //do insert action
 }
}

Another way to do this is by getting EmptyDataTemplate table and its controls:

if (e.CommandName == "Insert")
{
   Table table = (Table)GridView1.Controls[0];
   TextBox txtTest= (TextBox)table.Rows[0].FindControl("txtTest");

   //do insert actions
}

ASP.NET How to set and get key values for every gridrow in a gridview.

Setting primary key values during data binding:

gvUsersInRoles.DataSource=taUsersInRoles.GetData();
gvUsersInRoles.DataKeyNames = new string[] { "UserId", "RoleId" };
gvUsersInRoles.DataBind();    

This example uses composite primary key for showing more complicated usage.

Getting primary key values during performing row command (command, insert, delete, update):

Guid uid = new Guid(gvUsersInRoles.DataKeys[e.RowIndex]["UserId"].ToString());
int pid = int.Parse(gvUsersInRoles.DataKeys[e.RowIndex]["RoleId"].ToString());

Getting the the newly created primary key after sql insert operation

INSERT INTO Table(COL1) VALUES('213')

SELECT @@IDENTITY

or

Return SCOPE_IDENTITY()

or

Return IDENT_CURRENT('TableName')


SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.

SCOPE_IDENTITY returns values inserted only within the current scope.

@@IDENTITY is not limited to a specific scope. It returns the last identity value after performing all scopes (including triggers).


IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope. In othe words it teturns the last value inserted into 'Tablename'

Using ASP.NET SQL Server Registration Tool (aspnet_regsql.exe) with SQL Express 2005 databases

The aspnet_regsql can be found in
[HDD Drive]:\WINDOWS\Microsoft.NET\Framework\v2.0.50727

The command used for adding asp.net 2.0 membership stuff is

aspnet_regsql -A all
-C "Data Source=.\EXPRESS;Integrated Security=True;User Instance=True"
-d "[database path]"

-d - specifies the database by actual database path

-A all|m|r|p|c|w -

Adds support for one or more ASP.NET application services. Service identifiers can be specified together or separately. The following identifiers are used for ASP.NET application services:

all - All services, including common tables and stored procedures shared by the services

m - Membership

r - Role Manager

p - Profile

c - Web Parts Personalization

w - Web Events

-C - Specifies the connection string to the computer running SQL Server where the database will be installed, or is already installed. This option is not necessary if you specify only the server (-S) and login (-U and -P, or -E) information.

How to set up SMTP Virtual Server with IIS

Exception: Mailbox unavailable. The server response was: 5.7.1

Check the following settings in IIS

IIS -> Default SMTP Server -> Properties -> Access
1. Authentication -> Anonymous access -> Checked
2. Connection control -> Only the list below -> Add current web server ip
Relay restrictions > Relay > - Only the list below -> Add current web server ip

Also add localhost ip here 127.0.0.1 . If DNS lookup doesnt work you can add alias for 127.0.0.1 in your
C:\WINDOWS\system32\drivers\etc\hosts file.

Also it is necessary to have these lines in web.config


<system.net>
<mailsettings>
<smtp deliverymethod="network" from="user@domain.com">
<network host="localhost" port="25" defaultcredentials="true">
</network>
</smtp>
</mailsettings>
</system.net>

How to validate dates in ASP.NET

The simpliest way is by using range validator control and define date bounds

<asp:textbox id="textbox1" runat="server">
</asp:textbox>
<asp:rangevalidator id="valRange" runat="server" controltovalidate="textbox1" 
maximumvalue="31/12/2010" 
minimumvalue="1/1/2000" type="Date" 
errormessage="*" display="dynamic">
*
</asp:rangevalidator>

Some facts about Sessions in ASP.NET

The kind of objects that can be stored in a session variable depends on which mode is used:
InProc Mode- objects stored in session state are actually live objects, and so you can store whatever object you have created.
State Server or SQL Server mode, objects in the session state will be serialized and deserialized when a request is processed. So make sure your objects are serializable and their classes must be marked as so. If not, the session state will not be saved successfully.

To enable sessions in the asp.net site you need to
include a pages attribute in web.config:

<pages enablesessionstate="true">

or enable SessionState in the Page directive in your page :

<%@ Page enableSessionState = "true" %>

Also, the System.Web.SessionStateModule should exist in your
httpModules section in the application configuration.

<httpModules>
<add type="System.Web.SessionState.SessionStateModule" name="Session" />
</httpModules>

Page attributes in ASP.NET for extending System.Web.UI.Page

PageBaseType – Takes a string value defining the base type that will be used for the bage. The default is System.Web.UI.Page. The value gets overridden by the value in the inherits attribute in the standalone file.

userControlbaseType – specifies the base type for controls to be used when the page is standalone.

After implementing classes that extend System.Web.UI.Page or System.Web.UI.UserControl their type can be determined for all pages in the site by their page attributes in web.config. Another way to define the type of the base class is by explicitly defined property CodeFileBaseClass=... in the Page or Control tag (first row in the page).

The using of Page properties (fields) or custom methods by user control is possible by converting Page field to the base page type:

CType(Me.Page, PageBase).Property
CType(Me.Page, PageBase).Method()

Add/remove columns, changing default values in a SQL Server 2005 table

Add new column in a table with checking for existence.
IF NOT EXISTS(SELECT * FROM syscolumns WHERE id=object_id('ColumnName') and name='TableName')
 ALTER TABLE TableName ADD ColumnName bit not null default 0

Changing default values in a SQL Server 2005 table
ALTER TABLE [Table]  ADD CONSTRAINT
    ConstrName  DEFAULT 0 FOR ColumnName