Unable to retrieve or store image in database SQL Server 2005

Question

I am trying to retrieve image from back end in ASP.Net. Using SQL SERVER 2005 as back end. I have tried n number of codes including the one available online. Can any one guide me solve this issue.

My code is below

Table Design:-

create table Image
 (
ImageId Int identity (1,1),ImageName Varchar(50), Image image   
)

Code:-

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindGridData();
        }
    }
    string strcon = "Data Source=SUJITHA\\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
    protected void Button1_Click(object sender, EventArgs e)
    {
        if (FileUpload1.HasFile)
        {
            //getting length of uploaded file
            int length = FileUpload1.PostedFile.ContentLength;
            //create a byte array to store the binary image data
            byte[] imgbyte = new byte[length];
            //store the currently selected file in memeory
            HttpPostedFile img = FileUpload1.PostedFile;
            //set the binary data
            img.InputStream.Read(imgbyte, 0, length);
            string imagename =TextBox1.Text;
            //use the web.config to store the connection string
            SqlConnection connection = new SqlConnection(strcon);
            connection.Open();
            SqlCommand cmd = new SqlCommand("INSERT INTO Image (ImageName,Image) VALUES (@imagename,@imagedata)", connection);
            cmd.Parameters.Add("@imagename", SqlDbType.VarChar, 50).Value = imagename;
            cmd.Parameters.Add("@imagedata", SqlDbType.Image).Value = imgbyte;
            int count = cmd.ExecuteNonQuery();
            connection.Close();
            if (count == 1)
            {
                BindGridData();
                TextBox1.Text = string.Empty;
                ScriptManager.RegisterStartupScript(this, this.GetType(), "alertmessage", "javascript:alert('" + imagename + " image inserted successfully')", true);
            }
        }
    }

    private void BindGridData()
    {
        SqlConnection connection = new SqlConnection(strcon);
        SqlCommand command = new SqlCommand("SELECT ImageName,Image  from [Image]", connection);
        SqlDataAdapter daimages = new SqlDataAdapter(command);
        DataTable dt = new DataTable();
        daimages.Fill(dt);
       GridView1.DataSource = dt;
       GridView1.DataBind();
       GridView1.Attributes.Add("bordercolor", "black");
    }

Is there a particular error or exception of some kind? What indication do you have that it's not working? When you debug the code, at what point does the observed behavior deviate from the expected behavior? — David, Jun 14 '12 at 10:14
this is what you are looking for: http://www.codeproject.com/Articles/10861/Storing-and-Retrieving-Images-from-SQL-Server-usin — Rumplin, Jun 14 '12 at 10:15
You should use the `VARBINARY(MAX)` datatype - `IMAGE` has been deprecated with SQL Server 2005. — marc_s, Jun 14 '12 at 10:16
actually the image is not getting saved in the database. that particular column remains empty while inserting — Arjun, Jun 14 '12 at 12:12

score 1 · Answer 1 · edited Oct 12 '12 at 09:48

SqlConnection con = new SqlConnection(@"Data Source=AMAR-PC\SQLEXPRESS;Initial Catalog=a;User ID=sa;Password=amar");
string path = Server.MapPath("Images/");

if (FileUpload1.HasFile)
{
    byte[] img = new byte[FileUpload1.PostedFile.ContentLength];
    HttpPostedFile myimage = FileUpload1.PostedFile;
    myimage.InputStream.Read(img, 0, FileUpload1.PostedFile.ContentLength);
    SqlCommand cmd = new SqlCommand("insert into images values ('" + TextBox1.Text + "','" + FileUpload1.PostedFile + "')", con);

    con.Open();

This is what i did...and I also know that there are many other ways to upload file.... Now I want that a the uploaded pic should be dispayed in Image Control....could you help me now

Nikhil D · Answer 2 · 2012-06-15T10:13:11.540

    */Your code Like this*         

    **//Insert the file into database**

            string strQuery = "insert into tblFiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)";

            SqlCommand cmd = new SqlCommand(strQuery);

            cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;

            cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/vnd.ms-word";

            cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;

            InsertUpdateData(cmd);

    **//Select the file from database**
        string strQuery = "select Name, ContentType, Data from tblFiles where id=@id";

        SqlCommand cmd = new SqlCommand(strQuery);

        cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;

        DataTable dt = GetData(cmd);

        if (dt != null)

        {

            download(dt);

        }

        private void download (DataTable dt)

        {

            Byte[] bytes = (Byte[])dt.Rows[0]["Data"];

            Response.Buffer = true;

            Response.Charset = "";

            Response.Cache.SetCacheability(HttpCacheability.NoCache);

            Response.ContentType = dt.Rows[0]["ContentType"].ToString();

            Response.AddHeader("content-disposition", "attachment;filename="

            + dt.Rows[0]["Name"].ToString());

            Response.BinaryWrite(bytes);

            Response.Flush();

            Response.End();

        }
private DataTable GetData(SqlCommand cmd)
    {
        DataTable dt = new DataTable();
        String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
        SqlConnection con = new SqlConnection(strConnString);
        SqlDataAdapter sda = new SqlDataAdapter();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        try
        {
            con.Open();
            sda.SelectCommand = cmd;
            sda.Fill(dt);
            return dt;
        }
        catch 
        {
            return null;
        }
        finally
        {
            con.Close();
            sda.Dispose();
            con.Dispose();
        }
    }

Dear Nikhil, could you just tell if u have used any namespace/ assemblies for using getdata() — Arjun, Jun 15 '12 at 09:36

score 0 · Answer 3 · edited May 23 '17 at 11:43

0

Couple of things worry me

you use the master database
your table name is image which is a reserved word in SQL Server

If you need to name tables with reserved names, you need to quote them with square brackets: INSERT INTO [Image] ...

similar questions have been asked and answered many times here
your acceptance rate is 0%

edited May 23 '17 at 11:43

Community

1
1

answered Oct 12 '12 at 10:18

devio

36,858
7
80
143

Unable to retrieve or store image in database SQL Server 2005

3 Answers3