>
>No, If you use SPT.
>SQL Server has no such function as FIEDLS() and no macros.
>Of course you could build a dynamic SQL based on INFORMATION_SCHEMA.COLUMNS:
>
>DECLARE @lcSQL varchar(200)
>SELECT @lcSQL = 'update mytable set ' + COLUMN_NAME + ' = 222 where MyField = ''SomeValue'''
> FROM INFORMATION_SCHEMA.COLUMNS
>WHERE Table_Name = 'mytable' AND Ordinal_Position = @nColumn -- Where nColumn is a column number
>EXEC (@lcSQL)
>
>
>not tested at all :-)
>
>So in VFP you could use something like this:
>
>mColumn = 1
>
>TEXT TO lcSQL NOSHOW TEXTMERGE
> DECLARE @lcSQL varchar(200)
> SELECT @lcSQL = 'update mytable set ' + COLUMN_NAME + ' = 222 where MyField = ''SomeValue'''
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE Table_Name = 'mytable' AND Ordinal_Position = <<nColumn>>
> EXEC (@lcSQL)
>ENDTEXT
>
>IF SQLEXEC(...., lcSQL) < 0
> AERROR(laError)
> MessageBox(laError[1,2])
>ENDIF
>
>
>But if you use CA or Remote view to handle data, there is no need of any of these. You could update VFP cursor and than just use TABLEUPDATE().
Thank you very much for your helpful input. I think I am going to be changing the structure not to rely on the column number. I think I made the initial design to rely on the column number (way back when) thinking that it will be faster to update and read from this type of table. Now, as I am moving away from VFP database (hopefully) to SQL Server, the speed will not be an issue.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham