Editing a DataGrid using a DropDownList - the Complete Package!

This demo wraps up Part 7 of the DataGrid article series.

 FAQ IDCategoryQuestionSubmitted By
Edit2 Strings How do you convert a string to all uppercase or all lowercase? Scott Mitchell
Edit12 Email How can I display line breaks in an email message? Scott Mitchell
Edit14 Email Can I send emails without using CDONTS? Scott Mitchell
Edit15 Strings How can I determine the length of a string (how many characters are in a string)? Scott Mitchell
Edit17 Strings How can I find the position of a certain character (or substring) within a string? Scott Mitchell
Edit19 Dates and Times How can I return the current date/time? Scott Mitchell
Edit20 Functions and Subroutines What is recursion? Scott Mitchell
Edit23 FileSystemObject What, exactly, is the FileSystemObject? Scott Mitchell
Edit25 Email How can I send an email using CDONTS? Scott Mitchell
Edit8 Databases, General How do you create a database table through a SQL statement (via an ASP page, perhaps)? Scott Mitchell
Edit16 Dates and Times Using VBScript, how can I determine if a variable is a valid date or not? Scott Mitchell
Edit18 Dates and Times How can I easily display a nicely formatted date variable? Scott Mitchell
Edit24 FileSystemObject How can I read the contents of a text file through an ASP page? Scott Mitchell
Edit7 Arrays How do I dynamically resize an array? Scott Mitchell
Edit11 Email How can I have my ASP page send HTML-formatted email? Scott Mitchell
Edit10 Databases, General How can I tell what version of ADO I am using? Scott Mitchell
Edit13 Email How do you send email attachments through an ASP page? Scott Mitchell
Edit21 Arrays How can I determine the upper or lower bounds of an array? Scott Mitchell
Edit22 Email How can I validate an email adress using Jscript? shedao

Source Code
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
  'Create a connection
  Dim myConnection as New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
  Dim ddlDataSet as DataSet = New DataSet()

  Sub Page_Load(sender as Object, e as EventArgs)
    If Not Page.IsPostBack then
    End If
  End Sub
  Function GetCategories() as DataSet
    'Populate the ddlDataSet
    Const strSQLDDL = "SELECT FAQCategoryID, Name FROM tblFAQCategory ORDER BY Name"    
    Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(strSQLDDL, myConnection)    
    myDataAdapter.Fill(ddlDataSet, "Categories")

    Return ddlDataSet
  End Function

  Sub BindData()
    '2. Create the command object, passing in the SQL string
    Const strSQL as String = "SELECT FAQID, F.FAQCategoryID, SubmittedByName, F.Description, FC.Name AS CategoryName FROM tblFAQ F INNER JOIN tblFAQCategory FC ON F.FAQCategoryID = FC.FAQCategoryID WHERE FAQID <= 25"

    'Set the datagrid's datasource to the datareader and databind
    Dim resultsDataSet as New DataSet()
    Dim myDataAdapter as SqlDataAdapter = New SqlDataAdapter(strSQL, myConnection) 

    dgPopularFAQs.DataSource = resultsDataSet
  End Sub

  Sub dgPopFAQs_Edit(sender As Object, e As DataGridCommandEventArgs)
    dgPopularFAQs.EditItemIndex = e.Item.ItemIndex
  End Sub

  Sub dgPopFAQs_Cancel(sender As Object, e As DataGridCommandEventArgs)
    dgPopularFAQs.EditItemIndex = -1
  End Sub

  Sub dgPopFAQs_Update(sender As Object, e As DataGridCommandEventArgs)
    'Determine what category was selected
    Dim strCategoryID as String, strCategoryName as String    
    strCategoryID = CType(e.Item.FindControl("lstCategories"), DropDownList).SelectedItem.Value
    strCategoryName = CType(e.Item.FindControl("lstCategories"), DropDownList).SelectedItem.Text
    'Determine the FAQID
    Dim strFAQID as String
    strFAQID = e.Item.Cells(1).Text
    'Get the SubmittedByName
    Dim strSubmittedBy as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
    'Get the Description
    Dim strDesc as String
    strDesc = CType(e.Item.FindControl("txtDescription"), TextBox).Text
    lblUpdateResults.Text = "You opted to edit FAQID " & strFAQID & ".  You set the Category to " & _
                            strCategoryID & ", which is the " & strCategoryName & " category.  You " & _
                            "set the submitted by name to " & strSubmittedBy & " and the description to " & _
    dgPopularFAQs.EditItemIndex = -1
  End Sub
  Function GetSelectedIndex(CID as String) as Integer
    Dim iLoop as Integer
    Dim dt as DataTable = ddlDataSet.Tables("Categories")
    For iLoop = 0 to dt.Rows.Count - 1
      If Int32.Parse(CID) = Int32.Parse(dt.Rows(iLoop)("FAQCategoryID")) then
        Return iLoop
      End If
    Next iLoop
  End Function

  <form runat="server">
    <asp:label id="lblUpdateResults" Font-Bold="True" runat="server" />
    <asp:datagrid id="dgPopularFAQs" runat="server"
		<asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="OK" />
	    <asp:BoundColumn DataField="FAQID" ItemStyle-Width="10%" ReadOnly="True"
				ItemStyle-HorizontalAlign="Center" HeaderText="FAQ ID" />
	    <asp:TemplateColumn HeaderText="Category">
            <%# DataBinder.Eval(Container.DataItem, "CategoryName") %>
	        <asp:DropDownList runat="server" id="lstCategories"
	              DataValueField="FAQCategoryID"  DataTextField="Name"
	              DataSource='<%# GetCategories() %>'
	              SelectedIndex='<%# GetSelectedIndex(Container.DataItem("FAQCategoryID")) %>' />

	    <asp:TemplateColumn HeaderText="Question">
            <%# DataBinder.Eval(Container.DataItem, "Description") %>
	        <asp:TextBox runat="server" id="txtDescription" Columns="75" 
	             Text='<%# Container.DataItem("Description") %>' />
	    <asp:BoundColumn DataField="SubmittedByName" HeaderText="Submitted By" />	  

[Return to the article]