Posts by vikram

ROW_NUMBER(), NTILE(), partition by, Duplicate Records, CTE sql server 2008

Feb08
2013 1 Comment Written by vikram

–Now create a table  tbemp1 and insert some records in it.

create table tbemp1(empno int, ename varchar(50), eadd varchar(50), esal int, edno int)

 

–Insert some records in it

insert tbemp1 values(1,’Amit’,'sample address’,12000,10)

insert tbemp1 values(2,’Raj’,'sample address’,14000,20)

insert tbemp1 values(3,’John’,'sample address’,18000,30)

insert tbemp1 values(4,’Rajni’,'sample address’,20000,10)

insert tbemp1 values(5,’Suraj’,'sample address’,18000,20)

insert tbemp1 values(6,’Rohit’,'sample address’,22000,10)

insert tbemp1 values(7,’Bharat’,'sample address’,12000,30)

GO

select * from tbemp1

– ROW_NUMBER() will display records with serial no.
select ROW_NUMBER() over (order by esal) as sr, esal,ename from tbemp1

– partition by It will display serial no. according to edno
select ROW_NUMBER() over (partition by edno order by esal desc) as sr, edno,esal,ename from tbemp1
READ MORE »

Posted in Sql Server - Tagged

How to get second highest Salary sql server 2008

Feb08
2013 Leave a Comment Written by vikram

 

create table tbemp(empno int, ename varchar(50), eadd varchar(50), esal int, edno int)

GO

 

insert tbemp values(1,’Amit’,'sample address’,12000,10)

insert tbemp values(2,’Raj’,'sample address’,14000,20)

insert tbemp values(3,’John’,'sample address’,18000,30)

insert tbemp values(4,’Rajni’,'sample address’,20000,10)

insert tbemp values(5,’Suraj’,'sample address’,18000,20)

insert tbemp values(6,’Rohit’,'sample address’,22000,10)

insert tbemp values(7,’Bharat’,'sample address’,12000,30)

 

select * from tbemp

 

– second highest salary

select sr,empno,esal,ename from

(select empno,esal,ename, ROW_NUMBER() over(order by esal desc) as sr from tbemp) as p

where p.sr=2 order by esal desc

 

– another way to get second highest salary

select top 1 empno, esal,ename from

(select top 2 empno, esal,ename from tbemp order by esal desc) as p

order by esal asc

Posted in Sql Server - Tagged ,

Insert values, insert into, insert default value, insert execute and select into sql server 2008

Feb08
2013 Leave a Comment Written by vikram

Insert statements

There are five types to save the records

1)      Insert values

2)      Insert into

3)      Insert Default value

4)      Insert Execute (It will execute store procedure)

5)      Select into (It will create table in runtimeand  fetch record from one table and create same structure but will not copy the constraints.

 

READ MORE »

Posted in Sql Server - Tagged ,

How to Bind GridView with SqlDataReader in Asp.net

Sep29
2012 Leave a Comment Written by vikram

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection();
SqlCommand cmd;

protected void Page_Load(object sender, EventArgs e)
{
try
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["cn"].ConnectionString;
con.Open();
}
catch { }
con.Close();
if (!IsPostBack)
{
Bind_Gridview_with_SqlDataReader();
}

}

private void Bind_Gridview_with_SqlDataReader()
{
if (con.State == ConnectionState.Closed)
{ con.Open(); }

cmd = new SqlCommand(“Select * from tablename”, con);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
//***we can also use:-> grd_product_details.DataSource = cmd.ExecuteReader();
GridView1.DataSource = dr;
GridView1.DataBind();
}
else
{
GridView1.DataSource = dr;
GridView1.DataBind();

}

dr.Close();
dr.Dispose();
cmd.Dispose();

con.Close();

}
}

 

With Regards
Vik

Posted in Asp.net - Tagged , ,

how to attach files to email without storing on disk using Asp.net FileUpload control

Sep28
2012 Leave a Comment Written by vikram

Here i will explain how to send e-mail with any attachment without saving on the server. You can put validations on file-uploader control

Add Default.aspx in your project and copy paste the below source code in it.

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>

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

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

<head runat=”server”>

<title>Attach File to Email.</title>

</head>

<body>

<form id=”form1″ runat=”server”>

<table style=”width: 100%” runat=”server” id=”tb”>

<tr>

<td>

Your name:</td>

<td>

<asp:TextBox ID=”txt_name” runat=”server”></asp:TextBox>

</td>

</tr>

<tr>

<td>

Email:</td>

<td>

<asp:TextBox ID=”txt_email” runat=”server”></asp:TextBox>

<asp:RequiredFieldValidator ID=”RequiredFieldValidator1″ runat=”server”

ControlToValidate=”txt_email” ErrorMessage=”Required”></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

Upload your resume:</td>

<td>

<asp:FileUpload ID=”FileUpload1″ runat=”server” />

<asp:RegularExpressionValidator ID=”RegularExpressionValidator1″ runat=”server”

ControlToValidate=”FileUpload1″ Display=”Dynamic”

ErrorMessage=”Valid format &quot;.doc, .docx, .pdf, .txt&quot;”

ValidationExpression=”^.+(.docx|.DOCX|.doc|.DOC|.txt|.pdf|PDF)$”></asp:RegularExpressionValidator>

<asp:RequiredFieldValidator ID=”RequiredFieldValidator2″ runat=”server”

ControlToValidate=”FileUpload1″ Display=”Dynamic” ErrorMessage=”Required”></asp:RequiredFieldValidator>

</td>

</tr>

<tr>

<td>

&nbsp;</td>

<td>

&nbsp;</td>

</tr>

<tr>

<td>

&nbsp;</td>

<td>

<asp:Button ID=”btnResumeSumit” runat=”server” Text=”Submit”

onclick=”btnResumeSumit_Click” />

</td>

</tr>

</table>

</form>

</body>

</html>


 READ MORE »

Posted in Asp.net - Tagged ,

Create CheckBox dynamically in usercontrol asp.net

Sep11
2012 Leave a Comment Written by vikram

In this article I will explain how to create CheckBox dynamically in UserControl and how to access value dynamically generated CheckBox in Usercontrol .Hope this will help you.

Add a Default3.aspx on page and copy below source code in it.

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default3.aspx.cs” Inherits=”Default3″ %>

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

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

<head runat=”server”>

<title>Untitled Page</title>

</head>

<body>

<form id=”form1″ runat=”server”>

<div>

<asp:PlaceHolder ID=”PlaceHolder1″ runat=”server”></asp:PlaceHolder>

</div>

</form>

</body>

</html>

Now, Add a Webusercontrol and name “CheckBoxUserControl.ascx” and copy and paste the below source code in it also.

READ MORE »

Posted in Asp.net - Tagged , , , , ,

how to access dynamically created CheckBoxList in usercontrol asp.net

Sep10
2012 Leave a Comment Written by vikram

In this article I will explain how to create CheckBoxList dynamically in UserControl and how to access or find dynamically generated CheckBoxList values in Usercontrol .
Hope this will help you in somewhere as helped me as such and so finally I m writing on it.

Add a Default2.aspx on page and copy below source code in it.

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default2.aspx.cs” Inherits=”Default2″ %>

 

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

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

<head runat=”server”>

<title>Dynamically generated checkboxlist</title>

</head>

<body>

<form id=”form1″ runat=”server”>

<div>

<asp:PlaceHolder ID=”PlaceHolder1″ runat=”server”></asp:PlaceHolder>

</div>

</form>

</body>

</html>

Now, Add a Webusercontrol and name “usercontrol.ascx” and copy and paste the below source code in it also.

<%@ Control Language=”C#” AutoEventWireup=”true” CodeFile=”usercontrol.ascx.cs” Inherits=”usercontrol” %>

<asp:PlaceHolder ID=”PlaceHolder1″ runat=”server”></asp:PlaceHolder>

<br /><br />

<asp:Button ID=”btn_submit” runat=”server” Text=”Click me” onclick=”btn_submit_Click” />

<asp:Button ID=”btn_reset” runat=”server” Text=”Clear check box”

onclick=”btn_reset_Click” />

Now, replace the code in “usercontrol.ascx.cs” and copy and paste the below source code in it.
READ MORE »

Posted in Asp.net - Tagged , , ,

Asp.net Bind Data to Datatable using JSON with webmethod

Sep07
2012 Leave a Comment Written by vikram

In this article I have explained how can we use webmethod in asp.net . I have just tried with json method. In this article I have used datatable and returning the values in array
without using database table. Hope you will like this article.
*Any idea will be appreciated.

Add a Default.aspx page in your website and replace the source with below code:

<%@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”Default.aspx.cs” Inherits=”_Default” %>

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml”>

<head runat=”server”>

<title>Asp.net Binding Data to  Datatable using JSON</title>

<script type=”text/javascript” src=”http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js”></script>

<script type=”text/javascript”>
$(document).ready(function() {
$.ajax({
type: “POST”,
contentType: “application/json; charset=utf-8″,
//binddata webmethod
url: “Default.aspx/binddata“,
data: “{}”,
dataType: “json”,
success: function(data) {
for (var i = 0; i < data.d.length; i++) {
//id of Gridview (*Note name of the columns are CaseSensitive)
$(“#gvUserDetails”).append(“<tr><td>” + (data.d[i].id) + “</td><td>” + (data.d[i].name) + “</td><td>” + (data.d[i].age) + “</td></tr>”);
} },
error: function(result) {
alert(“Error”);
}   });   });
</script>

</head>

<body>

<form id=”form1″ runat=”server”>

<div>

<asp:GridView ID=”gvUserDetails” runat=”server” CellPadding=”4″
ForeColor=”#333333″ GridLines=”None” >
<RowStyle BackColor=”#FFFBD6″ ForeColor=”#333333″ />
<FooterStyle BackColor=”#990000″ Font-Bold=”True” ForeColor=”White” />
<PagerStyle BackColor=”#FFCC66″ ForeColor=”#333333″ HorizontalAlign=”Center” />
<SelectedRowStyle BackColor=”#FFCC66″ Font-Bold=”True” ForeColor=”Navy” />
<HeaderStyle BackColor=”#990000″ Font-Bold=”True” ForeColor=”White” />
<AlternatingRowStyle BackColor=”White” />
</asp:GridView>

</div>

</form>

</body>

</html>

Now, replace .cs code below code:

READ MORE »

Posted in Asp.net, jQuery - Tagged , , , , , ,

how to create match the following concept with jquery

Sep05
2012 Leave a Comment Written by vikram

i have just tried and got the result so i would like to share it. hope you will like this one :) , for the “match the following” concept just create a .html page and paste the following html code in it and kindly download the jquery also.

How it works ?
just click on any left image and after click on right side correct image. so, if its match with the left one clicked image then it will display the combined image and let you know that you have selected right option.

 

<!DOCTYPE html>
<html>
<head>
<title>test</title>
<script type=”text/javascript” src=”jquery.js”></script>

<script type=”text/javascript”>
//**set global variables
var val = “”;
var val2= “”;

//**************  LEFT ONES.. ***************//
//** click event for images
$(document).ready(function(){
$(function() {

$(“#A”).click(function() {
val= $(this).attr(‘id’);
$(“#A”).animate({left:”200px”});
abc(val,val2);
});

$(“#B”).click(function() {
val=   $(this).attr(‘id’);
$(“#B”).animate({left:”200px”});
abc(val,val2);
});

$(“#C”).click(function() {
val=   $(this).attr(‘id’);
$(“#C”).animate({left:”200px”});
abc(val,val2);
});

$(“#D”).click(function() {
val= $(this).attr(‘id’);
$(“#D”).animate({left:”200px”});
abc(val,val2);
});

//**************  RIGHT ONES.. ***************//
//** click event for images
$(“#Apple”).click(function() {
val2= $(this).attr(‘id’);
$(“#Apple”).animate({right:”200px”});
abc(val,val2);
});

$(“#Banana”).click(function() {
val2=   $(this).attr(‘id’);
$(“#Banana”).animate({right:”200px”});
abc(val,val2);
});

$(“#Cherry”).click(function() {
val2=   $(this).attr(‘id’);
$(“#Cherry”).animate({right:”200px”});
abc(val,val2);
});

$(“#Dog”).click(function() {
val2= $(this).attr(‘id’);
$(“#Dog”).animate({right:”200px”});
abc(val,val2);
});

READ MORE »

Posted in jQuery - Tagged , ,

how to use isnull(), if, else if, while loop, return, select case in sql server

Apr21
2012 Leave a Comment Written by vikram

– Here i m writing how can we use
–1. delay/timer
–2. goto
–3. isnull()
–4. select case
–5. while loop
–6. return
–7. if else
–8. if else if
– in sql server.

–1. using WAIT FOR
Select ‘Before Delay’ as [wait/delay]
WAITFOR DELAY ’00:00:05′ — will wait for 5 seconds
Select ‘After Delay’ as [wait/delay]

————–
–2. using How can we use go to statement
– using GOTO statement
GOTO a
select ‘not accessing’ as [goto]
a:
select ‘accessing call2′ as [goto]

——————
–3. using ISNULL
declare @a1 int
set @a1 = null
select ISNULL(@a1,0) as [isnull]

——————-
–4. using SELECT CASE
declare @a2 varchar(50)
set @a2 = ‘ok’
Select CASE
when @a2 = ‘true’
then ’1′
else ‘false’
end as [select case]

———————-
–5. using WHILE LOOP
declare @a3 int
set @a3=1
while(@a3 < =2)
begin
select @a3 as [While Loop]
set @a3 = @a3+1
end

—————–
–6. using return
declare @a4 int
set @a4=1
while(@a4 < =10)
begin
select @a4 as [return]
if (@a4 = 2)
return — you can use here GOTO also
set @a4 = @a4+1
end

—————–
–7. using if else
declare @a5 int
set @a5=1
if(@a5=1)
select ‘true’ as [if else]
else
select ‘false’ as [if else]

—————–
–8. using if elseif kind of select case statement.
declare @a6 int
set @a6=3
if(@a6=1)
select ‘true’ as [else if]
else if(@a6=2)
select ‘in else 1′ as [else if]
else if(@a6=3)
select ‘in else 2′ as [else if]
else
select ‘in else’ as [else if]

with regards

vik

Posted in Sql Server
« Older Entries