Problems with executescalar (System.Data.SqlClient.SqlException)

Question

Problems with executescalar (System.Data.SqlClient.SqlException)

Navigation

#1 by (1 votes)

0

I have the following code block:

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'm inserting the student through my form's textboxes. In insert, I am retrieving the last ID entered by the student and the store in my variable ID.

In the second command, I am entering the enrollment of this newly enrolled student (both on the same form), using his retrieved id to insert into my enrollment table. However, when you confirm the registration, the system returns the following error in the line

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

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

Does anyone have any idea why this exception?

sql sql-server c#

asked by anonymous 12.03.2018 / 23:09

1 answer

Python 3 and Tkinter: progress bar does not update during script execution AppendTo is not a function / Shopping cart using jQuery [closed]

score 1 · Accepted Answer

I see two errors in your code:

First, space in the query string. A space was missing 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 no rows are included for any reason, ExecuteScalar will return null , giving cast error to int , so it is advisable to treat:

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

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