Stone Coast Web Design Blog

One web comic I commonly read is xkcd. It is a great comic that has a lot of good computer as well as just nerdy jokes. It has a great comic about SQL injection attacks and why you need to sanitize your database inputs. This is a lesson in what to not name your child.

You can loop through all or certain type of controls on ASP.NET Page using this code. Code will loop through also those controls that are contained in some other container that Form, Panel for example. Example of looping through all TextBoxes on Page.

[C#]
private void LoopTextBoxes (Control parent) 
        {
            foreach (Control c in parent.Controls) 
            {
                TextBox tb = c as TextBox;
                if (tb != null)
                    //Do something with the TextBox

                if (c.HasControls())
                    LoopTextBoxes(c);
            }
        }

And you can start the looping by calling: 
LoopTextBoxes(Page);

[VB]
  Private Sub LoopTextBoxes(ByVal parent As Control)
        Dim c As Control
        For Each c In parent.Controls
            If c.GetType() Is GetType(TextBox) Then
                'Do something with the TextBox
            End If

            If c.HasControls Then
                LoopTextBoxes(c)
            End If
        Next
    End Sub

LoopTextBoxes(Me)

This example shows a GridView control that displays some rows from a database table. The <Columns> section adds two ButtonField columns and a TemplateField column. The first ButtonField column generates a normal Button control every each row, because it has the ButtonType="Button" attribute. The second ButtonField column, which does not contain this attribute, generates the default of a LinkButton in every row.

The ButtonField column exposes a CommandName property, declared as "SendButtonField" for the first column and "CopyButtonField" for the second column. The TemplateField column contains both a Button and a LinkButton control, with the CommandName properties set to DetailsButton and DetailsLinkButton respectively.

When declaring individual button-type controls, you can also set the CommandArgument property. In this example, the CommandArgument for both buttons is the value of the CategoryName and CategoryID columns of the current row generated with Eval data-binding statements.

ASP.NET automatically wires up the CommandArgument of the buttons in the ButtonField columns to the RowIndex of each row in the GridView control. This means that you can extract the row index, and use it to access the rows or the other properties of the GridView. This example sets the DataKeyNames property of the GridView to the CategoryName column through the attribute DataKeyNames="CategoryName". This means that you can access the value of the DataKey for the current row using the index returned.

<script runat="server">

  protected void GridView1_RowCommand(Object sender, GridViewCommandEventArgs e)
  {
    Label1.Text = "CommandName '<b>" + e.CommandName + "</b>' detected in Row_Command event.<br />";
    Label1.Text += "CommandArgument = '<b>" + e.CommandArgument + "</b>'.<br />";
    Label1.Text += "CommandSource = '<b>" + e.CommandSource.ToString() + "</b>'.<br />";
    if (e.CommandName.IndexOf("ButtonField") > 0)
    {
      Int32 rowIndex = Int32.Parse(e.CommandArgument.ToString());
      Label1.Text += "DataKeys[0] = '<b>" + GridView1.DataKeys[rowIndex].Value + "</b>'.";
    }
  }
</script>

  <asp:GridView ID="GridView1" runat="server" BackColor="White" BorderColor="#999999"
    BorderStyle="None" BorderWidth="1px" CellPadding="3" DataKeyNames="CategoryName"
    DataSourceID="SqlDataSource1" GridLines="Vertical" OnRowCommand="GridView1_RowCommand">
    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />
    <Columns>
      <asp:ButtonField HeaderText="ButtonField" Text="Send"
            CommandName="SendButtonField" ButtonType="Button" />
      <asp:ButtonField HeaderText="ButtonField" Text="Copy"
            CommandName="CopyButtonField" />
      <asp:TemplateField HeaderText="TemplateField">
        <ItemTemplate>
          <asp:Button runat="server" ID="Button1"
                Text="Details" CommandName="DetailsButton"
                CommandArgument='<%# Eval("CategoryName")
                      + " [" + Eval("CategoryID") + "]" %>' />
          <asp:LinkButton runat="server" ID="Button2"
                Text="Details" CommandName="DetailsLinkButton"
                CommandArgument='<%# Eval("CategoryName")
                      + " [" + Eval("CategoryID") + "]" %>' />
        </ItemTemplate>
      </asp:TemplateField>
    </Columns>
    <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
    <AlternatingRowStyle BackColor="#DCDCDC" />
  </asp:GridView>
  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT CategoryID, CategoryName FROM Categories">
  </asp:SqlDataSource>
  <p />
  <asp:Label ID="Label1" runat="server" EnableViewState="false" />
  

I often need to grab the id of the record I just inserted into a database as an output parameter. This is relatively straightforward using a SqlDataConnection but I never knew how to do it using a SqlDataSource. The following code example demonstrates how to use the SqlDataSourceStatusEventArgs class to examine the return value and values of output parameters that are returned when using a SqlDataSource control with a stored procedure to populate a GridView control. The stored procedure selects data that is displayed in the GridView, but also passes other information back to the caller, such as an integer output parameter and a return value. The parameters that the SqlDataSource uses for the stored procedure are contained by the SelectParameters collection, and consist of parameters that pass information from the Web form to the stored procedure as well as parameters that pass information back to the form. The Direction property of these parameters is set to Output and ReturnValue.

For more information see http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasourcestatuseventargs.aspx

<%@Page  Language="C#" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.Common" %>
<%@Import Namespace="System.Data.SqlClient" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
// Clicking the Submit button explicitly refreshes the data 
// by calling the Select() method.
private void Submit(Object source, EventArgs e) {
  SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}

// This event handler is called after the Select() method is executed.
private void OnSelectedHandler(Object source, SqlDataSourceStatusEventArgs e) {

  IDbCommand cmd = e.Command; 
  
  Label1.Text = "Parameter return values: ";

  foreach (SqlParameter param in cmd.Parameters) {
    //  Extract the value of the parameter.
    Label1.Text += param.ParameterName + " - " + param.Value.ToString();
  }
}
</script>

<html  >
  <head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:sqldatasource
            id="SqlDataSource1"
            runat="server"
            datasourcemode="DataSet"
            connectionstring="<%$ ConnectionStrings:MyNorthwind%>"
            selectcommand="getordertotal"
            onselected="OnSelectedHandler">
            <selectparameters>
              <asp:querystringparameter name="empId" querystringfield="empId" />
              <asp:parameter name="total" type="Int32" direction="Output" defaultvalue="0" />
              <asp:parameter name="_ret" type="Int32" direction="ReturnValue" defaultvalue="0" />
            </selectparameters>
        </asp:sqldatasource>
        <!--
          CREATE PROCEDURE dbo.getordertotal
            @empId int,
            @total int OUTPUT
          as
            set nocount on
            select @total    = count(1) from orders where employeeid=@empid;
            select * from orders where employeeID = @empId ;
            return (-1000);
          GO
        -->

        <asp:gridview
          id="GridView1"
          runat="server"
          allowpaging="True"
          pagesize="5"
          datasourceid="SqlDataSource1" />

        <asp:button
          id="Button1"
          runat="server"
          onclick="Submit"
          text="Refresh Data" />

        <asp:label id="Label1" runat="server" />

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

This example shows an example of linking controls together, this time providing a master/details view. The first grid, a GridView, shows the basic details of a product. The second control, a DetailsView is bound to a SqlDataSource, but with the data source having a SelectParameter bound to the SelectedValue property of the first grid. This means that when no row is selected on the first grid, the DetailsView is not shown as there is no data to bind to. Once a row is selected, however, the SelectedValue holds the ProductID and the DetailsView only shows data for that selected product.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
    SelectCommand="SELECT ProductID, ProductName FROM Products" />
    
  <asp:GridView ID="GridView1" runat="server"
    AllowPaging="true" PageSize="5"
    DataKeyNames="ProductID" AutoGenerateColumns="true"
    DataSourceID="SqlDataSource1">
    <Columns>
      <asp:CommandField ShowSelectButton="true" />
    </Columns>
  </asp:GridView>
  
  <br /><br />
  
  <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
    DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"
    InsertCommand="INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], 
[UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]) VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, 
@UnitPrice, @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued)"
SelectCommand="SELECT Products.ProductID, Products.ProductName, Products.SupplierID, Products.CategoryID, 
Products.QuantityPerUnit, Products.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder, Products.ReorderLevel, 
Products.Discontinued, Suppliers.CompanyName, Categories.CategoryName FROM Products INNER JOIN Suppliers 
ON Products.SupplierID = Suppliers.SupplierID INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID 
WHERE (Products.ProductID = @ProductID)"
UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, 
[CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, [UnitPrice] = @UnitPrice, [UnitsInStock] = 
@UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder, 
[ReorderLevel] = @ReorderLevel, [Discontinued] = @Discontinued WHERE [ProductID] = @ProductID">
    <DeleteParameters>
      <asp:Parameter Name="ProductID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
      <asp:Parameter Name="ProductName" Type="String" />
      <asp:Parameter Name="SupplierID" Type="Int32" />
      <asp:Parameter Name="CategoryID" Type="Int32" />
      <asp:Parameter Name="QuantityPerUnit" Type="String" />
      <asp:Parameter Name="UnitPrice" Type="Decimal" />
      <asp:Parameter Name="UnitsInStock" Type="Int16" />
      <asp:Parameter Name="UnitsOnOrder" Type="Int16" />
      <asp:Parameter Name="ReorderLevel" Type="Int16" />
      <asp:Parameter Name="Discontinued" Type="Boolean" />
      <asp:Parameter Name="ProductID" Type="Int32" />
    </UpdateParameters>
    <SelectParameters>
      <asp:ControlParameter ControlID="GridView1" Name="ProductID" PropertyName="SelectedValue"
        Type="Int32" />
    </SelectParameters>
    <InsertParameters>
      <asp:Parameter Name="ProductName" Type="String" />
      <asp:Parameter Name="SupplierID" Type="Int32" />
      <asp:Parameter Name="CategoryID" Type="Int32" />
      <asp:Parameter Name="QuantityPerUnit" Type="String" />
      <asp:Parameter Name="UnitPrice" Type="Decimal" />
      <asp:Parameter Name="UnitsInStock" Type="Int16" />
      <asp:Parameter Name="UnitsOnOrder" Type="Int16" />
      <asp:Parameter Name="ReorderLevel" Type="Int16" />
      <asp:Parameter Name="Discontinued" Type="Boolean" />
    </InsertParameters>
  </asp:SqlDataSource>
  
  <asp:DetailsView ID="DetailsView1" runat="server"
    AutoGenerateRows="False" DataSourceID="SqlDataSource2"
     GridLines="None">
    <Fields>

      <asp:BoundField DataField="ProductName"
        HeaderText="Name" SortExpression="ProductName" />

      <asp:TemplateField HeaderText="Supplier">
        <ItemTemplate>
          <%#Eval("CompanyName") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:SqlDataSource ID="Sds1" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
            SelectCommand="SELECT SupplierID, CompanyName FROM Suppliers ORDER BY CompanyName" />
          <asp:DropDownList ID="SupplierID" runat="server"
            DataSourceId="Sds1"
            DataValueField="SupplierID" DataTextField="CompanyName"
            SelectedValue='<%#Bind("SupplierID")%>' />
        </EditItemTemplate>
      </asp:TemplateField>

      <asp:TemplateField HeaderText="Category" >
        <ItemTemplate>
          <%#Eval("CategoryName") %>
        </ItemTemplate>
        <EditItemTemplate>
          <asp:SqlDataSource ID="Sds2" runat="server"
            ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
            SelectCommand="SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryName" />
          <asp:DropDownList ID="CategoryID" runat="server"
            DataSourceID="Sds2"
            DataValueField="CategoryID" DataTextField="CategoryName"
            SelectedValue='<%#Bind("CategoryID")%>' />
        </EditItemTemplate>
      </asp:TemplateField>

      <asp:BoundField DataField="QuantityPerUnit"
        HeaderText="Quantity Per Unit" SortExpression="QuantityPerUnit" />
      <asp:BoundField DataField="UnitPrice"
        HeaderText="Unit Price" SortExpression="UnitPrice" />
      <asp:BoundField DataField="UnitsInStock"
        HeaderText="Units In Stock" SortExpression="UnitsInStock" />
      <asp:BoundField DataField="UnitsOnOrder"
        HeaderText="Units On Order" SortExpression="UnitsOnOrder" />
      <asp:BoundField DataField="ReorderLevel"
        HeaderText="Reorder Level" SortExpression="ReorderLevel" />
      <asp:CheckBoxField DataField="Discontinued"
        HeaderText="Discontinued" SortExpression="Discontinued" />
      <asp:CommandField ShowDeleteButton="True"
        ShowEditButton="True" ShowInsertButton="True" />
    </Fields>
    <HeaderStyle BackColor="Green" />
    <FieldHeaderStyle BackColor="LightGray" Height="20px" Width="110px"/>
  </asp:DetailsView>

This is a more advanced example of updating and editing a Gridview control

<asp:sqldatasource ID="ds1" runat="server"
    ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
    SelectCommand="SELECT ProductID, ProductName, QuantityPerUnit, Discontinued, UnitPrice FROM Products"
    UpdateCommand="UPDATE Products SET QuantityPerUnit=@QuantityPerUnit, Discontinued=@Discontinued, UnitPrice=@UnitPrice WHERE ProductID=@ProductID"
    DeleteCommand="DELETE FROM Products WHERE ProductID=@ProductID"
   />

  <asp:GridView ID="grid1" runat="server" DataSourceID="ds1"
    DataKeyNames="ProductID"
    AutoGenerateColumns="False"
    AllowSorting="True"
    AllowPaging="True" 
    PageSize="5">
    <Columns>

      <asp:ButtonField ButtonType="Button" DataTextField="ProductID" SortExpression="ProductID"
        HeaderText="ID" />

      <asp:HyperLinkField DataTextField="ProductName"
        DataNavigateUrlFields="ProductID,ProductName"
        DataNavigateUrlFormatString="http://www.site-that-shows-more-info.com/products?product={0}&amp;name={1}"
        SortExpression="ProductName" HeaderText="Product"
        ItemStyle-Font-Bold="True" ItemStyle-BackColor="Yellow" />

      <asp:BoundField DataField="QuantityPerUnit" HeaderText="Packaging"  />

      <asp:CheckBoxField DataField="Discontinued" HeaderText="N/A" />

      <asp:TemplateField HeaderText="Price"  SortExpression="UnitPrice"
        ItemStyle-Font-Bold="True">
        <ItemTemplate>
          <asp:Label runat="server" text='<%# Eval("UnitPrice", "${0:F2}") %>' />
        </ItemTemplate>
        <AlternatingItemTemplate>
          <asp:Label runat="server" ForeColor="DarkGray" text='<%# Eval("UnitPrice", "${0:F2}") %>' />
        </AlternatingItemTemplate>
        <EditItemTemplate>
          <asp:TextBox ID="UnitPrice" runat="server" Text='<%#Bind("UnitPrice")%>' />
          <asp:RequiredFieldValidator ID="rfv1" runat="server"
          ControlToValidate="UnitPrice" Text="You must enter the price" />
        </EditItemTemplate>
      </asp:TemplateField>
      
      <asp:CommandField ButtonType="Image" ShowCancelButton="True"
        ShowEditButton="True" ShowDeleteButton="True"
        CancelImageUrl="s.gif" EditImageUrl="q.gif"
        UpdateImageUrl="i.gif" DeleteImageUrl="x.gif"
        CancelText="Cancel this update" EditText="Edit this row"
        UpdateText="Apply these changes" DeleteText="Delete this row" />

    </Columns>
    <HeaderStyle Font-Bold="true" Font-Names="Verdana" />
    <RowStyle Font-Names="Verdana" />

  </asp:GridView>

This is the simplest example of how to edit and update a Gridview

Product Name: <asp:TextBox ID="TextBox1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Find Products" />

<br /><br />

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:NorthwindConnectString %>"
SelectCommand="SELECT * FROM [Products] WHERE ([ProductName] LIKE '%' + @ProductName + '%')"
DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID"
InsertCommand="INSERT INTO [Products] ([ProductName], [UnitPrice]) VALUES (@ProductName, @UnitPrice)"
UpdateCommand="UPDATE [Products] SET [ProductName] = @ProductName, [UnitPrice] = @UnitPrice WHERE [ProductID] = @ProductID">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="ProductName" PropertyName="Text"
Type="String" />
</SelectParameters>
<DeleteParameters>
<asp:Parameter Name="ProductID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="UnitPrice" Type="Decimal" />
<asp:Parameter Name="ProductID" Type="Int32" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ProductName" Type="String" />
<asp:Parameter Name="UnitPrice" Type="Decimal" />
</InsertParameters>
</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server"
AllowPaging="true" PageSize="5" AutoGenerateColumns="true"
DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
</Columns>
</asp:GridView>

This dumb post is just for my own reference.  This catch-all CSS references uses a font style that I like for all of the most common HTML page elements.

body, input, select, textarea, button {font-family:Verdana, sans-serif; font-size:x-small}