| Author |
Topic |
|
davenims
Starting Member
21 Posts |
 Posted - 2007-01-19 : 07:36:45
|
| Hi, does anyone know if there is some T-SQL allowing me to select by field number rather than name?I need to select the first field in a result set, but the fields are not always the same.Cheers! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-19 : 07:39:03
|
| Why is that?Are you delaing with dynamic sql?Peter LarssonHelsingborg, Sweden |
 |
|
|
davenims
Starting Member
21 Posts |
Posted - 2007-01-19 : 08:29:23
|
| Aye, certainly am.I'm writing a function to take a resultset and display it formatted as a comma-delimited string. To do this I believe I need to use two of the most evil functions of T-SQL, i.e. a cursor and dynamic sql, unless anyone can advise me otherwise... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-19 : 08:34:46
|
| This can ADO do for you automatically!rs.GetString() function does this...Use comma as field separator and vbCrLf as row separator.Peter LarssonHelsingborg, Sweden |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-19 : 08:35:51
|
| Can you explain bit more of how you are forming comma-delimited string from the resultset? May be you can post the code you have. I suspect there are more clever ways to do this rather than cursor and D-Sql.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
davenims
Starting Member
21 Posts |
Posted - 2007-01-19 : 08:48:25
|
| Peso - I think I need to stick with SQL in this instance (see below), however I didn't know about that rs.GetString() function, so that will no doubt come in handy, cheers!harsh_athalye - I haven't any code at the moment. What I'm trying to do is create a listbox (in an Access ADP), which lists orders for a company with the value and number of items, with a column showing a preview of what is on the order (perhaps the first 3 items or whatever will fit on within a certain amount of characters).The reason for this is so that the user can see at a glance what is on each order and locate the right one, without having to go through each order. They can then double-click the item in the listbox to open up the full detail of the order.I figured the best way to do this would be to create a User Defined Function, which receives the order no, and returns a string of what items are on the order. I can then use this UDF as one of the columns in the listbox.Of course if anyone has a better way to do this I'm open to suggestions! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-19 : 08:53:04
|
| SELECT TOP 3 ItemName, OrderedAmount, CustomerFROM x???Peter LarssonHelsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-19 : 08:56:04
|
| How come you don't know the order of the table columns?Peter LarssonHelsingborg, Sweden |
|
|
|
davenims
Starting Member
21 Posts |
Posted - 2007-01-19 : 09:02:45
|
| OK cheers, I guess that achieves what I want from this example, what I really wanted though was to be able to reuse this UDF for other listboxes where I want a similar feature of listing sub-items belonging to a specified item. I guess I was also just curious to see whethre there is some sort of T-SQL syntax that can be used this way, for example with an ADODB recordset I can say:rs!FieldNameor rs("FieldName")or I can also say rs(0) or rs.Fields(0) |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-19 : 10:05:20
|
| The reason SQL doesn't support this is because the order of the columns in the table is immaterial and so is their position ordinals.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
|
SELECT by field number