SubsDotNet: February 2018

Sunday, 25 February 2018

Example of Cursor, While Loop and Split comma all together in SQL SERVER

Declare @SBUCode2 varchar(50)
Declare @Percentage2 varchar(50)
Declare @CONTROLID2 int
Declare @countSBU int,@countPER int
Declare @SBU int,@PER numeric
Declare sBUCur cursor local scroll for Select CONTROLID,SBUCode,Percentage from #tblAcVoucherFinal where CONTROLID= @CONTROLID
open sBUCur
fetch next from sBUCur into @CONTROLID2,@SBUCode2,@Percentage2
while not @@fetch_status <> 0
Begin

--here its getting count of SBU after splitting commas
Select @countSBU= count(*) from dbo.split(@SBUCode2,',')

declare @I int=0
WHILE @I <= @countSBU-1
BEGIN
--here its getting value of SBU n Percentage as per ID from Split Function and Saving in table
Select @SBU=Value from dbo.split(@SBUCode2,',') where id=@I
Select @PER=Value from dbo.split(@Percentage2,',') where id=@I

Insert into tblAcvoucherDetails_SBU (VNo,SBUCode,Percentage,TransDate,UserID)
Values(@VNo,@SBU,@PER ,@Trans_Date,@UserID)

SET @I = @I + 1
END

fetch next from sBUCur into @CONTROLID2,@SBUCode2,@Percentage2
End
close sBUCur
deallocate sBUCur

Execute function to see out put in Sql Server

declare @aaa varchar(50)
select @aaa= dbo.Udfgetstarttimefromorderadvancedschedule(904977, '2018-02-25 00:00:00.000')
select @aaa

Execute SP with Out Put Parameter in Sql Server

DECLARE @return_Value int
EXEC [SP_ASSIGN_CHECK_5] 0,818645, @return_Value = @return_Value OUTPUT
SELECT @return_Value

Differents between Cast and Convert function using Sql Server

1.select distinct cast(FromDate as Varchar) FromDate from tblpolicymaster where policyno=234000211

2.select distinct Convert(Varchar,FromDate,103) FromDate from tblpolicymaster where policyno=234000211

3.using both cast & convert
select distinct cast(Convert(Varchar,FromDate,103)as varchar) FromDate from tblpolicymaster where policyno=234000211

Example of Left, Right, Charindex, Patindex using Sql Server

like left
select substring(jobdesc, 0, charindex('Teachers are not eligible for overtime because', jobdesc)) from Order_Gen where Client=48670

--1.Right
select RIGHT(CEM.ReimbHospital,CHARINDEX('-',REVERSE(CEM.ReimbHospital)) - 1) from tblClaimsEntryMaster cem WHERE cem.ClaimNo=234006607

--1.Left
select Left(CEM.ReimbHospital,CHARINDEX('-',(CEM.ReimbHospital)) - 1) from tblClaimsEntryMaster cem WHERE cem.ClaimNo=234006607

--2.Right
select right(policyname, len(policyname)-charindex('-',policyname)) from tblpolicy
select RIGHT('policy-12',9-7)
--ans->12

--2.Left
select left(policyname, charindex('-',policyname)-1 ) from tblpolicy
select LEFT('policy-12',7-1)
--ans->policy

--it returns one value
select left(ClientName,CHARINDEX(' ',ClientName)-1) from tblClientMaster where ClientNo=89 or ClientNo=71

select left('Client Name',CHARINDEX(' ','Client Name')-1)
--it returns the word frm left when see the space

select right('Client dName',CHARINDEX(' ','Client dName')-1)
--it returns the word frm right when see the space

--it returns multiple value
select left(ClientName,CHARINDEX(' ',ClientName,1)) from tblClientMaster
--it returns 1st word frm left

select right(ClientName,CHARINDEX(' ',ClientName,1)) from tblClientMaster
--it returns 1st word frm right--wrong

select CHARINDEX(' ','Client Name') --it return position of space-7

--It returns 3 letters from left and right
select left(ClientName,3) from tblClientMaster
select right(ClientName,3) from tblClientMaster

-- I need to get the last RIGHT 3 characters and I need to get whatever is to the LEFT
select right(ClientName,3),LEFT(ClientName,LEN(ClientName)-3) from tblClientMaster

-- finds the position at which the pattern ensure starts in a specific row of the Clientname column in the Document table in the AdventureWorks2012 database.
select PATINDEX('%MANSARD%',Clientname) from tblClientMaster

SELECT PATINDEX ( '%ein%', 'Das ist ein Test')
SELECT PATINDEX('%en_ure%', 'please ensure the door is locked')

Differents between STUFF and REPLACE in Sql Server

1.Select STUFF ('this is a test', 6,2, 'was')

o/p:-this was a test

2.Select REPLACE ('That is a test and it is successful!','is','was')

o/p:- That was a test and it was successful!

Adding comma using STUFF in Sql Server

1.select distinct id,name,
STUFF((select ','+descrptn from test t1 where t2.id=t1.id for xml path('')),1,1,'') as description
from test t2

2.SELECT DISTINCT StateId, StateName
, STUFF((SELECT ','+City
FROM [StateProvince] S2
WHERE S1.StateId = S2.StateId
FOR XML PATH('')), 1, 1, '') AS City
FROM [StateProvince] S1

How to get unique record in Sql Server

create table test(id int,name varchar(30),descrptn varchar(30))

1.(it contain duplicate records)
select * from test
--here this table(test1) only keep unique records
select distinct * into test1 from test
--here drop the table test
drop table test
--here rename the table test1 to test
sp_rename test1,test

OR

2.(it contain duplicate records)
select * from test
--here this table(test1) only keep unique records
select distinct * into test1 from test
select * from test1
--here delete all the records of table(test) test
truncate table test
select * from test
--here insert one whole table(test1) records into another table(test)
insert into test select * from test1
select * from test
-- then we can drop the table(test1)
drop table test1

OR

insert into test select * from test
3.(it contain duplicate records)
select * from test
--select whole query given below and exucute,we will get unique records
with cte as(
select id,name,descrptn,RN = ROW_NUMBER()OVER(PARTITION BY id ORDER BY id)
FROM test
)
DELETE FROM CTE WHERE RN > 1

Example of Pivot in Sql Server

SELECT ClientNo,isnull([1],0) [0-30],isnull([2],0) [31-60],isnull([3],0) [61-90],isnull([4],0) [>90],BrokerName,AsOfDate,UserName
From (Select Amount,ClientNo,DaysInGroup,BrokerName,AsOfDate,UserName FROM #tblPivot) a
PIVOT( sum(Amount)
FOR DaysInGroup IN ([1],[2],[3],[4])) AS PVTTable1

Ex:

Amount ClientNo DaysInGroup BrokerName AsOfDate UserName
16288.333 2 2 GUARDIAN INSURANCE BROKERS 2016-09-10 00:00:00.000 amtpl
479850.254 2 3 GUARDIAN INSURANCE BROKERS 2016-09-10 00:00:00.000 amtpl

Ans:
ClientNo 0-30 31-60 61-90 >90 BrokerName AsOfDate UserName
2 0.00 16288.333 479850.254 0.00 GUARDIAN INSURANCE BROKERS 2016-09-10 00:00:00.000 amtpl

Rename the Column Name in Sql Server

sp_RENAME 'tblWebClaimsBatch.TransactionDate' , 'Trans_Date', 'COLUMN'

Validating or Comparing between Two Dates-one from textbox-two from RaddatePicker

Example of Java script validation using ASP.Net

--ddl validation
var ddlBusinessCat = document.getElementById('ddlBusinessCat').value;
if (ddlBusinessCat == '') {
alert('Select Business Category');
document.getElementById('ddlBusinessCat').focus();
return false;
}

--telerik ddl validation
var ddlCurrency = document.getElementById('ddlCurrency').value;
if (ddlCurrency == '') {
alert('Select Currency');
//document.getElementById('ddlCurrency').focus();
var comboBox = $find('ddlCurrency');
var input = comboBox.get_inputDomElement();
input.focus();
return false;
}

--listbox Validation
var lstRomType = document.getElementById('lstRomType').value;
if (lstRomType == '') {
alert('Select Room Type');
document.getElementById('lstRomType').focus();
return false;
}

--RaddatePicker Validation--No Focus
var txtEffectiveFrom = document.getElementById('txtEffectiveFrom');
if (txtEffectiveFrom.value == '') {
alert("Enter the Effective From Date")
$find("txtEffectiveFrom ").clear();
RPfFocus();
return false;
}

-----------

<div>
<script language="javascript" type="text/javascript">
function RPfFocus() {
var picker = $find("<%= txtDate.ClientID %>");
picker.showPopup();
}
</div>

-----------

--set date to RaddatePicker

datepicker.set_selectedDate('2005-08-06 00:00:00.000');

--Clear to RaddatePicker

$find('txtDate').clear();

--Enabled to RaddatePicker

$find("txtDate").set_enabled(true);

-----------

--Assigning/Changing Label Name in inline Page

document.form1.txtBank.disabled = false;
document.form1.txtAcNo.disabled = false;
document.form1.txtChequeNo.disabled = false;

document.getElementById('txtBank').style.backgroundColor = "white";
document.getElementById('txtAcNo').style.backgroundColor = "white";
document.getElementById('txtChequeNo').style.backgroundColor = "white";
document.getElementById('lblChequeNo').innerText = "ChequeNo";
document.getElementById('lblAcNo').innerText = "A/C No";
$find('txtDate').clear();
$find("txtDate").set_enabled(true);
RPfFocus();

To get dependent SP's name in which the SP/Table is using_in Sql Server

1.To get dependent SP's name in which the table is using.(means-table's used by which SP's)

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

2.To get dependent SP's name in which the SP is using.(means-SP used by which SP's)

sp_whereiscolumn 'SP_ORDER_GEN_NUM'

3.Get SP names by any related text names

SELECT name
FROM sys.procedures
WHERE Object_definition(object_id) LIKE '%deletecandidatefromcloud%'

4.For performance issue...run below SP

exec sp_updatestats

Write a function to split comma's in Sql Server

Drop function dbo.SplitingCommas
Go
Create function dbo.SplitingCommas(@String nvarchar(4000), @Delimiter char(1))
Returns @Results Table (Id int identity(1,1),Items nvarchar(4000))
As

Begin
Declare @Index int
Declare @Slice nvarchar(4000)
Select @Index = 1

If @String Is NULL Return

While @Index != 0
Begin

Select @Index = CharIndex(@Delimiter, @String)
If @Index != 0
Select @Slice = left(@String, @Index - 1)
else
Select @Slice = @String

Insert into @Results(Items) Values (@Slice)

Select @String = right(@String, Len(@String) - @Index)

If Len(@String) = 0 break
End

Return
End

Numeric with only one dot allowed to textbox in ASP.Net

TextBox1.Attributes.Add("onfocus","validatetxtbx()");

MsgBox and ConfirmationBox Example in ASP.Net

1.Confirmation Box-JS

<asp:Button CssClass="srsbutton" ID="btnActivate" Text="Active" runat="server" Width="120px" Visible="false" OnClientClick="return confirmation();"/>

<script type="text/javascript">
function confirmation() {
if (confirm('Are you sure you want to Inacive ?')) {
return true;
} else {
return false;
}
}
</script>

2.VB.Net-Code Behind

If MsgBox("Prompt", "Are you sure you want to Inactive ?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
InActivePostDelete()
End If

3.VB.Net-Code Behind

Dim resultResponse = MsgBox("Are you sure you want to add/remove students of new order?", MsgBoxStyle.YesNo, "Prompt")
If resultResponse = MsgBoxResult.Yes Then
Page.RegisterClientScriptBlock("ChildInfo", "<script type='text/javascript'>window.open('frmChildInfo.aspx?qOrderId=" & Request.QueryString("NewOrderId") & "','_blank','width=1200,height=600,top=50px,left=100px,scrollbars=no,status=yes,resizable=false');</script>")
End If

4.using JS and call from Code Behind using Ajax ScriptManager

-----In frmViewOrder.aspx
<script type="text/javascript">
function fnConfirmARChild(NewOrderId) {
var x;
x = confirm('Are you sure you want to add/remove students of new order?')
if (x == true) {
window.open('frmChildInfo.aspx?qOrderId=' + NewOrderId, '_blank', 'width=1200,height=600,top=50px,left=100px,scrollbars=no,status=yes,resizable=false');
}
else {
return false;
}
}
</script>

-----In frmViewOrder.aspx.vb
Me.Page.RegisterStartupScript("confirm", "<script type='text/javascript'>fnConfirmARChild('" & Request.QueryString("NewOrderId") & "');</script>")

5.Show Confirm Box from GridView by setting value to HiddenField

-----frmChildInfo.aspx
<asp:HiddenField ID="hdnReturnDeleteValue" runat="server" />
<asp:Button ID="btnDeleteChild" runat="server" Text="Delete" CommandName="DeleteChild" OnClientClick="fnConfirmARChild()" CommandArgument='<%# Convert.ToString(Eval("Child_Id")) + "," + Convert.ToString(Eval("Order_Id")) %>' /></td>

<script type="text/javascript">
function fnConfirmARChild() {
var x;
x = confirm('Are you sure you want delete student from this order?')
document.getElementById('hdnReturnDeleteValue').value = false;
if (x == true) {
document.getElementById('hdnReturnDeleteValue').value = true;
return true;
}
else {
document.getElementById('hdnReturnDeleteValue').value = false;
return false;
}
</script>

-----frmChildInfo.aspx.vb
If hdnReturnDeleteValue.Value = "true" Then
ciSqlString = "uspDelete_OrderChildInfo"
SqlHelper.ExecuteNonQuery(ciSqlConnection, CommandType.StoredProcedure, ciSqlString, ciSqlParameter)
Me.Page.ClientScript.RegisterStartupScript(Me.GetType, "alert", "alert('Child Info deleted successfully.');", True)
BindChildInfoGrid()
End If

Hide GridView Column using CSS in .Net

Execute Stored Procedure with OUT PUT Parameter in SQL Server

1.declare @new_waiver_id int
exec uspInsert_Cwadoesp_waiver_requests 875118,0,2,0,1,0,0,0,0,0,0,'New York City Prevailing Wage Law : Pay rate is below required minimum pay rate for the position.',@new_waiver_id output
select @new_waiver_id as new_waiver_id

2.declare @temp_reg_hrs float ,@temp_ot_hrs float
exec tpweb_cwa_doe_get_order_total_hours 7,7,7,7,7,0,0,'09/16/2017','09/16/2017',@temp_reg_hrs output,@temp_ot_hrs output
select '@temp_reg_hrs'=@temp_reg_hrs,'@temp_ot_hrs'=@temp_ot_hrs

Exception Handling using ajax jquery client side script

1.$.post(url, { model: dataModel ,weekending:weekending})
.done(function (data) {
if (data != "") {
$("#ConfictTimeSlip").val(data.ConfictTimeSlip);
if (data.ConfictTimeSlip > 0) {
$("#lblPopupmessage").html("The hours you have entered above conflict with a time slip that already exists in the our system.<br/>Duplicate time slips are not allowed!").css('color', 'red');
$('#btnCorrect').prop('disabled', true);
}
else{$('#btnCorrect').prop('disabled', false);}
}
})
.fail(handleAjaxError)

2.$.ajax({
type: "GET",
url: url,
success: function (data) { $("#tblBindPersonalJobBank").html(data); },
error: handleAjaxError
});

Example of Table variable in Sql Server

1.Creating Table Variable:

DECLARE @Posted_Split TABLE (
Post_Site INT
,Post_Type TINYINT
)

2.Inserting data into Table Variable:

INSERT INTO @Posted_Split
select 2,0

3.To view data of Table Variable:

select Post_Site,Post_Type from @Posted_Split

4.Joining with Table Variable:

UPDATE tblJobPostData set JobPost_ID=450,Post_Type=PS.Post_Type,Post_Site=PS.Post_Site,IsProcessed=0
from tblJobPostData JP inner join @Posted_Split PS on PS.Post_Type=JP.Post_Type and PS.Post_Site=JP.Post_Site
where JP.jobpost_id=450

CTE example in SQL Server_To get Previous n Next value

WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.jobpost_id),
p.jobpost_id
FROM tblJobPostDetails p
)
SELECT
prev.jobpost_id PreviousValue,
CTE.jobpost_id,
nex.jobpost_id NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1

How to call client side function from Server side or vice versa in VB.Net

1st option

ClientScript.RegisterClientScriptBlock(Page, typeof"Validate(ID), true);

OR

<asp:Button OnClientClick = "Validate(ID)"/>

Use this on page load:

RegisterStartupScript("Unique key", "Validate(ID);");

2nd option

protected void MyButton_Click(object sender, EventArgs e)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "myScript", "AnotherFunction();", true);
}

Interview question:5 anchor tag and 5 span tag. on click of anchor tag show only that respective span tag others should not show.

<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script>

$(document).ready(function(){
$(".item span").hide();

$(".item a").click(function(e){
e.preventDefault();
var $this=$(this).parent().find("span");
$(".item span").not($this).hide();
$this.toggle();

});

});
</script>
</head>
<body>

<div class="item"><a href="#">aaa1</a><span>ppp1</span></div>
<div class="item"><a href="#">aaa2</a><span>ppp2</span></div>
<div class="item"><a href="#">aaa3</a><span>ppp3</span></div>
<div class="item"><a href="#">aaa4</a><span>ppp4</span></div>
<div class="item"><a href="#">aaa5</a><span>ppp5</span></div>

</body>
</html>

Interview question: what is the out put of below question?

Check string is Palindrome or not using Console Application in C#

static void Main(string[] args)
{
Console.WriteLine("Enter a string to check Pallindrom.");
string str=Console.ReadLine();
string strRev = "";
for (int i = str.Length - 1; i >= 0; i--)
{
strRev += str[i];
}
if (str.ToUpper() == strRev.ToUpper())
{
Console.WriteLine("its a Pallidrom.");
Console.ReadLine();
}else
{
Console.WriteLine("its not a Pallidrom.");
Console.ReadLine();
}
}