Reading hex from SQL into VB.Net
-
SQL Command: ------------------------------------------------- USE MyDB SELECT SUBSTRING(HEXTEXT, 1, 100) AS HEXTEXT FROM MyTable WHERE MyID=200 ------------------------------------------------- In SQL 2005, this returns: ------------------------------------------------- 0x680065006C006C006F002100 the Hex representation of 'hello!' ------------------------------------------------- I try to get it back into my VB.NET app using: ------------------------------------------------------------------------------------------------ ConnectionString = "Provider=" + Provider + "Driver=" + Driver + "Server=" + Server + "; Database=" + DB + "; UID=" + Username + "; PWD=" + Password + ";Trusted_Connection=yes;" Dim queryString As String = "SELECT SUBSTRING(HEXTEXT, 1, 100) AS HEXTEXT FROM MyTable WHERE MyID=200" Try Using connection As New OdbcConnection(ConnectionString) Dim command As New OdbcCommand(queryString, connection) connection.Open() Dim reader As OdbcDataReader = command.ExecuteReader() While reader.Read() MsgBox(reader(0).ToString) End While connection.Close() reader.Close() End Using Catch ex As Exception MsgBox("Something database related screwed up.") Exit Sub End Try ------------------------------------------------- When I run this i do not get any errors, i get 'System.Byte[]' in my message box? What gives? :doh:
You get that because you called the ToString method on an Array. This will NOT automatically convert the numbers in the array to characters or to a string of numbers. What's the output you're looking for? Provide an example of what you want it to look like.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
You get that because you called the ToString method on an Array. This will NOT automatically convert the numbers in the array to characters or to a string of numbers. What's the output you're looking for? Provide an example of what you want it to look like.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
I would be more than happy with my datareader returning the hex characters to me. If possible, converting it to the ascii would be even better, but hex would be a great place to start!
svanwass wrote:
I would be more than happy with my datareader returning the hex characters to me.
It looks like you stored a string in the database, that looks like "0x680065006C006C006F002100". So, you're looking to convert this string of characters into an array of numbers?? Like "0x6800" would become an integer 6800h, and so on down the line?
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
svanwass wrote:
I would be more than happy with my datareader returning the hex characters to me.
It looks like you stored a string in the database, that looks like "0x680065006C006C006F002100". So, you're looking to convert this string of characters into an array of numbers?? Like "0x6800" would become an integer 6800h, and so on down the line?
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
The information was stored as data type 'image'. 0x680065006C006C006F002100=hello! in ascii. Does that make more sense?
That would explain why it's being returned as an array of bytes. Ascii is a set of characters numbered 0 thru 128. An extended set goes to 255. What you have in this array is UniCode. Every other byte is 0x00, giving it away. What the Reader returned was equivilent to:
Dim bytes() As Byte = {&H68, &H00, &H65, &H00, &H6C, &H00, &H6C, &H00, &H6F, &H00, &H21, &H00}You can convert this array of bytes to a string using:
Imports System.Text
.
.
.
Dim str As String = Encoding.Unicode.GetString(bytes)A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
That would explain why it's being returned as an array of bytes. Ascii is a set of characters numbered 0 thru 128. An extended set goes to 255. What you have in this array is UniCode. Every other byte is 0x00, giving it away. What the Reader returned was equivilent to:
Dim bytes() As Byte = {&H68, &H00, &H65, &H00, &H6C, &H00, &H6C, &H00, &H6F, &H00, &H21, &H00}You can convert this array of bytes to a string using:
Imports System.Text
.
.
.
Dim str As String = Encoding.Unicode.GetString(bytes)A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007Rock out! I changed it up to Dim str As String = Encoding.Unicode.GetString(reader(0)) and it works! On a side note, I DID scour the MSDN articles, CodeProject, and Google but never found what i was looking for. This is more than likely because i am not sure what topic i would need. Knowing where i fell down, any suggestions on topics to read up on? THANKS A MILLION!:laugh:
-
Rock out! I changed it up to Dim str As String = Encoding.Unicode.GetString(reader(0)) and it works! On a side note, I DID scour the MSDN articles, CodeProject, and Google but never found what i was looking for. This is more than likely because i am not sure what topic i would need. Knowing where i fell down, any suggestions on topics to read up on? THANKS A MILLION!:laugh:
Well, that's the hardest part about learning the .NET Framework. It's massive collection of thousands of classes, containing tens-of-thousands of methods and properties. It involves LOTS of reading ;) and experimentation. About 6 years ago, when I got my hands on the Beta for .NET 1.0, I started by reading whatever documentation I could find in the MSDN Library, evaluating which namespaces were going to help me the fastest and built from there. Today, I probably suggest picking up a beginners book on VB.NET and start reading through the clas documentation in the following namespaces: System.Collections System.Data (stick to OleDb for now) System.IO (just the root, not the child namespaces under it!) System.Text System.Windows.Forms (just the root, not the child namespaces under it!) Then start adding other stuff like System.Xml and System.Runtime.Serialization.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
Well, that's the hardest part about learning the .NET Framework. It's massive collection of thousands of classes, containing tens-of-thousands of methods and properties. It involves LOTS of reading ;) and experimentation. About 6 years ago, when I got my hands on the Beta for .NET 1.0, I started by reading whatever documentation I could find in the MSDN Library, evaluating which namespaces were going to help me the fastest and built from there. Today, I probably suggest picking up a beginners book on VB.NET and start reading through the clas documentation in the following namespaces: System.Collections System.Data (stick to OleDb for now) System.IO (just the root, not the child namespaces under it!) System.Text System.Windows.Forms (just the root, not the child namespaces under it!) Then start adding other stuff like System.Xml and System.Runtime.Serialization.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007I seem to have run into a second problem that seems to be related. For my sample data, I have " hello! GOODBYE! hello! GOODBYE! BONJOUR! HALLO! NOWAY! " in the cell that i am pulling from with your code. When i run it, i only get this much: " hello! GOODBYE! hello! GOODBYE! BONJOUR! HALL " As you can see, there is some missing text (O!NOWAY!). Could this be related to Encoding.Unicode.GetString(reader(0))? I am storing the result in a string so I do not think that it is getting chopped on that end. The While reader.Read() does not execute a second time (my first thought was that there was a limit to how much it could take). If you have the time or the patience, your help would be greatly appreciated. -Steve
-
I seem to have run into a second problem that seems to be related. For my sample data, I have " hello! GOODBYE! hello! GOODBYE! BONJOUR! HALLO! NOWAY! " in the cell that i am pulling from with your code. When i run it, i only get this much: " hello! GOODBYE! hello! GOODBYE! BONJOUR! HALL " As you can see, there is some missing text (O!NOWAY!). Could this be related to Encoding.Unicode.GetString(reader(0))? I am storing the result in a string so I do not think that it is getting chopped on that end. The While reader.Read() does not execute a second time (my first thought was that there was a limit to how much it could take). If you have the time or the patience, your help would be greatly appreciated. -Steve
It's possible. Two 0x00 characters in a row would normally denote the end of a string. Why are you using a binary data type for storing string data? I really can't tell you what's wrong. There's a bunch of different places this can fail, such as what the code looks like that stored the data, if the data get stored properly, if the encoding is consistent throughout the data, ...
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
It's possible. Two 0x00 characters in a row would normally denote the end of a string. Why are you using a binary data type for storing string data? I really can't tell you what's wrong. There's a bunch of different places this can fail, such as what the code looks like that stored the data, if the data get stored properly, if the encoding is consistent throughout the data, ...
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007I found a fix for it. I changed from using System.Data.ODBC to System.Data.SQLClient. That does not limit me at all. Dim myConnection As SqlConnection Dim myCommand As SqlCommand myConnection = New SqlConnection("server=" + Server + ";uid=" + Username + ";pwd=" + Password + ";database=" + DB + ";Trusted_Connection=yes;") 'establishing connection. you need to provide password for sql server Try myConnection.Open() 'opening the connection myCommand = New SqlCommand("SELECT *FROM MYTABLE WHERE MYID='" + MYTHING.ToString + "'", myConnection) Dim dr As SqlDataReader = myCommand.ExecuteReader() While dr.Read() 'mystr = dr.GetString(0) 'reading from the datareader MsgBox(Encoding.Unicode.GetCharCount(dr(0))) mystr = Encoding.Unicode.GetString(dr(0)) MsgBox(mystr) 'displaying data from the table End While dr.Close() myConnection.Close() Catch End Try