How to insert image from gridview to another table in database

Question

Dear all I am displaying an image in my gridview, this image is saved in database in varbinary format, with its content type and image name. And my image in gridview is displaying perfect, now I want to insert this same image from gridview to another table from the button click outside the gridview, How do I achieve this can anyone please guide me? I tried achieving it by receiving this image data from gridview such as Varbinary data - which is an image in database and content type and imagename into textbox but it thorws an error "Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query"

<asp:TemplateField HeaderText="" ItemStyle-Width="" Visible="true">
            <ItemTemplate>
                <asp:HyperLink ID="HyperLink1" class="preview" ToolTip='<%#Bind("StaffName") %>'
                    NavigateUrl='' runat="server">
                    <asp:ImageButton runat="server" ID="Image2" class="img2" ImageUrl='<%# Eval("ImageName") %>'
                        CommandName='<%# Eval("Id") %>' CommandArgument='<%# Eval("ImageName") %>' />
                </asp:HyperLink>
                <asp:TextBox ID="txtFileType" runat="server" Text='<%# Eval("FileType") %>' Visible="true"></asp:TextBox>
                <asp:TextBox ID="txtBData" runat="server" Text='<%# Eval("BData") %>' Visible="true"></asp:TextBox>
                <asp:TextBox ID="txtImageName" runat="server" Text='<%# Eval("ImageName") %>' Visible="true"></asp:TextBox>
                <br />
                <br />
            </ItemTemplate>
            <ControlStyle Width="100%" />
            <HeaderStyle HorizontalAlign="Left" VerticalAlign="Middle" Width="10%" />
            <ItemStyle HorizontalAlign="Center" VerticalAlign="Middle" Width="20%" />
        </asp:TemplateField>




foreach (GridViewRow row1 in gvImage.Rows)
            {
                if (row1.RowType == DataControlRowType.DataRow)
                {
                    //  txtFileType
                    //   txtBData
                    //   txtImageName
                    TextBox txtFileType, txtBData, txtImageName;
                    txtFileType = (row1.Cells[1].FindControl("txtFileType") as TextBox);
                    txtBData = (row1.Cells[1].FindControl("txtBData") as TextBox);
                    txtImageName = (row1.Cells[1].FindControl("txtImageName") as TextBox);
                    string constr = ConfigurationManager.ConnectionStrings["CONNECTION"].ConnectionString;
                    using (SqlConnection con8 = new SqlConnection(constr))
                    {
                        string query = "insert into SShare (FId,UDetails,ShareBy,ShareByUserId,BData,FileType,ImageName) values(@FId,@UDetails,@ShareBy,@ShareByUserId,@BData,@FileType,@ImageName)";

                        using (SqlCommand cmd8 = new SqlCommand(query))
                        {

                            cmd8.Parameters.AddWithValue("@FId", txt_Tester.Text);
                            cmd8.Parameters.AddWithValue("@UDetails", TextBox1.Text);
                            cmd8.Parameters.AddWithValue("@ShareBy", txt_StaffId.Text);
                            cmd8.Parameters.AddWithValue("@ShareByUserId", txt_Employee.Text);
                            cmd8.Parameters.AddWithValue("@BData", txtBData.Text);
                            cmd8.Parameters.AddWithValue("@FileType", txtFileType.Text);
                            cmd8.Parameters.AddWithValue("@ImageName", txtImageName.Text);

                            con8.Open();
                            // cmd8.ExecuteNonQuery();
                            this.ExecuteQuery(cmd8, "SELECT");

                            con8.Close();
                        }
                    }
                }
            }

If one of those SqlParameters is supposed to be the image then you need to make the value a byte array, not a string. — Crowcoder
– Crowcoder, Commented Apr 17, 2017 at 11:00
Could you provide the output of your html for the image please. — Randi Ratnayake
– Randi Ratnayake, Commented Apr 17, 2017 at 11:04
I can't because I don't see the relevant code. The only image I see is an image button and it pulls its image from a URL, not an actual image. So I really have no idea where the image is. I just know if you are trying to insert into a varbinary field you can't use a string. — Crowcoder
– Crowcoder, Commented Apr 17, 2017 at 11:04
@jdweng, that would be quite a trick since this is asp.net webforms :) — Crowcoder
– Crowcoder, Commented Apr 17, 2017 at 11:49

Crowcoder · Accepted Answer · 2017-04-17 13:36:57Z

2

Here is what I suggest. You could get all the data out of the grid, but you can also just do it in SQL.

Notice BDAta is NOT a SqlParameter, it is pulled from the Employee table:

INSERT INTO [SShare](FId,UDetails,ShareBy,ShareByUserId,BData,FileType,ImageName)
    SELECT @FId, @UDetails, @ShareBy, @ShareByUserId, BData, @FileType, @ImageName
    FROM Employee
    WHERE FId = @FId;

answered Apr 17, 2017 at 13:36

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

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

2 Comments

Dharam Rai Over a year ago

Let me test, it will take time.

Dharam Rai Over a year ago

thank you for giving the logic, I figured it out. I am posting an answer.

Dharam Rai · Accepted Answer · 2017-04-17 16:59:47Z

After beating head everywhere atlast I figured out and I am posting it incase if someone may refer to. Thanks to @Crowcoder for giving a logic to make it happen.

foreach (GridViewRow row1 in gvImage.Rows)
                {
                    if (row1.RowType == DataControlRowType.DataRow)
                    {
                        string Id = gvImage.DataKeys[row1.RowIndex].Value.ToString();
                        ImageButton imgbtn = (ImageButton)gvImage.Rows[row1.RowIndex].FindControl("Image2");
                        string filename = imgbtn.ImageUrl;

                        TextBox ftype = (row1.FindControl("txtFileType") as TextBox);

                        byte[] bytes = (byte[])GetData("SELECT BData FROM Employee WHERE Id =" + txt_StaffId.Text).Rows[0]["BData"];
                        string base64String = Convert.ToBase64String(bytes, 0, bytes.Length);
                        imgbtn.ImageUrl = "data:image/png;base64," + base64String;

                        {

                            string constr = ConfigurationManager.ConnectionStrings["CONNECTION"].ConnectionString;
                            using (SqlConnection con8 = new SqlConnection(constr))
                            {
                                string query = "insert into SShare (FId,UDetails,ShareBy,ShareByUserId,BData,ImageName, FileType) values(@FId,@UDetails,@ShareBy,@ShareByUserId,@BData,@ImageName,@FileType)";

                                using (SqlCommand cmd8 = new SqlCommand(query))
                                {

                                    cmd8.Parameters.AddWithValue("@FId", txt_Tester.Text);
                                    cmd8.Parameters.AddWithValue("@UDetails", TextBox1.Text);
                                    cmd8.Parameters.AddWithValue("@ShareBy", txt_StaffId.Text);
                                    cmd8.Parameters.AddWithValue("@ShareByUserId", txt_Employee.Text);
                                    cmd8.Parameters.AddWithValue("@BData", bytes);

                                    cmd8.Parameters.AddWithValue("@ImageName", filename);
                                    cmd8.Parameters.AddWithValue("@FileType", ftype.Text);

                                    con8.Open();
                                    // cmd8.ExecuteNonQuery();
                                    this.ExecuteQuery(cmd8, "SELECT");

                                    con8.Close();
                                }


                            }
                        }
                    }
                }

You are making more database trips than you need to, but working is working so cheers.

Collectives™ on Stack Overflow

How to insert image from gridview to another table in database

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related