>>>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.
>>
>>The following are the results I see in the SQL Server Profiles for the two cases:
>>
>>Case 1. When value to the VARCHAR(MAX) column is not sent and therefore SQL Server inserts NULL. The same happens if send NULL value from the application:
>>
>>

>>declare @p9 float
>>set @p9=6803
>>exec sp_executesql N'INSERT INTO PURCHASEORDERS (PONUMBER,VENDORID,ORDERDATE,POAMOUNT,PROJECTACCOUNT,REC_ID) VALUES (@P1 ,@P2 ,@P3 ,@P4 ,@P5 ,@P6 ); 
>>DECLARE @id int; SELECT @id = SCOPE_IDENTITY(); EXEC Get_identity_Value @id, @P7 OUTPUT ',N'@P1 varchar(10),@P2 varchar(8),@P3 datetime,@P4 float,@P5 
>>varchar(16),@P6 varchar(1),@P7 float OUTPUT','333       ','ABBOTT  ',''2010-09-03 00:00:00:000'',333,'1200101420701849','0',@p9 output
>>select @p9
>>

>>
>>Case 2. When a string value ('my test) is set to the column of VARCHAR(MAX) type:
>>
>>

>>INSERT INTO PURCHASEORDERS (PONUMBER,VENDORID,ORDERDATE,POAMOUNT,PROJECTACCOUNT,PODESCRIPTION,REC_ID) VALUES ('888       ' ,'ABBOTT  ' ,'20100903 
>>00:00:00.000' ,888.0 ,'1200101121700607' ,'my test ' ,'0' ); DECLARE @id int; SELECT @id = SCOPE_IDENTITY(); EXEC Get_identity_Value @id,0.0 
>>

>>
>>Why does SQL Server process each so differently?
>
>Can you try using parameterized approach instead of embedding parameters into the string. In the first case it looks like SQL Server made assumptions about parameters types and parameterized the INSERT statement.
>In the second case, it, for some reason, was unable to parameterize and sent the statement as is.

Thank you. I will try to change the parameterized approach although I will have to think on how to do it using the CA and tableupdate().