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

    Question

  • 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

    Thursday, April 27, 2017 2:43 AM

All replies

  • 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 2:49 AM
  • Will repost


    Thursday, April 27, 2017 10:13 AM
    Moderator
  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites




    Thursday, April 27, 2017 10:29 AM
    Moderator
  • In the stored procedure below I convert to hex via the CONVERT function

    "0x352635lfdk1313" is not hex.

    Thursday, April 27, 2017 10:55 AM