CODEWITHSUNDEEP
c#sql-serverwinforms
awochacnaib
awochacnaib

Reputation: 13

what does implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed mean and how to fix it?

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!

Upvotes: 1

Views: 5944

Answers (3)

Abdülkadir MAVİ
Abdülkadir MAVİ

Reputation: 11

cmd.Parameters.AddWithValue("@value1" , entryno.Text == DBNull.Value ? System.Data.SqlTypes.SqlBinary.Null : entryno.Text);

Use This. I think Your problem solved.

Upvotes: 0

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93424

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.

Upvotes: 2

Zinov
Zinov

Reputation: 4119

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

Upvotes: 0

Related Questions