AspDotNetFreaks - An answer to your Queries: January 2013

Wednesday, January 30, 2013

Query for fetching the records in Parent-Child DB table in SQL Server

DECLARE @FileTreeId INT = 300 (For instance)

;WITH FileTreeHierarchy AS

(
    SELECT FileTreeID, Company, Parent, Name, 1 AS 'Level'
    FROM FileTree 
    WHERE FileTreeID = @FileTreeId

    UNION ALL

    SELECT F.FileTreeID, F.Company, F.Parent, F.Name,

    FH.Level + 1 AS 'Level'
    FROM FileTree F
    INNER JOIN FileTreeHierarchy FH ON FH.Parent = F.FileTreeID

SELECT FileTreeId, Company CompanyId, Parent, Name,

[Level] = CAST([Level] as int)

FROM FileTreeHierarchy Order By [Level] Desc

Tuesday, January 29, 2013

Add Dynamic textboxes using Jquery

<head>
<script type="text/javascript">
function AddMoreTextboxes() {
var counter = 1;
var firstTextboxValue = $("#txtselectedValue").val();
if (firstTextboxValue == '') {
alert('Enter the value in First Textbox !');
return;
}
else {
if (counter > 5) {
alert("Only 5 textboxes allow");
return false;
}
var newTextBoxDiv = $(document.createElement('div'))
.attr("id", 'TextBoxDiv' + counter);

newTextBoxDiv.attr("class", "newTextBoxDiv");

newTextBoxDiv.after('<label></label>' + '<input type="text" name="textbox' + counter +
'" id="textbox' + counter + '" />').html('<label></label>' +
'<input type="text" style="width:200px;" class="newTextbox" name="textbox' + counter + '" id="textbox' + counter + '" value="" />');

newTextBoxDiv.appendTo("#TextBoxesGroup");
counter++;
}
}
</script>
</head>
<body>
<div>
<img src="../../Images/plus.png" width="16" height="16" id="ImgAddMoreTextbox" onclick="AddMoreTextboxes();" />
</div>
</body>

Monday, January 21, 2013

Delete all User-Defined Stored Procedures in SQL Server

declare @procName varchar(500)
declare cur cursor

for select [name] from sys.objects where type = 'p'
open cur
fetch next from cur into @procName
while @@fetch_status = 0
begin
exec('drop procedure ' + @procName)
fetch next from cur into @procName
end
close cur
deallocate cur