Software Bugs and Solutions

Wednesday, 30 January 2013

Nested Datalist in asp.net

This article shows how to implement a nested DataList and also display category and subcategory using a nested DataList. You create two tables, one for category and another for subcategory and also create a stored procedure for selecting category and sub category from both tables in SQL Server. You have to create a web site and add a new page to the website. Drag and drop two DataList controls on the form, one is to bind category and the other is for subcategory. Let's take a look at a practical example. We create tables for category and subcategory.

Creating Table in SQL Server Database

Now create two tables named MajorCategory and MinorCategory. We create CategoryID in MajorCategory table that is the foreign key for the MinorCategory also set the identity property of the CategoryID. The table looks as below.

MajorCategory Table

MinorCategory Table

Creating a Stored Procedure

Now create a stored procedure to select data from both tables.

ALTER PROCEDURE [dbo].[usp_GetProductsForCategories]

SELECT * FROM MajorCategory WHERE CategoryID IN

( SELECT CategoryID FROM MinorCategory )

SELECT p.MinorCategoryId , p.SubCategoryName ,p.CategoryID FROM MinorCategory p

First you have to create a web site.

Go to Visual Studio 2010
New-> Select a website application
Click OK

Now add a new page to the website.

Go to the Solution Explorer
Right Click on the Project name
Select add new item
Add new web page and give it a name
Click OK

Now create a new website and drag and drop two DataList controls onto the aspx page. TheDataList code looks like this.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="categorySubcategory.aspx.cs"

Inherits="categorySubcategory" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<ul>

<asp:DataList ID="outerDataList" runat="server"OnItemDataBound="outerRep_ItemDataBound">

<li>

<asp:Label Font-Size="Large" Font-Bold="true" ID="lblCategoryName"runat="server"

Text='<%# Eval("CategoryName") %>' />

</li>

<ul>

<asp:DataList ID="innerDataList" runat="server">

<asp:HyperLink ID="hlProductName" runat="server" Text='<%#Eval("SubCategoryName")%>' />

</li>

</ItemTemplate>

</asp:DataList>

</ul>

</ItemTemplate>

</asp:DataList>

</ul>

</div>

</form>

</body>

</html>

Now add the following namespaces.

using System.Data.SqlClient;

using System.Data;

Now write the connection string to connect to the database.

string strConnection = "Data Source=.; uid=sa; pwd=wintellect;database=registration;";

Now double-click on the page and write the following code for binding the data with the DataList.

private void BindData()

{

SqlConnection myConnection = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=registration;");

SqlCommand myCommand = new SqlCommand("usp_GetProductsForCategories", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

SqlDataAdapter ad = new SqlDataAdapter(myCommand);

DataSet ds = new DataSet();

ad.Fill(ds);

// Attach the relationship to the dataSet

ds.Relations.Add(new DataRelation("CategoriesRelation", ds.Tables[0].Columns["CategoryID"],

ds.Tables[1].Columns["CategoryID"]));

outerDataList.DataSource = ds.Tables[0];

outerDataList.DataBind();

}

Now write the below code on the OnItemDataBound event of the DataList.

protected void outerRep_ItemDataBound(object sender, DataListItemEventArgs e)

{

if (e.Item.ItemType == ListItemType.Item)

{

DataRowView drv = e.Item.DataItem as DataRowView;

DataList innerDataList = e.Item.FindControl("innerDataList") as DataList;

innerDataList.DataSource = drv.CreateChildView("CategoriesRelation");

innerDataList.DataBind();

}

In Codebehind write the following code like this.

Codebehind

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

public partial class categorySubcategory : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

BindData();

}

private void BindData()

{

SqlConnection myConnection = new SqlConnection("Data Source=.; uid=sa; pwd=wintellect;database=registration;");

SqlCommand myCommand = new SqlCommand("usp_GetProductsForCategories", myConnection);

myCommand.CommandType = CommandType.StoredProcedure;

SqlDataAdapter ad = new SqlDataAdapter(myCommand);

DataSet ds = new DataSet();

ad.Fill(ds);

// Attach the relationship to the dataSet

ds.Relations.Add(new DataRelation("CategoriesRelation", ds.Tables[0].Columns["CategoryID"],

ds.Tables[1].Columns["CategoryID"]));

outerDataList.DataSource = ds.Tables[0];

outerDataList.DataBind();

}

protected void outerRep_ItemDataBound(object sender, DataListItemEventArgs e)

{

if (e.Item.ItemType == ListItemType.Item)

{

DataRowView drv = e.Item.DataItem as DataRowView;

DataList innerDataList = e.Item.FindControl("innerDataList") as DataList;

innerDataList.DataSource = drv.CreateChildView("CategoriesRelation");

innerDataList.DataBind();

}

Now run the application and test it.

The above output displays subcategory according to the category.

Reference :

http://www.c-sharpcorner.com/UploadFile/rohatash/nested-datalist-in-Asp-Net/

Cylinder's Expiry Date

Tuesday, 29 January 2013

sql concatenate string group by

Concatenate many rows into a single text string:

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT

[ID],

STUFF((

SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))

FROM #YourTable

WHERE (ID = Results.ID)

FOR XML PATH (''))

,1,2,'') AS NameValues

FROM #YourTable Results

GROUP BY ID

DROP TABLE #YourTable

Turn textbox into label

1. Change the readonly property of textbox to TRUE

2. Change the back color of textbox to transparent.

3. Change the border of the text box to none.

This will feel TextBox as a Label

Friday, 25 January 2013

Create Linked Server SQL Server 2008

Creating a linked server in SQL Server 2008 is a great way to run CRUD statements against a completely different remote server. This method relies on Windows Authentication.

This is only one way to do it. There are others. And, if you are going to run SQL statements against a linked server from a web app, you need to have the permissions set up properly. From the article “How do I… Query foreign data using SQL Server’s linked servers?” by Susan Harkins:

The biggest catch to all this simplicity is, as always, security. If you’re working with Windows Authentication, the system accommodates well. If the user has the appropriate permissions for the servers and data sources, linked queries will work.

If you have users outside the Windows Authentication environment, you can use a linked login. Create a standard login and assign the appropriate permissions on the remote server.

Setting It Up

1. Go to “Server Objects” of the database server where the linked server will be added and right click on “Linked Servers”. Select “Add New Linked Server”.

2. In the “General” tab, add a name for the new linked server in the “Linked Server” field.

3. Select “Other data source”and for “Provider” select “Microsoft OLE DB for SQL Server”

4. For “Product Name” type in “SQLOLEDB”

5. In the “Data Source” field, enter the IP address of the server to be linked

6. “Catalog” is the name of the database on the linked server and is optional

7. Go to the “Security” tab and select “Be made using this security context”. Type in the remote login and credentials

Running Queries

To query against this server user the syntax:

SELECT * FROM [MY_LINKED_SERVER].[database_name].[dbo].[table_name]

Or, for a join, use linked table with an alias (example):

SELECT * FROM Table
RIGHT OUTER JOIN

[MY_LINKED_SERVER].[database_name].[dbo].[table_name] LinkedTable
ON Table.column = LinkedTable.column

Reference:

http://www.jensbits.com/2010/11/10/create-linked-server-sql-server-2008/

Thursday, 24 January 2013

ContextSwitchDeadlock was detected

ContextSwitchDeadlock was detected
Message: The CLR has been unable to transition from COM context 0x1b7370 to COM context 0x1b74e0 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations.

Solution:

Debug -> Exceptions -> Managed Debug Assistants

untick ContextSwitchDeadlock

Tuesday, 8 January 2013

Compare comma separated values with comma separated values in sql

CREATE FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))

returns @temptable TABLE (items varchar(8000))

begin

declare @idx int

declare @slice varchar(8000)

select @idx = 1

if len(@String)<1 or @String is null return

while @idx!= 0

begin

set @idx = charindex(@Delimiter,@String)

if @idx!=0

set @slice = left(@String,@idx - 1)

else

set @slice = @String

if(len(@slice)>0)

insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)

if len(@String) = 0 break

end

return

end

--function end

--now check below sample

--table variable to hold data

declare @table table

(

id int,[key] varchar(100)

)

insert into @table

select 1,'session, state' union all

select 2,'a,b,c' union all

select 3,'hi, hello'

--parameter value

declare @data varchar(1000)

set @data='b,c'

--select query to find data

select distinct id

from @table

CROSS APPLY dbo.Split(strkeywords,',') AS AA

CROSS APPLY dbo.Split(@data,',') AS BB

WHERE AA.items=BB.items