Friday, July 6, 2007

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.

11 comments:

josh said...

Hi David,

Oh man, I have been searching everywhere for something like this. Is it possible to get the full source for this example you have posted? I am getting stuck on the stored procedures. Please let me know if this is possible. I just need to be able to work off of a working example.

David Smith said...

Hi Josh,

Here is the source for the stored procedure:

Please email me smithd98@gmail.com if you have any questions.

Create PROCEDURE [dbo].[usp_camp_resubmit_deposit_ticket]
-- Add the parameters for the stored procedure here
@deposit_id int,
@deposit_slip_no varchar(20),
@deposit_date datetime,
@Amount money,
@image_width int = null,
@image_height int = null,
@image image
AS
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
declare @ErrorMessage varchar(4000)

SET @ErrorMessage = ''

begin transaction

delete from camp_deposit_ticket
where deposit_slip_no = (select deposit_slip_no from camp_deposit_ticket where deposit_id = @deposit_id)
IF (@@ERROR <> 0) GOTO ERR_HANDLER

declare @image_id int
if datalength(@image) > 0 --if no image was supplied, don't replace the image
begin
insert into image_db.dbo.image_data (image_width,image_height,image_thumbnail,image_data) values
(@image_width, @image_height, 0x0, @image)
IF (@@ERROR <> 0) GOTO ERR_HANDLER
set @image_id = @@identity
end

insert into camp_deposit_ticket (DEPOSIT_DATE, DEPOSIT_WEEK, DEPOSIT_YEAR, DEPOSIT_SLIP_NO, PARK_NO, AMOUNT, CASHIER_NOTES, DEPOSIT_IMAGE_ID)
select @DEPOSIT_DATE, DEPOSIT_WEEK, DEPOSIT_YEAR, @DEPOSIT_SLIP_NO, PARK_NO, @AMOUNT, CASHIER_NOTES, isnull(@image_id,DEPOSIT_IMAGE_ID)
from camp_deposit_ticket_errors where deposit_id = @deposit_id
IF (@@ERROR <> 0) GOTO ERR_HANDLER

update camp_registrations set deposit_id = @@identity where deposit_id = @deposit_id
IF (@@ERROR <> 0) GOTO ERR_HANDLER

EXEC usp_camp_cars_transfer @deposit_id
IF (@@ERROR <> 0) GOTO ERR_HANDLER

commit transaction
return 0

ERR_HANDLER:
set @Errormessage = (select top 1 description from master.dbo.sysmessages where error = @@error)
RAISERROR(@ErrorMessage,16,1)
ROLLBACK
RETURN 1
END

Yonah said...

Hi,

I am not storing the binary data in my database rather a link, however I am essentially doing the same thing as you.
The problem I am having is that, if the user does not want to change the image then the field is updated to an empty string.
I was wondering how you handle the update when there is no change to that field?
Thanks in advance for any help,
Yonah Wahrhaftig

Scotty said...

Thanks David, been trying to figure out why my gridview wasn't updating. Specifying the DataKeyNames PK did the trick. Thanks heaps!

Anonymous said...

that,s wonderful. would be great if could give the code for the detailsview control. i m not able to get the value or convert the fileUpload control to binary for update..
thanx
vrhari@air.org.in

Anonymous said...

[url=http://girls.reira.ru/dirty-russian-girls.html]dirty russian girls[/url] [url=http://girls.reira.ru/russian-dating-domestically-submissive.html]russian dating domestically submissive[/url]

Anonymous said...

I am very impressed with the article I have just read. I wish the author of blog.davidsilvasmith.com can continue to provide so much useful information and unforgettable experience to blog.davidsilvasmith.com readers. There is not much to state except the following universal truth: My name is alex I will be back.

Anonymous said...

hello everybody

I figured it would be a good idea to introduce myself to everyone!

Can't wait to start some good conversations!

-Marshall

Thanks again!

Anonymous said...

hello


great forum lots of lovely people just what i need


hopefully this is just what im looking for looks like i have a lot to read.

Anonymous said...

It' s the first time I have heard that in Macedonia, obits are an unusual observe. You have wonderfully written the post. I have liked your way of writing this. Thanks for sharing this.

Anonymous said...

I find this article from google, it's really useful for me, hope I can post this in my blog.