Grid Updates and GridBinaryImageColumn

[Peter Parsons]

Hi,

is it possible to detect whether a file has been selected during the update process and only update the image if one has been selected, in other words be able to update the other fields without always having to re-upload the image?

-a-

[Peter Parsons]

so I found a way to do this.

I changed UpdateCommandType="StoredProcedure" and UpdateCommand to the sp name, then in the stored procedure checked for null on the data param and varied the update accordingly.

CREATE PROCEDURE udp_AdvertUpdate
    @advertId int = null,
    @title nvarchar(128) = null,
    @alt nvarchar(128) = null,
    @href nvarchar(256) = null,
    @imgData varbinary(max) = null,
    @refCode nvarchar(50) = null,
    @sizeId int = null,
    @consumerId int = null,
    @categoryId int = null,
    @active bit = 0
AS
BEGIN
    SET NOCOUNT ON;

    IF @imgData IS NULL
        UPDATE [Adverts] SET [title] = @title, [alt] = @alt, [href] = @href, [refCode] = @refCode, [sizeId] = @sizeId, [consumerId] = @consumerId, [categoryId] = @categoryId, [active] = @active WHERE [advertId] = @advertId
    ELSE
        UPDATE [Adverts] SET [title] = @title, [alt] = @alt, [href] = @href, [refCode] = @refCode, [imgData] = @imgData, [sizeId] = @sizeId, [consumerId] = @consumerId, [categoryId] = @categoryId, [active] = @active WHERE [advertId] = @advertId

END

The SqlDataSource is now...

<asp:SqlDataSource ID="dsAdverts" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnString %>"
    OnUpdated="DBCompleted" OnInserted="DBCompleted" OnDeleted="DBCompleted" SelectCommand="SELECT [advertId], [title], [alt], [href], [refCode], [imgData], s.[sizeId], s.[size], co.[consumerId], ISNULL(co.[name], 'All Consumers') AS consumerName, ca.[categoryId], ISNULL(ca.[name], 'All Categories') AS [categoryName], [active] FROM [Adverts] a INNER JOIN [Sizes] s ON a.[sizeId] = s.[sizeId] LEFT JOIN [Consumers] co ON a.[consumerId] = co.[consumerId] LEFT JOIN [Categories] ca ON a.[categoryId] = ca.[categoryId] ORDER BY [title]"
    DeleteCommand="DELETE FROM [Adverts] WHERE [advertId] = @advertId" InsertCommand="INSERT INTO [Adverts] ([title], [alt], [href], [refCode], [imgData], [sizeId], [consumerId], [categoryId], [active]) VALUES (@title, @alt, @href, @refCode, @imgData, @sizeId, @consumerId, @categoryId, @active)"
    UpdateCommand="udp_AdvertUpdate" UpdateCommandType="StoredProcedure">
    <DeleteParameters>
        <asp:Parameter Name="advertId" Type="Int32" />
    </DeleteParameters>
    <InsertParameters>
        <asp:Parameter Name="title" Type="String" />
        <asp:Parameter Name="alt" Type="String" />
        <asp:Parameter Name="href" Type="String" />
        <asp:Parameter Name="refCode" Type="String" />
        <asp:Parameter Name="imgData" DbType="Binary" />
        <asp:Parameter Name="sizeId" Type="Int32" />
        <asp:Parameter Name="consumerId" Type="Int32" />
        <asp:Parameter Name="categoryId" Type="Int32" />
        <asp:Parameter Name="active" Type="Boolean" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="title" Type="String" />
        <asp:Parameter Name="alt" Type="String" />
        <asp:Parameter Name="href" Type="String" />
        <asp:Parameter Name="refCode" Type="String" />
        <asp:Parameter Name="imgData" DbType="Binary" />
        <asp:Parameter Name="sizeId" Type="Int32" />
        <asp:Parameter Name="consumerId" Type="Int32" />
        <asp:Parameter Name="categoryId" Type="Int32" />
        <asp:Parameter Name="active" Type="Boolean" />
        <asp:Parameter Name="advertId" Type="Int32" />
    </UpdateParameters>
</asp:SqlDataSource>

This does not enable you to remove an image already uploaded, but in this case that is fine. I guess I could add a custom GridButtonColumn to fire a separate stored proc to remove the image if that is ever required.

-a-