Hi Steve,

Have you considered using SQL Stored Procedures? If you have to alias the bad field names at least you'll only have to change the stored procedure if the table changes. To get the identity out of a SQL stored procedure you pass an output parameter by reference into the stored procedure. Then within the stored procedure assign the @@Identity or Scope_Identity() to that parameter variable.

Although the example uses the CursorAdapter you could probably use the information in Thread #952188 especially Message #952196 to help you get it to work.

Aloha,

James
>I am trying to copy a record in a SQL Server table that uses a Identity column. I would like to do the following:
>

>INSERT INTO Mytable
>SELECT * FROM MyTable WHERE Unqkey = 123456
>

>
>However, the Identity column doesn't allow this. I was trying to take the record into a Foxpro Cursor via SQLEXEC and then create an insert statement to send back to SQL Server. I was attempting to create a FieldList and ValueList for an insert by scanning thru the SQLResult fields.
>

>lcComma =""
>FOR lnI = 1 TO lnFields
>   lcFieldName = laFields(lnI,1)
>   lcFieldType = laFields(lnI,2)
>   IF UPPER(ALLTRIM(lcFieldName)) <> "UNQKEY"
>      lcFieldList = lcFieldList + lcComma + '[' + lcFieldName + ']' + CHR(13) + CHR(10)
>      lcValueList = lcValueList + lcComma + .r_oDataClass.m_Format_DBFField_For_SQL(lcFieldName,lcFieldType) + CHR(13) + CHR(10)
>      lcComma = ","
>   ENDIF
>ENDFOR
>

>I was attempting to create a SQL statement like
>

>Insert into Mytable
>(
>lcFieldList
>)
>Values
>(
>lcValueList
>)
>

>
>However, the SQL Table has field names that FOXPRO doesn't like. There are field names that begin with Numeric characters. When I am scanning thru the fields, everything works fine until I hit the fields that begin with numeric characters and everything after that is NULL.
>
>I was using a select * so I would not have to maintain this procedure for changes in the SQL Table. I could list out all the fields in a select and alias the bad field names, but then I would have to change this procedure any time the table changed.
>
>Anybody got any good ideas on how the get the record copied easily and getting a new Identity value too? Thanks