Problems with executescalar (System.Data.Sqlclient.Sqlexception)

Asked

Viewed 99 times

0

I have the following block of code: 

sqlConn.Open();

        SqlCommand inserirAluno = new SqlCommand("INSERT INTO ALUNOS (Nome, DataNascimento, CPF, Endereco, Bairro, " +
             "CEP, Cidade, IdEstado, Sexo) OUTPUT INSERTED.ID" +
             "Values(@Nome, @DataNascimento, @CPF, @Endereco, @Bairro, @CEP, @Cidade, @IdEstado, @Sexo)", sqlConn);
        inserirAluno.Parameters.Add("@DataNascimento", SqlDbType.Date).Value = mtb_Nasc.Text;
        inserirAluno.Parameters.Add("@Nome", SqlDbType.NVarChar).Value = tb_Nome.Text;
        inserirAluno.Parameters.Add("@CPF", SqlDbType.NVarChar).Value = mtb_Cpf.Text;
        inserirAluno.Parameters.Add("@Endereco", SqlDbType.NVarChar).Value = tb_Endereco.Text;
        inserirAluno.Parameters.Add("@Bairro", SqlDbType.NVarChar).Value = tb_Bairro.Text;
        inserirAluno.Parameters.Add("@CEP", SqlDbType.NVarChar).Value = mtb_Cep.Text;
        inserirAluno.Parameters.Add("@Cidade", SqlDbType.NVarChar).Value = tb_Cidade.Text;
        inserirAluno.Parameters.Add("@IdEstado", SqlDbType.Int).Value = cb_Uf.SelectedIndex;
        if (rb_Masc.Checked)
        {
            inserirAluno.Parameters.Add("@Sexo", SqlDbType.Bit).Value = 0;
        }
        else
        {
            inserirAluno.Parameters.Add("@Sexo", SqlDbType.Bit).Value = 1;
        }

        int id = (int)inserirAluno.ExecuteScalar();

        SqlCommand inserirMatricula = new SqlCommand("INSERT INTO MATRICULAS(IdAluno, IdCurso, Periodo, Mensalidade, " +
            "FlagAtivo, DataMatricula)" +
            "Values(@IdAluno, @IdCurso, @Periodo, @Mensalidade, @FlagAtivo, @DataMatricula)");
        inserirMatricula.Parameters.Add("@IdAluno", SqlDbType.Int).Value = id;
        inserirMatricula.Parameters.Add("@IdCurso", SqlDbType.Int).Value = 1;
        inserirMatricula.Parameters.Add("@Periodo", SqlDbType.Int).Value = tb_Semestre.Text;
        inserirMatricula.Parameters.Add("@Mensalidade", SqlDbType.Decimal).Value = tb_Mensalidade.Text;
        inserirMatricula.Parameters.Add("@FlagAtivo", SqlDbType.Bit).Value = 1;
        inserirMatricula.Parameters.Add("@DataMatricula", SqlDbType.Date).Value = DateTime.Now;



        sqlConn.Close();

In the first command, I am inserting the student through the textbooks of my form. At Sert, I am recovering the last ID inserted by the student and the storage in my variable ID.

In the second command, I am entering the enrollment of this newly registered student (both in the same form), using his recovered id to enter in my enrollment table. However, when confirming the registration, the system returns the following error on the line 

"int id = (int)inserirAluno.ExecuteScalar();"

System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

Does anyone have any idea why this exception?

c# sql sql-server

  • I think this should be at the end of the syntax: ) OUTPUT INSERTED.ID

  • In this case, at the end of the passage of "values" of the Insert?

  • after reading this material: https://docs.microsoft.com/pt-br/sql/t-sql/queries/output-clause-transact-sql. saw that the syntax is this, disregard my suggestion

1 answer

1


I see two errors in your code:

First, space in the query string. Missing a space after "INSERTED.ID" and before "VALUES": 

SqlCommand inserirAluno = new SqlCommand("INSERT INTO ALUNOS (Nome, DataNascimento, CPF, Endereco, Bairro, " +
             "CEP, Cidade, IdEstado, Sexo) OUTPUT INSERTED.ID " +

Second, treat return null. If lines are not included for any reason, ExecuteScalar will return null, giving cast error to int, then it is advisable to treat:

int id = 0;
var resultado = inserirAluno.ExecuteScalar();

if (resultado!= null) {
    id = (int)resultado;
}

Browser other questions tagged c# sql sql-server

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