Post reply

How do I reference data without an alias?

  • March 22, 2007 at 5:19 am

    #369922

    Hi,

    I'm scripting using VBScript with SQL 2000 - I'm using someone else's view which I cannot change and I'm trying to reference ddata in a query from more than one table. The columns I'm trying to reference have been defined without an alias. I've tried using the table.column as in oRS("ee.PersonID") but this raises an error. Is there a way of referencing this column programatically with having an alias defined in the view?

     

    Rae MacLeman

  • March 22, 2007 at 6:02 am

    #696334

    It would help to see the DDL for the table(s) and what you have currently tried to do to solve your problem so far.  There really isn't enough information in your post to be able to help you very much at the moment.

  • March 22, 2007 at 8:02 am

    #696369

    Firstly, views cannot have duplicate column names therefore if you have duplicate names then they are from your own query not the view, so use the alias in your query or better still do not include the additional columns in your query.

    When accessing a recordset, ie oRS("column"), which is btw shorthand for oRS.Fields("column").Value, then you are using the column name, there are no alias references in recordset column names.

    You can access columns in a recordset by ordinal position, ie oRS.Fields(0).Value for the first column, oRS.Fields(1).Value for the second etc.

    So if you do have duplicate column names then you can loop through each column in the recordset looking for the column you want

    oRS.Fields.Count gives you the number of columns

    oRS.Fields(0).Name will give you then name of column 1

    oRS.Fields(1).Name the name of column 2 etc

    Far away is close at hand in the images of elsewhere.
    Anon.

  • March 22, 2007 at 8:21 am

    #696379

    Spot on, David - Many thanks!

     

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply