Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-11 : 10:05:50
|
Tim writes "I'm trying to figure out a way of getting SQL Server 7 to return the field names as the first row of it's results in a select statement, and I'm not having much luck. As an example, it needs to output something like this:id name----- ---------id name1 bill2 ben3 flowerpot4 men now this has me totally stumped - any of you gurus have any thoughts on how (and if?) this can be accomplished?Many ThanksTim" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 10:27:36
|
| Why do you need the column names in the query itself? |
 |
|
|
bblgoose
Starting Member
4 Posts |
Posted - 2002-06-11 : 10:51:49
|
| I have a site which is currently under development, with a load of tables, which I'm using ASP Getrows to pull into my page. Since the database is still undergoing major tweaking, the structure changes day to day. Also, a lot of the data types are Bit or Int, so the actual data doesn't give me any help when I dump the arrays onto my debugging page. I'm thinking that if I can get it to pull the fields into the first record, it performs two funtions: firstly, it increments the actual record numbers by one, so the data array is 1..n rather than 0..n. This makes pulling records out that little bit more readable. Second, when I want the field names for debugging, updating etc, I can just use ARRAY(Colref,0) to get the name in there. It's a function I could do other ways, but if this can be done this way it's a general purpose handy trick to keep in my bag :o) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-11 : 11:15:49
|
| The problem is that you'd have to CONVERT() every column to char or varchar datatypes if you're gonna include the column names in the actual query results. That'll be a huge performance killer, basically it would negate the benefit you'd get from using GetRows.You *might* be able to play with ReDim...Preserve to resize the GetRows array and add the column names, but it only adds elements at the end of the array. You'd have to shuffle the elements around to get column names into the zero row.If you want to learn a little JavaScript/JScript, there are some more array handling functions that might make the last bit easier to do. You can mix VB and JS in the same ASP page by using the @language setting for each block of code. Arrays and variables are available and interoperable to both languages within the same page. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-06-11 : 12:38:17
|
| maybe i'm missing the point here.....but why not just get the field names from the properties of the recordset returned...and store that somewhere in your app....using rsName.Fields.Item(indexnumber).Namewhere rsName is defined as an ADODB.RECORDSETand indexnumber is a number starting at 0....and increasing for each field in the recordset.... |
|
|
|
bblgoose
Starting Member
4 Posts |
Posted - 2002-06-12 : 03:48:11
|
| That's close enough for me - I've done it using two arrays now - arrData for the data and fldData for the names. Thanks for the pointer Andrew - wasn't aware of the Fields.Item - I got dumped with this project about a month ago with zero asp or sql experience, so I'm kinda picking it up as I go! Gonna hafta get the 'em to buy me some decent books I think :o)Thanks for the help guysTim |
|
|
|
|
|
|
|
|
Selecting field names alongside results?