VB.NET how retrieve SQL varbinary(max) data to string (e.g. "0x352635lfdk1313")

Question

_{Thursday, April 27, 2017 2:43 AM}

Good Day Everyone

I want to retrieve the varbinary(max) data on my stored procedure, but it show the result as "System.Byte[]", i want to show is as like this "0x352635lfdk1313", can anyone help me on this? please don't give a sample that putting it in a textfile, if there is any other way than that kindly help me.

Thanks and regards

Aron

All replies (4)

_{Thursday, April 27, 2017 2:49 AM}

I want to retrieve the varbinary(max) data on my stored procedure, but it show the result as "System.Byte[]", i want to show is as like this "0x352635lfdk1313"

You will need to indicate how a "System.Byte[]" can be expressed as a string.  For instance, is the byte array an ASCII or Unicode character sequence, or is it formatted data that needs several different conversions (as you example string implies - a hex value followed by some char and then an integer)?

_{Thursday, April 27, 2017 10:13 AM}

Will repost

_{Thursday, April 27, 2017 10:29 AM}

Here is a solution

Stored procedure getting primary key and the varbinary(MAX)

Imports System.Data.SqlClient
Public Class Form1
    Private ConnectionString As String =
        <T>
            Data Source=KARENS-PC;
            Initial Catalog=ForumExamples;
            Integrated Security=True
        </T>.Value
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With
                {
                    .Connection = cn,
                    .CommandText = "uspGetEventAttachments",
                    .CommandType = CommandType.StoredProcedure
                }
                cn.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader
                reader.Read()
                Dim data = reader.GetString(1)
                Console.WriteLine("Place break point here to view variable data above")
            End Using
        End Using

    End Sub
End Class

In the stored procedure below I CONVERT function to get the format you showed.

USE [ForumExamples]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspGetEventAttachments] AS
BEGIN
SELECT [id]
      ,CONVERT(NVARCHAR(MAX), [FileContent], 1) AS FileContent
  FROM [ForumExamples].[dbo].[EventAttachments]
END
GO

And (the SP will need to be modified to exclude CONVERT from the above SP)

Imports System.Data.SqlClient
Imports System.Text

Public Class Form1
    Private ConnectionString As String =
        <T>
            Data Source=KARENS-PC;
            Initial Catalog=ForumExamples;
            Integrated Security=True
        </T>.Value
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With
                {
                    .Connection = cn,
                    .CommandText = "uspGetEventAttachments",
                    .CommandType = CommandType.StoredProcedure
                }
                cn.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader
                reader.Read()
                Dim data = reader.GetString(1)
                Console.WriteLine("Place break point here to view variable data above")
            End Using
        End Using

    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
            Using cmd As New SqlCommand With
                {
                    .Connection = cn,
                    .CommandText = "uspGetEventAttachments",
                    .CommandType = CommandType.StoredProcedure
                }
                cn.Open()
                Dim reader As SqlDataReader = cmd.ExecuteReader
                reader.Read()
                Dim data = reader.GetString(1)
                Dim itemBytes = CType(reader("FileContent"), Byte())
                Dim ItemHex = BitConverter.ToString(itemBytes)
                Dim ItemHex1 = ByteArrayToString(itemBytes)
                Console.WriteLine("Place break point here to view variable data above")
            End Using
        End Using
    End Sub
    Public Function ByteArrayToString(ByVal ba() As Byte) As String
        Dim hex As New StringBuilder(ba.Length * 2)
        For Each b As Byte In ba
            hex.AppendFormat("{0:x2}", b)
        Next
        Return hex.ToString()
    End Function
End Class

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Thursday, April 27, 2017 10:55 AM}

In the stored procedure below I convert to hex via the CONVERT function

"0x352635lfdk1313" is not hex.