Cannot retrieve inserted id - no primary key was found.

SolucionesOptimas

I am using Editor 1.6.3 inside WebAPI. I have no problem when modifying or deleting a record. The problem is when inserting a new record, I'm using SQL SERVER 12 and the default scheme (dbo). I receive the error: Cannot retrieve inserted id - no primary key was found. I would like to know if there is a solution: here the code on the server:

        using (var db = new Database(dbCon.dbType, dbCon.dbConexion))
        {
            var response = new Editor(db,  dbCon.NomBase + ".dbo.Productos2", "id")
                .Debug(true)
                .Model<Models.ConfiguracionViewModels.ProductoShow>()
                .Field(new Field("id").Name("idp"))
                .Field(new Field("NPRODUCTO").Name("producto")
                    .Validator(Validation.NotEmpty(new ValidationOpts { Empty = false, Message = "Es necesario proporcionar el nombre" }))
                )
                .Field(new Field("CPRODUCTO").Name("codProd"))
                .Field(new Field("TPRODUCTO", "TIPOPROD")
                    .Validator(Validation.NotEmpty(new ValidationOpts { Empty = false, Message = "Proporcione la Referencia" }))
                )
                .Field(new Field("PRECIOPROD").Name("precio"))
                .Field(new Field("ESTATUSP").Name("estado")
                    .Validator(Validation.Numeric())
                )
                .Process(request)
                .Data();
            Debug.WriteLine(string.Format("{0}: {1} ",response.data,response.error));
            return Json(response);
        }

My Table

CREATE TABLE Productos2(
[id] [int] NOT NULL IDENTITY(1,1),
[CPRODUCTO] varchar NOT NULL default '',
[NPRODUCTO] varchar NOT NULL default '',
[CPRODUCTO] [int] NOT NULL default '0',
[PRECIOPROD] [float] NOT NULL default '0',
[ESTATUSP] [int] NOT NULL default '0',
PRIMARY KEY (id)
)

Thank you

allan

Hi,

That error indicates that the table doesn't have a primary key - however, you use both IDENTITY and PRIMARY KEY in the above schema, so that shouldn't be the issue.

When I run that SQL I get an error stating that CPRODUCTO is included twice (which it is), so I'm not 100% certain that the schema there exactly matches what your SQL server is actually running. Are you able to dump the table so we can see what it thinks it is using?

I've just tried it locally with only one CPRODUCTO column and it appears to work okay.

Thanks,
Allan

SolucionesOptimas

Thanks Allan, You are right in the SQL twice the CPRODUCT appears, but it is a mistake to copy the second CPRODUCT is actually CCPRODUCT.
The TABLE:

CREATE TABLE Productos2(
[id] [int] NOT NULL IDENTITY(1,1),
[CPRODUCTO] varchar NOT NULL default '',
[NPRODUCTO] varchar NOT NULL default '',
[CCPRODUCTO] [int] NOT NULL default '0',
[PRECIOPROD] [float] NOT NULL default '0',
[ESTATUSP] [int] NOT NULL default '0',
PRIMARY KEY (id)
)

allan

Thanks! I've just tried that variant as well, and it appears to be working for me. What version of SQL server are you using?

Allan

SolucionesOptimas

Hi Allan.
Within Visual studio 2015 the SQL version is SQL SERVER 2012 (11.0.3000) and I have just tested on the production server, also Server 2012 (11.0.2100).

allan

The only thing I can come up with is that it is related to the schema name being used, that's not something that my examples make use of, nor has it been tested.

Are you able to try it without the schema name (you might need to modify the database connection to have it use that schema automatically)?

Thanks,
Allan

SolucionesOptimas

Hi Allan, thank you for your comments. I tried your suggestions but I got the same result: In addition to not inserting the record, I got the same answer. But after doing several tests I manually inserted a record and noticed that the id of the table had increased the number of times I had tried through "Editor", i.e. from id=7 the next was id=12. This means that it is likely that in the tests I did, the registry was actually inserted but for some reason it was deleted, which is why the inserted id is not found later. Do you think there is any reason why there is this behavior?

featherss

set the field id (your primary key) to true as shown below on line 5

# # .Field(new Field("id").Set(true))

public class StaffController : ApiController
{
[Route("api/staff")]
[HttpGet]
[HttpPost]
public IHttpActionResult Staff()
{
var request = HttpContext.Current.Request;
var settings = Properties.Settings.Default;

        using (var db = new Database(settings.DbType, settings.DbConnection))
        {
            var response = new Editor(db, "DT_STAFF")
                .Model<StaffModel>()
                .Field(new Field("id").Set(true))
                .Field(new Field("first_name")
                    .Validator(Validation.NotEmpty())
                )
                .Process(request)
                .Data();

            return Json(response);
        }
    }
}