Select with a ExecuteScalar() not working when i use a variable, always return empty, when i don't use variable it works - C#

Question

When I use a variable, the result of command.ExecuteScalar() it's always empty (blank), I'm not receiving any error messages, just a blank value, but when I use the same SQL query without using a variable it works.

What I have tried so far:

//variable @num is a long and contais the value = '8131818060'              

string teste = num.ToString();


// 1º: In this case ip value: it's empty

string sql = @"select Sites_IP_Rede from tblRamais where E164 = '" + teste + "'";

command = new SqlCommand(sql, con.conectar());
var ip = (string)command.ExecuteScalar();
con.desconectar();
MessageBox.Show("IP Value: " + ip);


// 2º: In this case ip value: it's empty

string sql = @"select Sites_IP_Rede from tblRamais where E164 = " + teste;

command = new SqlCommand(sql, con.conectar());
var ip = (string)command.ExecuteScalar();
con.desconectar();
MessageBox.Show("IP Value: " + ip);


// 3º: In this case ip value: it's empty

string sql = @"select Sites_IP_Rede from tblRamais where E164 = " + num";

command = new SqlCommand(sql, con.conectar());
var ip = (string)command.ExecuteScalar();
con.desconectar();
MessageBox.Show("IP Value: " + ip);


// 4º In this case ip value: it's empty

string sql = @"select Sites_IP_Rede from tblRamais where E164 = @e164";

SqlCommand command = new SqlCommand(sql, con.conectar());
command.Parameters.Clear();
command.Parameters.AddWithValue("@e164", teste);

//string ip = command.ExecuteScalar() as string;
var ip = (string)command.ExecuteScalar();

MessageBox.Show("IP Value:: " + ip);
con.desconectar();


// 5º In this case ip value: it's empty

string sql = @"select Sites_IP_Rede from tblRamais where E164 = @e164";

SqlCommand command = new SqlCommand(sql, con.conectar());
command.Parameters.Add(new SqlParameter
{
    ParameterName = "@e164",
    Value = teste,
}
);

//string ip = command.ExecuteScalar() as string;
var ip = (string)command.ExecuteScalar();

MessageBox.Show("IP Value: " + ip);
con.desconectar();


//6º This one it's the only way it works
// That's the only way it works, When i write the parameter = "8131818060"

string sql = "select Sites_IP_Rede from tblRamais where E164 = 8131818060";

SqlCommand command = new SqlCommand(sql, con.conectar());

string IP = (string)command.ExecuteScalar();

MessageBox.Show("IP Value: " + IP);

//The ip values in this case is "10.101.1.0"

E164 is a nvarchar(10)

I've made a test and result 1 is true, so teste is equal to "8131818060" But result2 is false, "sql" is not equal to "sql2"

        bool result1 = string.Equals(teste,"8131818060");





        string sql = "select Sites_IP_Rede from tblRamais where E164 = 8131818060";


        string sql2 = "select Sites_IP_Rede from tblRamais where E164 = " + teste;


        bool result2 = string.Equals(sql,"sql2");

//following Roeland suggestion //I tested that way, and i'm reciving a NullReferenceException error on ip.ToString() //System.NullReferenceException: Object reference not set to an instance of an object

         //num is a long variable pass from the user

         string sql = @"select Sites_IP_Rede from tblRamais where E164 = '" + num.ToString() + "'";
         SqlCommand command = new SqlCommand(sql, con.conectar());
         var ip = command.ExecuteScalar();
         con.desconectar();
         MessageBox.Show("IP Value: " + ip.ToString());

Answer 1

I tried this example code:

long test = 8131818060;
string sql = @"select Sites_IP_Rede from tblRamais where E164 = '" + test.ToString() + "'";
SqlConnectionStringBuilder sqlConnectionStringBuilder = new SqlConnectionStringBuilder
{
    InitialCatalog = "xxx",
    DataSource = @"127.0.0.1\V2016",
    UserID = "xxx",
    Password = "xxx"
};
SqlConnection con = new SqlConnection(sqlConnectionStringBuilder.ConnectionString);
con.Open();
SqlCommand command = new SqlCommand(sql, con);
var ip = command.ExecuteScalar();
con.Close();
MessageBox.Show("IP Value: " + ip.ToString());

Along with this SQL Code:

CREATE TABLE [dbo].[tblRamais](
    [E164] [nvarchar](10) NOT NULL,
    [Sites_IP_Rede] [varchar](20) NULL,
 CONSTRAINT [PK_tblRamais2] PRIMARY KEY CLUSTERED 
(
    [E164] ASC
)
) 
INSERT INTO tblRamais SELECT '8131818060','10.101.1.0'

The code did return the value of 10.101.1.0

So your first try should be OK.

Your second example will fail if there is any record where column E164 has a non numeric value.

In your third try, this line won't compile:

string sql = @"select Sites_IP_Rede from tblRamais where E164 = " + num";

Your fourth and fifth try are OK too.

When I encounter problems like these, I alway use the SQL Profiler to check the statement SQL Server executed.