Tearing hair out trying to read binary

[RoddyPiper]

I hope someone here can help me.

We have a SQL server database that had ID fields that used to be defined as integer.

The new version of the database has the ID fields defined as BINARY(16).

The VB project I am ugrading reads these tables and spits out Crystal Reports. The problem I am having is this portion of code:


Do While (adoOleDbDataReader.Read)
DoEvents()
ReDim Preserve strRptPars(iCount)
For i = 0 To adoOleDbDataReader.FieldCount
Select Case i
Case 0

strRptPars(iCount).lID = adoOleDbDataReader.GetInt32(i)

Case 1
strRptPars(iCount).sOranizationName = adoOleDbDataReader.GetString(i).Trim
End Select
Next
cboReports.Items.Add(strRptPars(iCount).sOranizationName)
iCount = iCount + 1
Loop


Obviously strRptPars(iCount).lID = adoOleDbDataReader.GetInt32(i) will not work with a column that is defined as BINARY(16). I have tried adoOleDbDataReader.toString(i) - this returns the letter 'S' every time, tried adoOleDbDataReader.GETGUID(i) - no dice as the column in SQL server must be defined as GUID, and so on. Can anyone help? I have been stuck on this for several days. Redefining the table column as a GUID will not work as GUID will store the binary number, but insert dashes in the ID (stored in strRptPars(iCount).lID) which cause selects later in the project to fail.

I am not sure what else to try.

[jmcilhinney]

Binary data gets returned from a database as a Byte array, so you'd call GetBytes instead of GetInt32. You can then use the BitConverter.ToInt16 method to convert that array to a Short, which can be implicitly converted to an Integer if required.

Dim int As Integer = BitConverter.ToInt16(myDataReader.GetBytes(index), 0)

[RoddyPiper]

Post deleted - expanded on this below.

[RoddyPiper]

Thank you. This is what I have now:

Dim ByteBuf(15) As Byte

sqlString = "SELECT a.id, b.org_name FROM ActivityReportOrgParameters a, ca_organization b WHERE a.id = b.organization_uuid and a.ReportParam = 1 AND a.OrgParameterGroup <= " + iParamGroup.ToString + " ORDER BY b.org_name"

adoOleDbCommand = New OleDbCommand(sqlString, adoOleDbConnection)
adoOleDbConnection.Open()
adoOleDbDataReader = adoOleDbCommand.ExecuteReader()

Do While (adoOleDbDataReader.Read)

DoEvents()
ReDim Preserve strRptPars(iCount)

For i = 0 To adoOleDbDataReader.FieldCount

Select Case i
Case 0
strRptPars(iCount).lID = adoOleDbDataReader.GetBytes(1, i, ByteBuf, 0, 15)
Case 1
strRptPars(iCount).sOranizationName = adoOleDbDataReader.GetString(i).Trim

End Select

Next

cboReports.Items.Add(strRptPars(iCount).sOranizationName)
iCount = iCount + 1

Loop


The value retrieved in strRptPars(iCount).lID (defined as LONG) is used in a SQL select against another BINARY(16) field later in the code. I think I need to convert this to a string to do the select correctly - does this sound correct?

I hope I am on the right track here! I am a bit unsure of my arguments for getbytes. Project compiles correctly - will test against DB tomorrow.

[jmcilhinney]

First up, I think I owe you an apology. I didn't actually read the documentation for the GetBytes method and the code I provided was quite wrong. You look to be calling the method correctly but your code is a little bit off as that Select Case doesn't really do anything useful. You also shouldn't be using ReDim Preserve over and over like that. If you want to group an unknown number of items then you should use a collection, not an array.

Here's some code that's a bit more streamlined. I don't know what type your strRptPars array contains so I'll just use 'SomeType'. You can put the correct type in yourself.

Dim ByteBuf(15) As Byte
Dim strRptPars As New List(Of SomeType)
 
sqlString = "SELECT a.id, b.org_name " & _
            "FROM ActivityReportOrgParameters a, ca_organization b " & _
            "WHERE a.id = b.organization_uuid and a.ReportParam = 1 AND a.OrgParameterGroup <= @OrgParameterGroup " & _
            "ORDER BY b.org_name"
 
Using adoOleDbCommand As New OleDbCommand(sqlString, adoOleDbConnection)
    adoOleDbCommand.Parameters.AddWithValue("@OrgParameterGroup", iParamGroup)
 
    adoOleDbConnection.Open()
 
    Using adoOleDbDataReader As OleDbDataReader = adoOleDbCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Do While (adoOleDbDataReader.Read)
            adoOleDbDataReader.GetBytes(0, 0, ByteBuf, 0, ByteBuf.Length)
 
            strRptPars.Add(New SomeType With {.lID = BitConverter.ToInt16(ByteBuf, 0), _
                                              .sOrganizationName = adoOleDbDataReader.GetString(1).Trim()})
        Loop
    End Using
End Using

Now, once you've done that you can simply bind your List to your ComboBox to display the names to the user:

cboReports.DisplayMember = "sOrganizationName"
cboReports.ValueMember = "lID"
cboReports.DataSource = strRptPars

Just note that this requires that lID and sOrganizationName are properties, not fields. You'd need to change that if they are fields because you can only bind properties.

When the user makes a selection from the ComboBox you can get the corresponding ID from the SelectedValue property:

Dim id As Long = CLng(cboReports.SelectedValue)

You can then insert that value into a SQL statement using a parameter, much as I have above with OrgParameterGroup.

Two final points to note:

1. You've misspelled "sOrganization".
2. Why is "lID" defined as Long when it's values can be no more than two bytes in size? All possible values would fit in a Short.

[RoddyPiper]

Thanks!!!