none
How to save image in SQLServer 2005 RRS feed

  • Question

  • I have winform and when i browse image i want to save the image to SQL Server along with other values from textfields.
    Currently when i do below procedure it save image in SQL Server as:0x433A5C446F637..... some hexadecimal characters.

    How I can save the image in a proper format and later i can retrieve it as well.


    Code Snippet

    private void empbrowseimg_Click(object sender, EventArgs e)
            {
                OpenFileDialog openFile = new OpenFileDialog();
                openFile.InitialDirectory = @"C:\Documents and Settings\hahmed\My Documents\family pics";
                openFile.Filter = "JPEG files (*.jpg)|*.jpg|GIF files (*.gif)|*.gif |BMP files (*.bmp)|*.bmp |All files (*.*)|*.*";
                openFile.FilterIndex = 4;
                openFile.RestoreDirectory = true;
                if ( openFile.ShowDialog() != DialogResult.Cancel)
                {
                    empimgTextbox.Text = openFile.FileName;
                   
                }
                else
                {
                    empimgTextbox.Text = "";
                }
    String query=null;
    query = "INSERT INTO Employee (EmpID, EmpName,Phone,Rank,PerHour,Address,Image) " +
                                             "Values ('" + empidtxtbox.Text + "','" + empNametxtbox.Text + "','" + empphonetxtbox.Text + "','" + empRanktxtbox.Text + "','" + empperhrtxtbox.Text + "','" + empAddrtxtbox.Text + "','" + empimgTextbox.Text+"')";
                    SqlCommand myCommand = new SqlCommand(query, myConnection);
                    myCommand.ExecuteNonQuery();
                    MessageBox.Show("Data Has Been Inserted");


                

    Thanks in Advance
    Wednesday, June 13, 2007 4:13 PM

All replies

  • http://www.codeproject.com/cs/database/ImageSaveInDataBase.asp

    Charles
    Wednesday, June 13, 2007 7:29 PM
  • Thanks and Now i did following but got the exception

    I have a table of employee in SQL Server 2005 DB as:

    Name, Address,Phone,Rank, Image (binary) [There is no data type BYTE in SQLServer 2005]

    Now in C# I did following on Browse Button

    Code Snippet

     private void empbrowseimg_Click(object sender, EventArgs e)
            {
                OpenFileDialog openFile = new OpenFileDialog();
                openFile.InitialDirectory = @"C:\Documents and Settings\hahmed\My Documents\family pics";
                openFile.Filter = "JPEG files (*.jpg)|*.jpg|GIF files (*.gif)|*.gif |BMP files (*.bmp)|*.bmp |All files (*.*)|*.*";
                openFile.FilterIndex = 4;
                openFile.RestoreDirectory = true;
                String strFn;
                if ( openFile.ShowDialog() != DialogResult.Cancel)
                {
                    empimgTextbox.Text = openFile.FileName;
                    strFn = openFile.FileName;
                    FileInfo fiImage = new FileInfo(strFn);
                    this.m_lImageFileLength = fiImage.Length;
                    FileStream fs = new FileStream(strFn, FileMode.Open, FileAccess.Read, FileShare.Read);
                    m_barrImg = new byte[Convert.ToInt32(this.m_lImageFileLength)];
                    int iBytesRead = fs.Read(m_barrImg, 0, Convert.ToInt32(this.m_lImageFileLength));
                    fs.Close();
                   
                }
                else
                {
                    empimgTextbox.Text = "";
                }
               

            }



    Now on Register Button

    Code Snippet

    public void InsertDB()
            {
                String query = null;
                SqlCommand myCommand=null;
                
            
                try
                {
                    myConnection.Open();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }
                try
                {
                    query = "INSERT INTO Employee (EmpID, EmpName,Phone,Rank,PerHour,Address,Image) " +
                                                 "Values ('" + empidtxtbox.Text + "','" + empNametxtbox.Text + "','" + empphonetxtbox.Text + "','" + empRanktxtbox.Text + "','" + empperhrtxtbox.Text + "','" + empAddrtxtbox.Text + "','" + empimgTextbox.Text + "')";
                    myCommand = new SqlCommand(query, myConnection);
                    myCommand.ExecuteNonQuery();
                    MessageBox.Show("Data Has Been Inserted");
                }
                catch (Exception e2)
                {
                    MessageBox.Show(e2.ToString());
                }



    Exception Occurs: System.Data.SqlClient.SqlException-Implicit Conversion of datatype varchar to binary is not allowed. Use the CONVERT function to run this query.

    Please help out
    Thursday, June 14, 2007 9:44 AM