Alter the table to add a new Column

Syntax

GO
ALTER TABLE [InphoDBS].dbo.USER_MASTER ADD [LAST_NAME] [nvarchar](50) NULL;

Note: While altering the table to add a Column the Column does not use NOT NULL property. Only NULL property can be used as we are altering the table.

Using dynamic index number with Select Queries

To include index numbers in select query SQL Server provides “Row_Number()” and “rank()” methods. Following is the example to use the methods.

Example

SELECT Row_Number() OVER (ORDER BY ORD.OrderDesc) as LIST_ORDER ,ORD.OrderDesc
FROM tblOrderDescaster ORD

Using Case Statement in Select Query

Syntax:

(CASE WHEN [COLUMN_NAME] = VALUE THEN VALUE End) COLUMN_NAME
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END


Examples

(CASE WHEN ActiveStatus = 'P' Then 'Premium' When ActiveStatus = 'N' Then 'Non Premium' end)ActiveStatus

Another example

select sno,sname,
(case when total>50
then 'good'
when total>35 and total<50
then 'average'
else
'fail'
end)result

from student_result

Insert "Select" in DropDown List

To insert “Select” at first in the drop down list use the code after the databind option in dropdown list.

ddlusername.DataSource = ds;
ddlusername.DataTextField = "UserName";
ddlusername.DataValueField = "UserID";
ddlusername.DataBind();
ddlusername.Items.Insert(0, "--Select--");

To Insert datas in a table by selecting from another table use the following method

The following example explains how to select and insert data from one table to other.

Insert INTO T_UserTimeDetail (FK_UID_UserID,DTD_TimeIn)
(
Select PK_UID_UserID,'9/1/2008 11:38:21 AM' from dbo.T_UserMaster
)

To Find the Difference between two time intervals use the following code in SQL Server

Use the following code to find the difference between two time intervals.

abs(datediff(hh, DTD_TimeOut , DTD_TimeIn)) as Hours
,abs(datediff(mi, DTD_TimeOut , DTD_TimeIn)%60) as Minutes

Where DTD_TimeOut,DTD_TimeIn are the timein and timeout fields in the database. Hours and Minutes are the alias names to output the Hours and Minutes.(abs is used to return the absolute value because sometimes it will return 3.00)

Passing multiple query string params from datagrid hyperlink column

To pass multiple query string parameters from a datagrid column, create the hyperlink column as in the example below.

<asp:DataGrid id="DataGrid1" AutoGenerateColumns="False" style="Z-INDEX: 101; LEFT: 66px; POSITION: absolute; TOP: 79px" runat="server">
<Columns>
<asp:TemplateColumn HeaderText="Order">
<ItemTemplate>
<asp:Hyperlink runat="server" Text='<%#DataBinder.Eval(Container.DataItem,"Order ID")%>' NavigateUrl='<%# "page.aspx?Orderid=" + DataBinder.Eval(Container.DataItem,"Orderid") + "&ProductID=" + DataBinder.Eval(Container.DataItem,"ProductID")%>' ID="Hyperlink1" NAME="Hyperlink1"/>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>

Clear all the values in the Controls

To reset or clear all the values in the Controls just place the following method in the codebehind file i.e) .cs file and call the method "ResetFormControlValues" to clear data in the controls.

private void ResetFormControlValues(Control parent)
{
foreach (Control c in parent.Controls)
{
if (c.Controls.Count > 0)
{
ResetFormControlValues(c);
}
else
{
switch (c.GetType().ToString())
{
case "System.Web.UI.WebControls.TextBox":
((TextBox)c).Text = "";
break;
case "System.Web.UI.WebControls.CheckBox":
((CheckBox)c).Checked = false;
break;
case "System.Web.UI.WebControls.RadioButton":
((RadioButton)c).Checked = false;
break;
case "System.Web.UI.WebControls.ListBox":
((ListBox)c).SelectedValue = null;
break;
case "System.Web.UI.WebControls.DropDownList":
((DropDownList)c).SelectedValue = null;
break;
}
}
}
}

Get IP address of the Current Machine

TextBox1.Text = Request.UserHostAddress.ToString();

DataGrid Checkbox to Select the Rows

The main intend of this datagrid checkbox column is to select all the rows of the datagrid using a single click. If for example you want to choose all the rows or else if you want to select 8 out of 10 rows, instead of selecting each time a row you can select all the rows by clicking the header checkbox and deselect the two rows by unchecking the checkbox which you might not need to select. Now, to Select the rows in a datagrid using checkbox add a Checkbox column to the datagrid as follows.

<asp:DataGrid ID="dgrdcheck" DataKeyField="UserID" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateColumn>
<HeaderTemplate>
<asp:CheckBox ID="chkAll" runat="server" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chkdatas" runat="server" />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="UserID">
<HeaderStyle />
<ItemStyle />
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem,"UserID") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Username">
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem,"UserName") %>
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Password">
<ItemTemplate>
<%#DataBinder.Eval(Container.DataItem,"Password") %>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>

So now, in the above example the first column of the datagrid is checkbox column with a header checkbox.

Note:- The above code should be placed in .aspx i.e) the source file.

Place the following javascript to the same (.aspx) source file.


<script type="text/javascript">

var dgrdtest = document.getElementById('<%=dgrdcheck.ClientID %>');

function CheckAll(event)
{
var dgrdRows = dgrdtest.childNodes(0).childNodes;
var srcControl = event.srcElement || event.target;
for(index = 1; index < dgrdRows.length; index++)
{
try
{
if(srcControl.checked)
{
dgrdRows(index).childNodes(0).childNodes(0).checked=true;
}
else
{
dgrdRows(index).childNodes(0).childNodes(0).checked=false;
}
}
catch(e)
{
}
}
}

</script>


Add the following event to the .cs file i.e) the code behind file for the header checkbox to function properly.

protected void dgrdcheck_ItemCreated(object sender, DataGridItemEventArgs e)
{
switch (e.Item.ItemType)
{
case ListItemType.Header:
TableCell objcell = e.Item.Cells[0];
CheckBox objcheck = (CheckBox)objcell.Controls[1];
objcheck.Attributes.Add("onClick", "CheckAll(event)");
break;
}
}

Note:- You have to place the below Event Handler code in Page_Load or Page_Init method for the event "dgrdcheck_ItemCreated" to get fired.

this.dgrdcheck.ItemCreated +=new DataGridItemEventHandler(this.dgrdcheck_ItemCreated);


For Example:

protected void Page_Init(object sender, EventArgs e)
{
this.dgrdcheck.ItemCreated +=new DataGridItemEventHandler(this.dgrdcheck_ItemCreated);

}

Convert date in dd/mm/yyyy format to mm/dd/yyyy format

DateTime date = DateTime.Now;
string strdate = date.ToString("MM/dd/yyyy"); // or "dd/MM/yyyy"

How to Pass null value to an integer Variable?

Hello friends i had came across a situation where i have to pass null value to a integer variable. Below is the solution i found to pass null value to integer variable.

int? id = null;

Remove Unwanted strings in a Column while inserting to another table using Select Query

INSERT INTO dbo.EMPLOYEE_MASTER
(
EMPLOYEE_STATE
)
SELECT DISTINCT REPLACE(EMPLOYEE_DETAILS.STATE,'"','') STATE FROM dbo.EMPLOYEE_DETAILS

Syntax for adding Rows in DataTable

DataRow row = dt.NewRow();
row["NAME"] = objInfo.Name;
dt.Rows.Add(row);