I try to import image data into a sql server 2008 db with code like this:
INSERT INTO [TAB] (ID_PHOTO,PHOTO)VALUES(CAST('333EFB54-7062-E043-F088-FE0A916C0297' as uniqueidentifier),CONVERT(varbinary(max),'0xFFD8FFE000'))The string is just a dummy but when I make the insert I found something like this in the database
0x307846464438464645303030which isn't exactly what I expected. Does anybody know what I have done wrong?
Best Answer
The issue here is simply that a string -- '0xFFD8FFE000' -- is being converted to VARBINARY, and so each character -- first 0, then x, then F, and so on -- is "converted" to its hex representation:
Character -- Hex value0 30x 78F 46F 46D 448 38F 46F 46E 450 300 300 30So, the 0xFFD8FFE000 was seen as just a string of characters, just like "this is a test", instead of as a sequence of bytes.
Fortunately, the fix is quite simple: just add a "style" value of 1 for the optional 3rd parameter to CONVERT:
SELECT CONVERT(VARBINARY(MAX), '0xFFD8FFE000', 1);-- 0xFFD8FFE000What you are seeing is correct. You should be able to run the following to see that they conversion is happening correctly (basically, convert the varbinary value in your DB back to a VARCHAR):
SELECT CONVERT(varbinary(max),'0xFFD8FFE000')SELECT CONVERT(varchar(100), 0x307846464438464645303030)OR
SELECT CONVERT(varchar(100), CONVERT(varbinary(max),'0xFFD8FFE000'))Note the lack of single quotes around the varbinary value - not needed in SQL Server
I want to insert data into sql DB with a pic, for which I have set datatype in sql as varbinary, but following error
System.Data.SqlClient.SqlException: 'Implicit conversion from datatype varchar to varbinary(max) is not allowed.
Use the CONVERT function to run this query.'Consider my code:
private void InsertButton_Click(object sender, EventArgs e){string grno = GRNoTextBox.Text;string rollno = RollNoTextBox.Text;string name = NameTextBox.Text;string fname = FatherNameTextBox.Text;string cno = ContactNoTextBox.Text;string clas = ClassComboBox.SelectedItem.ToString();string sec = SectionComboBox.SelectedItem.ToString();string picadd = ofg.FileName;conn.Open();string query = "insert into stdinfo values ('"+grno+ "','" + rollno + "','" + name + "','" + fname + "','" + clas + "','" + sec + "','" + cno + "','" + picadd + "')";SqlCommand cmd = new SqlCommand(query, conn);cmd.ExecuteNonQuery();conn.Close();MessageBox.Show("Student info inserted into database successfully");}