Creating a Datatable Manually

This sample shows how to create a datatable manually, adding files from a directory (using System.IO), and then binding the datatable to a Gridview

<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<script language="VB" Runat="server">
    Dim dt As DataTable
    Dim dr As DataRow
	Sub Page_Load(Source as Object, E as EventArgs)
        dt = GetFiles()
        gvFiles.DataSource = dt
        gvFiles.DataBind()
	End Sub
    Public Function GetFiles() As DataTable
        Dim strFilePath = Server.MapPath("\test\")
        Dim DirInfo As New DirectoryInfo(strFilePath)
        Dim Files As FileInfo() = DirInfo.GetFiles()

        Dim myTable As New DataTable
        myTable.Columns.Add("File Name", Type.GetType("System.String"))
        myTable.Columns.Add("Last Write Time", Type.GetType("System.String"))
        Dim i As Integer
        For i = 0 To Files.Length - 1
            Dim Filename As String = Files(i).Name
            Dim sWrite As String = Files(i).LastWriteTime
            Dim myrow As DataRow
            ' create new row
            myrow = myTable.NewRow
            ' add files/write times into cells
            myrow("File Name") = Filename
            myrow("Last Write Time") = sWrite
            myTable.Rows.Add(myrow)
        Next
        Return myTable
    End Function
</script>
<html>
	<head runat="server">
		<meta name="GENERATOR" Content="ASP Express 5.0">
		<title>Untitled</title>
	</head>
	<body>
        <asp:GridView ID="gvFiles" runat="server" Width="432px">
        </asp:GridView>
		</form>
	</body>
</html>

Gridview Columns – Visibility

The main point of this sample is showing the way to programmatically (during runtime) make a Gridview column visible or invisible. One thing to remember, is that the numbering of columns are zero-based, so, a Gridview with 11 columns are numbered 0-10.

One other item shown here, is the CommandArguments property of ASP.Net buttons. With only one click event, you can program the click events of many buttons, based on the CommandArguments (new with ASP.Net v2.0). Also, using the FindControl method, you can set the ids of your buttons similarly, with numbers, and easily use concatenation to find the button and make it either enabled or disabled, based on the visibility of the represented row.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" Runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
BindGrid
End Sub
Sub BindGrid()
Dim strConn As String = ConfigurationManager.ConnectionStrings("Northwind").ConnectionString
Dim MySQL As String = "Select * from Employees"
Dim MyConn As New SQLConnection(strConn)
Dim ds As DataSet = New DataSet()
Dim Cmd As New SQLDataAdapter(MySQL, MyConn)
Cmd.Fill(ds, "Employees")
GridView1.DataSource = ds.Tables("Employees").DefaultView
GridView1.DataBind()
End Sub

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim num As String
With GridView1
If sender.id = "Button0" Or sender.id = "Button1" Or sender.id = "Button2" _
Or sender.id = "Button3" Or sender.id = "Button4" Or sender.id = "Button5" _
Or sender.id = "Button6" or sender.ID="Button7"  or sender.ID="Button8"  _
or sender.ID="Button9" or sender.ID="Button10"   Then
If sender.enabled = True Then
GridView1.Columns(sender.commandargument).Visible = False
sender.enabled = False
num = sender.id.substring(6)
num = num + 11
Session("num") = "Disabled"
Dim btn As Button = CType(FindControl("Button" & num), Button)
btn.Enabled = True
End If
Else
If sender.enabled = True Then
num = sender.commandargument
num = num - 11
GridView1.Columns(num).Visible = True
BindGrid()
sender.enabled = False
Dim btn As Button = CType(FindControl("Button" & num), Button)
btn.Enabled = True
End If
End If
End With
End Sub
</script>
<html>
<head id="hdr" runat="server">
<meta name="GENERATOR" Content="ASP Express 5.0">
<title>Gridview Columns - Visibility</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:GridView ID="GridView1" DataKeyNames="EmployeeID"
AutogenerateColumns="False"  Runat="Server">
<Columns>
<asp:BoundField DataField="LastName" HeaderText="LastName"/>
<asp:BoundField DataField="FirstName" HeaderText="FirstName"/>
<asp:BoundField DataField="Title" HeaderText="Title"/>
<asp:BoundField DataField="BirthDate" DataFormatString="{0:d}" HtmlEncode="False" HeaderText="BirthDate"/>
<asp:BoundField DataField="HireDate" DataFormatString="{0:d}" HtmlEncode="False" HeaderText="HireDate"/>
<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"/>
<asp:BoundField DataField="HomePhone" HeaderText="HomePhone"/>
</Columns>
</asp:GridView>
<span style="font-family:Courier">Column to Make Invisible:
<asp:Button ID="Button0" runat="server" OnClick="Button1_Click" CommandArgument="0" Text="0" />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" CommandArgument="1" Text="1" />
<asp:Button ID="Button2" runat="server" OnClick="Button1_Click" CommandArgument="2" Text="2" />
<asp:Button ID="Button3" runat="server" OnClick="Button1_Click" CommandArgument="3" Text="3" />
<asp:Button ID="Button4" runat="server" OnClick="Button1_Click" CommandArgument="4" Text="4" />
<asp:Button ID="Button5" runat="server" OnClick="Button1_Click" CommandArgument="5" Text="5" />
<asp:Button ID="Button6" runat="server" OnClick="Button1_Click" CommandArgument="6" Text="6" />
<asp:Button ID="Button7" runat="server" OnClick="Button1_Click" CommandArgument="7" Text="7" />
<asp:Button ID="Button8" runat="server" OnClick="Button1_Click" CommandArgument="8" Text="8" />
<asp:Button ID="Button9" runat="server" OnClick="Button1_Click" CommandArgument="9" Text="9" />
<asp:Button ID="Button10" runat="server" OnClick="Button1_Click" CommandArgument="10" Text="10" />
<br>
<span style="font-family:Courier">Column to Make Visible: &nbsp;</span>
<asp:Button ID="Button11" runat="server" OnClick="Button1_Click" CommandArgument="11" Text="0" Enabled="False" />
<asp:Button ID="Button12" runat="server" OnClick="Button1_Click" CommandArgument="12" Text="1" Enabled="False" />
<asp:Button ID="Button13" runat="server" OnClick="Button1_Click" CommandArgument="13" Text="2" Enabled="False" />
<asp:Button ID="Button14" runat="server" OnClick="Button1_Click" CommandArgument="14" Text="3" Enabled="False" />
<asp:Button ID="Button15" runat="server" OnClick="Button1_Click" CommandArgument="15" Text="4" Enabled="False" />
<asp:Button ID="Button16" runat="server" OnClick="Button1_Click" CommandArgument="16" Text="5" Enabled="False" />
<asp:Button ID="Button17" runat="server" OnClick="Button1_Click" CommandArgument="17" Text="6" Enabled="False" />
<asp:Button ID="Button18" runat="server" OnClick="Button1_Click" CommandArgument="18" Text="7" Enabled="False" />
<asp:Button ID="Button19" runat="server" OnClick="Button1_Click" CommandArgument="19" Text="8" Enabled="False" />
<asp:Button ID="Button20" runat="server" OnClick="Button1_Click" CommandArgument="20" Text="9" Enabled="False" />
<asp:Button ID="Button21" runat="server" OnClick="Button1_Click" CommandArgument="21" Text="10" Enabled="False" />
</form>
</body>
</html>

Search With a DropDownlist – 2.0

This sample shows a non-code way to use a Dropdownlist for a search. It populates with the Categories table of the Northwind Database, so to use this code, as presented, you’ll need to create a connectionString entry in your Web.config called ‘NorthwindConnectionString’.

As you will see, by choosing a category in the DropDownlist, the Gridview is then populated using the selected item from the Dropdownlist.

<%@ Page Language="VB" %>
<head runat="server">
    <title>Search Products by Category</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DropDownList ID="ddlCategories" runat="server" DataSourceID="dsCategories" DataTextField="CategoryName"
            DataValueField="CategoryID">
        </asp:DropDownList>
        <br />
        <asp:GridView ID="gv1" runat="server" AutoGenerateColumns="False" DataKeyNames="ProductID"
            DataSourceID="dsProducts">
            <Columns>
                <asp:BoundField DataField="ProductID" HeaderText="ProductID" InsertVisible="False"
                    ReadOnly="True" SortExpression="ProductID" />
                <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
            </Columns>
        </asp:GridView>
         <asp:SqlDataSource ID="dsCategories" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT [CategoryID], [CategoryName] FROM [Categories] ORDER BY [CategoryName]">
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="dsProducts" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
            SelectCommand="SELECT [ProductID], [ProductName] FROM [Products] WHERE ([CategoryID] = @CategoryID) ORDER BY [ProductName]">
            <SelectParameters>
                <asp:ControlParameter ControlID="ddlCategories" Name="CategoryID" PropertyName="SelectedValue"
                    Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>

Different Text Colors in DDL and Listbox

This sample shows how to use CSS to create conditional item coloring with DropDownLists and ListBoxes (ASP.Net controls). As you see, in order to show the code here, with a DropDownList and a Listbox, some code lines are doubled. Just use whichever one applies for your useage.

(Many thanks to Juan Llibre, ASP.Net MVP)

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" Runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim conn as SqlConnection
conn = New SqlConnection(ConfigurationManager.ConnectionStrings("YourCnString").ConnectionString)
Dim da as SqlDataAdapter
Dim ds as DataSet
da = New SqlDataAdapter("Select * from Products ", conn)
ds = New DataSet()
da.Fill(ds, "Products")
Dim i as Integer
For i = 0 To ds.Tables(0).Rows.Count - 1
ddl1.Items.Add(New ListItem(String.Format("{0:c}",ds.Tables(0).Rows(i)("UnitPrice")), ds.Tables(0).Rows(i)("ProductID")))
List1.Items.Add(New ListItem(String.Format("{0:c}",ds.Tables(0).Rows(i)("UnitPrice")), ds.Tables(0).Rows(i)("ProductID")))
If ds.Tables(0).Rows(i)("UnitPrice") <= 20 Then
ddl1.Items(i).Attributes.Add("style", "color:red")
List1.Items(i).Attributes.Add("style", "color:red")
Else
ddl1.Items(i).Attributes.Add("style", "color:green")
List1.Items(i).Attributes.Add("style", "color:green")
End If
Next
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 5.0">
<title>Untitled</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:DropDownList ID="DDL1" Runat="Server" ></asp:DropDownList>
<asp:ListBox ID="List1" Runat="Server" ></asp:ListBox>
</form>
</body>
</html>

RSS Feed With Gridview and Paging

In this sample, we simply use an XMLDataSource control, to point to an RSS feed, and then add an ItemTemplate, along with an Alternating ItemTemplate in a GridView, to view an RSS feed. The neat thing here, is that, with a Gridview, you can use Paging, in case the number of actual records returned is too long.

<html>
<head>
<meta name="GENERATOR" Content="ASP Express 5.0">
<title>Untitled</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:GridView ID="bank" Runat="server" DataSourceID="xmlBanking"
CellPadding="2" ForeColor="Black" AllowPaging="True" PageSize="5">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="Label1" Runat="server" Text='<%# XPath("pubDate") %>' ForeColor="gray" Font-Bold="True"
Font-Names="Verdana" Font-Size="XX-Small">
</asp:Label>
<br />
<asp:HyperLink ID="HyperLink1" Runat="server" Text='<%# XPath("title") %>' NavigateUrl='<%# XPath("link") %>'
Target="_blank" class="LinkTags" Font-Names="Verdana" Font-Size="X-Small">
</asp:HyperLink>
<br />
</ItemTemplate>
<AlternatingItemTemplate>
<asp:Label ID="Label3" Runat="server" Text='<%# XPath("pubDate") %>'
ForeColor="gray" Font-Bold="True"
Font-Names="Verdana" Font-Size="XX-Small">
</asp:Label>
<br />
<asp:HyperLink ID="HyperLink2" Runat="server"
Text='<%# XPath("title") %>' NavigateUrl='<%# XPath("link") %>'
Target="_blank" class="LinkTags" Font-Names="Verdana" Font-Size="X-Small">
</asp:HyperLink>
<br />
</AlternatingItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:XmlDataSource ID="xmlBanking" Runat="server"
DataFile="http://feeds.feedburner.com/NysecomFinancialNewsReleases"
XPath="rss/channel/item">
</asp:XmlDataSource>
</form>
</body>
</html>

Master Detail – Multiple Tables

This sample shows how to create a full Master/Detail page, using the Northwind Database. First, we choose the Customer from a DropDownList, which populates the orders for that customer in a GridView.
By Selecting one of the orders in the Orders Gridview, we can get the details of that order, populating a second GridView.

Keep in mind – this sample is using ASP.Net 2.0 and there is no code making this all happen.

<html>
<head runat="server">
<script language="VB" Runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
SQLDSDetails.SelectCommand="SELECT dbo.[Order Details].OrderID, dbo.Products.ProductName, " & _
"dbo.[Order Details].UnitPrice, dbo.[Order Details].Quantity, dbo.[Order Details].Discount FROM " & _
"dbo.[Order Details] INNER JOIN dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID " & _
"WHERE (dbo.[Order Details].OrderID = @OrderID)"

SqlDataSource1.SelectCommand="SELECT dbo.Orders.OrderID, dbo.Orders.OrderDate, " & _
"dbo.Orders.ShippedDate, dbo.Employees.LastName FROM dbo.Orders INNER JOIN dbo.Employees " & _
"ON dbo.Orders.EmployeeID = dbo.Employees.EmployeeID WHERE (dbo.Orders.CustomerID = @CustomerID)"
End Sub
</script>
<title>Master-Detail/Order Details</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="ddlCustomers" runat="server" AutoPostBack="True" DataSourceID="sqlDS1"
DataTextField="CompanyName" DataValueField="CustomerID" AppendDataBoundItems="True">
<asp:ListItem>Select</asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="sqlDS1" runat="server"
ConnectionString="<%$ ConnectionStrings:NWConn2 %>"
SelectCommand="SELECT [CustomerID], [CompanyName] FROM [Customers]"></asp:SqlDataSource>

<table style="width: 100%">
<tr>
<td style="width: 166px" valign="top">
<asp:GridView ID="gvMaster" runat="server"
HeaderStyle-BackColor="#A0A0D0"
BackColor="#9DADEE"
AlternatingRowStyle-BackColor="#DFDFDF"
AutoGenerateColumns="False" DataKeyNames="OrderID" DataSourceID="SqlDataSource1"
AllowPaging="True" AllowSorting="True">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="OrderID" HeaderText="OrderID" InsertVisible="False" ReadOnly="True"
SortExpression="OrderID" />
<asp:BoundField DataField="OrderDate" HeaderText="Ordered" SortExpression="OrderDate"
DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="ShippedDate" HeaderText="Shipped" SortExpression="ShippedDate"
DataFormatString="{0:d}" HtmlEncode="False" />
<asp:BoundField DataField="LastName" HeaderText="SalesPerson" SortExpression="LastName" />
</Columns>
<SelectedRowStyle BackColor="AntiqueWhite" />
</asp:GridView>
</td>
<td style="width: 422px" valign="top">
<asp:GridView ID="gvDetails" runat="server" AutoGenerateColumns="False"
HeaderStyle-BackColor="#A0A0D0"
BackColor="#9DADEE"
AlternatingRowStyle-BackColor="#DFDFDF"
DataSourceID="sqlDSDetails">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="OrderID" SortExpression="OrderID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />
<asp:BoundField DataField="UnitPrice" DataFormatString="{0:c}" HeaderText="UnitPrice"
HtmlEncode="False" SortExpression="UnitPrice">
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
<asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity">
<ItemStyle HorizontalAlign="Center" />
</asp:BoundField>
<asp:BoundField DataField="Discount" DataFormatString="{0:P2}" HeaderText="Discount"
HtmlEncode="False" SortExpression="Discount">
<ItemStyle HorizontalAlign="Right" />
</asp:BoundField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="sqlDSDetails" runat="server"
ConnectionString="<%$ ConnectionStrings:NWConn2 %>">
<SelectParameters>
<asp:ControlParameter ControlID="gvMaster" Name="OrderID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
&nbsp;
</td>
</tr>
</table>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWConn2 %>">
<SelectParameters>
<asp:ControlParameter ControlID="ddlCustomers" Name="CustomerID" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>

Changing Table Cell Colors with Code Behind

This code sample shows how simple it is to change the background color of an HTML Table cell through code behind. The main points are :

  1. Give the Cell an ID, just like ASP.Net Server Controls
  2. Add ‘runat=”server”‘ to the tag, just like ASP.Net Server Controls

The rest is handled with the same html attributes as you would use in a simple HTML page.

<script language="VB" Runat="server">
Sub changeColor(Source as Object, E as EventArgs)
myCell.bgcolor="#5d7b9d"
End Sub
Sub doSecond(Source as Object, E as EventArgs)
num2.bgColor="Pink"
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 5.0">
<title>Changing Table Cell Colors with Code Behind</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:Button id="btn" Text="Change Cell Color" onclick="changeColor" Runat="server" />
<asp:Button id="btn2" Text="Change Another" onclick="doSecond" Runat="server" />
<table width="100%" cellpadding="1" cellspacing="1" border="0" bgcolor=#BDCFE7>
<tr bgcolor="#BDCFE7">
<td align="left" style="color:#000000;font-weight: bold" valign="top"
id="myCell" runat="server">TEST</td>
<td align="left" style="color:#000000;font-weight: bold" valign="top">TEST</td>
<td align="left" style="color:#000000;font-weight: bold" valign="top">TEST</td>
</tr>
<tr bgcolor="#E7EFFF">
<td align="left" style="color:#000000"" valign="top">TEST</td>
<td align="left" style="color:#000000"" valign="top">TEST</td>
<td align="left" style="color:#000000"" valign="top"></td>
</tr>
<tr bgcolor="#E7EFFF">
<td align="left" style="color:#000000"" valign="top">TEST</td>
<td align="left" style="color:#000000"" valign="top">TEST</td>
<td align="left" style="color:#000000"" valign="top"
id="Num2" runat="server">TEST</td>
</tr>
</table>
</form>
</body>
</html>

Playable Song List

This sample uses a Gridview, with a HyperLink Field, to show how to simply create a song download list. In this sample, since it’s set up just to quickly stream/play the files, the target file is ‘.m3u’.

To make it downloadable, change the extension to ‘.mp3′ for your files. The only bad part about that, is most people’s systems are setup to automatically run any filetype it recognizes, so it will try to play the song, even after the long MP3 download. You can prompt the users to right-click and choose the ‘Save Target As’ option.

Note, too, that here, I’ve not included the extension or the path in the database – just the filename. There’s no really good reason I didn’t include the extension. But this shows how to use the extension with the DataNavigateUrlFormatString of the Gridview. Also, by not hardcoding the path in the database, if you decide to move it to a different location later, you only need to change it in one place, instead of in every row of the table.

The guitars are all played by David Wier. (Please don’t laugh at the singing, when aplicable)
:)

<%@ Page Language="VB" %>
<html>
<head>
<title>Music Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:Your_dbConnectionString %>"
SelectCommand="SELECT [id], [Title], [Filename], [Genre], [Author] FROM [SongTest]">
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
SortExpression="id" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="Genre" HeaderText="Genre" SortExpression="Genre" />
<asp:BoundField DataField="Author" HeaderText="Author" SortExpression="Author" />
<asp:HyperLinkField DataNavigateUrlFields="Filename"
DataNavigateUrlFormatString="http://YourPath/{0}.m3u"
DataTextField="Filename" DataTextFormatString="Get it Now" HeaderText="Download"
Target="_blank" />
</Columns>
</asp:GridView>

</div>
</form>
</body>
</html>

GridView With an ItemTemplate

This code sample uses the Northwind Database (Employees Table) to show how to use an ItemTemplage, within the columns of a Gridview, to get a more customized appearance.

<html>
<head>
<meta name="GENERATOR" Content="ASP Express 5.0">
<title>GridView With an ItemTemplate</title>
</head>
<body>
<form id="form1" Runat="Server">
<asp:GridView ID="gv1"
AutoGenerateColumns="False "
Runat="Server"
DataSourceID="DS1"
AllowPaging = "True"
PageSize = "5"
PagerSettings-Mode = "Numeric">
<columns>
<asp:TemplateField HeaderText="Name and Information">
<ItemTemplate> <b><font color="#0000FF">Name</font>:</b>
<%#Container.DataItem("firstname")%> <%#Container.DataItem("lastname")%><br />
<b><font color="#FF0000">Hire Date</font>: </b>
(<%# formatdatetime(Container.DataItem("Birthdate"),vbshortdate)%>) <br />
<b>Title: </b> <i><font Size="2"><%#Container.DataItem("title")%></font></i>
</ItemTemplate>
</asp:TemplateField>
</columns>
</asp:GridView>

<asp:SQLDataSource ID="DS1"
Runat="Server"
SelectCommand = "Select FirstName, LastName, Title, BirthDate from Employees"
ConnectionString="<%$ ConnectionStrings:YourConnStringGoesHere %>">
</asp:SQLDataSource>
</form>
</body>
</html>

Fully Editable GridView with Insert

This code sample shows how to create a fully editable GridView, including, the ability to add a new row to the top of the Grid, and use it for inserting. You will need to include an Update Stored Procedure which will either update or insert, based on whether or not you pass it an ID. The code for this sProc is here:

CREATE PROCEDURE dbo.procUpdateCategories @CategoryName nvarchar(15), @Description text, @CatID int=null AS Begin Update Categories Set CategoryName=@CategoryName, Description=@Description Where CategoryID=@CatID if @@RowCount=0 Insert into Categories (CategoryName, Description) Values(@CategoryName, @Description) Select @@identity Return @@Identity end

The code in the page will iterate through every row of the Gridview. If it’s a new row (only one can be added at once, here), it will insert that row. For each of the rest of the rows, it will update them, with either the data in the cells (either changed or not)

The Delete and Select statements should also be converted to sProcs, in our opinion,but for easier readability, they were simple enough to include in the subs.

Notice that there is a Property, which returns a DataTable, which retrieves the data to fill the Gridview, and it’s used to populate the Gridview whenever needed, in the ‘GetAndBind’ sub.

If you need to understand more about the ConnectionString and ConfigurationManager, check out this link:
ConnectionStrings and Connecting to a Database
ConfigurationManager