keyurpatel

Sample Sql server queris like print two concate value using cursor & How do I get the 2nd row in a SQL Server table

How do I get the last 2nd row in a SQL Server table?

ANSWER : select top 1 * from (select top 2 * from Product order by ProductID desc) test order by ProductID asc

How do I get the 2nd row in a SQL Server table?

ANSWER: select top 1 * from (select top 2 * from Product order by ProductID) q order by ProductID desc

How can I print value in cursor?

ANSWER:

CONCATE STRING AND PRINT IN CURSOR

DECLARE @CONCATE NVARCHAR(1000)

DECLARE @GETVALUE CURSOR

SET @GETVALUE = CURSOR FOR

SELECT (ListPriceCurrency + ‘ ‘ + Currency) AS CONCATESTRING FROM Product

OPEN @GETVALUE

FETCH NEXT

FROM @GETVALUE INTO @CONCATE

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @CONCATE

FETCH NEXT FROM @GETVALUE INTO @CONCATE

END

CLOSE @GETVALUE

DEALLOCATE @GETVALUE

PRINT PRODUCTID

Use HSP01-Verax

DECLARE @ProductID int

DECLARE @GetValue Cursor

SET @GetValue = CURSOR FOR

SELECT ProductID

FROM Product

OPEN @GetValue

FETCH NEXT

FROM @GetValue INTO @ProductID

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @ProductID

FETCH NEXT

FROM @GetValue INTO @ProductID

END

CLOSE @GetValue

DEALLOCATE @GetValue

Group by

select P.ProductName, M.MatSize, sum(P.ListPrice) as Kg

from Products P

join Material M

on P.PrdId = M.PrdId

Group By P.Name, M.MatId, M.MatSize

Inner join

select Supplier.SupplierName , Product.SupplierID

from Supplier Supplier

inner join Product Product on Product.SupplierID = Supplier.SupplierID

Left Outer join

select Supplier.SupplierName , Product.SupplierID

from Supplier Supplier

left outer join Product Product on Product.SupplierID = Supplier.SupplierID

Add comment September 11, 2009

Sample Add / Edit and View(using Popup) Employess in Asp.Net 2.0

Sample of Add / Edit and View using without Microsoft enterprise library

è Create Add/Edit Aspx Page

<%@ 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”>

.style1 {

width: 62%;

margin-left: 181px;

height: 110px;

}

.style2

{

}

</style>

</head>

<body>

<tr>

<asp:Label ID=”lblMessage” runat=”server”></asp:Label>

</td>

</tr>

<tr>

<td>

Name :</td>

<td>

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

</td>

</tr>

<tr>

<td>

Salary :</td>

<td>

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

</td>

</tr>

<tr>

<td>

Addresss:</td>

<td>

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

</td>

</tr>

<tr>

<td>

</td>

<td >

<asp:Button ID=”btnSave” runat=”server” Text=”Save” onclick=”btnSave_Click” />  

<asp:Button ID=”btnReset” runat=”server” Text=”Reset” />

</td>

</tr>

<tr>

<td>

</td>

<td >

<asp:GridView ID=”grdEmployee” runat=”server” CellPadding=”4″ ForeColor=”#333333″

GridLines=”None” AllowPaging=”True”

onpageindexchanging=”grdEmployee_PageIndexChanging”

onrowcommand=”grdEmployee_RowCommand”

onrowdatabound=”grdEmployee_RowDataBound” AutoGenerateColumns=”False”

onrowediting=”grdEmployee_RowEditing”>

<asp:BoundField DataField=”EmpID” HeaderText=”EmpID” />

<asp:BoundField DataField=”Name” HeaderText=”Name” />

<asp:BoundField DataField=”Salary” HeaderText=”Salary” />

<asp:BoundField DataField=”Address” HeaderText=”Address” />

<asp:TemplateField HeaderText=”Edit”>

<asp:LinkButton ID=”lnkEdit” runat=”server” Text=”Edit” CommandName=”Edit”></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText=”View”>

<asp:LinkButton ID=”lnkView” runat=”server” Text=”View”></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</td>

</tr>

<tr>

<td>

 </td>

<td >

 </td>

</tr>

</table>

</form>

</body>

</html>

This are the code for the cs (Code behind file)

=============================================================================

using System;

using System.Collections.Generic;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Enitity;

using BLL;

using System.Data;

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

{

#region Properties

EmployeeManagement objEmployeeManagement = new EmployeeManagement();

private int vwEmpID

{

get { return Convert.ToInt32(ViewState["EmpID"]); }

set { ViewState.Add(“EmpID”, value); }

}

#endregion

#region Events

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGrid();

}

protected void btnSave_Click(object sender, EventArgs e)

{

try

{

bool blnCheckSuccess = false;

EmployeeEnitity objEmployeeEnitity = new EmployeeEnitity();

objEmployeeEnitity.EmployeeName = Convert.ToString(txtName.Text);

objEmployeeEnitity.EmployeeSalary = Convert.ToInt32(txtSalary.Text);

objEmployeeEnitity.EmployeeAddress = Convert.ToString(txtAddeess.Text);

if (vwEmpID <= 0)

{

blnCheckSuccess = objEmployeeManagement.Employee_Insert(objEmployeeEnitity);

}

else

{

objEmployeeEnitity.EmpID = vwEmpID;

blnCheckSuccess = objEmployeeManagement.Employee_Update(objEmployeeEnitity);

}

if (blnCheckSuccess)

lblMessage.Text = “Record Successfully Saved”;

else

lblMessage.Text = “Record not Successfully saved , please contact administrator”;

BindGrid();

Reset();

}

catch (Exception ex)

{

throw ex;

}

protected void grdEmployee_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

((LinkButton)(e.Row.FindControl(“lnkEdit”))).CommandArgument = Convert.ToString(DataBinder.Eval(e.Row.DataItem, “EmpID”));

string PageName = Page.ResolveUrl(@”~/PopupWindow/ViewEmployeeDetails.aspx?EmpID=” + Convert.ToString(DataBinder.Eval(e.Row.DataItem, “EmpID”)));

((LinkButton)(e.Row.FindControl(“lnkView”))).Attributes.Add(“onclick”, “window.open(‘” + PageName + “‘,’Popup’,'width=410,height=300,top=150,left=150,scrollbars=yes,resizable=yes,location=no’);return false;”);

}

protected void grdEmployee_RowCommand(object sender, GridViewCommandEventArgs e)

{

if (e.CommandName == “Edit”)

{

vwEmpID = Convert.ToInt32(e.CommandArgument);

SetDefaults(vwEmpID);

}

protected void grdEmployee_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

grdEmployee.PageIndex = e.NewPageIndex;

BindGrid();

}

private void SetDefaults(int EmpID)

{

try

{

DataSet ds = new DataSet();

ds = objEmployeeManagement.GetEmployeeDetailsByID(EmpID);

txtName.Text = Convert.ToString(ds.Tables[0].Rows[0].ItemArray[1]);

txtSalary.Text = Convert.ToString(ds.Tables[0].Rows[0].ItemArray[2]);

txtAddeess.Text = Convert.ToString(ds.Tables[0].Rows[0].ItemArray[3]);

}

catch (Exception ex)

{

throw ex;

}

#endregion

#region Private Methods

private void Reset()

{

txtAddeess.Text = string.Empty;

txtName.Text = string.Empty;

txtSalary.Text = string.Empty;

vwEmpID = 0;

}

private void BindGrid()

{

try

{

DataSet ds = new DataSet();

ds = objEmployeeManagement.GetAllEmployee();

grdEmployee.DataSource = ds;

grdEmployee.DataBind();

}

catch (Exception ex)

{

throw ex;

}

#endregion

protected void grdEmployee_RowEditing(object sender, GridViewEditEventArgs e)

{

}

================Enitity=====================================

using System;

using System.Collections.Generic;

using System.Text;

namespace Enitity

{

public class EmployeeEnitity

{

#region “Private Variables”

private string strName;

private string strAddress;

private int intSalary;

private int intEmpID;

#endregion

#region “Properties”

/// <summary>

/// get & set EmployeeName

/// </summary>

public string EmployeeName

{

get

{

return strName;

}

set

{

strName = value;

}

/// <summary>

///

/// </summary>

public int EmployeeSalary

{

get { return intSalary; }

set { intSalary = value; }

}

/// <summary>

///

/// </summary>

public string EmployeeAddress

{

get { return strAddress; }

set { strAddress = value; }

}

public int EmpID

{

get { return intEmpID; }

set { intEmpID = value; }

}

#endregion

}

==================BLL================================

using System;

using System.Collections.Generic;

using System.Text;

using Enitity;

using DAL;

using System.Data;

namespace BLL

{

public class EmployeeManagement

{

public bool Employee_Insert(EmployeeEnitity objEmployeeEnitity)

{

return Employee.Employee_Insert(objEmployeeEnitity);

}

public bool Employee_Update(EmployeeEnitity objEmployeeEnitity)

{

return Employee.Employee_Update(objEmployeeEnitity);

}

public DataSet GetAllEmployee()

{

return Employee.GetAllEmployee();

}

public DataSet GetEmployeeDetailsByID(int EmpID)

{

return Employee.GetEmployeeDetailsByID(EmpID);

}

==========================DAL=========================

using System;

using System.Collections.Generic;

using System.Text;

using Enitity;

using System.Data.Common;

using System.Data.SqlClient;

using System.Data;

namespace DAL

{

public class Employee

{

private const string ConnectionString = “Data Source=CIPL-PC49\\SQLEXPRESS;Initial Catalog=HSP01-Verax;Integrated Security=True”;

public static bool Employee_Insert(EmployeeEnitity objEmployeeEnitity)

{

int intEmployeeID;

SqlConnection sqlconn = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = “Usp_EmployeeInsert”;

cmd.Connection = sqlconn;

cmd.Parameters.Add(“@Name”, DbType.String).Value = objEmployeeEnitity.EmployeeName;

cmd.Parameters.Add(“@Salary”, DbType.Int16).Value = objEmployeeEnitity.EmployeeSalary;

cmd.Parameters.Add(“@Address”, DbType.String).Value = objEmployeeEnitity.EmployeeAddress;

cmd.Parameters.Add(“@EmpID”, DbType.Int16).Direction = ParameterDirection.Output;

sqlconn.Open();

cmd.ExecuteNonQuery();

intEmployeeID = Convert.ToInt32(cmd.Parameters["@EmpID"].Value);

sqlconn.Close();

if (intEmployeeID > 0)

return true;

else

return false;

}

public static bool Employee_Update(EmployeeEnitity objEmployeeEnitity)

{

//int intEmployeeID;

SqlConnection sqlconn = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = “Usp_EmployeeUpdate”;

cmd.Connection = sqlconn;

cmd.Parameters.Add(“@EmpID”, DbType.Int32).Value = objEmployeeEnitity.EmpID;

cmd.Parameters.Add(“@Name”, DbType.String).Value = objEmployeeEnitity.EmployeeName;

cmd.Parameters.Add(“@Salary”, DbType.Int16).Value = objEmployeeEnitity.EmployeeSalary;

cmd.Parameters.Add(“@Address”, DbType.String).Value = objEmployeeEnitity.EmployeeAddress;

// cmd.Parameters.Add(“@EmpID”, DbType.Int16).Direction = ParameterDirection.Output;

sqlconn.Open();

cmd.ExecuteNonQuery();

//intEmployeeID = Convert.ToInt32(cmd.Parameters["@EmpID"].Value);

sqlconn.Close();

return true;

}

public static DataSet GetAllEmployee()

{

SqlConnection sqlcon = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = “GetAllEmployee”;

cmd.Connection = sqlcon;

sqlcon.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

sqlcon.Close();

return ds;

}

public static DataSet GetEmployeeDetailsByID(int EmpID)

{

SqlConnection sqlCon = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = “GetEmployeeDetailsByID”;

cmd.Connection = sqlCon;

cmd.Parameters.Add(“@EmpID”, DbType.Int32).Value = EmpID;

sqlCon.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

sqlCon.Close();

return ds;

}

========================View Popup Page (Aspx Page)===================

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

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

.style1

{

width: 38%;

margin-left: 110px;

}

.style2

{

}

</style>

</head>

<body>

<div>

</div>

<table>

<tr>

<td>

EmployeeName:</td>

<td>

<asp:Label ID=”lblName” runat=”server” Text=”lblName”></asp:Label>

</td>

</tr>

<tr>

<td>

EmployeeSalary:</td>

<td>

<asp:Label ID=”lblSalary” runat=”server” Text=”lblSalary”></asp:Label>

</td>

</tr>

<tr>

<td>

EmployeeAddress:</td>

<td>

<asp:Label ID=”lblAddress” runat=”server” Text=”lblAddress”></asp:Label>

</td>

</tr>

<tr>

<asp:Button ID=”btnClose” runat=”server”

onclientclick=”Javascript:self.close();” Text=”Close” />

</td>

</tr>

</table>

</form>

</body>

</html>

============View Popup Page(Code behind)==============================

EmployeeManagement objEmployeeManagement = new EmployeeManagement();

#region “Property”

private int vwEmpID

{

get { return Convert.ToInt32(ViewState["EmpID"]); }

set { ViewState.Add(“EmpID”, value); }

}

#endregion

protected void Page_Load(object sender, EventArgs e)

{

if (Request.QueryString["EmpID"] != null)

{

vwEmpID = Convert.ToInt32(Request.QueryString["EmpID"]);

}

if (!IsPostBack)

{

SetDefaults();

}

private void SetDefaults()

{

try

{

DataSet ds = new DataSet();

ds = objEmployeeManagement.GetEmployeeDetailsByID(vwEmpID);

lblName.Text = Convert.ToString(ds.Tables[0].Rows[0].ItemArray[1]);

lblSalary.Text = Convert.ToString(ds.Tables[0].Rows[0].ItemArray[2]);

lblAddress.Text = Convert.ToString(ds.Tables[0].Rows[0].ItemArray[3]);

}

catch (Exception ex)

{

throw ex;

}

====================Insert Employee============

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

– =============================================

– Author: <Author,,Name>

– Create date: <Create Date,,>

– Description: <Description,,>

– =============================================

ALTER PROCEDURE [dbo].[Usp_EmployeeInsert]

– Add the parameters for the stored procedure here

@EmpID int = NULL output,

@Name varchar(50) ,

@Salary int,

@Address varchar(50)

BEGIN

– SET NOCOUNT ON added to prevent extra result sets from

– interfering with SELECT statements.

SET NOCOUNT OFF;

INSERT INTO EMPLOYEE

(

[Name],

Salary,

Address

)

VALUES

(

@Name,

@Salary,

@Address

)

SET @EmpID = SCOPE_IDENTITY()

END

========================Employee Update===============================

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

– =============================================

– Author: <Author,,Name>

– Create date: <Create Date,,>

– Description: <Description,,>

– =============================================

ALTER PROCEDURE [dbo].[Usp_EmployeeUpdate]

– Add the parameters for the stored procedure here

@EmpID int,

@Name varchar(50) ,

@Salary int,

@Address varchar(50)

BEGIN

– SET NOCOUNT ON added to prevent extra result sets from

– interfering with SELECT statements.

SET NOCOUNT OFF;

– Insert statements for procedure here

UPDATE EMPLOYEE

SET

[NAME]= @Name,

Salary= @Salary ,

Address=@Address

WHERE

EmpID = @EmpID

END

=================End This Sample======================================

Add comment September 11, 2009

Html File Upload control With read only text box in mozilla and i.e browser

I have facing one problem today

when i upload my file using file upload html control then i edited file path in file upload control textbox.
but when i upload file i want read only file upload control text box. i can’t change in file upload control textbox.

here is my code i done tested in i.e and mozilla both browser
For internet browser i am using contenteditable=”false” property on file upload html control

<%@ 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“>

    <title>Untitled Page</title>

</head>
<body>
    <form id=”form1″ runat=”server”>
    <div>
        <asp:radiobuttonlist id=”radio1″ runat=”server”>
          <asp:listitem value=”Madrid” Selected=”True” />

<asp:listitem value=”Oslo” />

<asp:listitem value=”Lisbon” />

</asp:radiobuttonlist>
        <!–Put contenteditable=”false” here –>
        <input type=”file” contenteditable=”false” id=”FileUpload1″ runat=”server” />
        </div>
    </form>
</body>
</html>
================================================================================================
For Mozilla Browser

Mozilla is not supported contenteditable=”false” property on file upload control i have use one js for solve this issue with help of my Colleague Mayank.

<%@ 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“>

<title>Untitled Page</title>
<script language=”javascript” type=”text/javascript”>
// Js for mozilla browser
function FileUpload_OnChange(e)
{

return false;
}

</script>
</head>
<body>
    <form id=”form1″ runat=”server”>
    <div>
        <asp:radiobuttonlist id=”radio1″ runat=”server”>
          <asp:listitem value=”Madrid” Selected=”True” />

<asp:listitem value=”Oslo” />

<asp:listitem value=”Lisbon” />

</asp:radiobuttonlist>
       <!– Call js for mozilla browser –>
        <input type=”file” onkeypress=”return FileUpload_OnChange(event)” id=”FileUpload1″ runat=”server” />
        </div>
    </form>
</body>
</html>
=================================================================================================

Add comment September 27, 2007

Finding radio button is selected or not in gridview

I have develop one javascript. it’s finding radio button value is selected or not in grid.

for(i=0;i<Emlements.length;i++){// Check for Radio buttonif(Emlements[i].type == “radio”){// If radio buttton is checked than make IsAtleastOneChecked = true

if(Emlements[i].checked == true)IsAtleastOneChecked = true;// Increament CountRadioButton each time you find radio buttonCountRadioButton = eval(CountRadioButton + 1);// We have 5 radio buttons per datagrid row. // When count becomes 5 means we have checked a row

// Set IsAtleastOneChecked = false// Set CountRadioButton = 0// So we have clear our parameters for next rowif(CountRadioButton == 5){// Check if in current row none of the radio button is selected than show alert message.// break the llop

if(IsAtleastOneChecked == false){alert(“Please select atleast one option for each feedback parameter.”);return false;

break;}// Clear CountersCountRadioButton =0;IsAtleastOneChecked = false;}}}

}

</script>I called this function serversideif (SessionInfo.IsFeedbackMandatory == true){ibtnSave.Attributes.Add(“onclick”, “return IsFeedbackMandatory();”);}

Add comment June 15, 2007

move items in a list box from one to the other in javascript

I have used two list boxes on my form, one initially displays with items and the
other displays blank, by clicking a button, it is possible to move items
from one box to another using javascript.

if ((Source != null) && (Target != null)) {
while ( Source.options.selectedIndex >= 0 ) {
var newOption = new Option(); // Create a new instance of ListItem
newOption.text = Source.options[Source.options.selectedIndex].text;
newOption.value = Source.options[Source.options.selectedIndex].value;

Target.options[Target.length] = newOption; //Append the item in Target
Source.remove(Source.options.selectedIndex); //Remove the item from Source
}
}
}
</script>

i called this function server side,

<table >
<tr>
<td>
<asp:ListBox id=”ListBox1″ runat=”server” Height=”111px” SelectionMode=”Multiple”>
<asp:ListItem Value=”1″>One</asp:ListItem>
<asp:ListItem Value=”2″>Two</asp:ListItem>
<asp:ListItem Value=”3″>Three</asp:ListItem>
</asp:ListBox>
</td>
<td>
<p>
<input onclick=”Javascript:MoveItem(‘ListBox1′, ‘ListBox2′);” type=”button” value=”->” />
</p>
<p>
<input onclick=”Javascript:MoveItem(‘ListBox2′, ‘ListBox1′);” type=”button” value=”<-” />
</p>
</td>
<td>
<asp:ListBox id=”ListBox2″ runat=”server” Height=”111px” SelectionMode=”Multiple”>
<asp:ListItem Value=”8″>Eight</asp:ListItem>
<asp:ListItem Value=”9″>Nine</asp:ListItem>
<asp:ListItem Value=”10″>Ten</asp:ListItem>
</asp:ListBox>
</td>
</tr>
</table>

1 comment June 6, 2007

keyurpatel

Sample Sql server queris like print two concate value using cursor & How do I get the 2nd row in a SQL Server table

Sample Add / Edit and View(using Popup) Employess in Asp.Net 2.0

Html File Upload control With read only text box in mozilla and i.e browser

Finding radio button is selected or not in gridview

move items in a list box from one to the other in javascript

Blog Stats

Top Clicks