Updating Binary Data in a Gridview

Our customer needed the ability to update an image in a database. I wanted them to be able to view and edit all the data through the datagrid.

In your data source you need to specify the select command to display the data and the updateCommand to update the data:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:campDBConnection %>"

SelectCommand="usp_CAMP_get_rejected_batches"
SelectCommandType="StoredProcedure"
UpdateCommand="usp_camp_resubmit_deposit_ticket"
UpdateCommandType="StoredProcedure">

<UpdateParameters>
<asp:Parameter Name="deposit_id" Type="Int32" />
<asp:Parameter Name="deposit_slip_no" Type="String" />
<asp:Parameter Name="deposit_date" Type="DateTime" />
<asp:Parameter Name="Amount" Type="Decimal" />
<asp:Parameter Name="image" /> </UpdateParameters>
</asp:SqlDataSource>

I used a TemplateField to include the FileUpload control which allows a user to select a file from their computer for upload. This also allowed the same column to be used to display the image and change it.

<asp:TemplateField HeaderText="Deposit Ticket">
<EditItemTemplate>
<asp:FileUpload ID="imgUpload" runat="server" />
</EditItemTemplate>
<ItemTemplate>
<asp:Image ImageUrl='<%#DataBinder.Eval(Container.DataItem, "DEPOSIT_IMAGE_URL")%>' runat = "server" />
</ItemTemplate>
</asp:TemplateField>

I needed to add in the binary data into e.NewValues:

Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating

e.NewValues.Add("image", CType(GridView1.Rows(e.RowIndex).Cells.Item(5).Controls(1), FileUpload).FileBytes)

End Sub

I also found a couple more gotchas:

1. To format data in the grid you need to have HtmlEncode
   turned off


2. For the upldate to send the primary key field and have that field set to invisible you need to specify the primary key in the DataKeyNames property for the GridView.