1

I am trying to save an image to my sql database using picturebox and save button on c# windows form application. this are the codes I used to save an image:

private void btnsave_Click(object sender, EventArgs e)
{
   MemoryStream ms = new MemoryStream();
   pictureBox2.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
   Byte[] picarray = ms.ToArray();
   String picbase64 = Convert.ToBase64String(picarray);

   SqlConnection con = new SqlConnection("Data Source=LAPTOP-EUNPD14B;Initial Catalog=db;Integrated Security=True");
    
   SqlCommand cmd = new SqlCommand();
   con.Open();
   cmd.CommandType = CommandType.Text;

   cmd.CommandText = @"INSERT INTO tblreport([entrynum],[reportdate],[reporttime],[vicfirstname],[viclastname],[vicmidname], [vicage],[vicgender],[vicaddress],[incident],[time],[date],[place],[casefiled],[susfirstname],[suslastname],[susmidname],[susage],[susgender],[susaddress],[suspic]) VALUES(@value1,@value2,@value3,@value4,@value5,@value6,@value7,@value8,@value9,@value10,@value11,@value12,@value13,@value14,@value15,@value16,@value17,@value18,@value19,@value20,@value21)";

   cmd.Parameters.AddWithValue("@value1", entryno.Text);
   cmd.Parameters.AddWithValue("@value2", dtreport.Text);
   cmd.Parameters.AddWithValue("@value3", timereport.Text);
   cmd.Parameters.AddWithValue("@value4", txtcomfirst.Text);
   cmd.Parameters.AddWithValue("@value5", txtcomlast.Text);
   cmd.Parameters.AddWithValue("@value6", txtcommid.Text);
   cmd.Parameters.AddWithValue("@value7", txtcomage.Text);
   cmd.Parameters.AddWithValue("@value8", cbocomgen.Text);
   cmd.Parameters.AddWithValue("@value9", txtcomaddress.Text);
   cmd.Parameters.AddWithValue("@value10", txtincident.Text);
   cmd.Parameters.AddWithValue("@value11", timeincident.Text);
   cmd.Parameters.AddWithValue("@value12", dtincident.Text);
   cmd.Parameters.AddWithValue("@value13", txtincidentplace.Text);
   cmd.Parameters.AddWithValue("@value14", txtcase.Text);
   cmd.Parameters.AddWithValue("@value15", susfirst.Text);
   cmd.Parameters.AddWithValue("@value16", suslast.Text);
   cmd.Parameters.AddWithValue("@value17", susmid.Text);
   cmd.Parameters.AddWithValue("@value18", susage.Text);
   cmd.Parameters.AddWithValue("@value19", cbosusgender.Text);
   cmd.Parameters.AddWithValue("@value20", susadd.Text);
   cmd.Parameters.AddWithValue("@value21", picbase64);
   cmd.Connection = con;

   cmd.ExecuteNonQuery();
   MessageBox.Show("Report Saved");

   con.Close();
}

the codes worked when the data type that I used was Varchar but the picture won't show on the datagridview so I changed the datatype to varbinary after that I had an error.

and this is the error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.

enter image description here

Please help me! Thank you!

Community
  • 1
  • 1
awochacnaib
  • 13
  • 1
  • 4
  • 2
    Is one of your columns a `VARBINARY(MAX)` type? Also this: http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – DavidG Mar 28 '17 at 15:17
  • 3
    Instead of a screen capture, please copy/paste the error – Pikoh Mar 28 '17 at 15:17
  • What is the sql script which uses varbinary(max)? What was the error text? – Kannan Kandasamy Mar 28 '17 at 15:21
  • @KannanKandasamy only the picture is the one that uses (varbinary max) the error is: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll Additional information: Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query. – awochacnaib Mar 28 '17 at 15:24
  • 2
    Do yourself a favor and give your parameters a name that isn't developer hostile. Value1, value2 is just awful. – Sean Lange Mar 28 '17 at 15:26
  • oh, thanks guys for the heads up! I'll be sure to change those – awochacnaib Mar 28 '17 at 15:33

3 Answers3

2

When you use AddWithValue() it assumes the database column type based on the type of the parameter you are passing.

You converted your picture into a base64 String, which in .NET are stored internally as Unicode characters, and are stored in the database as nvarchars. When you add this to the parameters, it assumes you want to store text characters, which would be a nvarchar, but the real database type is varbinary, which as the error suggests doesn't have an implicit conversion between them. You want to save your Byte array to the field, not convert it to base64.

I should also mention, that it's generally not very efficient to store anything but very small pictures in the database. You would be better off storing just the name of the picture and keeping it on disk in most cases. If you insist on storing them in the db, then you should create a separate table just for your blobs.

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • 1
    In addition, it might be worth specifying the type of the parameter as well to avoid any ambiguity: `cmd.Parameters.Add("@value21",SqlDbType.VarBinary).Value=ms.ToArray();` – Bridge Mar 28 '17 at 15:28
0

your @value21 is a string, please check that column on the database, if it is nvarbinary(max), change the data type to nvarchar(max) to save it, as @Bridge said in the comment of the previous answer, try to specify the datatype

Zinov
  • 3,817
  • 5
  • 36
  • 70
0
cmd.Parameters.AddWithValue("@value1" , entryno.Text == DBNull.Value ? System.Data.SqlTypes.SqlBinary.Null : entryno.Text);

Use This. I think Your problem solved.

Abdülkadir MAVİ
  • 11
  • 1