hmm,

You have a row of data set up like this a,b,c,d these are the columns. The
values are a=3, b=1, c=5, d=4 and I need these put in descending order but
this is one row and I need it to come back looking like multiple rows. How
would you suggest I go about this?
Shawntry..
select a as Result from table
union all
select b from table
union all
select c from table
union all
select d from table
order by Result
might be a better way to do this, anyone?
"Shawn Mason" wrote:

> You have a row of data set up like this a,b,c,d these are the columns. Th
e
> values are a=3, b=1, c=5, d=4 and I need these put in descending order but
> this is one row and I need it to come back looking like multiple rows. Ho
w
> would you suggest I go about this?
> Shawn
>
>|||So this is the reverse of the ubiquitous post "How do I combine multiple
rows into one row?":
select a as x from mytable
union all
select b as x from mytable
union all
select c as x from mytable
union all
select d as x from mytable
order by x desc
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
> The values are a=3, b=1, c=5, d=4 and I need these put in descending order
> but this is one row and I need it to come back looking like multiple rows.
> How would you suggest I go about this?
> Shawn
>
>|||Try this:
select a as Result
union
select b
union
select c
union
select d
order by Result
ML|||Sorry, correction:
select a as Result
from <table_name>
union
select b
from <table_name>
union
select c
from <table_name>
union
select d
from <table_name>
order by Result desc
ML|||Have you looked up UNION ALL in BOL?
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
The
> values are a=3, b=1, c=5, d=4 and I need these put in descending order but
> this is one row and I need it to come back looking like multiple rows.
How
> would you suggest I go about this?
> Shawn
>
>|||Please post DDL, sample data, and expected results
(http://www.aspfaq.com/etiquette.asp?id=5006)
Ideally, if each column is the same type of "thing", you would normalize the
table design such that these values *are* in multiple rows.
Barring that, you can do some kind of kludge like this:
SELECT BadColumn FROM
(SELECT ColumnA AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnB AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnC AS BadColumn FROM BadTable
UNION ALL
SELECT ColumnD AS BadColumn FROM BadTable) ReallyBadTable
ORDER BY BadColumn DESC
"Shawn Mason" <shawn@.issda.com> wrote in message
news:OQTuhkTuFHA.3528@.TK2MSFTNGP15.phx.gbl...
> You have a row of data set up like this a,b,c,d these are the columns.
> The values are a=3, b=1, c=5, d=4 and I need these put in descending order
> but this is one row and I need it to come back looking like multiple rows.
> How would you suggest I go about this?
> Shawn
>
>

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