Visual Studio INSERT command error

Question

I have a web application in visual studio where I am getting the following message whenever I try to give an Insert command

System.Data.Sqlclient.Sqlexception: 'An Explicit value for the Identity column in table 'Funcionario' can only be specified when a column list is used and IDENTITY_INSERT is ON.'

Code of the C#:

SqlDataSource1.InsertCommand = "SET IDENTITY_INSERT Funcionario ON " +
            "INSERT INTO Funcionario VALUES ('" + func.nome + "'," + func.salarioBase + "," + func.inss + "," + func.irrf + "," + func.hrsExtras + "," + func.dependentes + "," + func.salarioLiquido + ")" +
            "SET IDENTITY_INSERT Funcionaio OFF";
SqlDataSource1.Insert();

Table structure :

create table Funcionario(cod_Func int identity (1,1),nome varchar (50),salarioBase decimal,inss decimal,irrf decimal,qtdHoraExtra int,dependente int,salarioLiquido decimal);

If I try to enter the table fields, the error becomes another

Insert :

SqlDataSource1.InsertCommand = "SET IDENTITY_INSERT Funcionario ON " +
            "INSERT INTO Funcionario (nome,salarioBase,inss,irrf,qtdHoraExtra,dependente,salarioLiquido) VALUES ('" + func.nome + "'," + func.salarioBase + "," + func.inss + "," + func.irrf + "," + func.hrsExtras + "," + func.dependentes + "," + func.salarioLiquido + ")";
SqlDataSource1.Insert();

Error:

System.Data.Sqlclient.Sqlexception: 'There are Fewer Columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of Columns specified in the INSERT statement.'

I don’t understand why I made a mistake, since cod_Func is identity, that is, we cannot set its value.

Answer 1

You define SET IDENTITY_INSERT as ON but does not pass the cod_Func in charge Insert.

According to the documentation: https://msdn.microsoft.com/pt-br/library/ms188059%28v=sql.120%29.aspx? f=255&Mspperror=-2147217396

If you enabled the option to insert the Identity column, you must pass it in the command.

Answer 2

First you must declare the columns that will be inserted in your instruction insert. Another very important thing, never concatenate SQL statements as you are doing, if you concatenate your SQL statements, your system will be vulnerable to SQL Injection

With the SqlDataSource I don’t know how it looks, but I’m putting in my answer an example using the SqlCommand.

using (SqlConnection conn = new SqlConnection(@"Data Source=.\SQLExpress;Initial Catalog=StackOverflow;Integrated Security=True"))
{
    conn.Open();
    string insert = @"INSERT INTO [dbo].[Funcionario]
                            ([nome]
                            ,[salarioBase]
                            ,[inss]
                            ,[irrf]
                            ,[qtdHoraExtra]
                            ,[dependente]
                            ,[salarioLiquido])
                        VALUES
                            (@nome
                            , @salarioBase
                            , @inss
                            , @irrf
                            , @qtdHoraExtra
                            , @dependente
                            , @salarioLiquido)";
    SqlCommand command = new SqlCommand(insert, conn);
    command.Parameters.Add(new SqlParameter("nome", func.nome));
    command.Parameters.Add(new SqlParameter("salarioBase", func.salarioBase));
    command.Parameters.Add(new SqlParameter("inss", func.inss));
    command.Parameters.Add(new SqlParameter("irrf", func.irrf));
    command.Parameters.Add(new SqlParameter("qtdHoraExtra", func.qtdHoraExtra));
    command.Parameters.Add(new SqlParameter("salarioLiquido", func.dependente));
    command.Parameters.Add(new SqlParameter("dependente", func.dependente));

    command.ExecuteNonQuery();
}

Answer 3

In my code there were two errors:

1- I was using the simplest variation of the INSERT command, where I would only need to specify the table and values (INSERT INTO TABLE VALUES (X)), and this was a mistake, because I believe, this simpler variation does not take into account fields Identity, including them in the columns to be filled;

2- The command SET IDENTITY_INSERT Tabela ON/OFF tells the program if fields Identity should be taken into consideration or not. Consider the following instruction :

SqlDataSource1.InsertCommand = "SET IDENTITY_INSERT Funcionario ON " +
        "INSERT INTO Funcionario (nome,salarioBase,inss,irrf,qtdHoraExtra,dependente,salarioLiquido) VALUES ('" + func.nome + "'," + func.salarioBase + "," + func.inss + "," + func.irrf + "," + func.hrsExtras + "," + func.dependentes + "," + func.salarioLiquido + ")";
SqlDataSource1.Insert();

Soon you see I’m saying that fields Identity MUST be taken into account, however I do not attribute any value to it in the column VALUES, therefore the error of having less fields than specified.

Finally, it is necessary to amend only the ON for OFF that will work normally