Deleting With a GridView/No-Code

Posted on March 15, 2009 by admin

**** When testing, please be considerate and do not delete more than two records. ****

This sample uses the Northwind Customers Table as a sample, using SQL Server. The title of the code sample pretty much tells it all – here, we’re showing how to use the GridView (ASP.Net 2.0 control) to delete files from a database.

Notice that there, technically, is no code required to do this. All that is required is population of a SQLDataSource control and a GridView Control. The Primary Key is noted in two places. In the GridView, it is assigned, using the DataKeyNames Property. In the SQL DataSource control, an extra ‘Delete Parameters’ section is added, and the CustomerID field is assigned to the ‘Name’ property of the ‘ASP:Parameter’.

<html>
<head>
<meta name="GENERATOR" Content="ASP Express 4.5">
<title>Deleting With a GridView</title>
</head>
<body>
<form id="form1" Runat="server">

<asp:GridView id="gvMain"
DataSourceID="SQLDS1"
DataKeyNames="CustomerID"
AutoGenerateDeleteButton="True"
HeaderStyle-BackColor="Blue"
HeaderStyle-foreColor="White"
HeaderStyle-Font-Bold="True"
Backcolor="#8CB2FF"
Font-Name="Arial"
Font-Size="9pt"
Gridlines="Both"
BorderColor="Black"
BorderWidth="1pt"
HeaderStyle-Font-Size="11pt"
AlternatingRowStyle-Backcolor="#E0E0E0"
PagerStyle-Font-Size="8pt"
Runat="Server"/>

<asp:SQLDataSource ID="SQLDS1" Runat="Server"
ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>"
SelectCommand="SELECT CustomerID, CompanyName, ContactName, ContactTitle, Phone From Customers"
DeleteCommand="Delete from Customers where [CustomerID] = @CustomerID">
<DeleteParameters>
<asp:Parameter Name="CustomerID"></asp:Parameter>
</DeleteParameters>
</asp:SQLDataSource>
</form>
</body>
</html>

Setting Focus on a Button

Posted on March 10, 2009 by admin

This shows, technically, in ASP.Net 2.0, how to set the focus on any control, programmatically. However, in this sample, we’re using buttons. By setting focus on a particular button on a page, it can easily by accessed by pressing the ‘ENTER’ key, thereby making it the default button for the page.

<script language="VB" Runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
	button4.focus()
End Sub
Sub do2Focus(sender As Object, e As EventArgs)
	button2.Focus()
	label1.text="You just clicked Button 1 - focus is on the second button"
End Sub

Sub do3Focus(sender As Object, e As EventArgs)
button3.Focus()
label1.text="You just clicked Button 2 – focus is on the third button"
End Sub

Sub do4Focus(sender As Object, e As EventArgs)
button4.Focus()
label1.text="You just clicked Button 3 – focus is on the fourth button"
End Sub

Sub do1Focus(sender As Object, e As EventArgs)
button1.Focus()
label1.text="You just clicked Button 4 – focus is on the first button"
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 4.5">
<title>Setting a Button”s Focus</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:Button id="button1"
Text="Focus Button 2"
onclick="do2Focus" Runat="server" /><br>
<asp:Button id="button2"
Text="Focus Button 3"
onclick="do3Focus"
Runat="server" /><br>
<asp:Button id="button3"
Text="Focus Button 4"
onclick="do4Focus"
Runat="server" /><br>
<asp:Button id="button4"
Text="Focus Button 1"
onclick="do1Focus"
Runat="server" /><br>
<asp:Label ID="label1"
ForeColor="Blue"
Font-Bold="True" Runat="server" />
</form>
</body>
</html>

Editing with DetailsView

Posted on March 5, 2009 by admin

This sample shows (using the Pubs Database), using a Master/Detail view, how to use the new ASP.Net 2.0 DetailsView control to edit a record. One advantage of editing this way, is that the DetailsView control uses up less real estate on the screen.

There are very few items that actually change from our other Master/Detail sample. In order to set up the DetailsView control for editing, all that is needed is the added CommandField section, in the Fields Section, and an Update SQL Statement. Here, we could just as easily have assigned the UpdateCommand and SelectCommand in the SQL DataSource, but, for this sample, it uses less width to do it this way, and also it shows how to assign these statements programmatically.

Also notice, that, in order to update the GridView, after updating with the DetailsView control, there is one extra subroutine, (called ‘DVAfterUpdate’ in this sample). Its only purpose is to update the GridView. Of course we need to reference the sub, using the OnItemUpdated property of the DetailsView, but as you can see, with the new controls of ASP.Net 2.0, updating your database is much easier now!

<script language="VB" Runat="server">
Dim strUpdate as String
Dim strSelect as String
Sub Page_Load(Source as Object, E as EventArgs)
strSelect="SELECT [au_id], [au_lname], [au_fname], [phone], " & _
"[address], [city], [state], [zip], [contract] FROM [authors] " & _
"WHERE ([au_id] = @au_id)"
strUpdate="UPDATE [authors] SET [au_lname]=@au_lname, " & _
"[au_fname]=@au_fname, [phone]=@phone, [address]=@address, " & _
"[city]=@city, [state]=@state, [zip]=@zip, [contract]=@contract " & _
"WHERE [au_id]=@au_id"
SQLDS3.SelectCommand=strSelect
SQLDS3.UpdateCommand=strUpdate
End Sub

Sub DVAfterUpdate(sender As Object, e As DetailsViewUpdatedEventArgs)
gvAuthors.DataBind()
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 4.5">
<title>
Master/Detail Editing with DetailsView
</title>
</head>
<body>
<form id="form1" Runat="server">
<table>
<tr>
<td align="Center" valign="Top">
<asp:GridView Runat="server"
Id="gvAuthors"
GridLines="Both"
cellpadding="0"
cellspacing="0"
Headerstyle-BackColor="#BDCFE7"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="12"
BackColor="#E7EFFF"
Font-Name="Arial"
Font-Size="10"
BorderColor="Black"
DataSourceID="SQLDS2"
DataKeyNames="au_id"
AllowPaging="True"
AllowSorting="True"
AutoGenerateColumns="False">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="au_id" HeaderText="ID" ReadOnly="True" />
<asp:BoundField DataField="au_lname" HeaderText="LName" />
<asp:BoundField DataField="au_fname" HeaderText="Fname"  />
<asp:BoundField DataField="state" HeaderText="State" />
</Columns>
</asp:GridView>
</td>
<td align="Left" valign="Top">
<asp:DetailsView
DataKeyNames="au_id" DataSourceID="SQLDS3"
GridLines="Both"
cellpadding="0"
cellspacing="0"
Headerstyle-BackColor="#BDCFE7"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="12"
BackColor="#E7EFFF"
Font-Name="Arial"
Font-Size="10"
BorderColor="Black"
HeaderText="Author Details" AutoGenerateRows="False"
HeaderStyle-Font-Bold="True"
OnItemUpdated="DVAfterUpdate"
ID="dvPubs" runat="server">
<Fields>
<asp:BoundField DataField="au_id" HeaderText="ID" ReadOnly="True"  />
<asp:BoundField DataField="au_lname" HeaderText="Lname"  />
<asp:BoundField DataField="au_fname" HeaderText="Fname"  />
<asp:BoundField DataField="phone" HeaderText="Phone"  />
<asp:BoundField DataField="address" HeaderText="Address"  />
<asp:BoundField DataField="city" HeaderText="City"  />
<asp:BoundField DataField="state" HeaderText="St"  />
<asp:BoundField DataField="zip" HeaderText="Zip"  />
<asp:CheckBoxField DataField="contract" HeaderText="Contract"  />
<asp:CommandField ShowEditButton="True" />
</Fields>
</asp:DetailsView>
</td>
</tr>
</table>
<asp:SqlDataSource ID="SQLDS2" runat="server"
SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors]"
ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>">
</asp:SqlDataSource>
<asp:SqlDataSource ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>"
ID="SQLDS3" runat="server">
<SelectParameters>
<asp:ControlParameter ControlID="gvAuthors" Name="au_id"
PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>

Edit With a GridView-No Code!

Posted on February 28, 2009 by admin

Here’s another great No-Code sample, showing how to use a GridView to Edit a table in a database. And did I mention that again, this is with absolutely no code?

<html>

	<head>

		<meta name="GENERATOR" Content="ASP Express 4.5">

		<title>

			Editing with a GridView

		</title>

	</head>

	<body>

		<form id="form1" Runat="server">

			<div align="center">

				<asp:GridView Runat="server"

					Id="GVEmployees"

					GridLines="Both"

					cellpadding="0"

					cellspacing="0"

					Headerstyle-BackColor="#BDCFE7"

					Headerstyle-Font-Name="Arial"

					Headerstyle-Font-Size="12"

					BackColor="#E7EFFF"

					Font-Name="Arial"

					Font-Size="10"

					BorderColor="Black"

					DataSourceID="SQLDS1"

					AutoGenerateEditButton="True"

				 	AutoGenerateColumns="True"

					DataKeyNames="EmployeeID">

				</asp:GridView>

			</div>

<asp:SQLDataSource ID="SQLDS1"

	ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>"

		ProviderName="System.Data.SqlClient"

		SelectCommand="Select EmployeeID, FirstName, LastName, Title from Employees" Runat="Server"

		UpdateCommand="UPDATE Employees SET FirstName=@FirstName, LastName=@LastName,Title= @Title WHERE EmployeeID=@EmployeeID">

</asp:SQLDataSource>

		</form>

	</body>

</html>

Basic No-Code GridView

Posted on February 23, 2009 by admin

Sometimes, all you really need to do is to show your data (received from a database, etc) on a page – that’s all. With ASP.Net
2.0, it is extremely easy now. All you need is a GridView control and a DataSource Control. The DataSource control’s properties
get populated with all the connection information, and the DataSourceID property of the DataGrid references the DataSource
Control. There is absolutely no code necessary. From that point on, you can get as complicated as you like. This particular sample uses a SQLDataSourceControl, pointing to the Customers Table of the Northwind Database.

For more basic information about how the DataSource controls work, Click Here
For more basic information about how GridView (successor to the DataGrid) works, Click Here

<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.5">
		<title>Basic No-Code GridView</title>
</head>
	<body>
		<form id="form1" Runat="server">
			<div align="center">
			<asp:Label ID="label1" ForeColor="Blue" Font-Bold="True" Runat="server" />
				<asp:GridView Runat="server"
					Id="gvPublishers"
					GridLines="Both"
					cellpadding="0"
					cellspacing="0"
					Headerstyle-BackColor="#BDCFE7"
					Headerstyle-Font-Name="Arial"
					Headerstyle-Font-Size="12"
					BackColor="#E7EFFF"
					Font-Name="Arial"
					Font-Size="10"
					BorderColor="Black"
					DataSourceID="SQLDS1"
					AutogenerateColumns="True">
				</asp:GridView>
			</div>
		<asp:SQLDataSource ID="SQLDS1"
			ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>"
  			SelectCommand="Select Top 10 CustomerID, CompanyName, ContactName from Customers" Runat="Server">
  		</asp:SQLDataSource>
		</form>
	</body>
</html>

Conditional GridView Cell Coloring

Posted on February 18, 2009 by admin

Conditional GridView Cell Coloring is much like it is/was with the DataGrid, with a few differences, like OnRowDataBound, etc. This code sample shows the simple way in which it is accomplished

<%@ Import Namespace="System.Drawing" %>
<script language="VB" Runat="server">
    Sub doColor(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
         If e.Row.RowType = DataControlRowType.DataRow Then
            If e.Row.Cells(2).Text.Trim = "Sales Representative" Then
                e.Row.Cells(2).BackColor = Color.LightGreen
            End If
         End If
    End Sub
</script>
<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.5">
		<title>Conditional GridView Cell Coloring</title>
	</head>
	<body>
		<form id="form1" Runat="server">
		<asp:GridView OnRowDataBound="doColor"
			AutoGenerateColumns="False"
			DataSourceID="sqlds1"
			ID="GridView1" Runat="Server"
			HeaderStyle-Backcolor="Blue"
			HeaderStyle-ForeColor="White"
			HeaderStyle-Font-Bold="True">
		            <Columns>
		                <asp:BoundField DataField="FirstName" HeaderText="First" />
		                <asp:BoundField DataField="LastName" HeaderText="Last"  />
		                <asp:BoundField DataField="Title" HeaderText="Title" />
		                <asp:BoundField DataField="Address" HeaderText="Address" />
		                <asp:BoundField DataField="City" HeaderText="City" />
		                <asp:BoundField DataField="Region" HeaderText="Region" />
		                <asp:BoundField DataField="PostalCode" HeaderText="PostalCode" />
		                <asp:BoundField DataField="country" HeaderText="country" />
		            </Columns>
		</asp:GridView>
<asp:SQLDataSource ID="sqlds1"
	ConnectionString="<%$ ConnectionStrings:YourConnString %>"
	SelectCommand="Select FirstName, LastName, Title, Address, City, Region, PostalCode, country from Employees"
	Runat="Server"></asp:SQLDataSource>
		</form>
	</body>
</html>

Master/Detail With GridView and DetailsView

Posted on February 13, 2009 by admin

This sample shows the relatively easy way, with ASP.Net 20, to create a Master/Detail page using the new GridView and DetailsView controls.

Notice, that for each of the 3 controls on the page, we use a separate DataSource, for ease of understanding. Each DataSource control happens to be using the same connectionstring, based in the web.config file, connecting to the Pubs database (SQL Server, in this case).

Select a state in the Dropdownlist. With the state selected, the GridView will populate with all the authors from the selected state. Notice that this is accomplished via the ControlID attribute in the ControlParameter inside the SQLDataSource control. We assign it the DropDownList (ddlStates) and set the PropertyName of the DropDownList (SelectedValue).

The same is done, when clicking the Select button in the Gridview. In the DataSource control for the DetailsView, we assign the ControlID to the GridView (gvAuthors), tell it which field to use, and assign the PropertyName to the SelectedValue of the GridView.

All in all, this is fairly straightforward, and done with absolutely NO code!

<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.5">
		<title>Master Detail With GridView and DetailsView</title>
	</head>
	<body>
		<form id="form1" Runat="server">
			Choose a State:<br />
			<asp:DropDownList
				id="ddlStates" Runat="server"
				DataSourceID="SQLDS1" AutoPostBack="true"
			      DataTextField="State">
			</asp:DropDownList>
			<table>
				<tr>
					<td align="Center" valign="Top">
						<asp:GridView Runat="server"
							Id="gvAuthors"
							GridLines="Both"
							cellpadding="0"
							cellspacing="0"
							Headerstyle-BackColor="#BDCFE7"
							Headerstyle-Font-Name="Arial"
							Headerstyle-Font-Size="12"
							BackColor="#E7EFFF"
							Font-Name="Arial"
							Font-Size="10"
							BorderColor="Black"
							DataSourceID="SQLDS2"
							DataKeyNames="au_id"
							AutoGenerateColumns="False">
						            <Columns>
						              <asp:CommandField ShowSelectButton="True" />
						              <asp:BoundField DataField="au_id" HeaderText="au_id" ReadOnly="True" />
						              <asp:BoundField DataField="au_lname" HeaderText="au_lname" />
						              <asp:BoundField DataField="au_fname" HeaderText="au_fname"  />
						              <asp:BoundField DataField="state" HeaderText="state" />
						            </Columns>
							</asp:GridView>
					</td>
					<td align="Left" valign="Top">
					<asp:DetailsView DataKeyNames="au_id"
						DataSourceID="SQLDS3"
						GridLines="Both"
						cellpadding="0"
						cellspacing="0"
						Headerstyle-BackColor="#BDCFE7"
						Headerstyle-Font-Name="Arial"
						Headerstyle-Font-Size="12"
						BackColor="#E7EFFF"
						Font-Name="Arial"
						Font-Size="10"
						BorderColor="Black"
						HeaderText="Author Details" AutoGenerateRows="False"
						ID="dvPubs" runat="server">
						<Fields>
							<asp:BoundField DataField="au_id" HeaderText="au_id" ReadOnly="True"  />
							<asp:BoundField DataField="au_lname" HeaderText="au_lname"  />
							<asp:BoundField DataField="au_fname" HeaderText="au_fname"  />
							<asp:BoundField DataField="phone" HeaderText="phone"  />
							<asp:BoundField DataField="address" HeaderText="address"  />
							<asp:BoundField DataField="city" HeaderText="city"  />
							<asp:BoundField DataField="state" HeaderText="state"  />
							<asp:BoundField DataField="zip" HeaderText="zip"  />
							<asp:CheckBoxField DataField="contract" HeaderText="contract"  />
						</Fields>
					</asp:DetailsView>
					</td>
				</tr>
			</table>

			<asp:SQLDataSource
				ID="SQLDS1"
				ConnectionString="<%$ ConnectionStrings:Pubs %>"
			  	ProviderName="System.Data.SqlClient"
			  	SelectCommand="Select Distinct State from Authors"
				Runat="Server">
			</asp:SQLDataSource>

			<asp:SqlDataSource ID="SQLDS2" runat="server"
				SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [Authors] WHERE ([state] = @state)"
			ConnectionString="<%$ ConnectionStrings:Pubs20 %>">
				<SelectParameters>
				<asp:ControlParameter
					ControlID="ddlStates"
					Name="state"
					PropertyName="SelectedValue"
					Type="String" />
				</SelectParameters>
			</asp:SqlDataSource>

			<asp:SqlDataSource
				ConnectionString="<%$ ConnectionStrings:Pubs20 %>" ID="SQLDS3"
				runat="server"
				SelectCommand="SELECT (FieldList) FROM [Authors] WHERE ([au_id] = @au_id)">
				<SelectParameters>
				<asp:ControlParameter ControlID="gvAuthors" Name="au_id"
					PropertyName="SelectedValue"
					Type="String" />
				</SelectParameters>
			</asp:SqlDataSource>
		</form>
	</body>
</html>

Paging/Sorting with a GridView

Posted on February 8, 2009 by admin

We’ve come SUCH a long way since the DataGrid, and even though the DataGrid, at the time was better than what we had before, now there’s the GridView – what we all wished the DataGrid had always been!

When MS says Paging and Sorting is built in – this time, they really mean it. What you see in this sample is two declarative controls on the page – that’s all – no code whatsoever.

Also look at the way you can control which columns get sorting capabilities. To keep any column(s) from being sorted, just leave out the SortExpression property in the BoundField for that column – that’s all there is to it!

Also notice, that for each column, the second time you click on the header link text, the order changes (back and forth between Ascending and Descending), with no extra code.

<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.5">
		<title>Paging and Sorting With a GridView</title>
</head>
	<body>
		<form id="form1" Runat="server">
			<div align="center">
			<asp:Label ID="label1" ForeColor="Blue" Font-Bold="True" Runat="server" />
				<asp:GridView Runat="server"
					Id="gvPublishers"
					GridLines="Both"
					cellpadding="0"
					cellspacing="0"
					Headerstyle-BackColor="#BDCFE7"
					Headerstyle-Font-Name="Arial"
					Headerstyle-Font-Size="12"
					BackColor="#E7EFFF"
					Font-Name="Arial"
					Font-Size="10"
					BorderColor="Black"
					DataSourceID="SQLDS1"
					AllowPaging="True"
					AllowSorting="True"
					AutogenerateColumns="False">
					<Columns>
						<asp:BoundField DataField="CustomerID"
							SortExpression="CustomerID"
							ItemStyle-HorizontalAlign="Center"
							HeaderText="ID">
						</asp:BoundField>
						<asp:BoundField DataField="CompanyName"
							SortExpression="CompanyName"
							ItemStyle-HorizontalAlign="Center"
							HeaderText="Company">
						</asp:BoundField>
						<asp:BoundField DataField="ContactName"
							SortExpression="ContactName"
							ItemStyle-HorizontalAlign="Center"
							HeaderText="Contact">
						</asp:BoundField>
						<asp:BoundField DataField="City"
							SortExpression="City"
							ItemStyle-HorizontalAlign="Center"
							HeaderText="City">
						</asp:BoundField>
						<asp:BoundField DataField="PostalCode"
							ItemStyle-HorizontalAlign="Center"
							HeaderText="Postal Code">
						</asp:BoundField>
					</Columns>
				</asp:GridView>
			</div>
		<asp:SQLDataSource ID="SQLDS1"
			ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>"
  			ProviderName="System.Data.SqlClient"
  			SelectCommand="Select (FieldList) from Customers" Runat="Server">
  		</asp:SQLDataSource>
		</form>
	</body>
</html>

Using Checkboxes in a GridView

Posted on February 3, 2009 by admin

This sample is based on the DataGrid sample with checkboxes, changing/updating the necessary items to allow it to work with a Gridview, since quite a bit of the code, etc is still the same.

<script language="VB" Runat="server">
dim ordList as String=""
Sub doSelectAll(Source as Object, E as EventArgs)
        Dim dgItem As GridViewRow
        Dim chkGView As System.Web.UI.WebControls.CheckBox
            For Each dgItem In gvPublishers.Rows
                chkGView = dgItem.FindControl("chk1")
                chkGView.Checked = "True"
            Next
End Sub

Sub doClearAll(Source as Object, E as EventArgs)
        Dim dgItem As GridViewRow
        Dim chkGView As System.Web.UI.WebControls.CheckBox
            For Each dgItem In gvPublishers.Rows
                chkGView = dgItem.FindControl("chk1")
                chkGView.Checked = "False"
            Next
            label1.text=""
End Sub

Sub GetAuthorIDs(Source as Object, E as EventArgs)
Dim i As Integer

For i = 0 To gvPublishers.Rows.Count - 1
   Dim dgItem As GridViewRow = gvPublishers.Rows(i)
   Dim cb As CheckBox = CType(dgItem.FindControl("chk1"), CheckBox)
   If Not (cb Is Nothing) And cb.Checked Then
   	ordList+=dgItem.Cells(2).Text & ","
   else
   	label1.text="Nothing is checked"
   End If
Next i
label1.text=KillEnding(Trim(ordList))
End Sub

Function KillEnding(sItem as String)
	if sItem.Length>0 then Return sItem.Substring(0,sItem.Length-1)
End Function
</script>
<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.5">
		<title>Using Checkboxes with a GridView</title>
</head>
	<body>
		<form id="form1" Runat="server">
			<div align="center">
			<asp:Label ID="label1" ForeColor="Blue" Font-Bold="True" Runat="server" />
				<asp:GridView Runat="server"
					Id="gvPublishers"
					GridLines="Both"
					cellpadding="0"
					cellspacing="0"
					Headerstyle-BackColor="#BDCFE7"
					Headerstyle-Font-Name="Arial"
					Headerstyle-Font-Size="8"
					BackColor="#E7EFFF"
					Font-Name="Arial"
					Font-Size="8"
					BorderColor="Black"
					DataSourceID="SQLDS1"
					AutogenerateColumns="False">
					<Columns>
						<asp:TemplateField HeaderText="Select">
							<ItemTemplate>
								<asp:Checkbox id="chk1" Runat="server" />
							</ItemTemplate>
						</asp:TemplateField>
						<asp:BoundField DataField="au_id" HeaderText="Author ID">
						</asp:BoundField>
						<asp:BoundField DataField="FullName" HeaderText="Author Name">
						</asp:BoundField>
					</Columns>
				</asp:GridView>
				<asp:Button id="btnSelectAll"
					Text="Select All" onclick="doSelectAll" Runat="server" />
				<asp:Button id="btnClearAll"
					Text="Clear All" onclick="doClearAll" Runat="server" />&nbsp;
				<asp:Button id="btnAuthorIDs"
					Text="Get Authors" onclick="GetAuthorIDs" Runat="server" />
			</div>
		<asp:SQLDataSource ID="SQLDS1"
			ConnectionString="<%$ ConnectionStrings:YourConnString %>"
  			ProviderName="System.Data.SqlClient"
  			SelectCommand="Select Top 10 au_id, au_fname + '' '' + au_lname as FullName from Authors" Runat="Server">
  </asp:SQLDataSource>
		</form>
	</body>
</html>

DropDownList Inside a DataGrid

Posted on January 29, 2009 by admin

This sample shows how to place a DropDownList inside a Datagrid, for editing purposes, along with different ways to refer to items/cells inside the DataGrid. It also shows how to maintain the current selection in the DropDownList when the Edit mode is initiated. This is handled by the OnItemDatabound event. In the DataGrid tag itself, refer to the event handler like this:
OnItemDataBound=”dg_ItemDataBound”

Look inside the ‘dg_ItemDataBound’ event for the code that handles it.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" Runat="server">
Dim strConn as string = ConfigurationSettings.AppSettings("YourConnectionString")
Dim dsStates as DataSet=New DataSet()
Dim ds2 as DataSet=New DataSet()
dim strState as String
Sub EmpGrid_EditCommand(s As Object, e As DataGridCommandEventArgs )
	EmpGrid.EditItemIndex = e.Item.ItemIndex
	BindData
End Sub

Sub EmpGrid_Cancel(Source As Object, e As DataGridCommandEventArgs)
	EmpGrid.EditItemIndex = -1
	BindData()
End Sub

Sub EmpGrid_UpdateCommand(s As Object, e As DataGridCommandEventArgs)
	Dim con As SqlConnection
	Dim cmd As SqlCommand
	Dim txtID As String = E.Item.cells(1).Text
	Dim txtFirstName As textbox = E.Item.cells(2).Controls(0)
	Dim txtLastName As textbox = E.Item.cells(3).Controls(0)
	Dim txtAddress As textbox = E.Item.cells(4).Controls(0)
	Dim txtCity As textbox = E.Item.cells(5).Controls(0)
	Dim sRegion As String = CType(e.Item.FindControl("ddlStates"), DropDownList).SelectedItem.Text
	Dim strUpdateStmt As String
	strUpdateStmt ="UPDATE NWEmployees SET [LastName]=@LastName,[FirstName]=@FirstName," & _
	"[Address]=@Address, [City]=@City,[Region]=@Region WHERE EmployeeID=@EmpID"
	con = New SqlConnection(strConn)
	cmd = New SqlCommand(strUpdateStmt, con)
	cmd.Parameters.Add(New SQLParameter("@FirstName", txtFirstName.Text))
	cmd.Parameters.Add(New SQLParameter("@LastName", txtLastName.Text))
	cmd.Parameters.Add(New SQLParameter("@Address", txtAddress.Text))
	cmd.Parameters.Add(New SQLParameter("@City", txtcity.Text))
	cmd.Parameters.Add(New SQLParameter("@Region", sRegion))
	cmd.Parameters.Add(New SQLParameter("@Empid", EmpGrid.DataKeys(e.Item.ItemIndex)))
	con.Open()
	cmd.ExecuteNonQuery()
	EmpGrid.EditItemIndex = -1
	BindData
End Sub

Sub Page_Load(Source as Object, E as EventArgs)
	if not Page.IsPostBack then
		BindData
	end if
End Sub

Sub BindData()
	PopulateGrid
End Sub

Function PopulateDDL()
	Dim mySQL as string = "Select [states].[STabbr], [states].[STname] from states order by STabbr"
	Dim con as New SQLConnection(strConn)
	Dim Cmd as New SQLDataAdapter(mySQL,con)
	Cmd.Fill(dsStates,"states")
	Return dsStates
End Function

Sub PopulateGrid
	Dim mySQL as string = "Select [EmployeeID], [LastName], " & _
	"[FirstName], [Title], [Address], " & _
	"[City], [Region], [PostalCode] from NWEmployees"
	Dim con as New SQLConnection(strConn)
	Dim Cmd as New SQLDataAdapter(mySQL,con)
	Cmd.Fill(ds2,"NWEmployees")
	EmpGrid.Datasource=ds2.Tables("NWEmployees").DefaultView
	EmpGrid.DataBind()
End Sub

Private Sub dg_ItemDataBound(ByVal sender As Object, _
      ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs)
   If e.Item.ItemType = ListItemType.EditItem Then
      Dim drv As DataRowView = CType(e.Item.DataItem, DataRowView)
      Dim currentRegion As String = CType(drv("Region"), String)
      Dim ddl As DropDownList
      ddl = CType(e.Item.FindControl("ddlStates"), DropDownList)
      ddl.SelectedIndex = ddl.Items.IndexOf(ddl.Items.FindByText(currentRegion))
   End If
End Sub

</script>
<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.5b1">
		<title>DropDownList Inside a DataGrid</title>
	</head>
	<body>
		<form id="form1" Runat="server">
			<div align="center">
				<asp:Datagrid Runat="server"
					Id="EmpGrid"
					GridLines="None"
					cellpadding="0"
					cellspacing="2"
					Headerstyle-BackColor="#7988B7"
					Headerstyle-Forecolor="#FFFFFF"
					Headerstyle-Font-Name="Arial"
					Headerstyle-Font-Bold="True"
					Headerstyle-Font-Size="12"
					BackColor="#E0E0F6"
					Font-Name="Arial"
					Font-Size="10"
					AlternatingItemStyle-BackColor="#DFDFDF"
					AlternatingItemStyle-Font-Name="Arial"
					AlternatingItemStyle-Font-Size="10"
					BorderColor="Black"
					BorderStyle="Solid"
					BorderWidth="1px"
					Width="75%"
					OnItemDataBound="dg_ItemDataBound"
					AutogenerateColumns="False"
					OnEditcommand="EmpGrid_EditCommand"
					OnCancelcommand="EmpGrid_Cancel"
					OnUpdateCommand="EmpGrid_UpdateCommand"
					DataKeyField="Employeeid">
					<Columns>
						<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="Update" CancelText="Cancel"
							EditText="Edit" HeaderText="Edit"></asp:EditCommandColumn>
						<asp:BoundColumn DataField="EmployeeID" HeaderText="ID" ReadOnly="True"></asp:BoundColumn>
						<asp:BoundColumn DataField="FirstName" HeaderText="FirstName"></asp:BoundColumn>
						<asp:BoundColumn DataField="LastName" HeaderText="LastName"></asp:BoundColumn>
						<asp:BoundColumn DataField="Address" HeaderText="Address"></asp:BoundColumn>
						<asp:BoundColumn DataField="City" HeaderText="City"></asp:BoundColumn>
						<asp:TemplateColumn HeaderText="State">
						   <ItemTemplate>
							      <asp:Label id=Label3 runat="server"
							          Text=''<%# Container.DataItem("Region") %>''>
							      </asp:Label>
						   </ItemTemplate>
						   <EditItemTemplate>
						      <asp:DropDownList id=ddlStates runat="server"
						         DataSource="<%# PopulateDDL %>" DataMember="States"
						         DataTextField="STabbr" DataValueField="STabbr">
						      </asp:DropDownList>
						   </EditItemTemplate>
						</asp:TemplateColumn>
					</Columns>
				</asp:DataGrid>
			</div>
		</form>
	</body>
</html>

ASP.NET 101

ASP.NET and MVC Tutorials

Category Archives: Code Samples