Nitin Sharma's Opus

Wednesday, May 6, 2009

Implement Ajax AutoComplete Extender inside Gridview

Hi All,

This Post implements enabling AJAX Autocomplete Extender while Editing Gridview Control in ASP.NET.Do change Connection String as of yours...

So...here it is.....

<asp:ScriptManager ID="ScriptManager1" runat="server">

<asp:ServiceReference Path="~/AutoComplete.asmx" />

Services>

asp:ScriptManager>

<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None"

AutoGenerateColumns="False" OnRowEditing="GridView1_RowEditing">

<asp:CommandField ShowEditButton="True" />

<asp:TemplateField HeaderText="Age" SortExpression="ID">

<asp:Label ID="lblID" runat="server" Text=''>asp:Label>

ItemTemplate>

<asp:Label ID="lblID" runat="server" Text=''>asp:Label>

EditItemTemplate>

asp:TemplateField>

<asp:TemplateField HeaderText="Name" SortExpression="Name">

<asp:Label ID="lblName" runat="server" Text=''>asp:Label>

ItemTemplate>

<asp:TextBox ID="txtName" runat="server" Text=''>asp:TextBox>

<ajaxToolkit:AutoCompleteExtender runat="server" ID="autoComplete1" TargetControlID="txtName"

ServicePath="AutoComplete.asmx" ServiceMethod="GetCompletionList" MinimumPrefixLength="1" CompletionInterval="10" EnableCaching="true" CompletionSetCount="12" />

EditItemTemplate>

asp:TemplateField>

<asp:TemplateField HeaderText="Location" SortExpression="Location">

<asp:Label ID="lblLocation" runat="server" Text=''>asp:Label>

ItemTemplate>

<asp:Label ID="lblLocation" runat="server" Text=''>asp:Label>

EditItemTemplate>

asp:TemplateField>

Columns>

asp:GridView>

WebService Code

[WebMethod]

public DataTable GetRecords(string strName)

{

string strConn = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;

SqlConnection con = new SqlConnection(strConn);

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

cmd.CommandType = System.Data.CommandType.Text;

cmd.Parameters.AddWithValue("@Name", strName);

cmd.CommandText = "Select Name from Emp where Name like '%'+@Name+'%'";

DataSet objDs = new DataSet();

SqlDataAdapter dAdapter = new SqlDataAdapter();

dAdapter.SelectCommand = cmd;

con.Open();

dAdapter.Fill(objDs);

con.Close();

return objDs.Tables[0];

}

[WebMethod]

public string[] GetCompletionList(string prefixText, int count)

{

if (count == 0)

{

count = 10;

}

DataTable dt = GetRecords(prefixText);

List<string> items = new List<string>(count);

for (int i = 0; i <>

{

string strName = dt.Rows[i][0].ToString();

items.Add(strName);

}

return items.ToArray();

}

ASPX.CS Code

protected void Page_Load(object sender, EventArgs e)

{

Bind();

}

public void Bind()

{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["TestConnectionString"].ToString());

conn.Open();

SqlCommand cmd = new SqlCommand("Select * from EMP", conn);

DataTable dt = new DataTable();

SqlDataAdapter da = new SqlDataAdapter();

da.SelectCommand = cmd;

da.Fill(dt);

GridView1.DataSource = dt;

GridView1.DataBind();

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

GridView1.EditIndex = e.NewEditIndex;

GridView1.DataBind();

}

Tuesday, May 5, 2009

SQL SERVER CURSOR

SQL Server cursors are database objects used to manipulate data in a set on a row-by-row basis. You can fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language. Before you can use a cursor, you need to declare it.

A Simple Example of CURSOR

USE [Test]

/****** Object: StoredProcedure [dbo].[ResetSlNoInPart] Script Date: 05/05/2009 10:59:14 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

Create PROCEDURE [dbo].[ResetSlNoInPart]

(

@Filter_AC int -- For which AC to perform action

)

BEGIN

DECLARE

@ID int, -- To identify record in which SLNOINPART is to be updated

@AC int, -- AC

@Part int, -- Part

@OldAC int, -- Old AC (for comparison in loop)

@OldPart int, -- Old Part (for comparison in loop)

@SerialNo int -- Serial No.

-- Create a new cursor that gets the ID, AC and Part for selected AC ordered by requirements

DECLARE SerialLoop CURSOR FOR

SELECT id, ac_no, part_no

FROM E_Draft_2

WHERE ac_no=@Filter_AC

ORDER BY ac_no ASC, part_no ASC, section_no ASC, house_no_en ASC, age DESC

-- Initialize the comparison variables

SET @OldAC=0

SET @OldPart=0

-- Open and start the loop

OPEN SerialLoop

FETCH NEXT FROM SerialLoop

INTO @ID, @AC, @Part

-- Repeat the loop till records exist

WHILE @@FETCH_STATUS=0

BEGIN

-- Compare and see whether Part or AC have changed

IF (@OldAC<>@AC) OR (@OldPart<>@Part)

BEGIN

-- If AC or Part changed, reset serial to 1 and update the comparison varialbes

SET @SerialNo=1

SET @OldAc=@AC

SET @OldPart=@Part

END

-- Update the current record with whatever serial no is current

UPDATE E_DRAFT_2

SET SLNoInPart=@SerialNo

WHERE ID=@ID

-- Debug print

PRINT 'AC='+Convert(varchar, @ac)+', part='+Convert(varchar, @part)+', sl='+Convert(varchar,@serialno)

-- Increment Serial no

SET @SerialNo=@SerialNo+1

-- Move to next record

FETCH NEXT FROM SerialLoop

INTO @ID, @AC, @Part

END

-- Cleanup

CLOSE SerialLoop

DEALLOCATE SerialLoop

END

Thanks,
Nitin Sharma