Problems adding an Image to SQL Database in ASP.NET

Question

I have been working on that for a while now. I am trying to add an Image to an SQL Database. I know it is not the best way but my boss really wants it that way. The table in which I will add the image has and Image column of type Image.

This is the stored procedure I am using to add it:

ALTER PROCEDURE pr_INSRT_Img (@Img image, @name varchar(50))
AS
BEGIN
    UPDATE usr
    SET Img= @Img
    WHERE disp_nm = @name
END

My application is made in ASP.NET 4.0. The it works is that the user chooses and Image with an AJAX AsyncFileUpload and on the UploadComplete function of that control, it calls the StoredProcedure to do the INSERT. The ASynchFileUPload is in an accordion that is in an UpdatePanel that is in a gridview.

UploadComplete:

protected void OnUpdateComplete(object sender, EventArgs e)
        {
            Image ImgUser = new Image();
            AsyncFileUpload asyncSender = new AsyncFileUpload();
            UpdatePanel pnlinfo = new UpdatePanel();
            AsyncFileUpload asyncGv = new AsyncFileUpload();
            Accordion accordion = new Accordion();
            Label lblName = new Label();

            //finding the row of the sender
            asyncSender = (AsyncFileUpload)sender;

            foreach (GridViewRow Row in gvData.Rows)
            {
                accordion = (Accordion)Row.Cells[0].FindControl("Accordion");
                asyncGv = (AsyncFileUpload)accordion.FindControl("AsyncFileUpload");
                if (asyncSender.ClientID == asyncGv.ClientID)
                {
                    ImgUser = (Image)accordion.FindControl("imgUser");
                    ImgUser.ImageUrl = asyncGv.FileName;
                    lblName = (Label)Row.Cells[0].FindControl("lblPnlName");
                    bool IsWorking = InsertImage(asyncGv.FileBytes, lblName.Text);
                    pnlinfo = (UpdatePanel)Row.Cells[0].FindControl("pnlInfo");
                    pnlinfo.Update();
                    break;
                }
            }

InsertImage:

    //pr_INSRT_Img
    SqlCommand cmd = new SqlCommand(ConfigManager.InsertImage);
    cmd.CommandType = CommandType.StoredProcedure;

    //set the parameters
    cmd.Parameters.Add("@Img", SqlDbType.Image).Value = ImgData;
    cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = Name;

    int ifWorks = DBUtils.ExecuteCmdScalar(cmd, ConfigManager.PhonebookSQLConnectionString);

    if (ifWorks != 0)
        return true;
    else
        return false;

ASPX portion of the code where the ASychFileUpload is:

<div runat="server" class="divRow" style="text-align:center; width:300px; float:left;">
                                        <asp:Accordion ID="Accordion" runat="server" FadeTransitions="true" FramesPerSecond="40" TransitionDuration="250" 
                                            AutoSize="None" SelectedIndex="-1" RequireOpenedPane="false" SuppressHeaderPostbacks="true" Height="50px" Width="360px">
                                            <Panes>
                                                <asp:AccordionPane ID="AccordionPane" runat="server">
                                                    <Header>
                                                        <asp:Image ID="imgUser" runat="server" ImageAlign="Middle" ImageUrl="~/silhouette.jpg"
                                                            Width="100px" Height="100px" EnableViewState="true"/>
                                                    </Header>
                                                    <Content>
                                                        <asp:AsyncFileUpload ID="AsyncFileUpload" runat="server" OnUploadedComplete="OnUpdateComplete" />
                                                    </Content>
                                                </asp:AccordionPane>
                                            </Panes>
                                        </asp:Accordion>
                                        <asp:Button ID="btnUpdate" runat="server" OnClick="btnUpdate_Click" Visible="false"  />
                                    </div><br /><br />

My problem is that when I upload the image, the ExecuteScalar() returns a 0 which means that the Image was not inserted in the Table. I've tried putting a varbinary(max) Field instead of an Image type but it did not work. All I can find on the Net tells me to use that code, or variant of that code (other ways to get the byte array, I just took the simplest).

Overall, why is the UPDATE failing and how can I make it work.

Thanks

Do you get an error? If not is there data in usr.Image or is it null? — Conrad Frix
– Conrad Frix, Commented Apr 15, 2011 at 19:02

Steve Wellens · Accepted Answer · 2011-04-15 20:56:11Z

0

This article shows everything:

http://www.dotnetcurry.com/ShowArticle.aspx?ID=129

Hopefully, when you get it to work and your boss sees how slow it is, you'll be allowed to do it the right way and store the image on disk.

answered Apr 15, 2011 at 20:56

Steve Wellens

20.6k2 gold badges31 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hugo Over a year ago

thx, I ended up convincing my boss to only send the path instead.

Steve Wellens Over a year ago

I would be hesitant to store the complete path - the images may move in the future. Often, just the filename is stored and the image directory is a setting.

Collectives™ on Stack Overflow

Problems adding an Image to SQL Database in ASP.NET

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related