Hmm - unique id is in Binary data - how do I query against it?

Hi - I've got a table in SQL server that has its unique ID field as binary data. I have a gridview displaying data from this table and I have set the datakey of this gridview as that binary data field.

I have a 'Select' ciolumn in my gridview that, when selected, will display more details from the selected record. The problem is, how do I pass the selected id back to sql server bearing in mind that it's binary data? Here's what I'm doing at the moment:

I have a function in my data accsess class that queries the database:

Public Function getDetailsById(ByVal Id As System.Byte) As DataSet

Dim con As New SqlConnection(conStr)
Dim cmd As New SqlCommand("SELECT * FROM tableName WHERE Id=@.Id", con)

cmd.Parameters.Add("@.Id", SqlDbType.Binary)
cmd.Parameters("@.Id").Value = Id

Dim ds As New DataSet
Dim adp As New SqlDataAdapter(cmd)
adp.Fill(ds)

Return ds

End Function

And then I call this function from my page with the gridview using:

Dim da As New myDataAccess
Dim ds As New DataSet
ds = da.getDetailsById(GridView1.SelectedDataKey.Value)

But I get the error:

Conversion from type 'Byte()' to type 'Byte' is not valid.

Any ideas where my data/code is going wrong? I'm not sure how to pass this sort of data around?

Thanks

loydall:

Hi - I've got a table in SQL server that has its unique ID field as binary data.

Not trying to be funny... but making your primary key a binary is where you are going wrong.

I know I've queried off of binary fields before, but I don't have any of that code infront of me now.

|||

Thanks but it's not my db so I have no control over how the ID is stored...

I've fixed it anyway - I just need to use system.byte() rather than system.byte as a parameter in my function - it obviously needed to treat the data as an array...

|||

Make sure you're getting the correct records back... I seem to remember that SQL will store a binary as 0x00000047 where .Net will see the same value as 0x47000000.

|||

You are using the wrong ADO.NET command object, you should use ExecuteScalar. Try the link below for complete sample code from Jeff Prosise Programming .NET. The code is in C# but it shows you the correct way to use ExecuteScalar. Hope this helps.

http://kurinjikumaravel.tripod.com/Asp.Net/AspWintellect.txt