Another method that I personally like to use is to create a remote view that returns the primary key field and has the candidate key in the where clause.

On the following SQL Server table:

CREATE TABLE [dbo].[customer] (
	[customerid] [int] IDENTITY (1, 1) NOT NULL ,
	[patientid] [int] NOT NULL ,
	[firstname] [varchar] (20) NULL ,
	[middleinit] [char] (1) NULL ,
	[lastname] [varchar] (25) NULL ,
	[salutation] [varchar] (6) NULL ,
	[address1] [varchar] (30) NULL ,
	[address2] [varchar] (30) NULL ,
	[city] [varchar] (25) NULL ,
	[state] [char] (10) NULL ,
	[zip] [varchar] (10) NULL ,
	[phone] [varchar] (10) NULL ,
	[fax] [varchar] (10) NULL 
)
GO

ALTER TABLE [dbo].[customer] WITH NOCHECK ADD 
	CONSTRAINT [PK_customer] PRIMARY KEY  CLUSTERED 
	(
		[customerid]
	)  ON [PRIMARY] ,
	CONSTRAINT [IX_customer_lastname_firstname_address1_zip] UNIQUE  NONCLUSTERED 
	(
		[lastname],
		[firstname],
		[address1],
		[zip]
	)

The "IX_customer_lastname_firstname_address1_zip" index is the candidate key for the table. So in VFP I would create a this remote view:

CREATE SQL VIEW new_customer REMOTE CONNECTION sqlconn AS ;
	SELECT customer.customerid ;
	FROM dbo.customer customer ;
	wHERE customer.lastname = ?cLastname ;
		AND customer.firstname = ?cFirstname ;
		AND customer.address1 = ?cAddress1 ;
		AND customer.zip = ?cZip

DBSETPROP('new_customer','VIEW','ShareConnection',.T.)

Now after I insert a new record into Customer through a remote view, I can set cLastname, cFirstname, cAddress1 and cZip to the same values just inserted into the Customer table and REQUERY('new_customer') for the PK.

This may seem like a lot of work but you can use it without code changes on any back-end database (including VFP).

- Keith

>Great! thank you - I'm reading the article now.
>
>>>Perhaps this is not the best place to post a SQL Server question, but here goes anyway...
>>>
>>>In VFP, I commonly insert parent and child rows into tables by first generating a primary key value for the parent table and then using that as the foreign key value for the child rows. This way, of course, the child rows are properly related to the parent.
>>
>>An article at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfoxtk00/html/ft00l1.asp shows how you can do the same on Sql Server.
>>
>>>
>>>How is this done in SQL Server? That is, how do I generate the "Next ID" for the parent so that I can use that value when inserting the child records? I suppose the T-SQL @@Identity could be used, but isn't that scoped to the connection and therefore not guaranteed to be the unique when I actually insert the parent record?
>>
>>It is unique but it's not guaranteed to be sequential. Also it always holds the last generated Identity value for a connection. It means, that if you do insert in the table A with identity column and it has insert trigger that inserts record in the table B that also has Identity column than the value of Identity column of table B will be stored into @@Identity variable. The other difference is that you have to insert record into parent table to "generate" new id. After tha you can retrieve @@Identity value and use it for chaild records.
>>
>>In SQL 2000 there are two new functions SCOPE_IDENTITY, and IDENT_CURRENT that return the last value inserted into the IDENTITY column of a table.
>>
>>See Sql Server Books Online for details.