I am using adapter for the first time and am struggling to get inserts on tables with a GUID PK to work:

When I call:

            using (Adapter)
            {
                if (contract.IsNew)
                    contract.ContractId = Guid.NewGuid();
                Adapter.SaveEntity(contract, true);
            } 

In researching other posts it was brought up that one should assign the guid before inserting because the identity could not be returned after insertion.

I get this message in the exception: "An exception was caught during the execution of an action query: Operand type clash: numeric is incompatible with uniqueidentifier. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

the executed query looks like this:

    Query: INSERT INTO [wsi].[dbo].[Contract_Contract] ([Description], [ContractTypeId], [VendorId], [BranchId], [AutoRenewing], [NonRenewalNotification], [Amount], [AmountPeriodId], [Buyout], [Comments], [IsActive]) VALUES (@Description, @ContractTypeId, @VendorId, @BranchId, @AutoRenewing, @NonRenewalNotification, @Amount, @AmountPeriodId, @Buyout, @Comments, @IsActive);SELECT @ContractId=SCOPE_IDENTITY()
    Parameter: @ContractId : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Output. Value: <undefined value>.
    Parameter: @Description : String. Length: 255. Precision: 0. Scale: 0. Direction: Input. Value: test.
    Parameter: @ContractTypeId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
    Parameter: @VendorId : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0.
    Parameter: @BranchId : AnsiStringFixedLength. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: .
    Parameter: @AutoRenewing : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: False.
    Parameter: @NonRenewalNotification : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: False.
    Parameter: @Amount : Currency. Length: 0. Precision: 19. Scale: 4. Direction: Input. Value: 0.
    Parameter: @AmountPeriodId : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: .
    Parameter: @Buyout : Currency. Length: 0. Precision: 19. Scale: 4. Direction: Input. Value: 0.
    Parameter: @Comments : AnsiString. Length: 2147483647. Precision: 0. Scale: 0. Direction: Input. Value: .
    Parameter: @IsActive : Boolean. Length: 0. Precision: 1. Scale: 0. Direction: Input. Value: False.

The Guid column should not be an identity. When it tries to run the query and set the scope identity to a value from the guid column that causes the error. See if you can reset that column to not be an identity.

Not be an identity? Not really sure what that means. Identity specification for the column is set to No (cannot be set to yes). RowGuid is set to yes but I have tried setting it to no and received the same error. I have the same setup in some SelfServicing scenarios and so long as you set the Guid value before the insert it works. I am stuck, please help!

PS: when setting the RowGuid to yes it sets the Default Value or Binding to (newid()). Not sure if this helps.

Set RowGuid to false, that's not used. RowGUID is a feature to set a row to a new GUID automatically but that's not supported as it's impossible to read back the generated GUID so the PK field isn't populated with the new value.

So the PK field should be of type uniqueidentifier and the default should be empty and you shold the value for the PK field in .NET code: myEntity.PkField = Guid.NewGuid();

I made the suggested change but get the same result.

Sam wrote:

I made the suggested change but get the same result.

After refreshing the catalog and re-generating code?

Yep. I have refreshed catalog, deleted generated code and regened. On thing that I have noticed is that in LLBLGen under Sequence name I still have SCOPE_IDENTITY() but cannot change this. Not sure if this should be there now that I have set RowGuid = no. I have tried two methods for creating new records now.

MyEntity entity = new MyEntity(Guid.NewGuid());
entity.SomeProp = 1;
adapter.SaveEntity(entity);

and as before:

MyEntity entity = new MyEntity();
entity.PK = Guid.NewGuid();
entity.SomeProp = 1;
adapter.SaveEntity(entity);

Same thing basically, but just to cover my bases I have tried both with the same result as above.

You can confirm the type of the field (the pk) in the DESIGNER is uniqueidentifier/GUID ? And if you scroll to the right, you see under sequence name 'SCOPE_IDENTITY' ?

Okay. I fixed it and this is how: I had to recreate the project. Simply refreshing the catalog did not work. So I recreated a new project and the sequence name 'SCOPE_IDENTITY' was gone from the project. That fixed the problem. Thanks for your help.

Sam wrote:

Okay. I fixed it and this is how: I had to recreate the project. Simply refreshing the catalog did not work. So I recreated a new project and the sequence name 'SCOPE_IDENTITY' was gone from the project. That fixed the problem. Thanks for your help.

But that's not a great solution of course. The refresher should reset the sequence name and shouldn't make the field an identity field anymore (the identity column should show 'false').

You still have your old project around, or could you specify some steps to reproduce it here? Please say where I go wrong: - column is of type identity - project created - column type is changed to uniqueidentifier and made RowGUID - catalog refreshed, sequence name isn't reset, field is still seen as an identity field - column Is RowGUID flag is set to false - catalog is refreshed, sequence name isn't reset, field is still seen as an identity field.

Is this correct? If so, what's the build of the designer you're working with (date, see window titel bar or about box)?

That is basically correct although you cannot change identity int column to unique identifier I was forced to delete that column and create a new column of type uniqueidentifier. -At the point I created the new uniqueidentifier PK I set it to RowGUID = true. -Refreshed catalog and got SCOPE_IDENTITY(). -Set RowGUID = false -Refresh catalog did not get rid of SCOPE_IDENTITY(). Tired both attended and unattended.

Thanks for your help!

Ok, thanks Sam, I think I can use that to try to reproduce it and add a bugfix for this, as it shouldn't be necessary to re-create a project

Next time, you can also delete / re-add the entity. Re-creating a project is almost never necessary (only with file corruption).

Reproduced: if the type of the field changes after a refresh, and it's not a sequenced field anymore, the sequence is still set.

Fixed in next build.