>>>>Hi,
>>>>
>>>>I have come across the following problem, which duplicated in multiple tests.
>>>>
>>>>First a little background for those who have not seen my other thread. In order to update Identity column in the cursor created with a CursorAdapter I use the following method. I have the following code in the BeforeInsert method of the CA:
>>>>
>>>>
>>>> cInsertCmd = cInsertCmd + ;
>>>> "; DECLARE @id int; SELECT @id = SCOPE_IDENTITY()" + ;
>>>> "; EXEC Get_identity_Value @id, ?@" + ;
>>>> this.Alias + "." + ALLTRIM(this.IDENTITY_FIELD)
>>>>
>>>>
>>>>The above code is suppose to set value of the identity column to the value of scope_identity(). And it work UNLESS one of the columns where entry is made is of type VARCHAR(MAX) NULL. That is, if I leave the editbox control empty and let SQL Server set NULL as the value in the VARCHAR(MAX) column, the above code sets the value to the identity column in the cursor to the identity value from the SQL Server. But if I make any entry into the editbox control, the value in the identity column of the cursor is 0 (zero).
>>>>
>>>>What could be happening here? TIA.
>>>
>>>post LIST STRUCTURE of the vfp cursor
>>
>>Here is the structure:
>>
>>
>> Memo file block size: 64
>> Code Page: 1252
>> Field Field Name Type Width Dec Index Collate Nulls Next Step
>> 1 PONUMBER Character 10 No
>> 2 VENDORID Character 8 No
>> 3 PROJECTMANAG.. Character 10 Yes
>> 4 ORDERDATE Date 8 No
>> 5 REQUESTEDDEL.. Date 8 Yes
>> 6 POAMOUNT Numeric 18 2 No
>> 7 PROJECTACCOU.. Character 16 No
>> 8 POCHANGE Character 2 Yes
>> 9 PAIDAMOUNT Numeric 18 2 No
>> 10 POREFERENCE Character 10 Yes
>> 11 AUTOINVOICE Integer 4 Yes
>> 12 PODESCRIPTIO.. Memo 4 Yes
>> 13 POPK Integer 4 No
>> 14 REC_ID Character 1 Yes
>>
>>
>>I just posted the structure and see that some of the field names are longer than 10. Could this have caused the issue?
>
>No.
>
>Uses SQL Server 2000 ODBC Driver ?
>Are you sure that this old drive supports VARCHAR(MAX) on every operation ?
>I'm not.
First, I am so glad you are saying that I don't have to change the structure of the SQL Server tables (to reduce the field names to only 10 char). And yes, I use SQL server 2000 ODBC driver. And so far I see no problem with VARCHAR(MAX) field except for the one I described in this message. So what I did is changed the approach and instead use properties InsertCmdRefresh... as following:
this.ca_object.InsertCmdRefreshFieldList = ALLTRIM(tcIdentityField)
this.ca_object.InsertCmdRefreshCmd = "SELECT @@IDENTITY"
And this works very well. And since I don't use triggers and only update one table using @@IDENTITY should be just as good as SCOPE_IDENTITY().
Thank you.
"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
