Stone Coast Web Design Blog

This demonstration will populate a ASP.NET checkbox control, check the matching records saved in a database and then save any changes made by the user
back to the database

'This first populates a checkboxlist control with a list of records 
'then loops back through the control and checks the checkboxes which have been saved to the database 

Public Sub BindAlertsList() 

        'This creates the list of checkboxes for the alert events types 
        Dim strSQLConn As SqlConnection
        Dim cmd As SqlCommand
        Dim objReader As SqlDataReader
        Dim list_id As Integer = CInt(lbllist_id.Text)
        strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
        cmd = New SqlCommand("SELECT ' ' + [AlertEvent] AS AlertEvent, [alert_id] FROM SavedCompanyListAlertValues ORDER BY AlertEvent", strSQLConn) 

        strSQLConn.Open()
        cblAlertTypes.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        cblAlertTypes.DataTextField = "AlertEvent"
        cblAlertTypes.DataValueField = "alert_id"
        cblAlertTypes.DataBind()
        'Dim cmd As SqlCommmand
        cmd = New SqlCommand("SELECT * FROM SavedCompanyListAlertTypes WHERE list_id=" & list_id, strSQLConn)
        strSQLConn.Open()
        objReader = cmd.ExecuteReader()
        While objReader.Read()
            Dim currentCheckBox As ListItem = cblAlertTypes.Items.FindByValue(objReader("alert_id").ToString())
            If Not (currentCheckBox Is Nothing) Then
                currentCheckBox.Selected = True
            End If
        End While
        strSQLConn.Close()
End Sub 

'This saves the checked records in the database 

Sub SaveAlerts_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        'The first step in the save alerts process is to delete all of the exising ones for the selected company list
        DeleteAlertTypes()
End Sub
    Sub DeleteAlertTypes()
        'The first step in the save alerts process is to delete all of the exising ones for the selected company list
        Dim strSQLConn As SqlConnection
        Dim cmd As SqlCommand
        Dim SqlText As String
        Dim intlist_id As Integer = CInt(lbllist_id.Text)
        strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
        SqlText = "p_SavedCompanyList_Delete_AlertTypes"
        cmd = New SqlCommand(SqlText)
        cmd.Connection = strSQLConn
        cmd.CommandType = CommandType.StoredProcedure
        'input parameters for the sproc
        cmd.Parameters.Add("@list_id", SqlDbType.Int).Value = intlist_id
        Try
            strSQLConn.Open()
            cmd.Connection = strSQLConn
            cmd.ExecuteNonQuery()
        Finally
            strSQLConn.Close()
        End Try
        'Now that the existing alert types are deleted, we can add the new ones
        SaveAlertTypes()
    End Sub 

Sub SaveAlertTypes()
        Dim strSQLConn As SqlConnection
        Dim cmd As SqlCommand
        Dim SqlText As String
        Dim intlist_id As Integer = CInt(lbllist_id.Text)
        'Make the lblError label invisible in case the user is resubmitting
        lblError.Visible = False 

        strSQLConn = New SqlConnection(ConfigurationManager.ConnectionStrings("Tearsheet_ConString").ConnectionString)
        SqlText = "p_SavedCompanyList_Save_AlertTypes"
        Try
            strSQLConn.Open()
            For Each Item As ListItem In cblAlertTypes.Items
                If (Item.Selected) Then
                    cmd = New SqlCommand(SqlText)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Connection = strSQLConn
                    'input parameters for the sproc
                    cmd.Parameters.Add("@list_id", SqlDbType.Int).Value = intlist_id
                    cmd.Parameters.Add("@alertevents", SqlDbType.Int).Value = Item.Value
                    cmd.ExecuteNonQuery()
                End If
            Next
        Finally
            strSQLConn.Close()
            lblSaveAlerts.Visible = True
        End Try
End Sub 

<asp:CheckboxList ID="cblAlertTypes" runat="server" 
CssClass="smalltextNoUnderline" BorderStyle="Solid" BorderWidth="2px">                               
</asp:CheckboxList> 

CREATE PROCEDURE [dbo].[p_SavedCompanyList_Delete_AlertTypes]
    @list_id int    
AS
BEGIN 

    SET NOCOUNT ON; 

    DELETE FROM 
        SavedCompanyListAlertTypes
    WHERE 
        list_id = @list_id
END 

CREATE PROCEDURE p_SavedCompanyList_Save_AlertTypes
    @list_id int,
    @alertevents int
AS
BEGIN 

INSERT INTO SavedCompanyListAlertTypes
    (list_id, alertevents)
VALUES
    (@list_id, @alertevents) 

END
GO    

I was looking for a way to filter the results in a gridview by selecting a value in a dropdownlist and came across this great example

http://blog.evonet.com.au/post/Creating-a-Stylish-looking-Gridview-with-Filtering.aspx

First the CSS

.GridviewDiv {font-size: 100%; font-family: 'Lucida Grande', 'Lucida Sans Unicode', Verdana, Arial, 
Helevetica, sans-serif; color: #303933;}
Table.Gridview{border:solid 1px #df5015;}
.GridviewTable{border:none}
.GridviewTable td{margin-top:0;padding: 0; vertical-align:middle }
.GridviewTable tr{color: White; background-color: #df5015; height: 30px; text-align:center}
.Gridview th{color:#FFFFFF;border-right-color:#abb079;border-bottom-color:#abb079;padding:0.5em 0.5em 0.5em 0.5em;text-align:center}  
.Gridview td{border-bottom-color:#f0f2da;border-right-color:#f0f2da;padding:0.5em 0.5em 0.5em 0.5em;}
.Gridview tr{color: Black; background-color: White; text-align:left}
:link,:visited { color: #DF4F13; text-decoration:none }

Then the .aspx page

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head profile="http://gmpg.org/xfn/11">
    <link rel="stylesheet" type="text/css" href="gridview.css" media="all" />
</head>
<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager" runat="server" />
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
        <h3>Gridview with Filtering</h3>
            <div class="GridviewDiv">
            <table style="width: 540px" border="0" cellpadding="0" cellspacing="1" class="GridviewTable">
                <tr >
                    <td style="width: 40px;">
                        ID
                    </td>
                    <td style="width: 120px;" >
                        First Name
                    </td>
                    <td style="width: 120px;">
                        Last Name
                    </td>
                    <td style="width: 130px;">
                        Department
                    </td>
                    <td style="width: 130px;">
                        Location
                    </td>
                </tr>
                <tr >
                    <td style="width: 40px;">
                    </td>
                    <td style="width: 120px;">
                    </td>
                    <td style="width: 120px;">
                    </td>
                    <td style="width: 130px;">
                        <asp:DropDownList ID="ddldepartment" DataSourceID="dsPopulateDepartment" AutoPostBack="true"
                            DataValueField="department" runat="server" Width="120px" Font-Size="11px" AppendDataBoundItems="true">
                            <asp:ListItem Text="All" Value="%"></asp:ListItem>
                        </asp:DropDownList>
                    </td>
                    <td style="width: 130px;">
                        <asp:DropDownList ID="ddlLocation" DataSourceID="dsPopulateLocation" AutoPostBack="true"
                            DataValueField="location" runat="server" Width="120px" Font-Size="11px" AppendDataBoundItems="true">
                            <asp:ListItem Text="All" Value="%"></asp:ListItem>
                        </asp:DropDownList>
                    </td>
                </tr>
                <tr>
                    <td colspan="5">
                        <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True"
                            AllowSorting="true" DataSourceID="dsGridview" Width="540px" PageSize="10" CssClass="Gridview">
                            <Columns>
                                <asp:BoundField DataField="id" HeaderText="Sort" SortExpression="id" ItemStyle-Width="40px"
                                    ItemStyle-HorizontalAlign="Center" />
                                <asp:BoundField DataField="FirstName" HeaderText="Sort" SortExpression="FirstName"
                                    ItemStyle-Width="120px" />
                                <asp:BoundField DataField="LastName" HeaderText="Sort" SortExpression="LastName"
                                    ItemStyle-Width="120px" />
                                <asp:BoundField DataField="Department" HeaderText="Sort" SortExpression="Department"
                                    ItemStyle-Width="130px" />
                                <asp:BoundField DataField="Location" HeaderText="Sort" SortExpression="Location"
                                    ItemStyle-Width="130px" />
                            </Columns>
                        </asp:GridView>
                    </td>
                </tr>
            </table>
            </div>
            <asp:SqlDataSource ID="dsGridview" runat="server" ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
                SelectCommand="SELECT * FROM [T_Employees]" FilterExpression="Department like '{0}%'
                and Location like '{1}%'">
                <FilterParameters>
                    <asp:ControlParameter Name="Department" ControlID="ddldepartment" PropertyName="SelectedValue" />
                    <asp:ControlParameter Name="Location" ControlID="ddllocation" PropertyName="SelectedValue" />
                </FilterParameters>
            </asp:SqlDataSource>
            <asp:SqlDataSource ID="dsPopulateDepartment" runat="server" ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
                SelectCommand="SELECT DISTINCT Department from [T_Employees]"></asp:SqlDataSource>
            <asp:SqlDataSource ID="dsPopulateLocation" runat="server" ConnectionString="<%$ ConnectionStrings:EvonetConnectionString %>"
                SelectCommand="SELECT DISTINCT Location FROM [T_Employees]"></asp:SqlDataSource>
            </div>
        </ContentTemplate>
    </asp:UpdatePanel>
    </form>
</body>
</html>

This is a nice example of how to pass a delimitted list of values to SQL Server and perform some kind of dbase function with those whether that be an UPDATE, DELETE or whatever.

First here is the sample stored procedure we are going to call updating each company sent to it by the PK value, id_company

USE [my_dbase]
GO
CREATE PROCEDURE [dbo].[p_UpdateCompany] 
    @id_assign int = NULL
AS
BEGIN

    SET NOCOUNT ON;

    UPDATE Companies 
    SET markedfordeletion = 1 --or whatever could be a delete statement as well    

END

Next, here is the VB.NET code used to call the sproc

Protected Sub btnCompanySubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim strConnection As SqlConnection
        Dim strSql As String
        Dim cmd As SqlCommand
        Dim CompanyValues As String = txtid_company.Text
        
        Dim CompanyList As New ArrayList
        CompanyList.AddRange(Split(CompanyValues, ","))
        
        strConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString_Repository").ConnectionString)
        strSql = "p_UpdateCompany"
        cmd = New SqlCommand(strSql)
        cmd.CommandType = CommandType.StoredProcedure
        
        For i As Integer = 0 To CompanyList.Count - 1
            Dim CompanyVal As Integer = CompanyList(i)
            
            cmd.Parameters.Add("@id_Company", SqlDbType.Int, CompanyVal)
            Try
                strConnection.Open()
                cmd.Connection = strConnection
                cmd.ExecuteNonQuery()
            Finally
                strConnection.Close()
            End Try
        Next
    End Sub

<form id="form1" runat="server">
    <div>
        <table id="Companies">
            <tr>
                <td>Enter id_company values:<br />
                    (separated by commas)</td>
                <td><asp:TextBox ID="txtid_company" runat="server" TextMode="MultiLine" Rows="5" Columns="25" /></td>
            </tr>           
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnCompanySubmit" runat="server" onclick="btnCompanySubmit_Click" Text="Submit" />
                </td>
            </tr>           
        </table>
    </div>
    
    </form>

lblCompanyName.Text = GetCompanyName(intid_company).ToString()


Public Shared Function GetCompanyName(ByVal id_company As Integer) As String
        Dim result As String = String.Empty
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnString").ConnectionString)

        Dim cmd As New SqlCommand("getCompanyName", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@id_company", id_company)
        Using con
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            If reader.Read() Then
                result = CType(reader("CompanyName"), String)
            End If
        End Using
        Return result
End Function

Pretty amazing grid plugin for ASP.NET. He has versions for both ASP.NET and PHP.

http://www.trirand.com/blog/?page_id=5

This is a cool trick which assigns a bit of javascript to a server-side button to hide one button and replace it with another. In this example, the submit button, chkReview, when clicked is hidden from the user and is replaced with another button, btnSaveDisabled, which says “Please wait…Your request is being processed.”

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)   
If Not Page.IsPostBack Then
chkReview.OnClientClick = string.Format("javascript:{0}.style.visibility = 'hidden';{0}.style.display  = 'none';{1}.style.visibility = 'visible'", 
chkReview.ClientID, btnSaveDisabled.ClientID)
End If
End Sub

<asp:Button ID="chkReview" runat="server" onclick="chkSubmitRequest_Click" Text="Submit Request" CssClass="button"  />
<br />
<asp:Button ID="btnSaveDisabled" Enabled="false" runat="server" Text="Please wait...Your request is being processed" 
Style="visibility: hidden;" CssClass="button" />

This is an example of how to remove duplicate values from a list or string separated by commas. You will also need to import the System.IO namespace to use the StringBuilder method: <%@ Import Namespace="System.IO" %>

'lblIDList contains a list of values separated by commas
'12,58,102,12,99,87,87

Dim values As String
values = lblIDList.Text
                
'Remove duplicates
values = RemoveDuplicates(values)                
                
'Reassign the lblIDList value without the duplicates
lblIDList.Text = values

Public Function RemoveDuplicates(ByVal items As String) As String
            Dim Result As StringBuilder = New StringBuilder()
            Dim newArray As Array
    
            newArray = Split(items, ",")
            For i As Integer = 0 To newArray.Length - 1
                If Result.ToString.IndexOf(newArray(i).ToString()) = -1 Then
                    Result.Append(newArray(i).ToString() & ",")
                End If
            Next
            Return Result.ToString.Substring(0, Result.ToString.LastIndexOf(","))
End Function

An example of how to generate a random number in VB.NET

intLowerBound = 0
intUpperBound = 1000      
'Get the random number and display it in lblRandomNumber
lblRandomNumber.Text = GetRandomNumberInRange(intLowerBound, intUpperBound)

Function GetRandomNumberInRange(intLowerBound As Integer, intUpperBound As Integer)
                
    Dim RandomGenerator As Random
    Dim intRandomNumber As Integer

    ' Create and init the randon number generator
    RandomGenerator = New Random()

    ' Get the next random number
        intRandomNumber = RandomGenerator.Next(intLowerBound, intUpperBound + 1)
                
    ' Return the random # as the functions return value
    GetRandomNumberInRange = intRandomNumber
                        
End Function

Here's an example of how to retrieve the list of checked values from a checkboxlist control

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyItem As ListItem
        For Each MyItem In CheckBoxList1.Items
            If MyItem.Selected = True Then
                Response.Write(MyItem.Value)
                Response.Write("<br/>")
            End If
        Next
End Sub


Here's an example of how to retrieve the list of checked values from a checkboxlist control and add them 
to an arraylist.  


Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim chkvalues As ArrayList = New ArrayList()

        For Each chkBoxListItem As ListItem In CheckBoxList1.Items
            If (chkBoxListItem.Selected) Then
                chkvalues.Add(chkBoxListItem.Value)
            End If
        Next chkBoxListItem

        Session("CheckedItems") = chkvalues
        Response.Redirect("Default2.aspx")

End Sub


To retrieve the values of how to retrieve those values from the arraylist. 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not (Session("CheckedItems") Is Nothing) Then
            Dim chkvalues As ArrayList = CType(Session("CheckedItems"), ArrayList)
            For Each chkBoxListItemValue As String In chkvalues
                Response.Write(System.Environment.NewLine + chkBoxListItemValue)
            Next chkBoxListItemValue
        End If

End Sub

There are many examples of this online and I’ve posted one myself. When using an updatepanel with your Gridview, it’s common to receive the error message I  "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server. " Just setting EnableEventValidation="false" in the page directive won’t work in this case.

<%@ Import Namespace="System.IO" %>

<asp:button id="btnExportExcel" Text="Export to Excel" onclick="ExcelExport" runat="server" />

 Sub ExcelExport(ByVal Source As Object, ByVal E As EventArgs)
        Export("CompanyList.xls", GridView1)
    End Sub

    Public Shared Sub Export(ByVal fileName As String, ByVal gv As GridView)
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
        HttpContext.Current.Response.ContentType = "application/ms-excel"
        Dim sw As StringWriter = New StringWriter
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        '  Create a form to contain the grid
        Dim table As Table = New Table
        table.GridLines = gv.GridLines
        '  add the header row to the table
        If (Not (gv.HeaderRow) Is Nothing) Then
            PrepareControlForExport(gv.HeaderRow)
            table.Rows.Add(gv.HeaderRow)
        End If
        '  add each of the data rows to the table
        For Each row As GridViewRow In gv.Rows
            PrepareControlForExport(row)
            table.Rows.Add(row)
        Next
        '  add the footer row to the table
        If (Not (gv.FooterRow) Is Nothing) Then
            PrepareControlForExport(gv.FooterRow)
            table.Rows.Add(gv.FooterRow)
        End If
            
        '  render the table into the htmlwriter
        table.RenderControl(htw)
        '  render the htmlwriter into the response
        HttpContext.Current.Response.Write(sw.ToString)
        HttpContext.Current.Response.End()
    End Sub

    ' Replace any of the contained controls with literals
    Private Shared Sub PrepareControlForExport(ByVal control As Control)
        Dim i As Integer = 0
        Do While (i < control.Controls.Count)
            Dim current As Control = control.Controls(i)
            If (TypeOf current Is LinkButton) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))
            ElseIf (TypeOf current Is ImageButton) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))
            ElseIf (TypeOf current Is HyperLink) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))
            ElseIf (TypeOf current Is DropDownList) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))
            ElseIf (TypeOf current Is CheckBox) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))
                'TODO: Warning!!!, inline IF is not supported ?
            End If
            If current.HasControls Then
                PrepareControlForExport(current)
            End If
            i = (i + 1)
        Loop
    End Sub

Here is a server-side approach to clearing all form fields on a form when a button is clicked.

    Protected Sub btnClearFields_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        'First clear all form fields of any existing values
        EmptyTextBoxValues(Me)
    End Sub

    Private Sub EmptyTextBoxValues(ByVal parent As Control)
        For Each c As Control In parent.Controls
            If (c.Controls.Count > 0) Then
                EmptyTextBoxValues(c)
            Else
                If TypeOf c Is TextBox Then
                    CType(c, TextBox).Text = ""
                End If
            
                If TypeOf c Is Dropdownlist Then
                    CType(c, Dropdownlist).SelectedValue = ""
                End If
                
                If TypeOf c Is ListBox Then
                    CType(c, ListBox).SelectedValue = ""
                End If
                
                If TypeOf c Is CheckBox Then
                    CType(c, CheckBox).Checked = False
                End If
            End If
        Next
    End Sub

This is an interesting example useful for many different applications.  Suppose you create a repeater containing multiple rows and each field contains various types of controls (textboxes, dropdownlists, checkboxes etc.) and you want to update every field in every row with a single button click.  The repeater control has no inherent update capabilities but it can be done.

Sub btnUpdateProductDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs)
      
        Dim id_fieldSelected As Integer
        Dim ProductSelected As Integer
        Dim FeatureSelected As Integer
        Dim SectionSelected As Integer
        Dim createdDateSelected As DateTime
        Dim createdBySelected As String = lblEnteredBy.Text
        Dim IsActiveSelected As Boolean
        Dim ProductLabelSelected As Integer
        
        Dim dataItem As RepeaterItem
        For Each dataItem In rptFieldsInProducts.Items
            
            id_fieldSelected = CType(dataItem.FindControl("lblId_field"), Label).Text
            ProductSelected = CType(dataItem.FindControl("ddlProducts"), DropDownList).SelectedValue
            FeatureSelected = CType(dataItem.FindControl("ddlFeatures"), DropDownList).SelectedValue
            SectionSelected = CType(dataItem.FindControl("ddlSections"), DropDownList).SelectedValue
            createdDateSelected = CType(dataItem.FindControl("lblCreatedDate"), Label).Text
            IsActiveSelected = CType(dataItem.FindControl("chkIsActive"), CheckBox).Checked
            ProductLabelSelected = CType(dataItem.FindControl("ddlProductLabel"), DropDownList).SelectedValue   
            
            'Updates the repeater row
            'The stored procedure that updates this needs these parameter
            'id_field, id_product, id_ProductFeature, id_ProductSection, createdDate, createdBy, IsActive, id_ProductLabel
            
            UpdateProductDetails(id_fieldSelected, ProductSelected, FeatureSelected, SectionSelected, createdDateSelected, _
createdBySelected, IsActiveSelected, ProductLabelSelected)
        Next
    End Sub
    
    Sub UpdateProductDetails(ByVal id_fieldSelected As Integer, ByVal ProductSelected As Integer, ByVal FeatureSelected As Integer, _
                             ByVal SectionSelected As Integer, ByVal createdDateSelected As DateTime, ByVal createdBySelected As String, _
                             ByVal IsActiveSelected As Boolean, ByVal ProductLabelSelected As Integer)
        
        Dim strConnection As SqlConnection
        Dim strSql As String
        Dim cmd As SqlCommand
        
        strConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("conString_FieldInventory").ConnectionString)
        strSql = "proc_InsertFieldsInProducts"
        cmd = New SqlCommand(strSql)
        cmd.CommandType = CommandType.StoredProcedure
        
        cmd.Parameters.AddWithValue("@id_field", SqlDbType.Int).Value = id_fieldSelected
        cmd.Parameters.AddWithValue("@id_Product", SqlDbType.Int).Value = ProductSelected
        cmd.Parameters.AddWithValue("@id_ProductFeature", SqlDbType.Int).Value = FeatureSelected
        cmd.Parameters.AddWithValue("@id_ProductSection", SqlDbType.Int).Value = SectionSelected
        cmd.Parameters.AddWithValue("@createdDate", SqlDbType.DateTime).Value = createdDateSelected
        cmd.Parameters.AddWithValue("@createdBy", SqlDbType.VarChar).Value = createdBySelected
        cmd.Parameters.AddWithValue("@IsActive", SqlDbType.Bit).Value = IsActiveSelected
        cmd.Parameters.AddWithValue("@id_ProductLabel", SqlDbType.Int).Value = ProductLabelSelected
        cmd.Parameters.AddWithValue("@batchCreatedDate", SqlDbType.DateTime).Value = System.DateTime.Now 

        
        Try
            
           strConnection.Open()
            cmd.Connection = strConnection
            cmd.ExecuteNonQuery()
        Finally
            strConnection.Close()
        End Try
    End Sub

Here is the code for the repeater used for the code above

<asp:SqlDataSource ID="dsProductName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductName), id_Product FROM Products 
WHERE ProductName IS NOT NULL ORDER BY ProductName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsFeatureName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(FeatureName), id_ProductFeature FROM ProductFeatures 
WHERE FeatureName IS NOT NULL ORDER BY FeatureName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsSectionName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(SectionName), id_ProductSection FROM ProductSections 
WHERE SectionName IS NOT NULL ORDER BY SectionName">
</asp:SqlDataSource>

<asp:SqlDataSource ID="dsProductLabel" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductLabel), id_ProductLabel FROM ProductLabels 
WHERE ProductLabel IS NOT NULL ORDER BY ProductLabel">
</asp:SqlDataSource>

<asp:Repeater id="rptFieldsInProducts" runat="server" OnItemDataBound="ddlProducts_ItemDataBound">
      <HeaderTemplate>
         <fieldset style="width: 900px">
         <legend><h4 class="pageHeader">Product Details</h4></legend>
         <table width="900px" Cellpadding="3" CellSpacing="1" border="0">
             <tr>
                <td class="fieldDetailLabel" width="15%">Product</td>
                <td class="fieldDetailLabel" width="15%">Feature</td>
                <td class="fieldDetailLabel" width="15%">Section</td>                
                <td class="fieldDetailLabel" width="15%">Label</td>
                <td class="fieldDetailLabel" width="15%">Is Active?</td>
            </tr>                  
      </HeaderTemplate>
      <ItemTemplate>                   
             <tr>
               
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlProducts" Runat="Server"
                    DataSourceID="dsProductName"
                    DataTextField="ProductName" 
                    DataValueField="id_Product"
                    CssClass="fieldControl" />
               </td>
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlFeatures" Runat="Server"
                    DataSourceID="dsFeatureName"
                    DataTextField="FeatureName" 
                    DataValueField="id_ProductFeature"
                    CssClass="fieldControl" />
               </td>      
               <td class="fieldControl" width="25%">
               <asp:DropDownList id="ddlSections" Runat="Server"
                    DataSourceID="dsSectionName"
                    DataTextField="SectionName" 
                    DataValueField="id_ProductSection"
                    CssClass="fieldControl" />
               </td>                                            
               <td>
               <asp:DropDownList id="ddlProductLabel" Runat="Server"
                    DataSourceID="dsProductLabel"
                    DataTextField="ProductLabel" 
                    DataValueField="id_ProductLabel"
                    CssClass="fieldControl" />
               </td>
               <td><asp:Checkbox ID="chkIsActive" runat="server" Checked='<%# Eval("IsActive") %>' /></td>
               <td><asp:Label ID="lblId_Field" runat="server" Text='<%# Eval("id_field") %>' class="displayNone" />
               <asp:Label ID="lblCreatedDate" runat="server" Text='<%# Eval("createdDate") %>' class="displayNone" />
               <asp:Label ID="lblCreatedBy" runat="server" Text='<%# Eval("createdBy") %>' class="displayNone" /></td>           
            </tr>
      </ItemTemplate>
      <FooterTemplate>
         <tr>
             <td colspan="5" align="center"><asp:Button ID="btnUpdateProductDetails" Text="Update Product Details" 
CssClass="button" runat="server" OnClick="btnUpdateProductDetails_Click"/></td>
         </tr>           
          </table>         
      </FooterTemplate>
   </asp:Repeater>

I have a dropdownlist inside of a repeater control and I need to make the selected item in the dropdown list the same selected item from the database.

Here’s how I did it.

Protected Sub rptProducts_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)
        Dim ddlProducts As DropDownList = e.Item.FindControl("ddlProducts")
        If Not e.Item.FindControl("ddlProducts") Is Nothing Then
            ddlProducts.Items.FindByValue(CType(System.Web.UI.DataBinder.Eval(e.Item.DataItem, "id_product"), Object)).Selected = True
        End If    
End Sub 

<asp:SqlDataSource ID="dsProductName" runat="server" ConnectionString="<%$ ConnectionStrings:conString_FieldInventory%>"
    ProviderName="System.Data.SqlClient" SelectCommand="SELECT DISTINCT(ProductName), id_Product FROM Products 
WHERE ProductName IS NOT NULL ORDER BY ProductName">
</asp:SqlDataSource>


<asp:Repeater id="rptFieldsInProducts" runat="server" OnItemDataBound="ddlProducts_ItemDataBound">      
      <ItemTemplate>                   
            
               <asp:DropDownList id="ddlProducts" Runat="Server"
                    DataSourceID="dsProductName"
                    DataTextField="ProductName" 
                    DataValueField="id_Product"
                    CssClass="fieldControl" /> 
             
      </ItemTemplate>      
</asp:Repeater>

The solution runs into problems if there aren’t any records to show for the Products section and fails with the error Conversion from type 'DBNull' to type 'String' is not valid.

Protected Sub rptProducts_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs)
        Dim ddlProducts As DropDownList = e.Item.FindControl("ddlProducts")
        Dim temp As Object

        If Not e.Item.FindControl("ddlProducts") Is Nothing Then
            temp = CType(System.Web.UI.DataBinder.Eval(e.Item.DataItem, "id_Product"), Object)
            If Not IsDBNull(temp) Then
                ddlProducts.Items.FindByValue(temp.ToString).Selected = True
            End If
        End If
    End Sub

In classic ASP, it was very simple to include external content inside of your pages using INCLUDE FILE

<!--#INCLUDE FILE="../../dbase_connection.asp"—>

I didn’t think this was possible in ASP.NET until I came across this little example from MSDN.

<%@ Page Language="vb" %>
   <html>
   <body>
        <%           
          Response.WriteFile ("Myfile.inc")
        %>
   </body>
   </html>

I’ve been working on a project recently where I needed to prevent users from adding the same value twice to a string.

Here is a working demo.

<script runat="server" type="text/VB">

    Sub btnRemoveDupes_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim dupeValues As String
        dupeValues = lblWithDupes.Text
                
        'Remove duplicates
        dupeValues = RemoveDuplicates(dupeValues)
                
        'Reassign the label value without the duplicates
        lblNoDupes.Text = dupeValues
    End Sub


    Public Function RemoveDuplicates(ByVal items As String) As String
        Dim Result As StringBuilder = New StringBuilder()
        Dim newArray As Array
    
        newArray = Split(items, ",")
        For i As Integer = 0 To newArray.Length - 1
            If Result.ToString.IndexOf(newArray(i).ToString()) = -1 Then
                Result.Append(newArray(i).ToString() & ",")
            End If
        Next
        Return Result.ToString.Substring(0, Result.ToString.LastIndexOf(","))
    End Function

</script>

<form id="form1" runat="server">
    <div>
    
    Enter a comma delimited string with some duplicate values:<br />
    <asp:TextBox runat="server" ID="lblWithDupes" Width="400px" />
    <br /><br />
    
    The result without the duplicate values is:<br />
    <asp:Label runat="server" ID="lblNoDupes" /> <br /><br />
       
    <asp:Button ID="btnRemoveDupes" runat="server" Text="Button" OnClick="btnRemoveDupes_Click" /> 
    </div>
</form>

Great article with examples:

http://www.aspsnippets.com/post/Get-ID-of-the-newly-inserted-record-in-SQL-Server-using-ADONet.aspx

Here is a working example.

There are a lot of paid as well as free services that can find Visitor Geographical information like country, region, city, latitude, longitude, ZIP code, time zone etc from I.P Addresses. http://iplocationtools.com/ip_location_api.php is one such free IP Geolocation API that returns geographical data in three formats: XML, JSON and CSV. In this article, we will consume this API and fetch visitor geographical information in XML format in the simplest possible way, using LINQ To XML.

Using the IP Address Location XML API is easy. Just specify the I.P. address as shown below http://iplocationtools.com/ip_query2.php?ip=74.125.45.100 and you will receive an XML document with the visitor geographical information in the following format:

<Locations>
 <Location id="0"> 
    <Ip>74.125.45.100</Ip> 
    <Status>OK</Status> 
    <CountryCode>US</CountryCode> 
    <CountryName>United States</CountryName> 
    <RegionCode>06</RegionCode> 
    <RegionName>California</RegionName> 
    <City>Mountain View</City> 
    <ZipPostalCode>94043</ZipPostalCode> 
    <Latitude>37.4192</Latitude> 
    <Longitude>-122.057</Longitude> 
 </Location> 
</Locations>

In order to specify multiple I.P(maximum 25), use a comma(,) separator as shown here: http://iplocationtools.com/ip_query2_country.php?ip=74.125.45.100,206.190.60.37

We will use the XDocument class to consume the XML API in just two lines of code.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Xml.Linq" %>

<script runat="server">

Protected Sub btnGetLoc_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGetLoc.Click
        Dim url As String = String.Empty

        If txtIP.Text.Trim() <> String.Empty Then
            url = String.Format("http://iplocationtools.com/ip_query2.php?ip={0}", txtIP.Text.Trim())
            Dim xDoc As XDocument = XDocument.Load(url)
            
            If xDoc Is Nothing Or xDoc.Root Is Nothing Then
                Throw New ApplicationException("Data is not Valid")
            End If

            Xml1.TransformSource = "IP.xslt"
            Xml1.DocumentContent = xDoc.ToString()
        End If
End Sub

</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>IP Address Location</title>
    <style type="text/css">
        body
        {
            font: normal 11px auto "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;    
            background-color: #ffffff;
            color: #4f6b72;       
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:ScriptManager ID="ScriptManager1" runat="server" />

        <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <asp:Panel ID="panelLoc" runat="server">
                    <asp:TextBox ID="txtIP" runat="server"></asp:TextBox>
                        <asp:Button ID="btnGetLoc" runat="server" Text="Get IP Details" />
                    <br />
                    <asp:Xml ID="Xml1" runat="server"></asp:Xml>
                </asp:Panel>
            </ContentTemplate>
        </asp:UpdatePanel>
        
        <asp:UpdateProgress ID="updProgress" AssociatedUpdatePanelID="UpdatePanel1" runat="server">
            <ProgressTemplate><img alt="progress" src="/images/progress.gif"/> </ProgressTemplate>
        </asp:UpdateProgress>

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

Our XSLT (IP.xslt) will look similar to the following:

<?xmlversion='1.0'?>
<xsl:stylesheetxmlns:xsl="http://www.w3.org/1999/XSL/Transform"version="1.0">
 <xsl:templatematch="/">
    <HTML>
      <BODY>
        <TABLEcellspacing="3"cellpadding="8">
          <TR>
            <TDclass="heading">
              <B>Ip</B>
            </TD>
            <TDclass="heading">
              <B>Status</B>
            </TD>
            <TDclass="heading">
              <B>CountryCode</B>
            </TD>
            <TDclass="heading">
              <B>CountryName</B>
            </TD>
            <TDclass="heading">
              <B>RegionCode</B>
            </TD>
            <TDclass="heading">
              <B>RegionName</B>
            </TD>
            <TDclass="heading">
              <B>City</B>
            </TD>
            <TDclass="heading">
              <B>ZipPostalCode</B>
            </TD>
            <TDclass="heading">
              <B>Latitude</B>
            </TD>
            <TDclass="heading">
              <B>Longitude</B>
            </TD>
          </TR>
          <xsl:for-eachselect="Locations/Location">
            <TRbgcolor="#C1DAD7">
              <TDwidth="5%"valign="top">
                <xsl:value-ofselect="Ip"/>
              </TD>
              <TDwidth="5%"valign="top">
                <xsl:value-ofselect="Status"/>
              </TD>
              <TDwidth="10%"valign="top">
                <xsl:value-ofselect="CountryCode"/>
              </TD>
              <TDwidth="10%"valign="top">
                <xsl:value-ofselect="CountryName"/>
              </TD>
              <TDwidth="10%"valign="top">
                <xsl:value-ofselect="RegionCode"/>
              </TD>
              <TDwidth="10%"valign="top">
                <xsl:value-ofselect="RegionName"/>
              </TD>
              <TDwidth="10%"valign="top">
                <xsl:value-ofselect="City"/>
              </TD>
              <TDwidth="10%"valign="top">
                <xsl:value-ofselect="ZipPostalCode"/>
              </TD>
              <TDwidth="10%"valign="top">
                <xsl:value-ofselect="Latitude"/>
              </TD>
              <TDwidth="10%"valign="top">
                <xsl:value-ofselect="Longitude"/>
              </TD>
            </TR>
          </xsl:for-each>
        </TABLE>
      </BODY>
    </HTML>
 </xsl:template>
</xsl:stylesheet>

http://technet.microsoft.com/en-us/magazine/dd797568.aspx

This example shows how you can use a DbTransaction object to add transaction context when using the SqlDataSource control to update data.

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

<script runat="server">

 Sub On_Click(ByVal source As Object, ByVal e As EventArgs)
        SqlDataSource1.Update()
 End Sub 'On_Click

 Sub On_Sql_Updating(ByVal source As Object, ByVal e As SqlDataSourceCommandEventArgs)
     Dim command as DbCommand
     Dim connection as DbConnection
     Dim transaction as DbTransaction

     command = e.Command
     connection = command.Connection     
     connection.Open()     
     transaction = connection.BeginTransaction()
     command.Transaction = transaction

 End Sub 'On_Sql_Updating

 Sub On_Sql_Updated(ByVal source As Object, ByVal e As SqlDataSourceStatusEventArgs)

    Dim command As DbCommand
    Dim transaction As DbTransaction

    command = e.Command
    transaction = command.Transaction

    ' We must succeed for the data change to be committed. For 
    ' simplicity, we set this value to true. 
    Dim OtherProcessSucceeded as Boolean = True

    If (OtherProcessSucceeded) Then
        transaction.Commit()
        Label2.Text="The record was updated successfully!"
    Else    
        transaction.Rollback()
        Label2.Text="The record was not updated."
    End If
    End Sub 'On_Sql_Updated
</script>

<html  >
  <head id="Head1" runat="server">
    <title>Command Transaction</title>
</head>
<body>
    <form id="form1" runat="server">
      <asp:SqlDataSource
          id="SqlDataSource1"
          runat="server"
          ConnectionString="<%$ ConnectionStrings:MyDatabase%>"
          SelectCommand="SELECT ID, Title, Director FROM Movies"
          UpdateCommand="UPDATE Movies SET Title=@Title WHERE ID=@ID"
          OnUpdating="On_Sql_Updating"
          OnUpdated ="On_Sql_Updated">
          <UpdateParameters>
              <asp:ControlParameter Name="Title" ControlId="TextBox1" PropertyName="Text" />
              <asp:ControlParameter Name="ID" ControlId="DropDownList1" PropertyName="SelectedValue" />
          </UpdateParameters>
      </asp:SqlDataSource>

      <asp:DropDownList
          id="DropDownList1"
          runat="server"
          DataTextField="Title"
          DataValueField="ID"
          DataSourceID="SqlDataSource1">
      </asp:DropDownList>

      <br /><br />
      <asp:Label id="Label1" runat="server" Text="Enter a new movie title." AssociatedControlID="TextBox1" />
      <asp:TextBox id="TextBox1" runat="server" />
      <asp:Button id="Submit" runat="server" Text="Submit" OnClick="On_Click" />

      <br /><asp:Label id="Label2" runat="server" Text="" />

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

One of the problems with the DBDataReader object is that it keeps an open server-side connection while you are looping through the results of a query.  If you try to execute another command while the first command is still executing, you receive an 'InvalidoperationException' stating that "There is already an open DataReader associated with this connection which must be closed first."  You can avoid this exception by setting the MultipleActiveResultSets connection string option to True when connecting to SQL Server 2005 and above.

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

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("PublishersConnectionString").ConnectionString)

        'Create the command object, passing in the SQL string
        Dim strSQL As String = "SELECT au_id, LastName FROM Publishers"
        Dim cmd As New SqlCommand(strSQL)

        Try
            con.Open()
            cmd.Connection = con
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Dim cmdSales As SqlCommand
                cmdSales.CommandType = CommandType.Text
                cmdSales.CommandText = "SELECT  SUM(royaltyper) FROM TitleAuthor WHERE (au_id = @auID)"
                cmdSales.Parameters.AddWithValue("@au_id", reader("au_id"))
                Dim qtySales As Object = cmdSales.ExecuteScalar()
                lbl.Text += reader("LastName").ToString() + ": " + String.Format("{0:C}", qtySales) + "<br />"
            End While
        Finally
            con.Close()
            con.Dispose()
            End
        End Try
    End Sub

Another example and working demo using a TreeView:

http://stonecoastwebdesign.com/code/ShowMARS.aspx

The built-in functions @@Identity() and Scope_Identity() are designed to retrieve the most recently added record's autoincrement identity value from Access and Sql Server respectively. Here are some usage examples.

Access and @@Identity()

The Jet 4.0 provider supports @@Identity(), which means that developers no longer need to use Select Max(ID) or some other method. The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.

Dim query As String = "Insert Into Categories (CategoryName) Values (?)"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=|DataDirectory|Northwind.mdb"
Using conn As New OleDbConnection(connect)
  Using cmd As New OleDbCommand(query, conn)
    cmd.Parameters.AddWithValue("", Category.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
    cmd.CommandText = query2
    ID = cmd.ExecuteScalar()
  End Using
End Using

SQL Server and Scope_Identity()

While Sql Server also supports @@Identity(), the recommended method for obtaining identity values on this platform is Scope_Identity(),

Dim query As String = "Insert Into Categories (CategoryName) Values (@CategoryName);" & _
    "Select Scope_Identity()"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" & _
    "Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.Parameters.AddWithValue("@CategoryName", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

Alternatively, you may prefer to use an output parameter from a stored procedure, and ExecuteNonQuery().

Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID")
    cmd.Parameters("@CategoryID").Direction = ParameterDirection.Output
    conn.Open()
    cmd.ExecuteNonQuery()
    ID = cmd.Parameters("@CategoryID").Value
  End Using
End Using

CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category nvarchar(15),
  @CategoryID int OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  Insert Into Categories (CategoryName) Values (@Category)
  Set @CategoryID = Scope_Identity()
END

Finally, you can create a stored procedure that contains no output parameter, but ends with 'Select Scope_Identity()'. This version requires ExecuteScalar(), and requires less ADO.NET code and a shorter Stored Procedure.

Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
  Using cmd As New SqlCommand(query, conn)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.AddWithValue("@Category", Category.Text)
    conn.Open()
    ID = cmd.ExecuteScalar()
  End Using
End Using

The stored procedure:

CREATE PROCEDURE AddCategory
  -- Add the parameters for the stored procedure here
  @Category nvarchar(15)
AS
BEGIN
  SET NOCOUNT ON;

  -- Insert statements for procedure here
  Insert Into Categories (CategoryName) Values (@Category)
  Select Scope_Identity()
END

I've done this many times in the past with other applications using the code below but once I moved the application into a Sharepoint environment coupled with AJAX extensions, I kept receiving the dreaded "RegisterForEventValidation can only be called during Render" error message. 

Sub ExcelExport(ByVal Source As Object, ByVal E As EventArgs)                    
            MyGrid.AllowPaging = "False"
            MyGrid.AllowSorting = "False"
            Dim tw As New StringWriter()
            Dim hw As New System.Web.UI.HtmlTextWriter(tw)
            Dim frm As HtmlForm = New HtmlForm()
            Response.ContentType = "application/vnd.ms-excel"
            Response.AddHeader("content-disposition", "attachment;filename=IT.Work.Requests.xls")
            Response.Charset = ""
            EnableViewState = False
            Controls.Add(frm)
            frm.Controls.Add(MyGrid)
            frm.RenderControl(hw)
            Response.Write(tw.ToString())
            Response.End()
            MyGrid.AllowPaging = "True"
            MyGrid.DataBind()
    End Sub

The most common fix for this problem is to set EnableEventValidation="false" in the page directive but this wasn't working for me either. 

Another common suggestion is to use the VerifyRenderingInServerForm method which ensures than a Htmlform control is rendered at the runtime.

Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control) 
    

End Sub 

Finally, which ultimately fixed the problem was Matt Berseth's blog post on exporting data from a gridview to Excel.  The VB version of the code is below.

** Default.aspx **

<%@ Page Language="VB" AutoEventWireup="true"  CodeFile="Default.aspx.vb" Inherits="_Default" %>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:SqlDataSource 
                ID="sqldsCustomers" runat="server" 
                SelectCommand="select * from dbo.customers" SelectCommandType="Text" 
                ConnectionString="server=PCFRED\SQLEXPRESS;database=northwind;Trusted_Connection=yes;" /> 
            
            <asp:GridView 
                id="gvCustomers" runat="server" 
                AllowPaging="true" AllowSorting="true" PageSize="10" DataSourceID="sqldsCustomers" />
            <asp:Button 
                ID="btnExportGrid" runat="server" 
                Text="Export to Excel" OnClick="BtnExportGrid_Click" />
        </div>
    </form>
</body>
</html>

** Default.aspx.vb **

Imports System
Imports System.Data
Imports System.Configuration
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls

Public Class _Default
    Inherits System.Web.UI.Page
    
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        
    End Sub
    
    Protected Sub BtnExportGrid_Click(ByVal sender As Object, ByVal args As EventArgs)
        '  pass the grid that for exporting ...
        GridViewExportUtil.Export("Customers.xls", Me.gvCustomers)
    End Sub
End Class

** App_Code/GridviewExportUtil.vb **

Imports System
Imports System.Data
Imports System.Configuration
Imports System.IO
Imports System.Web
Imports System.Web.Security
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.WebControls.WebParts
Imports System.Web.UI.HtmlControls

Public Class GridViewExportUtil

    Public Shared Sub Export(ByVal fileName As String, ByVal gv As GridView)
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", fileName))
        HttpContext.Current.Response.ContentType = "application/ms-excel"
        Dim sw As StringWriter = New StringWriter
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        '  Create a form to contain the grid
        Dim table As Table = New Table
        table.GridLines = gv.GridLines
        '  add the header row to the table
        If (Not (gv.HeaderRow) Is Nothing) Then
            GridViewExportUtil.PrepareControlForExport(gv.HeaderRow)
            table.Rows.Add(gv.HeaderRow)
        End If
        '  add each of the data rows to the table
        For Each row As GridViewRow In gv.Rows
            GridViewExportUtil.PrepareControlForExport(row)
            table.Rows.Add(row)
        Next
        '  add the footer row to the table
        If (Not (gv.FooterRow) Is Nothing) Then
            GridViewExportUtil.PrepareControlForExport(gv.FooterRow)
            table.Rows.Add(gv.FooterRow)
        End If
        '  render the table into the htmlwriter
        table.RenderControl(htw)
        '  render the htmlwriter into the response
        HttpContext.Current.Response.Write(sw.ToString)
        HttpContext.Current.Response.End()
    End Sub

    ' Replace any of the contained controls with literals
    Private Shared Sub PrepareControlForExport(ByVal control As Control)
        Dim i As Integer = 0
        Do While (i < control.Controls.Count)
            Dim current As Control = control.Controls(i)
            If (TypeOf current Is LinkButton) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, LinkButton).Text))
            ElseIf (TypeOf current Is ImageButton) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, ImageButton).AlternateText))
            ElseIf (TypeOf current Is HyperLink) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, HyperLink).Text))
            ElseIf (TypeOf current Is DropDownList) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, DropDownList).SelectedItem.Text))
            ElseIf (TypeOf current Is CheckBox) Then
                control.Controls.Remove(current)
                control.Controls.AddAt(i, New LiteralControl(CType(current, CheckBox).Checked))
                'TODO: Warning!!!, inline IF is not supported ?
            End If
            If current.HasControls Then
                GridViewExportUtil.PrepareControlForExport(current)
            End If
            i = (i + 1)
        Loop
    End Sub
End Class

This is based on Matt Beserth's article, Modifying Data with the ListView's EditItemTemplate. 

Here is my own example.

This is a simple login user control used to demonstrate how to expose (get and set) the properties associated with the control. Here is a working demo.

** LoginUserControl.ascx **

<script language="VB" runat="server">

  Public BackColor As String = "white"

  Public Property Login As String
    Get
      Return txtLogin.Text
    End Get
    Set
      txtLogin.Text = Value
    End Set
  End Property

  Public Property Password As String
    Get
      Return txtPassword.Text
    End Get
    Set
      txtPassword.Text = Value
    End Set
  End Property
  
  Public Property Status As String
  Get
      Return lblStatus.Text
  End Get
  Set
      lblStatus.Text = Value
  End Set
  End Property

</script>

<table style="background-color:<%=BackColor%>;font: 10pt verdana;border-width:1;border-style:solid;border-color:black;" cellspacing=15>
  <tr>
    <td><b>Login: </b></td>
    <td><ASP:TextBox id="txtLogin" runat="server"/></td>
  </tr>
  <tr>
    <td><b>Password: </b></td>
    <td><ASP:TextBox id="txtPassword" TextMode="Password" runat="server"/></td>
  </tr>
  <tr>
    <td></td>
    <td><ASP:Button Text="Submit" runat="server"/></td>
  </tr>
  <tr>
    <td></td>
    <td><asp:Label id="lblStatus" runat="server"/></td>
  </tr>  
</table>

** LoginControl.aspx **

<%@ Register TagPrefix="MyUserControl" TagName="Login" Src="LoginUserControl.ascx" %>

<html>

<script language="VB" runat="server">
    Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
        If (Page.IsPostBack) Then
            MyLabel.Text &= "The UserId is " & MyLogin.Login & "<br>"
            MyLabel.Text &= "The Password is " & MyLogin.Password & "<br>"
            MyLogin.Status = "Hello world"
        End If
    End Sub
</script>

<body style="font: 10pt verdana">
  <h3>A Login User Control</h3>

  <form runat="server">
    <MyUserControl:Login id="MyLogin" UserId="John Doe" Password="Secret" BackColor="beige" runat="server"/>
  </form>
  <asp:Label id="MyLabel" runat="server"/>
</body>
</html>

Having the ability to check or uncheck all of the checkboxes which appear inside of a GridView is a frequent requirement.  There are several javascript libraries which will handle this on the client-side which is usually the better way to go, but this can also be done server-side and along with an AJAX UpdatePanel can be pretty elegant.  Here is a working demo.

<%@ Page Language="VB" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
       
    Protected Sub MasterCheck_Click(ByVal sender As Object, ByVal e As CommandEventArgs)
        'Enumerate each GridViewRow
        For Each gvr As GridViewRow In GridView1.Rows
            'Programmatically access the CheckBox from the TemplateField
            Dim cb As CheckBox = CType(gvr.FindControl("Checkbox1"), CheckBox)
            
            Select Case e.CommandName

                Case "Check"
                    'Check all of the checkboxes
                    cb.Checked = True
                    'Change the CommandName, CommandArgument and Text of the button
                    btnMasterCheck.CommandName = "Uncheck"
                    btnMasterCheck.CommandArgument = "Uncheck"
                    btnMasterCheck.Text = "Uncheck All"
                Case "Uncheck"
                    'Uncheck all of the checkboxes
                    cb.Checked = False
                    'Change the CommandName, CommandArgument and Text of the button
                    btnMasterCheck.CommandName = "Check"
                    btnMasterCheck.CommandArgument = "Check"
                    btnMasterCheck.Text = "Check All"
                Case Else

            End Select
        Next
    End Sub

</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Check/Uncheck All Checkboxes in a GridView</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:SqlDataSource
        id="srcMovies"
        ConnectionString='<%$ ConnectionStrings:MyDatabase %>'
        SelectCommand="SELECT Title, Director FROM Movies ORDER BY Title"
        Runat="server" />
        
        <asp:ScriptManager id="sm1" runat="server" />

    <asp:UpdatePanel id="UpdatePanel1" Runat="server">
   
    <ContentTemplate>
        <asp:GridView ID="GridView1" runat="server" DataSourceID="srcMovies" AutoGenerateColumns="False">
        <Columns>
         <asp:TemplateField HeaderText="">
                 <ItemTemplate>
                    <asp:Checkbox id="Checkbox1" runat="server"  /> 
                 </ItemTemplate>
          </asp:TemplateField> 
         
         <asp:BoundField HeaderText="Title" DataField="Title" />
         <asp:BoundField HeaderText="Director" DataField="Director" />
        </Columns>
        </asp:GridView><br />
        <asp:Button ID="btnMasterCheck" runat="server" CommandName="Check" CommandArgument="Check" OnCommand="MasterCheck_Click" Text="Check All" />
    </ContentTemplate>
    </asp:UpdatePanel>
    
    </div>
    </form>
</body>
</html>

User controls are somewhat outdated already with the introduction of partial classes in ASP.NET 2.0 but I still find them useful.  I think of them similar to the include pages in classic ASP. This is a simple example of how to get the text value from a user control on the parent page.

<%@ Page Language="VB" %>
<%@ Register Src="header.ascx" TagName="header" TagPrefix="uc1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

Sub Page_Load(ByVal Src As Object, ByVal E As EventArgs)
    Dim CompanyName As TextBox = CType(Page.FindControl("Header1$txtCompanyName"), TextBox)
    lblCompanyName.Text = CompanyName.Text   
End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link href="StyleSheet.css" rel="stylesheet" type="text/css" />
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <!--This is my user control page header-->
     <uc1:header id="Header1" runat="server"></uc1:header>
     <br />
     <br />
     <asp:Label ID="lblCompanyName" runat="server" Text="Label" />     
     <asp:TextBox ID="TextBox1" runat="server" />    
    </div>
    </form>
</body>
</html>

This is an example of how to set the text value from inside the user control on the parent page.

<%@ Control Language="VB" ClassName="header" EnableViewState="false" %>

<script runat="server">
        
    Private Sub Page_Load(ByVal Src As Object, ByVal E As EventArgs)
        If Not Page.IsPostBack Then                        
            Dim TextBox1 As Textbox =CType(Me.Parent.FindControl("TextBox1"),TextBox)
            TextBox1.Text = "Eat your veggies"            
        End If
    End Sub
</script>
         

<table width="100%" border="0" cellpadding="3" cellspacing="0">
<tr>
    <td>Company: <asp:Textbox ID="txtCompanyName" runat="server" CssClass="textbox" Text="Company Name" ToolTip="Enter a company name" /> 
<asp:Button ID="btnCompanySearch" runat="server" Text="go" CssClass="buttons" OnClick="QuickSearch_Companies_Click" /></td>
     
</tr>    
</table>

In the previous example, Using a Timer Control With an Update Panel, we just refreshed a panel from data in a list.  In this more practical example, we pull the data from a live database which refreshes an UpdatePanel and an AJAX script manager.  Here is a working demo.

<%@ Page Language="vb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Timer Movies</title>
    <style type="text/css">
    
    .message
    {
        margin-left: 20px;
        font-style:italic;
    }
    
    </style>
</head>
<body>
    <form id="form1" runat="server">
    
    <asp:ScriptManager ID="sm1" runat="server" />
    
    <asp:Timer ID="Timer1" Interval="5000" runat="server" />
    
    <asp:UpdatePanel ID="up1" runat="server">
    <Triggers>
        <asp:AsyncPostBackTrigger ControlID="Timer1" EventName="Tick" />
    </Triggers>
    <ContentTemplate>
    Last Refresh <%= DateTime.Now.ToString("T") %><hr />
    <asp:ListView
        id="lstMovies"
        DataSourceID="srcMovies"
        Runat="server">
        <LayoutTemplate>
            <div id="itemPlaceholder" runat="server">
            </div>
        </LayoutTemplate>
        <ItemTemplate>
            <div>
                Movie Title: <%# Eval("Title") %><div class="message">
                Director: <%# Eval("Director") %></div>
            </div>    
        </ItemTemplate>
    </asp:ListView>    
    </ContentTemplate>      
    </asp:UpdatePanel>
    
    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="Message"
        SelectMethod="Select"
        Runat="server" />
    
    </form>
</body>
</html>

*** Message.vb ***

Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Public Class Message

    Private _Title As String

    Public Property Title() As String
        Get
            Return _Title
        End Get
        Set(ByVal value As String)
            _Title = value
        End Set
    End Property

    Private _Director As String

    Public Property Director() As String
        Get
            Return _Director
        End Get
        Set(ByVal value As String)
            _Director = value
        End Set
    End Property


    Public Shared Function [Select]() As ArrayList
        Dim results As New ArrayList()
        Dim conString As String = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
        Dim commandText As String = "SELECT Title, Director FROM Movies ORDER BY Id DESC"
        Dim con As New SqlConnection(conString)
        Dim cmd As New SqlCommand(commandText, con)
        Using con
            con.Open()
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                results.Add(New Message(reader))
            End While
        End Using
        Return results
    End Function

    Public Sub New(ByVal reader As SqlDataReader)
        Title = CType(reader("Title"), String)
        Director = CType(reader("Director"), String)
    End Sub

End Class

This is an interesting example on how to use the Timer control inside of an UpdatePanel to refresh part of a page with no user intervention.   Here is a working demo.

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Collections.Generic" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">

    protected sub Page_Load(sender as object, e as EventArgs)
        dim quotes as new List(of string)()
        quotes.Add("A fool and his money are soon parted")
        quotes.Add("A penny saved is a penny earned")
        quotes.Add("An apple a day keeps the doctor away")
        
        dim rnd as new Random()
        lblQuote.Text = quotes(rnd.Next(quotes.Count))
    end sub
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Timer Quote</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:ScriptManager ID="ScriptManager1" runat="server" />
    
    <asp:Timer ID="Timer1" Interval="2000" runat="server" />

    Page Time: <%= DateTime.Now.ToString("T") %>
    
    <fieldset>
    <legend>Quote</legend>
    <asp:UpdatePanel ID="up1" runat="server">
    <Triggers>
        <asp:AsyncPostBackTrigger ControlID="Timer1" EventName="Tick" />
    </Triggers>
    <ContentTemplate>
        <asp:Label ID="lblQuote" runat="server" />
    </ContentTemplate>
    </asp:UpdatePanel>
    </fieldset>
        
    </div>
    </form>
</body>
</html>

The web.config file is commonly used to add database connection strings, mail server settings, system-wide settings etc.  It's easier to store such settings here rather than hard-code them within the pages for obvious reasons. Adding custom key/value pairs in the web.config is pretty easy. Just make sure that the <appSettings> section is outside of the <system.web> section.

<configuration>

<appSettings>
      <add key="myCity" value="Anchorage" />
</appSettings>

<system.web>

....

</system.web>

This key/value is accessed in your application like this:

Public Sub Page_Load()
         Dim strCity As String
         strCity = ConfigurationSettings.AppSettings("myCity")
End Sub

Another way to return a custom key's value is to pass the key name to a function.  This function also adds the key name and value to cache if it's not already there

'Return the value of the specified custom key, stored in the Web.Config file.
' If the value has been already requested before, the function returns its 
' cached value.
' The second optional parameter is the path of the Web.Config file where this 
' custom key is stored, and it is necessary to add a dependency to that file,
' so that the cached value is discarded if the file is edited.
' 
' Example: Dim myCity = GetCustomKeyValue("myCity") 

Function GetCustomKeyValue(ByVal key As String, Optional ByVal webConfigUrl As String = "/Web.Config") As String
    ' if this is not the first time this value is needed,
    '  we can find it in the cache
    Dim value As String = CType(HttpContext.Current.Cache(key), String) 

    ' if the retrieved string is empty, the value is not present into the cache,
    '  thus it is retrieved it from Web.Config, and then cached
    If value Is Nothing OrElse value = "" Then
        value = ConfigurationSettings.AppSettings(key)
        HttpContext.Current.Cache.Insert(key, value, New Caching.CacheDependency(webConfigUrl))
    End If 

    Return value
End Function

Lastly, ASP.NET 2.0 makes it really easy to encrypt parts of the web.config to secure sensitive parts of the file. Here are some articles on how to do it.

http://odetocode.com/Blogs/scott/archive/2006/01/08/2707.aspx

http://www.developerfusion.co.uk/show/5263/

http://channel9.msdn.com/Showpost.aspx?postid=134210

Not only can you encrypt config sections using aspnet_regiis from the command line, but you can also encrypt and unencrypt Web.config on the fly in code. The code for protecting and unprotecting sections in your Web.config is fairly trivial, because WebConfigurationManager-related classes handle all the work for you.  I added two buttons to a web page, called btnProtect and btnUnProtect, to protect and unprotect on the fly.  Here is the code of interest:

Protected Sub UnProtect_Click(ByVal sender As Object, ByVal e As EventArgs) 
    UnProtectSection("appSettings") 
End Sub 

Protected Sub Protect_Click(ByVal sender As Object, ByVal e As EventArgs) 
    ProtectSection("appSettings", "DataProtectionConfigurationProvider") 
End Sub 

Private Sub ProtectSection(ByVal sectionName As String, ByVal provider As String) 
    Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath) 
    
    Dim section As ConfigurationSection = config.GetSection(sectionName) 
    
    If section IsNot Nothing AndAlso Not section.SectionInformation.IsProtected Then 
        section.SectionInformation.ProtectSection(provider) 
        config.Save() 
    End If 
End Sub 

Private Sub UnProtectSection(ByVal sectionName As String) 
    Dim config As Configuration = WebConfigurationManager.OpenWebConfiguration(Request.ApplicationPath) 
    
    Dim section As ConfigurationSection = config.GetSection(sectionName) 
    
    If section IsNot Nothing AndAlso section.SectionInformation.IsProtected Then 
        section.SectionInformation.UnprotectSection() 
        config.Save() 
    End If 
End Sub 

Here is what the application settings look like when encrypted:

<appSettings configProtectionProvider=
        "DataProtectionConfigurationProvider">
  <EncryptedData>
   <CipherData>
    <CipherValue>
        AQAAANCMnd8BFdERjHoAwE/Cl+sBAAA
        AXmrl4EN1VUSGDS9ZSSydRwQAAAACAA
        AAAAADZgAAqAAAABAAAAA280OtZlZwu
        D3U+ihvi2zpAAAAAASAAACgAAAAEAAA
        AJ6AnDzWM1o3osh/Y6fcYtwAAQAA1PR
        +wzfwgBgZ4y0yHU4uxaaMET13u21Bv3
        zVE7aA7Z5pCWAYs54LNLNYQ673kmzAL
        osWb7OMuzW6BPwMp18gKNQXOFSGNgA1
        ...
    </< SPAN>CipherValue>
   </< SPAN>CipherData>
  </< SPAN>EncryptedData>
</< SPAN>appSettings>

Making the selections in a dropdownbox dependent upon the selected item in another one is a pretty simple task. Here is a working demo.

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Cascading DropDownList Controls With AJAX</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

    <asp:ScriptManager
        id="sm1"
        Runat="server" />

    <asp:UpdatePanel
        id="UpdatePanel1"
        Runat="server">
   
    <ContentTemplate>
        
    <asp:Label
        id="lblTitle"
        Text="Title:"
        AssociatedControlID="ddlTitle"
        Runat="server" />
    <asp:DropDownList
        id="ddlTitle"
        DataSourceID="srcTitle"
        DataTextField="Title"
        DataValueField="Title"
        AutoPostBack="true"
        Runat="server" />
    <asp:SqlDataSource
        id="srcTitle"
        ConnectionString='<%$ ConnectionStrings:MyDatabase %>'
        SelectCommand="SELECT Title FROM Movies ORDER BY Title"
        Runat="server" />
    
    <br /><br />

    
    <asp:Label
        id="Label1"
        Text="Director:"
        AssociatedControlID="ddlDirector"
        Runat="server" />
    <asp:DropDownList
        id="ddlDirector"
        DataSourceID="srcDirector"
        DataTextField="Director"
        AutoPostBack="true"
        Runat="server" />
    <asp:SqlDataSource
        id="srcDirector"
        ConnectionString='<%$ ConnectionStrings:MyDatabase %>'
        SelectCommand="SELECT Director FROM Movies WHERE Title=@Title ORDER BY Title"
        Runat="server">
        <SelectParameters>
            <asp:ControlParameter Name="Title" ControlID="ddlTitle" />
        </SelectParameters>
    </asp:SqlDataSource>    
    
    </ContentTemplate>
    </asp:UpdatePanel>    
    
    </div>
    </form>
</body>
</html>

There are a lot of ways to grab the number of records returned in a dataset.   This is one of my useful ones. Here is a working demo.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Web.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
 
    Private Sub Page_Load()
        lblMovieCount.Text = GetMovieCount().ToString()
    End Sub
 
    Private Function GetMovieCount() As Integer
        Dim result As Integer = 0        
        Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString)
        Dim cmd As New SqlCommand("GetMovieCount", con)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@ReturnVal", SqlDbType.Int).Direction = ParameterDirection.Output
        Using con
            con.Open()
            cmd.ExecuteNonQuery()
            result = CType(cmd.Parameters("@ReturnVal").Value, Integer)
        End Using
        Return result
    End Function
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Movie Count</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    There are
    <asp:Label
        id="lblMovieCount"
        Runat="server" />
    movies in the database.
    
    </div>
    </form>
</body>
</html>

GetMovieCount.sql

CREATE PROCEDURE dbo.GetMovieCount 
    -- OUTPUT parameter to hold the count. 
    @ReturnVal int OUTPUT 
AS 
    -- This will set @recordcount the the number of records returned by the SELECT query. 
    Set @ReturnVal = (SELECT COUNT(*) FROM Movies)

I've been experimenting with LINQ to SQL using code examples from ASP.NET Unleashed 3.5. It's amazing how little code is needed to do some pretty amazing things without ever coding an SQL or ADO.NET. 

In this example, we're using paging on a GridView using a LINQ to SQL class. Paging in a Gridviews typically works by loading all of the records into memory on the server and then displaying only x records at a time.  This, of course, can place a tremendous load on the server if there are a lot of records.  In this example, using LINQ's Skip and Take operators we pass in the the page number and the size creating a much more efficient way to page through a recordset.

The first step is to create an entity using the Object Relational Designer in Visual Studio 2008.    Simply, create a new LINQ to SQL class and then drag whatever table you want from the database on to the designer surface.  In this example, I created a data entity named Movie along with a class named MoviesLINQPaging.  Inside of my class I created two methods named GetMovies and GetMovieCount. 

Here is a working demo

App_Code/MoviesLINQPaging.vb

Imports Microsoft.VisualBasic
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Data.Linq
Imports System.Web

Public Class MoviesLINQPaging

    Public Shared Function GetMovies(ByVal startRowIndex As Integer, ByVal maximumRows As Integer) As IEnumerable(Of Movie)
        Dim db As New MoviesDataContext()
        Return db.Movies.Skip(startRowIndex).Take(maximumRows)
    End Function

    Public Shared Function GetMovieCount() As Integer
        Dim context As HttpContext = HttpContext.Current
        If IsNothing(context.Cache("MovieCount")) Then
            context.Cache("MovieCount") = GetMovieCountFromDB()
        End If
        Return CType(context.Cache("MovieCount"), Integer)
    End Function

    Private Shared Function GetMovieCountFromDB() As Integer
        Dim db As New MoviesDataContext()
        Return db.Movies.Count()
    End Function

End Class

The next step is to create new aspx page with a gridview which uses a ObjectDataSource to display the data. Notice the properties of the ObjectDataSource:  TypeName="MoviesLINQPaging" - this is the class name found in MoviesLINQPaging.vb. SelectMethod="GetMovies" and SelectCountMethod="GetMovieCount" are the two methods inside the MoviesLINQPaging class.

MoviesLINQPaging.aspx

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <style type="text/css">
        .movies td,.movies th
        {
            padding:5px;
        }
    </style>
    <title>Show LINQ Paging</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        AllowPaging="true"
        PageSize="3"
        CssClass="movies"
        Runat="server" />
        
    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="MoviesLINQPaging"
        SelectMethod="GetMovies"
        SelectCountMethod="GetMovieCount"
        EnablePaging="True"
        Runat="server" />    
    
    </div>
    </form>
</body>
</html>

You can use all of the same types of parameters with the ObjectDataSource control that you can use with the SqlDataSource

control.  See this page for more info and a demo.

In this example notice that the ObjectDataSource control includes an UpdateMethod property that points to the UpdateMovie() method. See a working demo of this.

The GridView automatically adds the update parameters to the ObjectDataSource control's UpdateParameter collection. As an alternative, you can declare the parameters used by the ObjectDataSource control explicitly.  See this example for details on this.

*** Movies.vb ***

Imports System.Web.Configuration

Public Class Movies

    Private ReadOnly _conString As String

    Public Sub UpdateMovie(ByVal id As Integer, ByVal title As String, &_ 
    ByVal director As String, ByVal dateReleased As DateTime)
        ' Create Command
        Dim con As New SqlConnection(_conString)
        Dim cmd As New SqlCommand()
        cmd.Connection = con
        cmd.CommandText = "UPDATE Movies SET Title=@Title,Director=@Director,&_ 
        DateReleased=@DateReleased WHERE Id=@Id"

        ' Add parameters
        cmd.Parameters.AddWithValue("@Title", title)
        cmd.Parameters.AddWithValue("@Director", director)
        cmd.Parameters.AddWithValue("@DateReleased", dateReleased)
        cmd.Parameters.AddWithValue("@Id", id)

        ' Execute command
        Using con
            con.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Sub

    Public Function GetMovies() As SqlDataReader
        ' Create Connection
        Dim con As New SqlConnection(_conString)

        ' Create Command
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = con
        cmd.CommandText = "SELECT Id,Title,Director,DateReleased FROM Movies"

        ' Return DataReader
        con.Open()
        Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
    End Function

    Public Sub New()
        _conString = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
    End Sub

End Class


*** ShowMovies.aspx ***

<%@ Page Language="VB" %>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
    <title>Show Movies</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    <asp:GridView
        id="grdMovies"
        DataSourceID="srcMovies"
        DataKeyNames="Id"
        AutoGenerateEditButton="true"
        Runat="server" />
        
    <asp:ObjectDataSource
        id="srcMovies"
        TypeName="Movies"
        SelectMethod="GetMovies"
        UpdateMethod="UpdateMovie"
        Runat="server"/>
    
    </div>
    </form>
</body>
</html>

As long as all of the components in the App_Code folder are in the same language then you don't have to do anything special. However, if you want place components written in different languages - for example C# and VB.NET, then you need to place components in different languages in different subfolders.  You also need to modify the web.config file to recognize the different subfolders.

Web.Config

<configuration>
<system.web>
<compilation>
<codeSubDirectories>
<add directoryName="VBCode" />
<add directoryName="CSCode" />
</ codeSubDirectories>
</ configuration>
</ system.web>
</ compilation>

There are several ways to add client-side javascript to a ASP.NET page depending on your needs.  Other than using the traditional method of just adding a <script> section in the <head> section of the page, but what if want to reference that javascript across multiple pages?  Here are a few other methods.

Using an external .js file

You could just reference an external .js file like this:

<script language="javascript" src="scroller.js"></script>

Using a master page

This approach uses the ClientScriptManager to register a client-side script in a master page and makes it accessible across the whole site.

--masterpage.master

<%@ Master Language="VB" CodeFile="MasterPage.master.vb" Inherits="MasterPage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
  <title>Untitled Page</title>
</head>
<body onload="displayTime()">
  <form id="form1" runat="server">
    <div>
      <div>
        HEADER
        <div id="currentTime">
        </div>
      </div>
      <asp:ContentPlaceHolder ID="ContentPlaceHolder1" runat="server">
      </asp:ContentPlaceHolder>
    </div>
  </form>
</body>
</html>

--MasterPage.master.vb

Partial Class MasterPage
    Inherits System.Web.UI.MasterPage

  Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
    LoadScript()
  End Sub

  Private Sub LoadScript()

    Dim scriptName As String = "DisplayTime"
    Dim scriptUrl As String = "~/Scripts/DisplayTime.js"
    Dim scriptType As Type = Me.GetType()

    Dim clientScriptManager As ClientScriptManager = Page.ClientScript

    If (Not clientScriptManager.IsClientScriptIncludeRegistered(scriptType, scriptName)) Then
      clientScriptManager.RegisterClientScriptInclude(scriptType, scriptName, ResolveClientUrl(scriptUrl))
    End If

  End Sub
End Class

Build the javascript as a string and register it

<%@ Page Language="VB" AutoEventWireup="false"
  CodeFile="Default3.aspx.vb" Inherits="Default3" Title="Untitled Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
  <div>
    <input type="button" value="Change Color" onclick="changeColor('blue')" />
  </div>
</asp:Content>

Partial Class Default3
    Inherits System.Web.UI.Page

  Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
    LoadScript()
  End Sub

  Private Sub LoadScript()

    Dim clientScriptManager As ClientScriptManager = Page.ClientScript
    Dim sb As StringBuilder = New StringBuilder()

    sb.Append("<script language='javascript'>")
    sb.Append("function changeColor(color) {")
    sb.Append("document.bgColor=color;")
    sb.Append("}")
    sb.Append("<")
    sb.Append("/script>")

    If (Not clientScriptManager.IsClientScriptBlockRegistered("ColorScript")) Then
      clientScriptManager.RegisterClientScriptBlock(Me.GetType(), "ColorScript", sb.ToString())
    End If

  End Sub
End Class

Build the javascript as a string and register it on startup

Lastly, using the same approach as above except for this time the javascript doesn't get registered until the page starts using the RegisterStartupScript method

Partial Class Default4
    Inherits System.Web.UI.Page

  Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
    LoadScript()
  End Sub

  Private Sub LoadScript()

    Dim clientScriptManager As ClientScriptManager = Page.ClientScript
    Dim sb As StringBuilder = New StringBuilder()

    sb.Append("<script language='javascript'>")
    sb.Append("document.bgColor='green';")
    sb.Append("<")
    sb.Append("/script>")

    If (Not clientScriptManager.IsStartupScriptRegistered("ColorScript")) Then
      clientScriptManager.RegisterStartupScript(Me.GetType(), "ColorScript", sb.ToString())
    End If


  End Sub

End Class

Allowing users to select a theme for a site is a cool option and is pretty easy to do.  In these examples, I'll show how to allow a user to select a theme from a dropdownlist for a single page and for a master page which will carry the theme throughout the whole site.

The first step is to actually create your themes.  This is done by creating a App_Themes folder in the root of the site and then creating a separate folder for each theme beneath this one. For example, App_Themes/ThemeGreen/style.css,  App_Themes/ThemeBrown/style.css etc.

A Single Page

The theme for a page is created early during the page lifecycle - the page_preinit stage so we have to establish the theme at this point.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
</head>
<body>
    <form id="form1" runat="server">
    <div>
      <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
        <asp:ListItem Value="ThemeGreen">Theme Green</asp:ListItem>
        <asp:ListItem Value="ThemeBrown">Theme Brown</asp:ListItem>
      </asp:DropDownList>
    </div>
    </form>
</body>
</html>

Default.aspx.vb

Partial Class _Default
    Inherits System.Web.UI.Page

  Protected Sub Page_PreInit(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreInit
    If (Not Request.Form Is Nothing And Request.Form.Count > 0) Then
      Me.Theme = Request.Form("DropDownList1")
    End If
  End Sub
End Class

A Master Page

--masterpage.master

<%@ Master Language="VB" CodeFile="MasterPage.master.vb" Inherits="MasterPage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server" />
<body>
  <form id="form1" runat="server">
    <div>
      <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
        <asp:ListItem Value="ThemeBlue">Theme Blue</asp:ListItem>
        <asp:ListItem Value="ThemeRed">Theme Red</asp:ListItem>
      </asp:DropDownList>
    </div>   
  </form>
</body>
</html>

--masterpage.master.vb

Partial Class MasterPage
  Inherits System.Web.UI.MasterPage
End Class

--default3.aspx

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false"
  CodeFile="Default3.aspx.vb" Inherits="Default3" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
  Selectable them in master page drop down list set in a new base class (BasePage)
  which replaces Page as the parent for the page.
</asp:Content>

--default3.aspx.vb

Partial Class Default3
  Inherits BasePage
End Class

--App_Code/BasePage.vb

Imports Microsoft.VisualBasic

''' <summary>
''' Base class for Theme based pages.
''' </summary>
Public Class BasePage
  Inherits Page

  Protected Overrides Sub OnPreInit(ByVal e As System.EventArgs)
    MyBase.OnPreInit(e)
    If (Not Request.Form Is Nothing And Request.Form.Count > 0) Then
         Me.Theme = Request.Form(Me.Master.FindControl("DropDownList1").UniqueID)
    End If
  End Sub
End Class

If you're using a SQLDataSource, it's pretty simple to add sorting to a Gridview.  But often times we need some specific event to occur when we sort a column such as change the text in a label control, add ASC or DESC up and down arrows to indicate how a Gridview is being sorted, databind some other object on the page etc.  Below are two examples - the first one uses the Gridview.Sorting event and the last one uses Gridview.Sorted.

<script runat="server">

  Sub CustomersGridView_Sorting(sender As Object, e As GridViewSortEventArgs)

    ' Cancel the sorting operation if the user attempts
    ' to sort by address.
    If e.SortExpression = "Address" Then

      e.Cancel = True
      Message.Text = "You cannot sort by address."
      SortInformationLabel.Text = ""

    Else

      Message.Text = ""

    End If

  End Sub

  Sub CustomersGridView_Sorted(ByVal sender As Object, ByVal e As EventArgs)

    ' Display the sort expression and sort direction.
    SortInformationLabel.Text = "Sorting by " & _
      CustomersGridView.SortExpression.ToString() & _
      " in " & CustomersGridView.SortDirection.ToString() & _
      " order."

  End Sub

</script>

<html  >
  <head runat="server">
    <title>GridView Sorted and Sorting Example</title>
</head>
<body>
    <form id="form1" runat="server">

      <h3>GridView Sorted and Sorting Example</h3>

      <asp:label id="Message"
        forecolor="Red"
        runat="server"/>

      <br/>

      <asp:label id="SortInformationLabel"
        forecolor="Navy"
        runat="server"/>

      <br/>  

      <asp:gridview id="CustomersGridView" 
        datasourceid="CustomersSource" 
        autogeneratecolumns="true"
        allowpaging="true"
        emptydatatext="No data available." 
        allowsorting="true"
        onsorting="CustomersGridView_Sorting"
        onsorted="CustomersGridView_Sorted"  
        runat="server">

      </asp:gridview>

<asp:sqldatasource .... />

I often come across the need to share data between javascript client-side code and my asp.net server controls.  Many functions just perform better on the client-side and the need to pass data from my server controls to javascript comes up often.  One of the easier ways of doing this is to create a asp:hiddenfield. Doing this gives your javascript code access to the data it contains.  In this example, we programatically create a asp:Hiddenfield with a ID="sharedData" and assign a value of "New client initial value." When we click the server-side button, btnGetData which calls the javascript function getSharedData(), it passes the hiddenfield value from sharedData to getSharedData().

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
    <script type="text/javascript">
    function getSharedData()
    {
        alert("Shared data is " + document.getElementById("sharedData").value + ".");
    }
    
    </script>    
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
    </div>
        <asp:Button ID="btnGetData" runat="server" Text="Get Data" OnClientClick="getSharedData()" />
    </form>
</body>
</html>

Code behind - Default.aspx.vb:

Partial Class Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreLoad
        Dim hiddenField As System.Web.UI.WebControls.HiddenField = New System.Web.UI.WebControls.HiddenField
        hiddenField.ID = "sharedData"
        hiddenField.Value = "New client initial value"

        Page.Form.Controls.Add(hiddenField)
    End Sub
End Class

I've been working on a project which required the use of some javascript with some server-side controls. I spent considerable time monkeying around with the Page.ClientScript.RegisterClientScriptBlock() method which registers a client script on the page. I couldn't quite get it right using this method and ended up with a much simpler approach anyway.  Every server-side web control has an "Add" method which you can use when the page loads.   In the example below, I register a click event for a button and 2 different textboxes.  Take notice that for the textboxes I use the client side id value for them (e.g. 'Header1_txtFirstName').  These values can be obtained by looking at the source code of the page in the browser.

Private Sub Page_Load(ByVal Src As Object, ByVal E As EventArgs)
        If Not Page.IsPostBack Then
            btnDelete.Attributes.Add("onClick", "return confirm('You sure you want to delete the record?');")
            txtFirstName.Attributes.Add("onClick", "document.getElementById('Header1_txtFirstName').value = '';")
            txtLastName.Attributes.Add("onClick", "document.getElementById('Header1_txtLastName').value = '';")
        End If
End Sub

Candidate Name: 
<asp:TextBox ID="txtFirstName" runat="server" Text="First Name" /> 'this becomes Header1_txtFirstName
<asp:TextBox ID="txtLastName" runat="server" Text="Last Name" /> 'this becomes Header1_txtLastName

More information on this topic here: http://dotnetslackers.com/articles/aspnet/JavaScript_with_ASP_NET_2_0_Pages_Part1.aspx

I've been working on a project which has involved finding the values or setting the values on controls (label, dropdownlist etc.) embedded inside of other other controls (repeater, gridview etc.).  You have to drilldown or look inside the parent object containing these controls to make this work.  This is where the Page.FindControl method comes in useful. Here are some examples:

        Dim lb As LinkButton = sender
        If Not (lb Is Nothing) Then
                        
        'Get the value for a dropdownlist using Parent.FindControl
        Dim strDirRace As String = CType(lb.Parent.FindControl("ddlDirRace"), DropDownList).SelectedItem.Text

        'Get the value for a dropdownlist specifying the name of repeater control housing it
        Dim strDirRace As String = CType(rptDirectorDetails.FindControl("ddlDirRace"), DropDownList).SelectedItem.Text
            
        Dim lblDirRace As Label = CType(lb.Parent.FindControl("lblDirRaceUpdateStatus"), Label)
            lblDirRace.Text = "* Update Complete *"        
        End If

        When you use a Master page, then you need to do this        

          Dim cbx As CheckBox = CType(Master.FindControl("Content1").FindControl("cbxAllTel"), CheckBox)

More info on this topic: http://msdn2.microsoft.com/en-us/library/31hxzsdw.aspx

An interesting article on the drawbacks of using the UpdatePanel

http://encosia.com/2007/07/11/why-aspnet-ajax-updatepanels-are-dangerous/

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 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>

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>

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

Here are a few of development tools I've found useful

• RegExDesigner.NET - A tool for building and testing regular expressions in .NET
  
• Fiddler - Fiddler is a HTTP Debugging Proxy which logs all HTTP traffic between your computer and the Internet. Fiddler allows you to inspect all HTTP Traffic, set breakpoints, and "fiddle" with incoming or outgoing data. It's very similar to Firefox tool, Firebug. I found it very helpful with writing javascript that makes any type of XmlHttp calls.
  
• IE Development Toolbar - This one's very cool. Check out the Overview section for everything that it does.

This example is a gridview contains a checkbox which when checked turns the selected row a different color.  

<asp:GridView ID="MyGridView" runat="server">
<Columns>
<asp:TemplateField>
    <ItemTemplate>
       <asp:CheckBox ID="MyCheckBox" runat="server" AutoPostBack="true" OnCheckedChanged="CheckBox1_CheckedChanged" />
    </ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
  Dim DataTable As New Data.DataTable
  DataTable.Columns.Add("Column1", GetType(String))
  DataTable.Columns.Add("Column2", GetType(String))
  Dim DataRow As Data.DataRow = DataTable.NewRow
  DataRow.Item(0) = "Test1"
  DataRow.Item(1) = "Test2"
  DataTable.Rows.Add(DataRow)
  MyGridView.DataSource = DataTable
  MyGridView.DataBind()
End If
End Sub

Protected Sub CheckBox1_CheckedChanged(ByVal sender As Object, ByVal e As System.EventArgs)
  Dim checkbox As CheckBox = CType(sender, CheckBox)
  Dim row As GridViewRow = CType(checkbox.NamingContainer, GridViewRow)
   If checkbox.Checked = True Then
     row.BackColor = Drawing.Color.Red
     mygridview.Columns(0).Visible = False
   End If
End Sub

Often times when I'm developing a site for a client their budget permits that I use a low-cost or free database which means that I usually go with Access or MySQL.  Access has this nasty habit of not closing it's connection all of the time and the database is left in a "suspended" or locked state where the pages are not able to open another connection to it. I find that this happens sometimes even when I explicitly close the OleDBConnection. One solution is to run the bit of code below which has worked well for me.

** CloseDatabase.aspx.vb **

Partial Class CloseDatabase
   Inherits System.Web.UI.Page
End Class

** CloseDatabase.aspx **

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CloseDatabase.aspx.vb" Inherits="CloseDatabase" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
Dim Conn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/mydbase.mdb"))

Sub Page_Load()
Dim ConnState As String = Conn.State.ToString()
If ConnState = "Open" Then
Try
Conn.Close()
Response.Write("The database was closed successfully.")
Catch Exc As Exception
Response.Write("The database was opened and could not be closed:<br />" & Exc.ToString().Replace(Environment.NewLine(), "<br />"))
End Try
ElseIf ConnState = "Closed" Then
Response.Write("There must be something else wrong... the database is already closed.")
Else
Response.Write("You should probably take up another career... the database was not opened OR closed... :| ")
End If
End Sub

</script>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

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

The order of the parameters does not matter - just the names of the parameters.  You can specify the type of a parameter using the Type property - Int32, Decimal and DateTime.

Here is a demo of this code.

*** Movies.vb ***

Imports System.Web.Configuration

Public Class Movies

Private ReadOnly _conString As String

Public Sub UpdateMovie(ByVal id As Integer, ByVal title As String, &_
ByVal director As String, ByVal dateReleased As DateTime)
' Create Command
Dim con As New SqlConnection(_conString)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandText = "UPDATE Movies SET Title=@Title,Director=@Director,DateReleased=@DateReleased WHERE Id=@Id"

' Add parameters
cmd.Parameters.AddWithValue("@Title", title)
cmd.Parameters.AddWithValue("@Director", director)
cmd.Parameters.AddWithValue("@DateReleased", dateReleased)
cmd.Parameters.AddWithValue("@Id", id)

' Execute command
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub

Public Function GetMovies() As SqlDataReader
' Create Connection
Dim con As New SqlConnection(_conString)

' Create Command
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = con
cmd.CommandText = "SELECT Id,Title,Director,DateReleased FROM Movies"

' Return DataReader
con.Open()
Return cmd.ExecuteReader(CommandBehavior.CloseConnection)
End Function

Public Sub New()
_conString = WebConfigurationManager.ConnectionStrings("MyDatabase").ConnectionString
End Sub

End Class


*** ExplicitShowMovies.aspx ***

<%@ Page Language="VB" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show Movies</title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:GridView
id="grdMovies"
DataSourceID="srcMovies"
DataKeyNames="Id"
AutoGenerateEditButton="true"
Runat="server" />

<asp:ObjectDataSource
id="srcMovies"
TypeName="Movies"
SelectMethod="GetMovies"
UpdateMethod="UpdateMovie"
Runat="server">
<UpdateParameters>
<asp:Parameter Name="title" />
<asp:Parameter Name="director" />
<asp:Parameter Name="dateReleased" Type="DateTime" />
<asp:Parameter Name="id" Type="Int32" />
</UpdateParameters>
</asp:ObjectDataSource>

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

I just learned about the Page_Error sub which you can use to capture the error that occurred and redirect the user to a page which displays a user-friendly message that an error occurred.  The Page_Error event of the ASP.NET Page object is raised any time an unhandled error occurs in a page.

ASP.NET provides you with the ability to redirect the user to another page when an error occurs. To use this feature, set the ErrorPage property of the Page object to the URL of the page you want the user to see. You can add querystring parameters to the URL to pass specific error messages to the page. For instance, in the code snippets shown next, we've added three querystring parameters to the URL of an error message page: PageHeader, Message1, and Message2. PageHeader is set to the message "Error Occurred." Message1 is set to the message in the lastError exception. This will be the message from the last exception thrown. Message2 is a message we've added to say where the error was processed.

Example:

Protected Sub Page_Error(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Error
            Dim lastError As Exception

            'get the last error that occurred
            lastError = Server.GetLastError( )

            'do any logging, notifications, etc. here

            'set the URL of the page that will display the error and
            'include querystring parameters to allow the page to display
            'what happened
            Page.ErrorPage = "error_page.aspx" &_
                 "?PageHeader=Error Occurred" &_
                 "&Message1=" &lastError.Message &_
                 "&Message2=" &_
                 "This error was processed at the page level"

End Sub

ASP.NET comes with a RegularExpressionValidator which provides for a lot of flexbility when validating user input.  This is the best regular expression for emails I've seen. It tends to error on the side of accepting bad emails rather then rejecting good ones.

^([a-zA-Z0-9_'+*$%\^&!\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9:]{2,4})+$

Here is an example:

E-mail: <asp:textbox id="textbox1" runat="server"/>
<asp:RegularExpressionValidator id="valRegEx" runat="server"
ControlToValidate="textbox1"
ValidationExpression="^([a-zA-Z0-9_'+*$%\^&!\.\-])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9:]{2,4})+$"
ErrorMessage="* Your entry is not a valid e-mail address."
display="dynamic">*
</asp:RegularExpressionValidator>

<%@ Import Namespace="System.Drawing" %>
<script language="VB" Runat="server">
Sub DataCheck(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
Dim chkExp as CheckBox
If e.Row.RowType = DataControlRowType.DataRow Then
Dim sCode as String=e.Row.Cells(7).text
If Not isNumeric(sCode.Substring(1,1)) Then
e.Row.Cells(7).text="<i style='color:red'>(Alt Text)</i>"
        chkExp= CType(e.row.FindControl("ck1"), Checkbox)
        chkExp.Visible="False"
End If
End If
End Sub    
</script>
<html>
<head runat="server">
    <title>Conditional GridView Cell Formatting</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:GridView OnRowDataBound="DataCheck" AutoGenerateColumns="False"
    DataSourceID="SqlDataSource1" ID="GridView2" runat="Server">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="ck1" runat="server"></asp:CheckBox>
</ItemTemplate>
</asp:TemplateField>
<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>
<HeaderStyle BackColor="Blue" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
SelectCommand="Select FirstName, LastName, Title, Address, City, Region, PostalCode, country from Employees">
</asp:SqlDataSource>
        </form>
    </body>
</html>

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">        
        <title>Displaying all files in a directory and in a datatable</title>
    </head>
    <body>
<asp:GridView ID="gvFiles" runat="server" Width="432px">
</asp:GridView>
        </form>
    </body>
</html>

<asp:Button ID="btnReset" CssClass="smalltext" OnClientClick="Form1.reset();return false;" runat="server" Text="Clear Form" />

Something I do all of the time and I think this the best method to do it

If Not Request.QueryString("DashboardID") Is Nothing andalso Request.QueryString("DashboardID").trim.length > 0 then

The Format() Function

The Format() function is a general-purpose formatting function that returns a string value formatted according to a format string. The format strings duplicate numeric and date/time formats produced by the specialized formats described above. The general format for applying the Format() function is shown below.

Formatting Numbers

A format string for numeric values can use one of the predefined string values shown in the following table.

Examples of applying a format string to numeric values are shown in the following table.

Formatting Dates and Times

A format string for date/time values can use one of the predefined string values shown in the following table.

Examples of applying a format string to date/time values are shown in the following table.

User-defined Numeric Formats

Formats can be defined for displaying numeric values by composing a string to describe the format. This user-defined string is applied through the Format() function. The characters shown in the following table are used to compose the format string.

Examples of applying a user-defined format string to numeric values are shown in the following table.

User-defined Date/Time Formats

Formats can be defined for displaying date and time values by composing a string to describe the format. This user-defined string is applied through the Format() function. The characters shown in the following table are used to compose the date/time format string.

Examples of applying a user-defined format string to date/time values are shown in the following table.

I recently had to put into place a method that prevented more than one user from logging into our company web site under the same login ID. With the help of a friend, this is what we came up with. 

Basically, each time a user logs in, the user's UserID and sessionID are stored in a database table at login.  Each time the user visits a page in the site, the user's session ID is matched against what is stored in the database.  If the sessionIDs do not match, then the user is logged out and redirected to a page that says something like "duplicate login detected.  you're a cheating bum. etc."

A typical scenario would work like this:

User A logs into the site and the sessionID is recorded into the dbase. She browses from page and page within the site and on each page her sessionID is verified against what's stored in the database.

User B then logs into the site using User A's ID and password. The sessionID information stored in the database for User A is overwritten by User B. When user A tries to browse to another page, she receives a message stating that someone has logged into the site using her ID.  User A can log back in, but then User B will receive the duplicate login message and round and round they go.

Here's the code for it.

This stored procedure is called on the login page. If the user's userID is not already in the datbase, then it is inserted as a new record. Otherwise, the existing record is updated with the new session ID.

CREATE Procedure sp_LoginStatusCheck
@UserID as Int,
@SessionID as nvarchar(100)


AS
    IF (SELECT COUNT(UserID)
FROM TLoginStatus
WHERE UserID = @UserID) = 0
        
BEGIN
INSERT INTO TLoginStatus(UserID, SessionID) Values (@UserID, @SessionID)
    
END
ELSE
         UPDATE TLoginStatus
         SET SessionID = @SessionID
         WHERE UserID = @UserID
GO

Call sp_LoginStatusCheck in the login page.

Sub AddSessionCheck(ByVal UserID As Integer)
'Due to some bizarre requirement that there must be some value assigned to a Session variable not to lose
'the value for SessionID, I created a random session variable and value            
Session("BA") = "BA"
            
            
Dim myConnection As SqlConnection
Dim cmd As SqlCommand
            
myConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
cmd = New SqlCommand("sp_LoginStatusCheck", myConnection)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID
cmd.Parameters.Add("@SessionID", SqlDbType.NVarChar, 100).Value = Session.SessionID
                        
Try
myConnection.Open()
cmd.ExecuteNonQuery()
Finally
myConnection.Close()
End Try

End Sub

On each page that I Want to check for a duplicate login, I would first get the userID value which is stored in a cookie value in the Page_Load function. 

<%@ Page Language="VB" Inherits="MyBaseClass" src="~/classes/MyBaseClass.vb" %>
<%@ Import Namespace="System.Data" %>
<%@ 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">
Sub Page_Load()
If Not IsPostBack Then
Dim strUserID As String
If Not IsNothing(Request.Cookies("BA")) Then
strUserID = Request.Cookies("BA")("UserID").ToString()
lblUserID.Value = strUserID
End If


'Call CheckLoginStatus
    CheckLogin(strUserID)
    
End If
End Sub
</script>

I created a new class called MyBaseClass with a sub called CheckLogin that accepts UserID as a parameter. This gets called on every page that I check.

Imports Microsoft.VisualBasic
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Web.HttpContext

Public Class MyBaseClass
Inherits System.Web.UI.Page

Public Shared Sub CheckLogin(ByVal UserID As Integer)

'This entire section is the duplicate login prevention section
System.Web.HttpContext.Current.Session("Spice") = "Ginger"
Dim strSessionID = System.Web.HttpContext.Current.Session.SessionID
Dim strLoginStatus As String

Dim MyConnection As SqlConnection
Dim Cmd As SqlCommand

MyConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Cmd = New SqlCommand("sp_LoginStatusCheck2", MyConnection)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = UserID
Cmd.Parameters.Add("@SessionID", SqlDbType.NVarChar, 100).Value = strSessionID
Cmd.Parameters.Add("@LoginStatus", SqlDbType.Bit).Direction = ParameterDirection.Output

Try
MyConnection.Open()
Cmd.ExecuteNonQuery()
strLoginStatus = Cmd.Parameters(2).Value
'System.Web.HttpContext.Current.Response.Write(strLoginStatus)
Finally
MyConnection.Close()
End Try

If strLoginStatus = "False" Then
System.Web.HttpContext.Current.Response.Redirect("/duplicate.login.aspx")
End If

End Sub

End Class

Here's the code for the stored procedure sp_LoginStatusCheck2

CREATE Procedure sp_LoginStatusCheck2
@UserID as Int,
@SessionID as nvarchar(100),
@LoginStatus as Bit OUTPUT

--We don't want to check the LoginStatus of enterprise level accounts UserID 884, UserID 7996
AS
IF @UserID=884
    SET @LoginStatus = 1
ELSE IF @UserID=7996 
    SET @LoginStatus = 1
ELSE    

    IF (SELECT COUNT(*) FROM TLoginStatus WHERE UserID = @UserID AND SessionID = @SessionID) > 0
        SET @LoginStatus = 1
    ELSE
        SET @LoginStatus = 0
GO

Date and Time Properties

When working with dates and times it is often necessary to know the current date and/or time. These values can be extracted from the server's system clock through the properties shown in the table below. These properties are used in several of the date and time functions described below.

Date Functions

Date functions are summarized in the following table with functions described more fully below.

DateAdd() adds a given date interval to a date to produce a calculated date. Its general format is

DateAdd(interval, number, date)

where interval is one of the string values shown in the accompanying table, number gives the number of intervals to add, and date is the date and time to which the interval is to be added. For example, the following function returns the (formatted) date six months from today.

FormatDateTime(DateAdd("m", 6, Today),
DateFormat.LongDate)

DateDiff() returns a value giving the number of identified intervals between two dates. Its general format is

DateDiff(interval, date1, date2)

where interval is a string value representing an interval type using the date values shown in the table under the DateAdd() function, and date1 and date2 are the two dates between which the interval is calculated (date1 is subtracted from date2). The following example calculates the number of shopping days until Christmas.

DateDiff( "d", Today, "12/24/" & DatePart("yyyy", Today) )

Notice that the date2 argument passes "12/24" & DatePart("yyyy", Today). The DatePart() function (see below) extracts the current year from the current date and appends it to the string so that the calculation is always based on the current year.

DatePart() extracts a specified component of a given Date. Its general format is

DatePart(part, date)

where part is a string value representing a date component using the values shown in the table under the DateAdd() function, and date is any Date value. For example, the following function determines the day of the week on January 1, 2010.

WeekdayName(DatePart("w", "01/01/2010"))

DatePart() returns an integer value (Sunday = 1) representing the day of the week. This value is converted into a weekday name with the WeekDayName() function (see below).

DateSerial() returns a Date value based upon integer values representing a year, month, and day. Its general format is

DateSerial(year, month, day)

For example, the function DateSerial(5, 7, 15) returns 7/15/2005 12:00:00 AM. You might wonder why the need to return a date if you already know the date! The point is that the passed values are integers, perhaps collected from a form in which a year, month, and day are selected from drop-down lists. These integer values are combined and converted into a Date type with the DateSerial() function.

DateValue() returns a Date type from a String value representing a date. This function works as a converter to a Date data type. For example, the function call DateValue("January 1, 2005") returns 1/1/2005 12:00:00 AM.

Day() returns an Integer representing the day of the month from a passed date. For instance, function Day(Today) returns 15. This is the same value as returned from the function DatePart("d", Today).

IsDate() returns a Boolean value indicating whether the passed value can be converted into a Date type. This function is handy when testing user input to verify that an actual date has been entered. A function call in the format IsDate("02/31/2005") returns False.

Month() returns an Integer value from 1 through 12 representing the month of the year. It works similar to the Day() function. The function call Month(Today) returns 6.

MonthName() accepts an Integer representing a month of the year and returns a String value containing the name of the month. Used in conjunction with the Month() function, the function call MonthName(Month(Today)) produces June.

WeekDay() returns an Integer value between 1 and 7 (Sunday = 1) representing the day of the week. The function WeekDay(Today) returns 6, which is identical to the value returned by DatePart("w", Today).

WeekDayName() accepts an Integer representing a day of the week and returns a String value containing the name of the day of the week. Used in conjunction with the Weekday() function, the function call WeekDayName(WeekDay(Today)) produces Friday.

Time Functions

Time functions are summarized in the following table with functions described more fully below. Several of the date functions can be applied to time measurements.

DateAdd() adds a given time interval to a time to produce a calculated time. Its general format is

DateAdd(interval, number, time)

where interval is one of the string values shown in the accompanying table, number gives the number of intervals to add, and time is the date and time to which the interval is to be added. For example, the following function returns the (formatted) time twelve hours from now.

FormatDateTime(DateAdd("h", 12, Now), DateFormat.LongDate)

DateDiff() can be used to return a value giving the number of identified intervals between two times. Its general format is

DateDiff(interval, time1, time2)

where interval is a string value representing an interval type using the time values shown in the table under the DateAdd() function, and time1 and time2 are the two times between which the interval is calculated (time1 is subtracted from time2). The following example calculates the number of minutes until midnight.

DateDiff("n", TimeString, "11:59:59 PM") + 1

It can be a bit tricky working with times because of the roll-over that takes place at midnight. If the value "00:00:00 AM" were to be used in the above example, it would produce -933 minutes, which are the number of minutes from the beginning of the current day. To get around this problem, one minute is added to the time difference until "11:59:59 PM" of the current day.

DatePart() can be used to extract a specified component of a given time. Its general format is

DatePart(part, time)

where part is a string value representing a time component using the time values shown in the table under the DateAdd() function, and time is any Date value. For example, the following function determines the current hour (24-hour clock) of the current day.

DatePart("h", Now)

Hour() returns an Integer representing the hour of the day (24-hour clock). For instance, function Hour(Now) returns 15. This is the same value as returned from the function DatePart("h", Now).

Minute() returns an Integer representing the minute of the hour. For instance, function Minute(Now) returns 34. This is the same value as returned from the function DatePart("n", Now).

Second() returns an Integer representing the second of the minute. For instance, function Second(Now) returns 34. This is the same value as returned from the function DatePart("s", Now).

TimeSerial() returns a Date value representing a specified hour, minute, and second, with the date information set relative to January 1 of the year 1. Its general format is

TimeSerial(hour, minute, second)

For example, the function TimeSerial(5, 30, 45) returns 1/1/0001 5:30:45 AM. As in the case for the DateSerial() function, integer values are combined and converted into a Date type.

TimeValue() returns a Date type from a String value representing a time. This function works as a converter to a Date data type. For example, the function call TimeValue("4:35:17 PM") returns 1/1/0001 4:35:17 PM.

Some of the date and time functions appear trivial in isolation. However, you will find them crucial when you begin combining them in applications that relay on accurate reporting of and calculations involving dates and times.

Mathematical Functions

Popular mathematical functions are summarized in the following table. Note that certain functions do not require the Math. prefix.

Random Numbers

The Rnd() function returns a random number between 0 and 1. More likely, the need is to generate a number within a particular range, between a given low and high number. This is accomplished with the following formula.

For instance, to generate a random number between 0 and 10 the formula becomes

Math.floor((10 - 0 + 1) * Rnd() + 0)

I find I use these all of the time

String Functions

Several built-in string functions perform string manipulations to augment simple concatenation with the "&" operator. These functions are summarized in the following table.