Error adding data to database

Asked

Viewed 250 times

2

I’m having trouble inserting the information with parameters:

private static void InsereNovoMenuParaOUsuario(int idAcao, DTO.ModulosProgramaAcoes acao)
{
    using (var conn =  Conexao.ConectaDb()) {
        var listUser = new List<DTO.ModulosMenuAcessoUser>();

        var cmd = new SqlCommand
        {
            Connection = conn,
            CommandType = CommandType.Text,
            CommandText = "SELECT DISTINCT idUser FROM ModulosMenuAcessoUser"
        };

        conn.Open();

        var adap = new SqlDataAdapter(cmd);
        var dt = new DataTable();
        adap.Fill(dt);


        for (var i = 0; i < dt.Rows.Count; i++)
        {
            var user = new DTO.ModulosMenuAcessoUser()
            {
                IdUser = Convert.ToInt32(dt.Rows[i]["idUser"]),
                IdModulo = acao.IdModulo,
                IdPrograma = acao.IdPrograma,
                IdAcao = idAcao
            };

            listUser.Add(user);
        }

        foreach (var acesso in listUser)
        {

            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "INSERT ModulosMenuAcessoUser(IdModulo,IdPrograma,IdAcoes,IdUser)" + Environment.NewLine +
                               "VALUES " + Environment.NewLine +
                               "(@IdModulo,@IdPrograma,@IdAcoes,@IdUser)";

            cmd.Parameters.Add("@IdUser", SqlDbType.Int).Value = acesso.IdUser;
            cmd.Parameters.Add("@IdModulo", SqlDbType.Int).Value = acesso.IdModulo;
            cmd.Parameters.Add("@IdPrograma", SqlDbType.Int).Value = acesso.IdPrograma;
            cmd.Parameters.Add("@IdAcoes", SqlDbType.Int).Value = acesso.IdAcao;

        }

        var transaction = conn.BeginTransaction();
        cmd.Transaction = transaction;

        try {
            cmd.ExecuteNonQuery();
            transaction.Commit();
        }
        catch (Exception e) {
            transaction.Rollback();
            throw e;
        }

    }
}

displays the following error:

e={"The name of the@Iduser variable has already been declared. The names of variables should be unique in a batch of queries or in a stored procedure."}

c#

  • That’s what you’re talking about, and what you posted, we have no way of knowing. You can see that there are other problems in the code that let it work, but it’s not right.

  • @Andreandriotti, since I’m not sure about the problem, I made a change to the code and played on 1 Internet Internet, it’s not usual to do this, but could you test the code, please? If you decide to post as a solution, follow the link: https://www.invertexto.com/zjmk72

  • 1

    The problem is because there is already a parameter with that name @IdUser and consequently others should appear this problem. To solve put a cmd.Parameters.Clear() before the first line of cmd.Parameters.Add("@IdUser", SqlDbType.Int).Value = acesso.IdUser; will already work, of course your code can be improved.

1 answer

0

Although it’s been a long time... there’s the resolution:

How are you using:

cmd.Parameters.Add("@Idprograma", Sqldbtype.Int). Value = access.Idprograma;

To add the parameters in the Insert, if a single insertion occurs all right, but with the same parameter can no longer "Add", then must be cleaned this data before inserting again using:

cmd.Parameters.Clear();

Remembering: You must use this command after running the Insert

Browser other questions tagged c#

You are not signed in. Login or sign up in order to post.