How to get columns value separated by comma in MS-SQL?

There are many scenario come to show columns value separated by comma.

This can be achieved by FOR XML PATH with STUFF function.

Now you have tabled like below.

 

Now you want to fetch the records from table to show student name , corresponding subject which is separated by comma.

SQL-Query:-

 Select distinct s1.studentname,

 STUFF((select distinct ','+s2.[subject]

 from tbl_student s2 where s1.studentname=s2.studentname

 for xml Path(''),Type).value('.','varchar(max)'),1,1,'')subject

 from tbl_student s1

OutPut:-

How to auto refresh of web user control in web page in asp.net ?

By using Timer control , you can auto refresh a user control inside a page in asp.net. You can use timer control along with update panel control to auto refresh a user control.

Let’s see a simple example . I have created a user control and I keep one label, one timer and update panel control. Put label control inside update panel control. Keep timer control outside of update panel control.

UserControl.ascx:-

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="WebUserControl1.ascx.cs" Inherits="WebApplicationTest.UserControl1" %>

<asp:ScriptManager ID="ScriptManager1" runat="server" />

        <asp:Timer runat="server" id="UpdateTimer" interval="5000" ontick="UpdateTimer_Tick" />

        <asp:UpdatePanel runat="server" id="TimedPanel" updatemode="Conditional">

            <Triggers>

                <asp:AsyncPostBackTrigger controlid="UpdateTimer" eventname="Tick" />

            </Triggers>

            <ContentTemplate>

                Time On UserControl :

                <asp:Label runat="server" id="DateLabel" />

            </ContentTemplate>

        </asp:UpdatePanel>

UserControl.ascx.cs:-

protected void UpdateTimer_Tick(object sender, EventArgs e)

        {

            DateLabel.Text = DateTime.Now.ToString();

        }

Now create one default.aspx page and drag and drop usercontrol.ascx control on default.aspx page.

Default.aspx:-

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplicationTest.Default" %>

<%@ Register src="UserControl1.ascx" tagname="UserControl1" tagprefix="uc1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        Time on Default Page:<asp:Label runat="server" id="Label1" />

   

    </div>

        <uc1:WebUserControl1 ID="UserControl11" runat="server" />

    </form>

</body>

</html>

Default.aspx.cs:-

protected void Page_Load(object sender, EventArgs e)

        {

            Label1.Text = DateTime.Now.ToString();

        }

This is very simple. In Above code Timer is used, which will Tick every 5 second. So every 5 seconds it will show the updated time on user control.

Difference between PATINDEX and CHARINDEX in SQL SERVER 2008R2?

CHARINDEX :-  It is used to find a start_position of a specified text in the expression. It accepts two parameters.

Syantax:-

Select CHARINDEX(string_expression,target_expression)

Example:- 

Select CHARINDEX('e','abcdeavb')

In the above we try to find postion of ‘e’ in ‘abcdeavb’ expression.

PATINDEX:- It is also used to find start_position of specified text in the expression. It accepts two parameters. In PATINDEX we can use wildcard character like ‘%’ and ‘_’ . 

Syntax:-

Select PATINDEX(search_pattern_literal,expression)

Example:-

Select PATINDEX('%ab%','sadabdfkjafbksdfk')

Output:-

4

Select PATINDEX('%a_b%','sadabdfkjafbksdfk')

Output:-

10