View Single Post
  #6 (permalink)  
Old October 11th, 2003, 12:52 PM
Jeff Mason Jeff Mason is offline
Friend of Wrox
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post

quote:Originally posted by happygv
 Here is what I have done recently.

Query=SELECT MAX(ProductId)+1 AS NewProductId FROM Products

And have accessed it using


Which worked... and was able to use its value for inserting a new record into Products.
Yes, this would work, as the name of the column in the resultset (which gets turned into an ADO recordset by the OLEDB provider working with ADO) is in fact 'NewProductId'. That column only has that name, and you cannot refer to it by any other name, which is what I read the OP wanted to do.
Also used COALESC[sic] function in many occasions and accessed its values as Aliases.
Again, once you give a column an alias, no matter how the column was derived, be it a column from a table or an expression, you must refer to it by that name when you manipulate the resultset (recordset).
This is somthing that we could face manytimes going for aliases, in case there is a necessity to access a userdefined column but not the original column from the table itself.

E.g. - May be as I mentioned above one may need to get the incremneted value from the table or sometimes would try to calculate something from the table and try to get it back as aliases in the frontend.

Still eagar to hear his explanations, as I dont see any calculations done in his case.
Referring to a column in a resultset which is the result of evaluating an expression is a good reason for a column alias, but not the only one...

Jeff Mason
Custom Apps, Inc.
Reply With Quote