GridviewTips

LINK BUTTON IN GRIDVIEW:-
----------------------------------------------------------------------------------

<ItemTemplate>
<asp:LinkButton ID="lnkview" runat="server" CommandName="viewProject"
CommandArgument='<%#Eval("ID") %>'
CssClass="tahomatext"><img src="../images/icon_details.png" style="border:0;" alt="Bidding Details" title="Bidding Details" /></asp:LinkButton>
</ItemTemplate>
--------------------------------------

protected void grdProject_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.ToString() == "viewProject")
{
uid = Convert.ToInt32(e.CommandArgument.ToString());
Response.Redirect("BidDetails.aspx?pid=" + uid);
}
}

-------------------------------------------------------------------
ANCHOR TAG IN GRIDVIEW:-

<ItemTemplate>
<a href="addFeedBackLister.aspx?pid=<%#Eval("ID") %>&cid=<%#Eval("ContractorID") %>"
title="Give feedback" class='<%#visibleBtn(Convert.ToInt64(Eval("ID")))%>'>
<img src="../images/reviewadd.png" height="25" width="25" border="0px" /></a>
</ItemTemplate>

-----------------------------------------------------------------
GRIDVIEW PAGER FORMATTING
protected void grdBidHistory_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Pager)
{
TableCell tc = new TableCell();
TableCell tc1 = new TableCell();

tc.Text = "[Page:";
tc1.Text = "]";
Table tbl = new Table();
tbl = (Table)(e.Row.Cells[0].Controls[0]);
tbl.Rows[0].Cells.AddAt(grdBidHistory.PageIndex, tc);
tbl.Rows[0].Cells.AddAt(grdBidHistory.PageIndex + 2, tc1);
}

}

---------------------------------------------------------------------
NULL VALUE RECORD CHECK FOR GRIDVIEW




My Table in database look like as shown in image, some columns contains NULL values and i'll be showing "No Records Found" instead of NULL values in GridView.

To achieve this i've written a Method in code behind and will be calling this method in ItemTemplate of GridView.


call this server side method CheckNull written in code behind from ItemTemplate of GridView which check the NULL values and change it as we want.
C# code for the CheckNull method
protected string CheckNull(object objGrid)
{
if (object.ReferenceEquals(objGrid, DBNull.Value))
{
return "No Record Found";
}
else
{
return objGrid.ToString();
}
}

To create mailTo link

<td align="left" class="textgridtext">
<asp:HyperLink ID="lnkMail" runat="server"></asp:HyperLink>
</td>

lnkMail.Text = dt.Rows[0]["Email"].ToString();
lnkMail.NavigateUrl = "Mailto:" + dt.Rows[0]["Email"].ToString();

IntArrayToCommaString Function

public static string IntArrayToCommaString(int[] intArray)
{
string s = "";
foreach (int i in intArray)
{
s += intArray[i].ToString() + ",";
}
return s.TrimEnd(',');
}

Convert commaStringToIntArray Function

String strCategory = Convert.ToString(dt.Rows[0][&quot;ProjectCategoriesAlert&quot;]);

int[] category = clsBuilder.commaStringToIntArray(strCategory);

for (int i = 0; i &lt; category.Length; i++)
{
cbl_Categories.Items[category[i] - 1].Selected = true;

}


----------------------------------------------------------

public static int[] commaStringToIntArray(string str)
{
String[] strArray = str.Split(',');
int[] intArray = new int[strArray.Length];
for (int i = 0; i &lt; strArray.Length; i++)
{
intArray[i] = int.Parse(strArray[i]);
}
return intArray;
}

-----------------------------------------------------------------------
string str = string.Empty;
for (int i = 0; i &lt; cbl_Categories.Items.Count; i++)
{
if (cbl_Categories.Items[i].Selected == true)
{
str += cbl_Categories.Items[i].Value + &quot;,&quot;;
}

}

Email Already Exists(Ajax)

<td align="left" valign="top">

<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:TextBox ID="txtEmail" AutoPostBack="true" CssClass="textbox_reg"
OnTextChanged="txtEmail_TextChanged" MaxLength="50" runat="server"></asp:TextBox>

<div id="UserAvailability" runat="server" class="messages"></div>
</ContentTemplate>
</asp:UpdatePanel>
<asp:RequiredFieldValidator ID="rfv_email" ValidationGroup="submit" runat="server"
ErrorMessage="Please enter the Email."
ControlToValidate="txtEmail" Display="Dynamic"
Font-Size="X-Small" SetFocusOnError="True">*</asp:RequiredFieldValidator>

<cc1:ValidatorCalloutExtender TargetControlID="rfv_email" ID="VCE_email" runat="server">
</cc1:ValidatorCalloutExtender>

<asp:RegularExpressionValidator ID="rev_email" ValidationGroup="submit" runat="server"
ErrorMessage="Email not in proper format.Please enter again." ControlToValidate="txtEmail"
Display="Dynamic" Font-Size="X-Small" SetFocusOnError="True" ValidationExpression="\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*">*</asp:RegularExpressionValidator>
<cc1:ValidatorCalloutExtender TargetControlID="rev_email" ID="VCE_emailFormat" runat="server"></cc1:ValidatorCalloutExtender>

</td>





protected void txtEmail_TextChanged(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string sql = "select Email From tbl_user Where Email='" + txtEmail.Text.Trim() + "'";
dt = NSBuilder.DataAccess.GetDataSetwitrhcommand(sql).Tables[0];
if (dt.Rows.Count > 0)
{
UserAvailability.InnerText = "Email already taken,Please change!!";

}
else
{
UserAvailability.InnerText = "";
}
}

Checkbox validation

<script type="text/javascript" language="javascript">
function ValidateCheckBox(src, args) {
if (document.getElementById('<%=chk_Terms.ClientID %>').checked == false) {
alert("Please check Terms & Conditions before continuing.");
//return false;

args.IsValid = false;
}
}

------------------------------------------------

<asp:CheckBox ID="chk_Terms" runat="server" CssClass="arial11normalblack" Text="I agree to the Terms & Cconditions" />

<asp:CustomValidator ValidationGroup="submit" ID="cv_Checkbox" runat="server" ClientValidationFunction="ValidateCheckBox"
Display="None" SetFocusOnError="True">*</asp:CustomValidator>

At least n Characters required

-------------------------------------------------------------------

Last Name: <ASP:TEXTBOX id=TxtLastName runat="server"></ASP:TEXTBOX><ASP:REGULAREXPRESSIONVALIDATOR id=REVLastName runat="server" controltovalidate="TxtLastName" errormessage="please enter at least 3 charaters"

validationexpression="[0-9a-zA-Z]{3,}"></ASP:REGULAREXPRESSIONVALIDATOR>
� Here we used RegularExpressionValidator to validate that user enter at least 3 characters in the Last name TextBox by add this regular expression [0-9a-zA-Z]{3,}.

Javascript validation on multiline texbox for character count

<script language="javascript">
function validateLimit(obj, divID, maxchar) {

objDiv = get_object(divID);

if (this.id) obj = this;

var remaningChar = maxchar - trimEnter(obj.value).length;

if (objDiv.id) {
objDiv.innerHTML = remaningChar + " characters left";
}
if (remaningChar <= 0) {
obj.value = obj.value.substring(maxchar, 0);
if (objDiv.id) {
objDiv.innerHTML = "0 characters left";
}
return false;
}
else
{ return true; }
}

function get_object(id) {
var object = null;
if (document.layers) {
object = document.layers[id];
} else if (document.all) {
object = document.all[id];
} else if (document.getElementById) {
object = document.getElementById(id);
}
return object;
}
function trimEnter(dataStr) {
return dataStr.replace(/(\r\n|\r|\n)/g, "");
}

</script>


-------------------------
<asp:TextBox ID="txtprojectdesc"
MaxLength="240" CssClass="textbox_reg" TextMode="MultiLine"
Rows="5" Width="200px"

ToolTip="Summary:(240 characters)"

onkeyup="return validateLimit(this, 'lblMsg1',240)"

runat="server">
</asp:TextBox><br />
<div id="lblMsg1" class="messagetip">240 characters left</div>

JQuery validation on multiline textbox for character count

-------------------------------------------------------------------
JQuery validation on multiline textbox for character count
-------------------------------------------------------------------

<script src="../scripts/jquery-1.4.2.js" type="text/javascript"></script>

<script language="javascript">

$(document).ready(function() {
//Set the Variable for max length
var MaxLength = 50;

$('#<%=txtCount.ClientID%>').val(MaxLength);
$('#<%=txtprojectdesc.ClientID%>').keyup(function() {
if ($(this).text().length > MaxLength) {
$(this).text($(this).text().substring(0, MaxLength));
}
$('#<%=txtCount.ClientID%>').val(MaxLength - $(this).text().length);
});
$('#<%=txtprojectdesc.ClientID%>').blur(function() {
if ($(this).text().length > MaxLength) {
$(this).text($(this).text().substring(0, MaxLength));
}
$('#<%=txtCount.ClientID%>').val(MaxLength - $(this).text().length);
});
});
</script>
--------------------------------------
<asp:TextBox ID="txtprojectdesc" CssClass="textbox_reg" TextMode="MultiLine" Rows="5" Width="200px" runat="server"></asp:TextBox><br />

<asp:TextBox ID="txtCount" MaxLength="3" ReadOnly="true" BorderWidth="0" CssClass="textbox_reg" Width="20px" Font-Names="verdana" Font-Size="XX-Small"
ForeColor="#ad1800" runat="server"></asp:TextBox><span class="helptext">Characters left.</span>

To post HTML/Java Code in your blog posts

To post HTML/Java Code in your blog posts ... use this link...

http://centricle.com/tools/html-entities/

After converting the code , paste it into your blog between <blockquote> tags...
for eg.
<blockquote>

-- your code from http://centricle.com/tools/html-entities/

</blockquote>


<a href="http://bloggerstop.net/2008/10/blogger-help-how-to-show-html-text-in.html">Please see here to remove </em> tag and to add Blockquote tags</a>

-------------------------------------

Removing the blogger navigation bar from a template

--------------------------------------------
Removing the blogger navigation bar from a template


Go to your blogger dashboard> layout> edit HTML. Search this code,

]]>
Now add this code above the code,

#navbar-iframe {height:0px;visibility:hidden;display:none}


With this trick, you can remove the navbar from any blogger template


----------------------

To control excess data to display in gridview cell

------------------------------------------------------------
To control excess data to display in gridview cell
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) {
if (e.Row.RowType == DataControlRowType.DataRow)
{
ViewState["Data"] = e.Row.Cells[1].Text;
if (e.Row.Cells[1].Text.Length > 25)
{
e.Row.Cells[1].Text = e.Row.Cells[1].Text.Substring(0, 25) + “…”;
e.Row.Cells[1].ToolTip = ViewState["Data"].ToString();
}



-----------------------

Change row color in Gridview on mouse over

----------------------------------------------------------------
Change row color in Gridview on mouse over
If you have the need to change the row color of a row in a gridview when you mouse over (and mouse out) you can add some code to the RowDataBound event.
Here is a sample:
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
//Only apply to data rows
if (e.Row.RowType == DataControlRowType.DataRow)
{
//Add mouse over event to change row background color
e.Row.Attributes.Add("onmouseover", "this.style.backgroundColor=’Yellow’");
//Add mouse out event to change row background color back to white
e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=White");
}
}



-----------------------

GridView with clickable rows

-------------------------------------------------------------------
GridView with clickable rows
If you need to have a GridView with rows that you can click on you will need to set the onClick event for each row.
Here is a code sample for the RowDataBound event of a GridView.
This examples pulls the value of cell 0 and appends it to the url.
protected void grd_RowDataBound(object sender, GridViewRowEventArgs e)
{
GridView grid = (GridView)sender as GridView;
//Only format data rows
if (e.Row.RowType == DataControlRowType.DataRow)
{
string urlLink = “Link?number=” + e.Row.Cells[0].Text.ToString();
e.Row.Attributes["onClick"] = "location.href=’" + urlLink + "’";
}
}

-----------------------

Gridview Pager Formatting

-------------------------------------------------------------

protected void grdBidHistory_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.Pager)
{
TableCell tc = new TableCell();
TableCell tc1 = new TableCell();

tc.Text = "[Page:";
tc1.Text = "]";
Table tbl = new Table();
tbl = (Table)(e.Row.Cells[0].Controls[0]);
tbl.Rows[0].Cells.AddAt(grdBidHistory.PageIndex, tc);
tbl.Rows[0].Cells.AddAt(grdBidHistory.PageIndex + 2, tc1);
}

}





-----------------------

Null value record check for gridview

My Table in database look like as shown in image, some columns contains NULL values and i'll be showing "No Records Found" instead of NULL values in GridView.

To achieve this i've written a Method in code behind and will be calling this method in ItemTemplate of GridView.


call this server side method CheckNull written in code behind from ItemTemplate of GridView which check the NULL values and change it as we want.
C# code for the CheckNull method
protected string CheckNull(object objGrid)
{
if (object.ReferenceEquals(objGrid, DBNull.Value))
{
return "No Record Found";
}
else
{
return objGrid.ToString();
}
}

Format Gridview Cell Data with OnRowCreated function

---------------------------------------------------------------------


Format Gridview Cell Data with OnRowCreated function

if (e.Row.RowType == DataControlRowType.DataRow)
{
Comments = (String)DataBinder.Eval(e.Row.DataItem, "Comment");
if (Comments.Length > 30)
{
Comments = Comments.Substring(0, 40) +"...";
e.Row.ForeColor = System.Drawing.Color.Crimson;
e.Row.Font.Italic = true;
}
}


----------------------------------------------------------------------

caseSyntax

------------------------------------------
CASE WHEN EXISTS(select doc.id from tbl_Document doc where doc.ProjectID=prj.ID)
THEN 'Yes'
ELSE
'No'
END AS ATTACHED_FILES




-------------------------------------------
SELECT
ISNULL('$ ' + CONVERT(varchar(100), tbl_Contractor.Balance) + ' USD', '$ 0 USD ') as Balance
FROM tbl_Contractor where tbl_Contractor.UserID=@cid

--------------------------------------------

ISNULL('$ '+CONVERT(varchar(100),tbl_Bid.BidAmount)+' USD','N/A')
as BidAmount

--------------------------------------------

isnull(convert(varchar(100),tbl_Bid.DeliveryTime)+' Day(s)','N/A') as DeliveryTime

--------------------------------------------

TableToString_ListToTable_Use2

ALTER PROCEDURE [dbo].[Usp_GetAllProjects_ByCategory]
(
@categoryid bigint
)

AS -- [Usp_GetAllProjects_ByCategory] '34'

BEGIN

SELECT tbl_Project.ID,tbl_User.UserName,
CASE
WHEN LEN(tbl_Project.ProjectTitle) > 150
THEN SUBSTRING(tbl_Project.ProjectTitle,0,150) + '...'
ELSE
CASE
WHEN LEN(tbl_Project.ProjectTitle) < 150
THEN tbl_Project.ProjectTitle
ELSE
tbl_Project. ProjectTitle
END
END
AS ProjectTitle,

CONVERT(varchar(50), tbl_Project.DateCreated,106) as DateCreated,

dbo.TableToString(tbl_Project.CategoryID) as Category,

(select count(*) from tbl_Bid where tbl_Bid.ProjectID = tbl_Project.ID ) as Bids

FROM tbl_Project inner join tbl_User
on tbl_Project.UserID = tbl_User.UserID
where tbl_Project.IsActive=1 and tbl_Project.ProjectStatus='Open'

AND
EXISTS (select entry from ListToTable(tbl_Project.CategoryID)where entry=@categoryid)
ORDER BY ID DESC

END

IF @@ERROR <> 0
RETURN -1
ELSE
RETURN 1

TableToString_ListToTable_Use1

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[Usp_GetAllProjectsByLister]
(
@email VARCHAR(50)
) -- [Usp_GetAllProjectsByLister] 'a@a.com'
AS
DECLARE @userid bigint
SET @userid = (Select UserID from tbl_User where Email=@email)
BEGIN

SELECT ID, ProjectTitle,Description,CONVERT(varchar(50),DateCreated,106) as DateCreated,

dbo.TableToString(tbl_Project.CategoryID) as Category,

tbl_project.DaysOpenForBids,

(select count(*) from tbl_Bid where tbl_Bid.ProjectID = tbl_Project.ID ) as Bids,tbl_Project.ProjectStatus,

CASE
WHEN 0 < DATEDIFF(DAY,GETDATE(),
DateAdd(Day,tbl_project.DaysOpenForBids,tbl_Project.DateCreated))
THEN
CONVERT(varchar,DATEDIFF(DAY,GETDATE() ,
DateAdd(Day,tbl_project.DaysOpenForBids,tbl_Project.DateCreated))) + ' Day(s)'
ELSE
CASE
WHEN 0=DATEDIFF(DAY,GETDATE() ,
DateAdd(Day,tbl_project.DaysOpenForBids,tbl_Project.DateCreated))
THEN
'Today'
ELSE
'0 Day(s)'
END
END
AS DaysOpen
FROM tbl_Project
WHERE UserID=@userid
ORDER BY ID DESC
END
IF @@ERROR <> 0
RETURN -1
ELSE
RETURN 1

TableToString_ListToTable_Use

ALTER procedure [dbo].[Usp_ProfessionalEmails_ProjectActivated]
(
@pid bigint
) -- Usp_ProfessionalEmails_ProjectActivated '21'
AS


declare @ptitle varchar(500)
set @ptitle=(select tbl_Project.ProjectTitle from tbl_Project where tbl_Project.ID=@pid)

declare @listername varchar(100)
set @listername=(select tbl_User.UserName from tbl_User inner join tbl_Project on tbl_User.UserID=tbl_Project.UserID and tbl_Project.ID=@pid )

select @ptitle as ProjectTitle,@listername as ListerName

select tbl_Contractor.ID,tbl_User.UserName,tbl_User.Email from tbl_Contractor inner join tbl_User on tbl_Contractor.UserID=tbl_User.UserID

where exists
(
select entry from dbo.ListToTable
(
(select tbl_Project.CategoryID from tbl_Project where tbl_Project.ID=@pid)
)
where entry in (select e.entry from ListToTable(ProjectCategoriesAlert) e)
)

-----------------------------------------------------------
-----------------------------------------------------------

List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database

-----------------------------------------------------------------------------
List down the DataType and Size Of all Columns of All Tables in a SQL Server 2005/2008 Database

--------------------------------------------------------
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[UTILfn_Split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[UTILfn_Split]
GO

create function dbo.UTILfn_Split(
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)

--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT
--if (@CommaCheck <> @Delimiter )
set @String = @String + @Delimiter

--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1

--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
set @NextString = substring(@String,1,@Pos - 1)

insert into @ValueTable ( [Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end

return
end
--------------------------------------------
/*
Util_ListDBSchema N'MyDatabase', N'tbl_Class,MyTempTable1' –several tables
Util_ListDBSchema N'MyDatabase', N'*' –-all tables
Util_ListDBSchema N'MyDatabase', N'tbl_movies' –one table
*/
---------------------------------------------
CREATE PROCEDURE dbo.Util_ListDBSchema
(

@dbName varchar(100),
@tableName varchar(500)
)
AS -- Util_ListDBSchema 'ServiceMatchMaker','*'
BEGIN

SET NOCOUNT ON;

DECLARE @strSql varchar(2500), @strWhere varchar(1000)

--list tables only

SET @strWhere = 'objectproperty(object_id(table_name),''IsTable'') = 1'



IF (@tableName <> '*')

SET @strWhere = @strWhere + 'and

table_name in (SELECT * from dbo.UTILfn_Split ('''+@tableName+''','',''))'



SET @strSQL = N'

USE ['+ @dbName +']



SELECT

table_catalog, table_schema, table_name, column_name,

data_type, character_maximum_length as max_length

FROM information_schema.columns

WHERE '+@strWhere+' ORDER BY table_name, column_name'


-- print @strSQL

exec (@strSQL)


END
GO

SQL_Utility_StoredProcs

TO FIND STORED PROCEDURE CREATED WITHIN CERTAIN TIME RANGE
------------------------------------------------------------------
SELECT name
FROM sys.objects
WHERE type = 'P'
--AND DATEDIFF(D,create_date, GETDATE()) < 2
AND DATEDIFF(D,modify_date, GETDATE()) < 2
-------------------------------------------------------------------

TO FIND TABLES CREATED/MODIFIED WITHIN CERTAIN TIME RANGE

SELECT [name],create_date,modify_date FROM sys.Tables
WHERE modify_date > DATEADD(day,-2,GETDATE())

----------
SELECT name
FROM sys.objects
WHERE DATEDIFF(D,modify_date, GETDATE()) < 2
ORDER BY NAME ASC


-------------------------------------------------------------------

TO FIND TABLES/VIEWS CREATED/MODIFY WITHIN CERTAIN TIME RANGE
-------------------------------------------------------------------
SELECT [name],create_date,modify_date FROM sys.tables
order by modify_date desc

----------------------
SELECT [name],create_date,modify_date FROM sys.views
WHERE modify_date > DATEADD(day,-30,GETDATE())
-------------------------------------------------------------------

TO FIND ALL REFERENCES TO A COLUMN IN STORED PROCUDURES>
FINDING STORED PROCEDURES THAT REFERENCE\UPDATE A COLUMN
---------------------------------------------------------------------
select object_name(id)
from sysdepends
where depid = object_id('tbl_Directory')
and col_name(depid, depnumber) = 'IsRegistered'
order by 1
-----------------------------------------------------------------------


TO FIND TOTAL STORED PROCEDURE COUNT IN A DATABASE
----------------------------
select count(1) from sys.objects where type='P'
-------------------------------------------------------------------------


TO FIND STORED PROCS RELATED TO A TABLE IN DATABASE
SEARCH IN ALL STORED PROCEDURES
-----------------------------------

SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tbl_Territory%'

----------------------------------------------------------------------


TO FIND A STORED PROCEDURE CONTAINING SOME TEXT OR COLUMN NAME
-------------------------------------------------------------------------
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%TCounty%'
AND ROUTINE_TYPE='PROCEDURE'
-------------------------------------------------------------------------



TO FIND THE MODIFIED TABLES IN A DATABASE
------------------------------------------------
select name,create_date,modify_date from sys.tables
WHERE create_date <> modify_date
ORDER BY modify_Date
------------------------------------------------------------------------


TO FIND INFORMATION ABOUT DATABASE OBJECTS
-----------------------------------------------------------------------
SELECT Specific_Catalog,
Specific_Name,
Routine_Catalog,
Routine_Type,
Routine_Definition,
Created,
Last_Altered
FROM INFORMATION_SCHEMA.ROUTINES
WHERE Created <> Last_Altered
ORDER BY Last_Altered
--------------------------------------------------------


TO GET THE COLUMN NAMES AND DATATYPES IN A TABLE
------------------------------------------------------------------------

USE CRMK
GO
SELECT column_name 'Column Name',
data_type 'Data Type',
character_maximum_length 'Maximum Length'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'Country'
--------------------------------------------------------------------------

[dbo].[UTILfn_Split]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER function [dbo].[UTILfn_Split](
@String nvarchar (4000),
@Delimiter nvarchar (10)
)
returns @ValueTable table ([Value] nvarchar(4000))
begin
declare @NextString nvarchar(4000)
declare @Pos int
declare @NextPos int
declare @CommaCheck nvarchar(1)

--Initialize
set @NextString = ''
set @CommaCheck = right(@String,1)

--Check for trailing Comma, if not exists, INSERT
if (@CommaCheck <> @Delimiter)
set @String = @String + @Delimiter

--Get position of first Comma
set @Pos = charindex(@Delimiter,@String)
set @NextPos = 1

--Loop while there is still a comma in the String of levels
while (@pos <> 0)
begin
-- set @NextString = substring(@String,1,@Pos – 1)

insert into @ValueTable ([Value]) Values (@NextString)

set @String = substring(@String,@pos +1,len(@String))

set @NextPos = @Pos
set @pos = charindex(@Delimiter,@String)
end

return
end

dbo.NameWithCommaa

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER FUNCTION [dbo].[NameWithCommaa] (
/*
FUNCTION ListToTable
Usage: select dbo.TableToString('2,13') order by entry desc
PURPOSE: Takes a comma-delimited list as a parameter and returns the values of that list into a table variable.
*/
@mylist varchar(8000)
)
Returns varchar(1000)
as
BEGIN
DECLARE @ListTable TABLE (
seqid int not null,
entry varchar(255) not null)
DECLARE
@this varchar(255),
@rest varchar(8000),
@pos int,
@seqid int

SET @this = ' '
SET @seqid = 1
SET @rest = @mylist
SET @pos = PATINDEX('%,%', @rest)
WHILE (@pos > 0)
BEGIN
set @this=substring(@rest,1,@pos-1)
set @rest=substring(@rest,@pos+1,len(@rest)-@pos)
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,RTRIM(LTRIM(@this)))
SET @pos= PATINDEX('%,%', @rest)
SET @seqid=@seqid+1
END
set @this=@rest
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,@this)
-- RETURN
-----------------------------

declare @pages varchar(200)
SELECT @pages = COALESCE(@pages + ', ', '') + convert( varchar(50),Name)FROM
(
select Name from tbl_Rights,

(select entry from listtotable(@mylist)) newtable
where tbl_Rights.ID=cast (newtable.entry as bigint)

) as new

return @pages
END

dbo.TableToString

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



ALTER FUNCTION [dbo].[TableToString] (
/*
FUNCTION ListToTable
Usage: select dbo.TableToString('2,13') order by entry desc
PURPOSE: Takes a comma-delimited list as a parameter and returns the values of that list into a table variable.
*/
@mylist varchar(8000)
)
Returns varchar(1000)
as
BEGIN
DECLARE @ListTable TABLE (
seqid int not null,
entry varchar(255) not null)




DECLARE
@this varchar(255),
@rest varchar(8000),
@pos int,
@seqid int

SET @this = ' '
SET @seqid = 1
SET @rest = @mylist
SET @pos = PATINDEX('%,%', @rest)
WHILE (@pos > 0)
BEGIN
set @this=substring(@rest,1,@pos-1)
set @rest=substring(@rest,@pos+1,len(@rest)-@pos)
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,RTRIM(LTRIM(@this)))
SET @pos= PATINDEX('%,%', @rest)
SET @seqid=@seqid+1
END
set @this=@rest
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,@this)
-- RETURN


-----------------------------

declare @pages varchar(200)
SELECT @pages = COALESCE(@pages + ', ', '') + convert( varchar(50),Category)FROM

(
select Category from tbl_Category,

(select entry from listtotable(@mylist)) newtable
where tbl_Category.ID=cast (newtable.entry as bigint)

) as new

return @pages
END

dbo.ListToTable

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[ListToTable] (
/*
FUNCTION ListToTable
Usage: select entry from listtotable('abc,def,ghi') order by entry desc
PURPOSE: Takes a comma-delimited list as a parameter and returns the values of that list into a table variable.
*/
@mylist varchar(8000)
)
RETURNS @ListTable TABLE (
seqid int not null,
entry varchar(255) not null)

AS

BEGIN
DECLARE
@this varchar(255),
@rest varchar(8000),
@pos int,
@seqid int

SET @this = ' '
SET @seqid = 1
SET @rest = isnull(@mylist,'')
SET @pos = PATINDEX('%,%', @rest)
WHILE (@pos > 0)
BEGIN
set @this=substring(@rest,1,@pos-1)
set @rest=substring(@rest,@pos+1,len(@rest)-@pos)
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,@this)
SET @pos= PATINDEX('%,%', @rest)
SET @seqid=@seqid+1
END
set @this=@rest
INSERT INTO @ListTable (seqid,entry) VALUES (@seqid,@this)
RETURN
END