how to convert string datatype to SQL's varbinary equivalent datatype?

Question

Sorry for asking stupid question as I am newbie to C#.

Currently doing a task in c# using OLEDB. The scenario is, I want to insert in one table which has information like username, pasword, etc. The Database is SQL Server. The column Pasword has a datatype varbinary(100).

I want to convet from string datatype to SQL's varbinary equivalent datatype. I tried a little bit, but didn't succeed.

Initially I just tried with reader.GetString(5). But exception I got is :

"Specified cast is not valid."

* I forgot to mentioned below things *

Reader I used is OleDbDataReader.

oleParameter[7] = new OleDbParameter("@Password", OleDbType.VarBinary, 100);
oleParameter[7].Value = reader1.GetBytes(5);

(Here I am reading from one table and inserting into another table.)

Please help me.

Thanks.

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

You cannot read binary data as a string, use the SqlDataReader.GetBytes method.

Take a look at this...

edited May 23 '17 at 10:32

Community

1
1

answered Nov 01 '12 at 12:50

khellang

17,550
6
64
84

score 0 · Answer 2 · answered Nov 01 '12 at 12:54

You need to send/receive bytes between your application and the varbinary column. Once you get the byte array back, you can convert it to a string:

var bytes = reader.GetBytes(5);
var myString = Encoding.UTF8.GetString(bytes); // use the same encoding used
                                               // to create the bytes
                                               // when saving to the DB.

To convert string to byte array:

var bytes = Encoding.UTF8.GetBytes(myString);

score 0 · Accepted Answer · answered Nov 05 '12 at 05:20

I got the solution.

Instead of writing the below code line,

oleParameter[7] = new OleDbParameter("@Password",  OleDbType.VarBinary, 100);
oleParameter[7].Value = reader1.GetBytes(5);

I just tried like below code:

  byte[] array = (byte[])reader1.GetValue(9);
  oleParameter[7] = new OleDbParameter("@Password",array);

I didn't specify the datatype for this field and used the contructor OleDbParameter(string name, object value).

And it works fine.

Thanks Khellang and Eren Ersönmez for their responses.

score 0 · Answer 4 · answered Jul 22 '13 at 17:16

I just wanted to add a bit of explanation as to why the reader.GetBytes(5) didn't work. I've ran into that problem myself before.

In order for this to function properly, that particularly field cannot encounter any null values within it.

You can test the value utilizing the a IsDBNull(reader(5)) statement before attempting to perform the .GetBytes(5) action.

Just FYI!

how to convert string datatype to SQL's varbinary equivalent datatype?

4 Answers4