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 `SqlParameter`s is supposed to be the image then you need to make the value a byte array, not a string. — Crowcoder, Apr 17 '17 at 11:00
Could you provide the output of your html for the image please. — Randi Ratnayake, Apr 17 '17 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, Apr 17 '17 at 11:04
@Crowcoder Sorry I am bothering you :( I am actually binding image in imagebutton inside gridview from Gridview_RowDataBound, would you like to see that? — Dharam Rai, Apr 17 '17 at 11:13
You are not bothering me, no worries. ImageButton is not the image data, it is pulled from a URL. So if I understand what you need, you must use insert into select so that you can bring in the varbinary data from the original table. See this for an example of what I mean: http://stackoverflow.com/questions/23808216/t-sql-insert-into-with-left-join — Crowcoder, Apr 17 '17 at 11:27
The gridview is in a form and to pass data from one form to another you need to use an instance of the form. See my two form porject as an example : http://stackoverflow.com/questions/34975508/reach-control-from-another-page-asp-net — jdweng, Apr 17 '17 at 11:48
@jdweng, that would be quite a trick since this is asp.net webforms :) — Crowcoder, Apr 17 '17 at 11:49
@Crowcoder Could you help me if i shift from imagebutton to simple asp img? — Dharam Rai, Apr 17 '17 at 12:17
My workday has begun, but I may be able to write a reasonable answer if you can answer these: What table is the image varbinary field in and what is it's exact name? Does `txtImageName` contain the name (or url) of the image such that you could query the image table based on name? — Crowcoder, Apr 17 '17 at 12:27

score 2 · Answer 1 · answered Apr 17 '17 at 13:36

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 '17 at 13:36

Crowcoder

11,250
3
36
45

Let me test, it will take time. – Dharam Rai Apr 17 '17 at 13:44
thank you for giving the logic, I figured it out. I am posting an answer. – Dharam Rai Apr 17 '17 at 16:53

score 1 · Accepted Answer · answered Apr 17 '17 at 16:59

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. — Crowcoder, Apr 17 '17 at 17:33

How to insert image from gridview to another table in database

2 Answers2