Stone Coast Web Design's Weblog

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}

I know this issue is going to come up soon for me so I've been researching possible solutions and came across this one.

http://wss.asaris.de/sites/walsh/Lists/WSSv3%20FAQ/DispForm.aspx?ID=1034

How do you move a WSS 3.0 database to a different SQL Server?

You cannot move the configuration database. 

1.  Detached the content database using SQL Manager

2.  Copied the database and log files to the new server

3.  Attached the content database to the new server

4.  Run the SharePoint Products and Technologies Configuration Wizard to remove it from the farm

5.  Run SharePoint Products and Technologies Configuration Wizard to recreate the farm and add the new database server. This will also create a
new configuration database.

6.  Once that is done, go to SharePoint Central Administration website and click on Application Management

7.  Select Create or extend web application to setup your new web application

8.  Select Content Database and add the new content database

9.  Restart IIS

I Was recently asked to move a standalone application inside of WSS 3.0. No matter what I tried I was getting an error message "Connection String has not been properly iniitialized" even though it worked fine in a separate app. The problem turned out to be that the first version which worked in version 1.1 does not work ASP.NET 2.0. In the first example, the connection string is pulling it's connection info from the <AppSettings> section. Version 2.0 doesn't use <appsettings> and instead uses <ConnectionStrings> so the SqlConnection and SqlCommand objects have to be structured a little differently.

********************

My typical method which worked fine in a separate app but did not work in WSS:

Dim strSQLText As String = "SELECT UserID, Name FROM tblUsers ORDER BY Name"
Dim con As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("QCentralConnectionString"))
Dim cmd As New SqlCommand(strSQLText, con)
con.Open()
ddluserID.DataSource = cmd.ExecuteReader()
ddluserID.DataBind()
con.Close()

web.config:

<appSettings>       
    <add key="QCentralConnectionString" value="server=localhost; database=QCentral; uid=xxx; pwd=xxx" />   
</appSettings>

********************

This version worked in WSS 3.0 (ASP.NET 2.0):

Dim strSQLText As String = "SELECT UserID, Name FROM tblUsers ORDER BY Name"
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("QCentralConnectionString").ConnectionString)
Dim cmd As New SqlCommand(strSQLText)
con.Open()
cmd.Connection = con
ddluserID.DataSource = cmd.ExecuteReader()
ddluserID.DataBind()
con.Close()

web.config:

<configuration>
<connectionStrings>
<clear />
<add name="QCentralConnectionString" connectionString="Data Source=localhost;Initial Catalog=QCentral;User ID=xxx;Password=xxx" providerName="System.Data.SqlClient" /> 
</connectionStrings>

********************

Scott Mitchell provides a summary of the new features of ASP.NET 3.5 and Visual Studio 2008.  It turns out there aren't a lot of differences between ASP.NET versions 3.5 and 2.0. These are the main new features of version 3.5

• Integrated ASP.NET AJAX support,
• The ListView control, and
• The DataPager control

I'm very happy to see that with Visual Studio 2008 you are able to target which version of .NET you want to code against. This was a real plan with VS 2003 vs VS 2005.

http://aspnet.4guysfromrolla.com/articles/112107-1.aspx

This is the most comprehensive site I've come across for creating connection strings for pretty much any database type you can think of

http://www.connectionstrings.com/

Use this to find out where a given field name or column reside and is being used in SQL Server.

select so.name
from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name = 'YourColumnNameGoesHere'

or

select so.name, sc.name
from sysobjects so inner join syscolumns sc
ON so.id = sc.id where sc.name like '%YourPartialColumnNameGoesHere%'

or

I recently ran into a situation where I was unable to export data from a GridView to Excel using the same technique that I've used many times before using a DataGrid (see below).  I kept getting the error message "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server. "  I had to do this to get this to work.

<script runat="server">
Private Sub ExcelExport(ByVal sender As System.Object, ByVal e As System.EventArgs)
GridView1.AllowSorting = False
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=ConferenceAttendees.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.xls"
Dim stringWrite As System.IO.StringWriter = New System.IO.StringWriter()
Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
GridView1.RenderControl(htmlWrite)
Response.Write(stringWrite.ToString())
Response.End()
End Sub
</script>

The trick

was to set EnableEventValidation to false in the page declaration:

<%@ Page Language="VB" EnableEventValidation = "false" %>

Here are a few CSS tools and links that I came across that look useful

• Layoutomatic - Creates a CSS-based basic layout for your page
• List-o-matic - Creates a CSS-based navigation menu for your site
• CSS Cheatsheet - CSS properties with examples
• CSS Zen Garden -  Some really nice examples of the power of CSS