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

DropDownList in GridView

It is necessary to convert bound field to a template field, then set your DropDownList up. In the example it is bound to an ObjectDataSource. It is possible to use statement like (SelectedValue=<%# Bind("Field") %>), but this way is good only for read-only gridview.
Any postback can throw an exception connected to the Bind/Eval way of binding -
'Databinding methods such as Eval(), XPath(), and Bind() can only be used in the context of a databound control'.

The better way is by using customized code during processing the ItemUpdating event:

<asp:TemplateField HeaderText="Header text">
  <ItemTemplate>
    <asp:DropDownList ID="ddlItem"
        runat="server" OnDataBound="ddlItems_DataBound"
        DataSourceID="ObjectDataSource1"    AppendDataBoundItems="false"
        DataTextField="Name" DataValueField="Id" >
    </asp:DropDownList>
  </ItemTemplate>
</asp:TemplateField>

protected void ddlItems_DataBound(object sender, EventArgs e)
{
  DropDownList ddl = (DropDownList)sender;
  GridViewRow gvr = (GridViewRow)ddl.NamingContainer;
  if (gvr.DataItem != null)
  {
      string id = ((DataRowView)gvr.DataItem)["Id"].ToString();
      ddl.ClearSelection();
      ListItem li = ddl.Items.FindByValue(id);
      if (li != null) li.Selected = true;
  }     
}

It is important to use cast of sender, not direct calling to the GridView1 control. Every GridViewRow is actually DetailsView object.

protected void GridView1_RowUpdating
 (object sender, GridViewUpdateEventArgs e)
{     
 DropDownList ddl =
   (DropDownList)(((GridView)sender).Rows[e.RowIndex].FindControl("ddlItems"));
 e.NewValues["Id"] = ddl.SelectedValue;
}

Confirmation dialog to a GridView delete option

Actually it is not possible to attach client confirmation to the delete command filed. But it is pretty easy to do this by template field. The only think you have to do is to set command name to be "Delete" (CommandName="Delete"). GridView event handlers are responsible for the other things of delete process.

<asp:TemplateField>  
         <ItemTemplate>
         <asp:LinkButton ID="delete1"
  runat="server"
  OnClientClick=
  "return confirm('Are you sure you want to delete this record?');"
         CommandName="Delete">Delete
         </asp:LinkButton>
         </ItemTemplate>
</asp:TemplateField>

Passing data between two pages with Cross-page Postback and Server.Transfer

The ASP.NET control, that cause postback, post back to the page for processing. That page can be current page or another page. Every control that cause postback can be configured to post to a different page - this postback is called cross postback and is often used for getting information from the pages in the multi page form, especially convenient for creating wizards.

<asp:Button
ID="Button1"
PostBackUrl="~/TargetPage.aspx"
runat="server"
Text="Submit" />

Cross-page post back is similar to Server.Transfer method, but there are some imporrtant differences. Server.Transfer conserves the post data between pages and also it keeps the url. And also it has a documented bug with getting url parameters from the source page. The Server.Transfer method is a server-based operation whereas cross-page postback is a client-based transfer. The main aim of the Server.Transfre method is to reduce client request to the server which makes it faster than Response.Redirect().
Anyway the cross-page postaback can throw the user on another page but with possibility to get the post data.

if (Page.PreviousPage != null)
{
TextBox SourceTextBox =(TextBox)Page.PreviousPage.FindControl("TextBox1");
if (SourceTextBox != null) { Label1.Text = SourceTextBox.Text;
}
}

there is also possibility to get public properties from the source page with decalring the source:

<%@ PreviousPageType VirtualPath="~/SourcePage.aspx" %>

getting the whole source page:

SourcePage_aspx sourcePage;
sourcePage = (SourcePage_aspx) PreviousPage;
Label1.Text = sourcePage.CurrentCity;

With PreviousPage.IsCrossPagePostBack we can determine whether the page was invoked from a cross-page posting.

Resources:
http://msdn2.microsoft.com/en-us/library/ms178139.aspx
http://www.developer.com/net/asp/article.php/3299641
http://www.codeproject.com/useritems/CrossPagePosting.asp

Script Callbacks in ASP.NET

I am sure that every web developers hates page refresh. And everyone chases the way to make its page more interactive without postback. The best way is client side scripting, but it is not enough to generate all page activity. Javascript based technologies like callbacks and AJAX are at the bottom of the effcicient developer's code.
Here are some good posts for callback technology in ASP.NET 2.0.

http://msdn.microsoft.com/msdnmag/issues/04/08/cuttingedge/

http://www.developer.com/net/asp/article.php/3485991

http://www.mredkj.com/vbnet/scriptCallback.html

At the core, ASP.NET script callbacks consist of some client-side JavaScript code needed for asynchronous communication to the server. The first requirement for using the callbacks is that some server-side code awaits client calls. The client-side callback intermediary manages the call while the user continues to see the old page and interact with it.

Getting fixed number of rows with SQL Select query

The syntax for SQL Server:
SELECT TOP 10 id, name email
FROM person

The syntax for MySQL:
SELECT id, name email
FROM person
LIMIT 10

These examples get the first (top) 10 rows in the query.

I will describe also the paging technology of the asp.net.

Using DataTable (DataView) Filter expressions, Sorting

In some cases it is necessary to be made some calculations or data filtering and sorting of local data - already received from a database. These things can be made with the DataTable or DataView classes in a way similar to SQL Select queries.
When we have a filled DataTable we can easy retrieve only the rows we need with DataView.RowFilter. This property gets or sets the expression used to filter which rows are viewed in the DataView (often used for DataSourse in data components).

DataTable dt= ......... //filling the data table
DataView dv= dt.DefaultView;
dv.RowFilter="AreaId="+searchId+" AND SubAreaName LIKE 's*'; //getting all records for searched area whic name starts with 's'
dv.Sort="Name desc, number asc"; //sorting data

dv.RowFilter = "Name LIKE 's*'" // values that start with "s"'
dv.RowFilter = "Name LIKE '%s%'" // values that contain 's'
dv.RowFilter = "Name NOT LIKE 's*'" // values that don't start with 's'
dv.RowFilter = "Name <> 'MyName'" // values different than 'MyName'
dv.RowFilter = "Name IN ('MyName','MySecondName')" // value equal to 'MyName' or 'MySecondName'


If the pattern string in a LIKE statement contains special characters like * % [ ], they must be escaped in brackets [ ] in this way [*], [%], [[] or []].

RowFilter is an expression string that can be evaluated to true or false, and uses the syntax described in the DataColumn.Expression documentation.

It is very useful to use also aggregate functions for calculation. For example

dt.Columns("ItemCount").Expression = "Count(ItemID)"

dt.Columns("ItemCount").Expression = "FirstName = 'John'"   

dt.Columns("ItemCount").Expression = "Date < #1/31/82#" 

dt.Columns("ItemCount").Expression = (LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John' 

Here is the MSDN reference for the filter expressions:
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.71).aspx

SQL Server Express Edition attaching and using problems

"Cannot open user default database. Login failed.
Login failed for user ......"

This problem occurs very often while the developer uses Visual Studio and testing in a browser at the same time. The reason is the already opened connection to the database in the VS environment. For example every time when the developer edit DataSet connected to current database this connection stays opened. The solve this problem the connection to the db has to be closed by the right mouse button -> Close Connection in the Server Explorer tab.

Another problem with attaching database and using its connection is:
"An attempt to attach an auto-named database for file ..... failed. A database with the same name exists, or specified file cannot be Opened, or it is located on UNC share."

1 . The first thing that has to be done is to try to attach the database manually with Microsoft SQL Server Management Studio.

2. The data folder permissions have to be set to "Modify" for ASPNET account and NETWORK SERVICE for windows server 2003

3. Make ASPNET user a member of SQLServer2005MSSQLUser$server$SQLExpress group

4. Add new Login to the SQL Express server for ASPNET (NETWORK SEVICE) user

5. In the connection string User Instance=True has to be checked. It works only if SQL Server uses Windows Authentication mode.
Changing the authetication mode can be done by SQL Server Management Studio with right mouse click on the computer name and Properties -> Security.
When Integrated Security=SSPI in the connection string is used make sure you are using in system.web sections of the web.config and also make sure that a windows account for the "Anonymous User" is selected in the access section in IIS.

6. Also the sql settings in c:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\ can be deleted and the sql service restarted.

ASP.NET Request Validation has detected a potentially dangerous client input value

This exception occurs when the server meet content containing un-encoded HTML. This is action for preventing script atacks, for example <script> alert("blabla")</script> can be executed. In this way other harmful code can be executed. The request validation feature of ASP.NET doesn't allow this to be done.
To disable request validation on a page you must set the validateRequest attribute of the Page directive to false. The developer has to ensure that the content is properly encoded.
One more way to disable it is by web.config. You have to set
<configuration>
<system.web>
<pages validaterequest="false">
</pages>
</system.web>
</configuration>

The HTML content can be encoded/decoded using the Server.HtmlEncode(string)/Server.HtmlDecode(string) functions.