Question Working with Guids fields? (binary)

[mrdippy]

I am having trouble querying out a guid field which is binary in the database. Any tips or prior experiences to share?

I am trying to do a SELECT field1, field2, field3, guidField From table; sort of thing, and populate a datagridview control with the results. I want to:

1. Query the rows and present them in the datagridview, presenting that guid field as perhaps a hex string
2. Select a row in the datagridview control
3. Convert the text string (probably a hex num) back into binary so I can perform more queries and use field in a WHERE clause

The datagridview control doesn't know how to handle a binary field (lots of exceptions). I tried variations of "SELECT field1, field2, field3, CONVERT(binary, guidField, varchar) from table;" to no avail, but I think that is a step taking me a lil closer to the solution. Any tips / referrals / ideas appreciated.

 

[JohnH]

Use the CellFormatting event to translate the byte array to the string to be displayed. Example, ColumnIndex 2 here is the Guid column:

Private Sub Table1DataGridView_CellFormatting(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _
Handles Table1DataGridView.CellFormatting
    If e.ColumnIndex = 2 AndAlso e.Value IsNot Nothing AndAlso e.Value IsNot DBNull.Value Then
        e.Value = New Guid(DirectCast(e.Value, Byte())).ToString
    End If
End Sub

Attached is a sample project, mostly configured in Designer. Because a byte array field is assigned a Image column by default I had to change this to a TextboxColumn. As you can see from code the cell value is still the byte array, but is only presented as string. A Query is added in dataset designer to find rows with a certain Guid, resulting in FillByGuid/GetDataByGuid methods. I added two buttons for the sample, one to put a new Guid in current row (just to generate some data), and one that takes the Guid of current row and use GetDataByGuid method to make another db call and present one match in a Textbox. This should give you some pointers how to handle and presenting the Guid byte array with data binding.