Thanks Cetin,
This helps a lot.
Zoran
>>>>Hi all,
>>>>
>>>>I have a SQL SERVER Table with PK column defined as Uniqueidentifier
>>>>
>>>>
>>>>[CustomerID] uniqueidentifier ROWGUIDCOL NOT NULL ,
>>>>
>>>>CONSTRAINT [DF_TR_TRAN_tr_id] DEFAULT (newid()) FOR [CustomerID],
>>>>
>>>>
>>>>(Name:CustomerId RowGuid = Yes, Default value = Newid())
>>>>
>>>>The problem starts when I try to update the above table using SQL Pass-through from Foxpro.
>>>>
>>>>I have set up the cursor as follows:
>>>>
>>>> CURSORSETPROP("FetchMemo", .T.)
>>>> CURSORSETPROP("Tables", "Customers")
>>>> CURSORSETPROP("UpdatableFieldList", "Col1, Col2... )
>>>> CURSORSETPROP("UpdateNameList", "Col1 Customers.Col1, Col2... )
>>>> CURSORSETPROP("KeyFieldList","CustomerId")
>>>> CURSORSETPROP("WhereType", 3)
>>>> CURSORSETPROP("SendUpdates", .T.)
>>>>>>>>
>>>>
>>>>If I exclude the "CustomerId" column from the
>>>> "UpdatableFieldList" and "UpdateNameList" properties of the cursor,
>>>> - INSERT works ok,
>>>> - UPDATE and DELETE are throwing the following error :
>>>> "No key columns specified for the update table "name".
>>>> Use the KeyFieldList property of the cursor"
>>>>
>>>>(Please note that KeyFieldList property of the cursor has been set corectly.)
>>>>
>>>>
>>>>
>>>>When I add the "CustomerId" column to the "UpdatableFieldList" and "UpdateNameList"
>>>>
>>>> - UPDATE and DELETE work fine,
>>>> - INSERT repors the error :"Invalid character value for CAST specification"
>>>>
>>>>
>>>>I guess I can't have it both ways.
>>>>
>>>>Any ideas? Any help highly appreciated!
>>>>
>>>>
>>>>Regards,
>>>>
>>>>Zoran
>>>
>>>Zoran,
>>>Second version is the right one IMHO. It says invalid character for cast specification. Did you use some non hex digit as your GUID for insert?
>>>Guids are like:
>>>5D63CA4C-30E7-482C-8D2F-8E34AF1DA4FB
>>>Cetin
>>
>>
>>Cetin,
>>
>>I was trying to get away with empty CustomerId value, hoping that SQL server will do its work assigning the default value (NewId)
>>
>>I guess I will have to do one more trip to SQL server getting the NewId() value and assigning it to the column from the client side.
>>
>>Thanks,
>>
>>
>>Zoran
>
>Oh Zoran NO,
>The beauty of a GUID is that you can create and send a key w/o a need to roundtrip to backend (besides it's unicity). IOW you know the key before even the backend knows. All NewID() does is to create a GUID and you can do that w/o help of SQL server. ie:
>
>
>
>replace myPKID with chrtran(getGUID(), '{}','')
>
>
>* insert into mySPTCursor (myPKID) values (chrtran(getGUID(), '{}',''))
>* or
>* declared as default for myPKID on VFP side - jsut as NewID() is default on MSSQL side
>
>Function getGUID
>Declare Integer CoCreateGuid In 'OLE32.dll' ;
> string @pguid
>Declare Integer StringFromGUID2 In 'OLE32.dll' ;
> string rguid, String @lpsz, Integer cchMax
>Declare Integer UuidCreateSequential In 'RPCRT4.dll' String @ Uuid
>
>Local pguid,rguid, lcOldError, lnResult
>pguid=Replicate(Chr(0),16)
>rguid=Replicate(Chr(0),80)
>lcOldError = On('error')
>On Error lnResult = CoCreateGuid(@pguid)
>lnResult = UuidCreateSequential(@pguid)
>On Error &lcOldError
>Return ;
> Iif( InList(m.lnResult,0,1824) And ;
> StringFromGUID2(pguid,@rguid,40) # 0, ;
> StrConv(Left(rguid,76),10), "" )
>endfunc
>Cetin
Thanks Cetin,
This helps a lot.
Zoran
