CODEWITHSUNDEEP
sqlsql-server
Sergio Tapia
Sergio Tapia

Reputation: 41218

SQL INSERT stored procedure not working

Create Proc CrearNuevoAnuncio
    @Titulo varchar(250),
    @Precio int,
    @Descripcion varchar(250),
    @IDCategoria int,
    @IDImagen int,  
    @Login varchar(200)

AS

INSERT INTO Anuncio VALUES(
    @Titulo,
    @Precio,
    @Descripcion,
    @IDCategoria,
    @IDImagen,  
    @Login
    )

The error is because the table anuncio has 1 more attribute: "idAnuncio". It's the primary key, and it's the indentity (autoincrement).

So, how can I handle this is the missing thing is an Identity. I don't want to pass that parameter from my front-end.

Upvotes: 2

Views: 3315

Answers (2)

marc_s
marc_s

Reputation: 755381

You need to specify the explicit list of columns in your insert statement:

INSERT INTO 
   Anuncio(Titulo, Precio, Descripcion, IDCategoria, IDImagen, Login)
VALUES
   (@Titulo, @Precio, @Descripcion, @IDCategoria, @IDImagen,  @Login)

Otherwise, SQL Server will try to insert values for ALL columns which fails here. It cannot insert a value into an IDENTITY column - that column will be set automatically by SQL Server upon inserting a new row, and is guaranteed to be unique.

Is the ID field of type "INT IDENTITY" ? In that case, you could access the value of the newly inserted ID like this:

 DECLARE @NewID INT
 SET @NewID = SCOPE_IDENTITY()

and possibly return it from the stored proc:

 RETURN @NewID

Marc

Upvotes: 10

Zed
Zed

Reputation: 57678

You need to specify which value goes to which field.

INSERT INTO Anuncio (field1, field2, ...) VALUES (@Titulo, ...)

Upvotes: 8

Related Questions