How to convert Byte Array to String value?

Question

I have a logTable in my database for log changes on someTable.

logTable has three columns:

first for store column name which value was changed;
second for old value;
third for new value.

Second and third column has sql_variant data type.

In my program I get data from this table and oldValue and newValue are an arrays of bytes.

For example:

rows in logTable:


columnName(nvarchar(50)) oldValue(sql_variant)  newValue(sql_variant)

compName    name1       name2
address  "address1"  "address2"
zip      123         134
phone      123456789   987654321

But I get oldValue and newValue values in byte arrays.

Like:


address "byte array"   "byte array"
zip     "byte array"   "byte array"
phone   "byte array"   "byte array"

instead:


address  "address1" "address2"
zip     123         134
phone    123456789   987654321

This code I use to get data from DB:


Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open connStr
Set rsData = Server.CreateObject("ADODB.Recordset")
Set objComm = Server.CreateObject("ADODB.Command")
Set objComm.ActiveConnection= Conn
objComm.CommandText="usp_Log_GetOperationDetails"
objComm.CommandType = adCmdStoredProc 
objComm.Parameters.Append objComm.CreateParameter("@CompanyId",adInteger,adParamInput)
objComm.Parameters("@CompanyId")=Request("CompanyId")
objComm.Parameters.Append objComm.CreateParameter("@tableName",adVarChar,adParamInput, 50)
objComm.Parameters("@tableName")=Request("tableName")
rsData.CursorLocation = adUseClient
rsData.CursorType = adOpenStatic
Set rsData.Source = objComm
rsData.Open

If NOT rsData.EOF AND NOT rsData.BOF then
   Do While NOT rsData.EOF
    rsDataOldValue = rsData("OldValue")
    rsDatanewValue = rsData("NewValue")
        rsData.MoveNext
   Loop
End If

rsData.Close
Set rsData = Nothing
Conn.Close
Set Conn = Nothing
Set objComm = Nothing

How can I convert byte array to correct string value?

Thanks

can you show the code that you use to get newValue and oldValue? I don't really understand what you expect to get and what you're actually getting — user69820
– user69820, Commented Mar 31, 2011 at 12:45
Can you show the result of VarType(rsDataOldValue). If it is an Array it should be > 8192 — renick
– renick, Commented Mar 31, 2011 at 15:22
In good Answer below str = str & asc(theArray(i)) may be str = str & Chr(theArray(i)) — Alexander Alexander2
– Alexander Alexander2, Commented Oct 26 at 13:04

renick · Accepted Answer · 2011-03-31 15:55:49Z

3

Have you tried casting in your SQL query ?

SELECT CAST(oldvalue AS varchar(100)) from logTable

Edit: if the cast wont do good then you need to write a function like this one which converts the bytes to a string one at a time (note : I have not tested this as I am not currently on Windows)

function ByteArrayToString(theArray)
    dim i, str

    if vartype(theArray) < 8192 then
           exit function
    end if
    for i=lbound(theArray) to ubound(theArray) 
       str = str & asc(theArray(i))
    next
    ByteArrayToString = str
end function

To test it try

MsgBox(ByteArrayToString(rsDataOldvalue))

edited Mar 31, 2011 at 15:55

answered Mar 31, 2011 at 12:44

renick

3,9112 gold badges34 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vyacheslav Over a year ago

cast rounds big values which have float type. For example: 20000.75 . It returns 20000.8 . I need 20000.75 . I use CAST(oldvalue AS nvarchar(max)) but its not helped.

Collectives™ on Stack Overflow

How to convert Byte Array to String value?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related