SQL error in casting int to string

Question

I have a method where I need to return the ID (integer) of a particular record whose name I pass as parameter, but Visual Studio points out the following error :

Undefined object reference for an object instance

This error occurs on the commented line:

public int buscarIdPorNome(string nomeSetor) //Busca id do setor pelo nome
    {
        int idSetor = 0;

        con = dal.conectar();//Conectando com o BD - retorna "new SqlConnection(connectionStringBuilder.ToString());"
        string cmdText = "SELECT (ID_Setor) FROM dbo.Setor WHERE Nome_STOR = @Nome_STOR"; //Definindo comando
        SqlCommand cmd = new SqlCommand(cmdText, con); //Adicionando comando

        cmd.Parameters.AddWithValue("@Nome_STOR", nomeSetor);

        con.Open();
        idSetor = (Int32)cmd.ExecuteScalar(); //Dando erro

        if (con != null)
        {
            con.Close(); //Fechando conexão
        }

        return idSetor;

    }

I tried to change the code that is giving error, but I was not successful, keep pointing out the same error. Command I tried:

idSetor = int.Parse(cmd.ExecuteScalar().ToString());

Remembering that the object con is a SQLConnection and the method dal.conectar(); is only creating the StringBuilder, defining the server and the database, but I don’t think it’s relevant for this error to put the code here.

Answer 1

There is nothing wrong with your code, I replicated it and it works normally (check the print).

Your problem is in your Query, already tried to play your query in the database ?

DECLARE @Nome_STOR varchar(100)
SET @Nome_STOR = 'Nome do Setor'
SELECT (ID_Setor) FROM dbo.Setor WHERE Nome_STOR = @Nome_STOR

As a suggestion use the clause using in your Sqlconnection:

using (var con = new SqlConnection("Persist Security Info=False;Integrated Security=true;Initial Catalog=CourseDB;server=(local)"))
{
    string nomeSetor = "Thiago";
    string cmdText = "SELECT (Id) FROM dbo.Student WHERE Name = @Nome_STOR"; //Definindo comando
    SqlCommand cmd = new SqlCommand(cmdText, con); //Adicionando comando

    cmd.Parameters.AddWithValue("@Nome_STOR", nomeSetor);

    con.Open();
    idSetor = (Int32)cmd.ExecuteScalar(); //Dando erro
}