CODEWITHSUNDEEP
c#sqlstringbytevarbinary
atapi19
atapi19

Reputation: 227

String value in byte[] c#

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?

Upvotes: 1

Views: 711

Answers (2)

MethodMan
MethodMan

Reputation: 18843

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
}

Upvotes: 0

Pikoh
Pikoh

Reputation: 7703

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

Upvotes: 2

Related Questions