This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 1 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 2 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured slide 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

Me And My Respected Teacher Mr Kamal Sheel Mishra

Mr. K.S. Mishra is HOD of Computer Science from SMS Varanasi where I have completed my MCA

Me And My Respected Teacher Mr Udayan Maiti

Mr. Udayan Maiti is a senior .Net Expert and has guided many professionals of multi national Companies(MNC)

Me And My Best Friend Mr Ravinder Goel

Mr. Ravinder Goel is a senior Software Engineer and now he is working Wipro Technology

Thursday 23 May 2013

How to find nth maximum salary in sql server (using function)


select nth minimum salary,nth maximum salary  from table ins sql server 2008  with using sql function


use test

/create table EmpSalary……………………………..

create  table EmpSalary (SrNo int identity(1,1),EmpName nvarchar(50),Salary int)

insert into EmpSalary values('Dinesh Singh',8000)
insert into EmpSalary values('Roshan Sharma',14000)
insert into EmpSalary values('Rohi Tiwari',15000)
insert into EmpSalary values('Ramesh Katiyar',16000)
insert into EmpSalary values('Vinay Singh',20000)
insert into EmpSalary values('Anil Singh',60000)


/select salary in ascending order……………………………..

Select * from EmpSalary order by Salary asc

/select nth minimum salary from table EmpSalary …… nth= 6


select EmpName as EmployeeName,Salary as EmployeeSalary from EmpSalary es where 6=(Select COUNT(*) from EmpSalary es1 where es.Salary>=es1.Salary)


//select nth minimum salary from table EmpSalary  using function …..return only scalar value

create function getnthmaxsalary (@ntno int) returns int
as
begin
declare @salary int
select  @Salary=es.salary  from EmpSalary es where @ntno=(Select COUNT(*) from EmpSalary es1 where es.Salary<=es1.Salary)
return @salary
end

/execute  this  function……………………………..

select dbo.getnthmaxsalary(5)


//select nth minimum salary from table EmpSalary  using function …..return tabuler value

create function getnthmaxsalary_withtable (@ntno int)  
returns  table
as
return (select EmpName as EmployeeName,Salary as EmployeeSalary from EmpSalary es where @ntno =(Select COUNT(*) from EmpSalary es1 where es.Salary<=es1.Salary))

/execute  this  function……………………………..

select * from dbo.getnthmaxsalary_withtable(2)


Sunday 5 May 2013

How to use autocompleteextender Ajax Control example with textbox in asp.net


How to use autocompleteextender  Ajax Control example with textbox in asp.net

// For  Sql Server 2008 ……………………………..

create database test
use test
create table StateName(SrNo int identity(1,1),StateCode nvarchar(50) primary key,StateName nvarchar(50) unique)
insert into StateName values('Mahar11','Maharashtra')
insert into StateName values('Madhy11','Madhya Pradesh')
insert into StateName values('Manip11','Manipur')
insert into StateName values('Megha11','Meghalaya')
select * from StateName


// Create  connectionstring in  web.config  file……………………………

<connectionStrings>
<add name="kush" connectionString="Data Source=KUSH-PC\KUSH;Initial Catalog=test;Integrated Security=True"/>
</connectionStrings>


// How  to  apply   AutoCompleteExtender  on textbox.........................



<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>

<!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></title>
</head>
<body>
 <form id="form1" runat="server">
<div>
<table width="500px" border="5px">
<tr><td colspan="2">
<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
        </asp:ToolkitScriptManager></td>
 </tr>
    <tr><td>
        <asp:Label ID="Label1" runat="server" Text="StateName"></asp:Label>
        </td><td>
        <asp:TextBox ID="txtstatename" runat="server"></asp:TextBox>
    
 // after add AutoCompleteExtender write  this code …………………..
      
        <asp:AutoCompleteExtender ID="txtstatename_AutoCompleteExtender" runat="server" MinimumPrefixLength="1" EnableCaching="true" CompletionSetCount="1" CompletionInterval="1000" ServiceMethod="GetStateNames" TargetControlID="txtstatename">
        </asp:AutoCompleteExtender>
    
  </td></tr>
  </table>
   </div>
 </form>
</body>
</html>




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

public partial class AutoExtenderExample : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

[System.Web.Script.Services.ScriptMethod()]
[System.Web.Services.WebMethod]

public static List<string> GetStateNames(string prefixText)
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["kush"].ToString());
con.Open();
SqlCommand cmd = new SqlCommand("select * from StateName where StateName like @State+'%'", con);
cmd.Parameters.AddWithValue("@State", prefixText);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
List<string> StateNames = new List<string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
 StateNames.Add(dt.Rows[i][2].ToString());
}
return StateNames;
}
}


Friday 3 May 2013

How to Upload data of DataGridView in Microsoft Excel


How to Upload data of DataGridView in Microsoft Excel

create database test
use test
create table StudentDetails(Name nvarchar(50),FatherName nvarchar(50), MobileNo nvarchar(50))
insert into StudentDetails values('Anurag Sir','Mr Mudgal','08802200402')
insert into StudentDetails values('Kush Tiwari','Jai Prakash Tiwari','9451119029')

First we take a Windows Form after it we add add reference Microsoft  Excel 12.0 object Library 1.6    in our  application then take  a DataGridView and bind it with table   and when you click the button  you will see the all data inside DataGridView   in Microsoft Excel format
You  can also save  it if youwant to




using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace GridViewWithExcelSheet
{
    public partial class DataGridwithExcelSheet : Form
    {
        public DataGridwithExcelSheet()
        {
            InitializeComponent();
        }

  private void DataGridwithExcelSheet_Load(object sender, EventArgs e)
 {
   //create code to connectivity with table show  data  in gridview on Page Load

 SqlConnection con = new SqlConnection(@"Data Source=KUSH-PC\KUSH;Initial Catalog=test;Integrated Security=True");
 SqlCommand cmd = new SqlCommand("select * from StudentDetails", con);
 SqlDataAdapter da = new SqlDataAdapter(cmd);
 DataSet ds = new DataSet();
 da.Fill(ds, "StudentDetails");

 dataGridView1.DataSource = ds.Tables["StudentDetails"];
 }

private void btnUpload_Click(object sender, EventArgs e)
{
 try
{
  Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
  Microsoft.Office.Interop.Excel._Workbook ExcelBook;
  Microsoft.Office.Interop.Excel._Worksheet ExcelSheet;

  int i = 0;
  int j = 0;

 //create object of excel
 ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);
 ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
 //export header
 for (i = 1; i <= this.dataGridView1.Columns.Count; i++)
{
 ExcelSheet.Cells[1, i] = this.dataGridView1.Columns[i - 1].HeaderText;
 }

 //export data
 for (i = 1; i <= this.dataGridView1.RowCount; i++)
 {
  for (j = 1; j <= dataGridView1.Columns.Count; j++)
  {
 ExcelSheet.Cells[i + 1, j] = dataGridView1.Rows[i - 1].Cells[j - 1].Value;
   }
 }

ExcelApp.Visible = true;

//set font Khmer OS System to data range
 Microsoft.Office.Interop.Excel.Range myRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, 1], ExcelSheet.Cells[this.dataGridView1.RowCount + 1, this.dataGridView1.Columns.Count]);
Microsoft.Office.Interop.Excel.Font x = myRange.Font;
x.Name = "Arial";
x.Size = 10;

//set bold font to column header
 myRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, 1], ExcelSheet.Cells[1,this.dataGridView1.Columns.Count]);
                
x = myRange.Font;
 x.Bold = true;
               
//autofit all columns
 myRange.EntireColumn.AutoFit();
 //
 ExcelSheet = null;
ExcelBook = null;
ExcelApp = null;
  }
 catch (Exception ex)
 {
  LMessage.Text = ex.Message;
  }
}
}