Updating a table/field using the ordinal number

This works with the Northwind database
Table name is Orders
and notice the where clause in the function
where orderid=11077

Jay

declare  @colname varchar(55)
declare  @ordpos int

set @ordpos=6

select @colname=column_name from INFORMATION_SCHEMA.Columns
where table_name='orders'
and ordinal_position=@ordpos

exec ('update orders
set '+@colname+'=getdate() where orderid=11077')

Why would you like to do something like this?
Just out of curiousity...

LOL .. yeah i was kind of curious too ...

Jay

I am building a Content editor that will read the field to be edited dynamically. It is easier and cleaner to simply send over the Ordinal. I could send over the field name but that can get ugly.
If I can simply update to a field by it's ordianl number it would simplify the Update code

also since you are using VB you could simply select the record using an updatable
cursor, and then set the value by ordinal number.

Jay

>>It is easier and cleaner to simply send over the Ordinal.
Maybe it looks like it's a good idea, but it isn't. You have to take care of the data type, and using varchar or whatever else as common type to update ANY database type will be MUCH MORE UGLY than passing a column name (which will ALYWAYS be varchar).
As jltoops indicates, this will probably be much easier using VB:

dim rs as ADODB.Recordset
set rs = new ADODB.Recordset
rs.open (yourconnection, "SELECT * FROM YOURTABLE WHERE ... ", <options for a keyset cursor>)
rs.fields(yourordinalnumber).Value = yourvalue
rs.update
set rs = nothing

+ the errorhandling bla bla rhabarber rhabarber ...
+ check out the fields collection base number, i never remember if it's 0 or 1  :-)

Cheers

updatable cursor???
Here is the structure of what is happening:
A catalog page is generted in admin mode with a dynamic link that contains the info of the field being displayed.
The edit page it lead to reads the querystring and queries the field from the table.
The user edits the data in a text editor (FreeTextBox) and hits saved.
At this point the page goes to update this field using the ordinal id I had stored on the page.
Since there was only one record ever called I do not understand where a cursor comes in (my possible ignorance)

Even a recordset with 1 record becomes a cursor.

The problem with using a pure dynamic SQL as suggested above (i am sure jltoops agrees) are the negative side-effects:
* less performance
* security issues
Regarding the security issue, take this sample (pseudo-code, won't work exaclty like this):

exec ('update YOURTABLE set '+@colname+'=' + @colvalue +'  where PK=' + @PK_value   )

What will happen if the user will submit the following value for the column
"'anyvalue'  DROP DATABASE select * from YOURTABLE "

The resulting sql will be this:
update YOURTABLE set yourcolumn="anyvalue"  DROP DATABASE SELECT * FROM YOURTABLE  where PK= 7
Funny, you allowed your user to drop the database :-)
Actually, your generic dynamic SQL will error out because if the last SELECT, because meanwhile the database has been dropped...
Don't think this is only imagination, too many shopping web sites have been hacked/destroyed this way !!!!

Using the sample code I posted, such things cannot occur, as ADO will care about all this.

CHeers

Ok, see your point but do I want to use ADODB.
 I have been using primarily SQLClient and intellisense is not liking the Adodb stuff at all.
( I am using vb.net) so I probally have to inherit or import something?

I will follow your advice for now and drop my dream of using the Ordinal but I will let you know if I magically come up with a way to use it.
Thanx