0

I have a table in SQL Server that has a number of columns. One of these columns is a varbinary(4000).

Essentially I need to take several rows in the table and use the values in the columns. This includes the varbinary(4000) value. However I am having trouble passing the varbinary(4000) value into a VB parameter.

I need to be able to pass it into a parameter (the parameter types doesn't matter as long as I can compare it to other varbinary(4000) values (also taken from the same column in the DB)) and then send an update back to SQL Server a different table.

The varbinary(4000) values is being used as our primary key so obviously the value has to be the same when SQL Server receives it again.

I have tried a few different things. I believe that the varbinary(4000) value is a hexadecimal number.

TryCast(ds.Tables(0)("columnName"), System.Data.Linq.Binary).ToArray()
Encoding.UTF8.GetString(ds.Tables(0)("columnName"))
Encoding.Unicode.GetString(ds.Tables(0)("columnName"));
Encoding.ASCII.GetString(ds.Tables(0)("columnName"));

Edit

The possible duplication is not a solution because he simply needs to read the value from SQL. I need to compare it to other varbinary(4000) in the table in order to use the varbinary(4000) as a value in a sql insert command. Thus I need the varbinary(4000) value to be the same as when I received it (if it needs to be converted in between that's fine).

Regis
  • 166
  • 4
  • 17
  • 1
    I would think that `varbinary` is... binary? Perhaps `Dim data() As Byte = ds.Tables(0).Field(Of Byte())("columnName")`. Not sure that's a great choice for a primary key. – Mark Jul 31 '15 at 15:56
  • As Mark said, `Byte()` is the appropriate type here. [Here](https://stackoverflow.com/questions/425389/c-sharp-equivalent-of-sql-server-datatypes) is a nice overview of all types and their .NET equivalents. – Saragis Jul 31 '15 at 16:22
  • possible duplicate of [How to retrieve VARBINARY values from SQL Server 2008 using VB.Net](http://stackoverflow.com/questions/17613214/how-to-retrieve-varbinary-values-from-sql-server-2008-using-vb-net) – Behzad Jul 31 '15 at 17:25
  • `varbinary(4000)` as a primary key? Why? It will fail if you insert values over 900 bytes anyway. – Martin Smith Jul 31 '15 at 18:27
  • Not my decision (and like you not my preference). This is the way they set up the database before I began working with it. I would obviously rather use an Integer because of simplicity but I am stuck to work with what they gave me. – Regis Jul 31 '15 at 18:43

1 Answers1

0

I have come across a couple of things. A Hashtable will hold the varbinary value. It can then be used as needed.

 Dim table As New Hashtable
 Dim ds As DataSet
 Dim count As Integer = 0

 'ds now holds the table with the varbinary column'
 ds = getSqlTable()

 For Each row In ds.Tables(0).Rows
   table(count) = row("varBinaryColumn")
   count += 1
 Next

Uploading it back to SQL can be done by.

 For Each item In table
    Dim sqlCmd As New SqlCommand
    sqlCmd.Parameters.Add("@binaryValue", SqlDbType.VarBinary, 8000).Value = item.Value
 Next
Regis
  • 166
  • 4
  • 17