04-19-2004, 08:19 AM
I want to make a view that enumerates data dynamicly.. basicly
Select Blah, Identity as gennumber from tblFoo
which would return..
Blah | Number
A | 1
B | 2
C | 3
D | 4
how can this be done?
04-19-2004, 08:41 AM
What view? MS SQL Server? I am not sure I am following you. Do you want to create something that is dynamic SQL? Such as, you want to pass the fields and table name to a DB object and have it run the query?
04-19-2004, 08:43 AM
Sorry, its a MSSql Server Database. but I want to have a number field like an identity on a view that is created when I do a select.
I don't want to pass anything in. I just want a number that says its record 1,2,3,4,5,6 or whatever that is NOT the identity on the table but the number of the record as it exist in the view.
04-19-2004, 08:44 AM
Well, you should be creating your View first. Basically in SQL Server (I am assuming that is what we are working with, if not, tell me) Do a CREATE VIEW statement that formats and brings in all the fields you want from the specified table. Then, you can query this View like another other table.
04-19-2004, 08:53 AM
If you need to work with this imaginary record number in your VB code, you can use instead the recordset's AbsolutePosition property as a sort of record number. Or, you can just use a counter variable in thecode. I do not know how to do it server side if that is what you want, by adding in an impromptu autonumber-like field. Maybe look in Books On-Line for some sort of Transact-SQL function?
04-19-2004, 08:55 AM
Hmm..well dealing with a View and the recordset object's AbsolutePosition property are two completely different things. I thought the question was in regards to creating a View and then enumerating it, looking for data.
04-19-2004, 09:00 AM
Doh, seems like I made a few assumptions on his use of the enumeration. I really should wait to see what HE wants to do!!!