String value in byte[] c#

Question

I have to do a query to insert a value in a SQL table. The type of the value is varbinary and I have a string to insert with an hex value in it. So I tried to use the SQL Convert Function like this:

using (SqlCommand dbCommand = new SqlCommand("INSERT INTO [Link] ([Record]) values (CONVERT(varbinary,@myrecord);", dbConn))

dbCommand.Parameters.Add("myrecord", System.Data.SqlDbType.VarBinary).Value = recordString;

but I have this Error: Failed to convert parameter value from a String to a Byte[].

So I tried to convert my string to byte[], but all the function I found also on this site convert my string to byte changing its value, so it's not good because my string has inside the value I need to insert in the table.

Recap: I have a string with this value: "0x54000000006C00000000099W043100300030003100" how can I add it in a SQL table with a varbinary field?

If you are using the CONVERT function in your sqlcommand, why are you trying to add a VarBinary parameter? In mean, doesn't this work?: dbCommand.Parameters.Add("myrecord", System.Data.SqlDbType.VarChar).Value = recordString; — Pikoh
– Pikoh, Commented Jan 12, 2016 at 16:24
when I tried this the value of my string is converted and not what I wanted. — atapi19
– atapi19, Commented Jan 12, 2016 at 16:36
Example is not a valid hex string, there is a "W" in the middle. — Kevin
– Kevin, Commented Jan 12, 2016 at 16:59

Pikoh · Accepted Answer · 2016-01-12 16:56:10Z

2

Ok, i think i understand now. If you are using sql server 2008+, you can do this:

using (SqlCommand dbCommand = new SqlCommand("INSERT INTO [Link] ([Record]) values (CONVERT(varbinary,@myrecord,1);", dbConn))

dbCommand.Parameters.Add("myrecord", System.Data.SqlDbType.VarChar).Value = recordString;

Notice the 1 in CONVERT(varbinary,@myrecord,1). Btw, the example number you gave us is not a valid Hex number...

More info: Msdn

answered Jan 12, 2016 at 16:56

Pikoh

7,72331 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

MethodMan · Accepted Answer · 2016-01-12 17:10:56Z

0

you can and should be able to do this make sure you adjust the VarBinary Size to fit your case

using(SqlCommand dbCommand = new SqlCommand("INSERT INTO [Link] ([Record]) values(@myrecord);", dbCon))
{
    // Replace 8000, below, with the correct size of the field
    dbCommand.Parameters.Add("@myrecord", System.Data.SqlDbType.VarChar, recordString.Length).Value = recordString;
    dbCommand.ExecuteNonQuery();//wrap around a try catch if you need to catch exceptions
}

edited Jan 12, 2016 at 17:10

answered Jan 12, 2016 at 16:28

MethodMan

18.9k6 gold badges41 silver badges54 bronze badges

3 Comments

atapi19 Over a year ago

I change 8000 with 448 (myvarbinary size) and I have the same error again: Failed to convert parameter value from a String to a Byte[]

MethodMan Over a year ago

@Heisenberg you may need to change your datatype to System.Data.SqlDbType.VarChar

MethodMan Over a year ago

@Heisenberg did that fix the issue

Collectives™ on Stack Overflow

String value in byte[] c#

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related