Get image/picture from database

Sousa Pambo

12y1.9kVisual Basic .NET

Hi fellow, I need your help. I don't get the error on this

my Crud

public Image GetFuncImage(UIL.frmFunc FC, string lbFuncID)

{

string sql = "select funcFoto from FuncData where nomeFunc ='" + lbFuncID + "'";

using (SqlConnection myconn = new SqlConnection(con.stringConexaoRH()))

{

try

{

myconn.Open();

using (SqlCommand comand = new SqlCommand(sql, myconn))

{

using (SqlDataReader dtreader = comand.ExecuteReader())

{

if (dtreader.Read())

{

Byte[] bytePicData = dtreader["funcFoto"] as byte[] ?? null;

if(bytePicData != null)

{

MemoryStream mstream = new MemoryStream(bytePicData);

FC.pbFuncFoto.Image = System.Drawing.Image.FromStream(mstream);

{

System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(mstream);

}

catch (Exception ex)

{

return FC.pbFuncFoto.Image = RH_2013.Properties.Resources.icon_employer;

}

return FC.pbFuncFoto.Image = RH_2013.Properties.Resources.icon_employer;

}

__________________________________________________________________________________________

my Event

private void pbFuncFoto_Click(object sender, EventArgs e)

{

this.pbFuncFoto.Image = FuncDal.GetFuncImage(this, this.lbIdFunc.Text);

}

__________________________________________________________________________--

Where I'm wrong here....

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
Yes, there is a problem in the way you're saving the image to the database.

Try this instead:

public void newFuncMax(UIL.frmFunc Fc, string idLb)
{
System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();
sqlBuilder.Append("Update FuncData set ");
sqlBuilder.Append("func_id='" + Fc.lbIdFunc.Text + "',");
sqlBuilder.Append("FuncFuncao='" + Fc.cbFuncao.SelectedValue + "',");
sqlBuilder.Append("funcFoto=@Pic,"); // NOW USING A PARAMETER
sqlBuilder.Append("sexo='" + Fc.cbSexo.Text + "'"); // NO NEED FOR FINAL COMMA
sqlBuilder.Append(" where func_id='" + Fc.lbIdFunc.Text + "'");

using (SqlConnection myconn = new SqlConnection(con.stringConexaoRH()))

try
{
myconn.Open();
using (SqlCommand cm = new SqlCommand(sqlBuilder.ToString(), myconn))
{
MemoryStream ms = new MemoryStream();
Fc.pbFuncFoto.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
cm.Parameters.Add("@Pic", SqlDbType.Image).Value = ms.ToArray();
cm.ExecuteNonQuery();
MessageBox.Show ("Funcionário Adicionado com sucesso");
}
}
catch (Exception ex)
{
MessageBox.Show("" +ex);
}
}
Sousa Pambo12y ago
Hi Vulpes,

DONE! Thank u very much, its working.
Vulpes12y ago
Parameters don't need to be enclosed in single quotes - if they're needed, they're supplied automatically before the query is processed by the database server.

I don't understand why SqlDbType can't be found as it's an enum in the System.Data namespace.

You must have the following using directive in your file:

using System.Data;

because otherwise SqlConnection and SqlCommand couldn't be found either.
Sousa Pambo12y ago
Hi Vulpes its seems logical, but I found some error

sqlBuilder.Append("funcFoto=@Pic,"); don't missing something in this line like.....this?

sqlBuilder.Append("funcFoto='"+@Pic +"',"); (its not an error.)

cm.Parameters.Add("@Pic", SqlDbType.Image).Value = ms.ToArray();
- Error "doesn't exist in the current context"

Please help on this
Munesh Sharma12y ago
// You should use this code..
public partial class Form1 : Form
{ SqlConnection cnn ;
string connectionString ;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
connectionString = "Data Source=munesh; Initial Catalog=data; User ID=sa; Password=123";
cnn = new SqlConnection(connectionString);
MemoryStream stream = new MemoryStream();
cnn.Open();
SqlCommand command = new SqlCommand("select img from imgtable where id=2", cnn);
byte[] image = (byte[])command.ExecuteScalar();
stream.Write(image, 0, image.Length);
cnn.Close();
Bitmap bitmap = new Bitmap(stream);
pictureBox1.Image = bitmap;
}
}
}
Sousa Pambo12y ago
Hi Vulpes,

Help me to see the way I save it maybe something is wrong

public void newFuncMax(UIL.frmFunc Fc, string idLb)

        {

            System.Text.StringBuilder sqlBuilder = new System.Text.StringBuilder();

            sqlBuilder.Append("Update FuncData set ");

            sqlBuilder.Append("func_id='" + Fc.lbIdFunc.Text + "',");

            .

.

.

.

            sqlBuilder.Append("FuncFuncao='" + Fc.cbFuncao.SelectedValue + "',");

            sqlBuilder.Append("funcFoto='" + Fc.pbFuncFoto + "',");                   //PICTUREBOX – This is the way I save the image

            sqlBuilder.Append("sexo='" + Fc.cbSexo.Text + "',");

            .

.

.

.

            sqlBuilder.Append(" where func_id='" + Fc.lbIdFunc.Text + "'");

            using (SqlConnection myconn = new SqlConnection(con.stringConexaoRH()))



                try

                {

                    myconn.Open();

                    using (SqlCommand cm = new SqlCommand(sqlBuilder.ToString(), myconn))

                    {

                        cm.ExecuteNonQuery();

                       MessageBox.Show ("Funcionário Adicionado com sucesso");

                    }

                }

                catch (Exception ex)

                {

                    MessageBox.Show("" +ex);

                }

           }

private void btFoto_Click(object sender, EventArgs e)

       {

           try

           {

               // open file dialog

               OpenFileDialog open = new OpenFileDialog();

               // image filters

               open.Filter = "Image Files(*.jpg; *.jpeg; *.gif; *.bmp; *.png)|*.jpg; *.jpeg; *.gif; *.bmp; *.png";

               if (open.ShowDialog() == DialogResult.OK)

               {

                   // display image in picture box

                   pbFuncFoto.Image = new Bitmap(open.FileName);

                   // image file path

                   //textBox1.Text = open.FileName;

               }

           }

           catch (Exception)

           {

               throw new ApplicationException("Houve falha na busca de Imagem....");

           }

       }
Vulpes12y ago
Are you able to pinpoint with the debugger on which line the error is occurring?

If not, try commenting out this line which doesn't seem to be doing anything:

System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(mstream);

Incidentally, what type of image is it?
Sousa Pambo12y ago
Hi Vilpes, thanks again

the Error is "parameter is not valid". Well, I still don't get the point. please help
Vulpes12y ago
Well, if there is an exception, we need to get to the bottom of that before we can do anything else.

To try and get some information on what's happening, change your catch clause as follows:

catch (Exception ex)
{
MessageBox.Show(ex.Message);
return FC.pbFuncFoto.Image = RH_2013.Properties.Resources.icon_employer;
}
Sousa Pambo12y ago
Hi Vulpes I hope u r fine.

It gets the exception (ex) line, the default image. I think there is error at the lines before this one (ex) I really don't get what error is this. How could I do if it was a void not function? Please help.
Vulpes12y ago
I think the problem is this line right at the end of the method which is resetting the picturebox image back to the default image even if there's no error:

return FC.pbFuncFoto.Image = RH_2013.Properties.Resources.icon_employer;

Although the code is not ideal (it would have been better if the method had been void), the easiest way to deal with it is to change that line to simply:

return FC.pbFuncFoto.Image;
Sousa Pambo12y ago
Hi Vulpes, I think I was not clear.

Actually It doesn't show any error (this the problem for me) it continues showing the default image I chose when a click my pbox. and I dont know how to bring error message for solving this problem.

Thank u all
Vulpes12y ago
What error are you getting?