Creating a Fully Editable DataGrid - Saving the Updated Data

This demo illustrates how to iterate through the DataGrid's records and save back the changes to the database.


FAQ IDCategoryQuestionSubmitted By
2Strings
12Email
14Email
15Strings
17Strings
19Dates and Times
20Functions and Subroutines
8Databases, General
16Dates and Times
18Dates and Times
7Arrays
11Email
10Databases, General
13Email


Source Code
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Data.SqlClient" %>
<script language="vb" runat="server">
  Sub Page_Load(sender as Object, e as EventArgs)
    If Not Page.IsPostBack then
      BindData()
    End If
  End Sub
  
	
  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 <= 20"

    'Set the datagrid's datasource to the datareader and databind
    Dim myConnection as New SqlConnection(connection string)
    Dim myCommand as New SqlCommand(strSQL, myConnection)
    
    myConnection.Open()

    dgPopularFAQs.DataSource = myCommand.ExecuteReader()
    dgPopularFAQs.DataBind()    
    
    myConnection.Close()
  End Sub


  Sub UpdateAll_Click(sender as Object, e as EventArgs)
    Dim dgi as DataGridItem
    For Each dgi in dgPopularFAQs.Items
      'Read in the Primary Key Field
      Dim id as Integer = Convert.ToInt32(dgPopularFAQs.DataKeys(dgi.ItemIndex))
      Dim question as String = CType(dgi.FindControl("txtDescription"), TextBox).Text
      Dim submittedBy as String = CType(dgi.FindControl("txtSubmittedBy"), TextBox).Text
      
      'TODO: Issue an UPDATE statement
      
      Response.Write(String.Format("<p>For row {0}, the FAQID is {1}, the Question is ""{2}""" & _
                     " and the Submitted By is ""{3}""", dgi.ItemIndex, id, question, submittedBy))
    Next
  End Sub
</script>

<form runat="server">
  <asp:label id="lblUpdateResults" Font-Bold="True" runat="server" />
  <p>
  
  <asp:datagrid id="dgPopularFAQs" runat="server"
  	AutoGenerateColumns="False"
  	HeaderStyle-HorizontalAlign="Center"
  	HeaderStyle-BackColor="Red"
  	HeaderStyle-ForeColor="White"
  	HeaderStyle-Font-Bold="True"
  	HeaderStyle-Font-Name="Verdana"
  	AlternatingItemStyle-BackColor="#dddddd"
  	ItemStyle-Font-Name="Verdana"
		
  	DataKeyField="FAQID">
	
    <Columns>
      <asp:BoundColumn DataField="FAQID" ItemStyle-Width="10%"
  			ItemStyle-HorizontalAlign="Center" HeaderText="FAQ ID" />

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

    </Columns>	
  </asp:datagrid>
</form>
	


[Return to the article...]