ExecuteScalar returns ASP Null Id ASP.NET

Question

ExecuteScalar returns ASP Null Id ASP.NET

Navigation

#1 by (3 votes)
#2 by (1 votes)

0

I'm trying to create a comment system in a personal project and the following problem arose, when wanting to generate a new comment I do not recognize the Id of the comment and the ExecuteScalar returns me as null the Id, the idea is that when you generate a new comment the Id is increased.

Next I leave the code that inserts the comment in the database. I use Visual Studio 2015 and SQL Server 2014

// Insertar un nuevo comentario
public int InsertarComentario(Comentarios comentarios)
{
    // Conectarse a la base de datos
    SqlConnection conexion = new SqlConnection(ConfigurationManager.AppSettings["TecnoInfoConnectionString"]);

    // Conectarnos
    conexion.Open();

    // Ejecutar la consulta
    // Creamos la sentencia
    SqlCommand comando = conexion.CreateCommand();
    comando.CommandText = "INSERT INTO Comentarios (Comentario, IdUsuario, IdArticulo) OUTPUT INSERTED.IdComentario VALUES (@Comentario, @IdUsuario, @IdArticulo)";
    comando.Parameters.AddWithValue("@Comentario", comentarios.Comentario);
    comando.Parameters.AddWithValue("@IdUsuario", comentarios.IdUsuario);
    comando.Parameters.AddWithValue("@IdArticulo", comentarios.IdArticulo);

    // Ejecutarla
    int nuevoIdComentario = (int)comando.ExecuteScalar(); //devuelve la primer columna de la primer fila
    comentarios.IdComentario = nuevoIdComentario;

    // Cerrar la conexion
    conexion.Close();

    // Devuelve el Id del nuevo articulo
    return nuevoIdComentario;
}

The specific error is the following:

"Unable to insert NULL value in column 'CommentId', table 'Tecno_Info.dbo.Reviews'. Column does not support NULL values INSERT error."

sql-server asp.net-mvc-5

asked by Lautaro Piacquadio 19.12.2015 в 05:31

source

2 answers

1

The field in the bd sql server must be of type identity so that the consecutive one generates automatically.

answered by 19.12.2015 в 05:38

Error renaming index.html with PHP My contact form returns errors when sending messages [closed]

score 3 · Accepted Answer

piacquadio, as you said they were missing that IDENTITY is the DB column (NOTE: I also agree with the recommendation that you modularize your app in layers, for example your data layer that communicates with some ORM, Entity Framework, NHibernate, etc.)

Going back to your topic ... if, like this, your code in the question will not return the ID that was inserted, something is missing:

Return the identity column (key) that was inserted with ExecuteScalar

As you can read in ExecuteScalar , it says

Execute the query and return the first column of the first row of the result set returned by the query. Other columns or rows are not taken into account.

For this reason it does not return the rows affected by the insert and much less returns the key (identity) that was inserted. How do we solve it? For that, we helped in SQL Server of SCOPE_IDENTITY . In your example you only have to add the SELECT of this SQL function at the end, something like this:

//Ejemplo para devolver el Identity que insertó (al final SELECT...)
SqlCommand comando = conexion.CreateCommand();
comando.CommandText = "INSERT INTO Comentarios (Comentario, IdUsuario, IdArticulo) OUTPUT INSERTED.IdComentario VALUES (@Comentario, @IdUsuario, @IdArticulo); SELECT SCOPE_IDENTITY();";

There, the SQL will return a SELECT with 1 column 1 row, which contains in it the value you need.

Links that can help or guide you