c# - 当我使用变量时使用 ExecuteScalar() 选择不起作用，总是返回空，当我不使用变量时它起作用 - C#

Question

当我使用一个变量时，command.ExecuteScalar()它的结果总是空的（空白），我没有收到任何错误消息，只是一个空白值，但是当我使用相同的 SQL 查询而不使用变量时它可以工作。

到目前为止我已经尝试过：

//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 是一个 nvarchar(10)

我做了一个测试，结果1是真的，所以teste等于“8131818060”但是result2是假的，“sql”不等于“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");

//遵循 Roeland 的建议 //我以这种方式进行了测试，并且我在 ip.ToString() 上收到 NullReferenceException 错误 //System.NullReferenceException：对象引用未设置为对象的实例

         //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());

标签： c#sqlsql-servervariablesexecutescalar

Answer 1

我试过这个示例代码：

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());

连同此 SQL 代码：

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'

该代码确实返回了 10.101.1.0 的值

所以你的第一次尝试应该没问题。

如果有 E164 列具有非数值的任何记录，您的第二个示例将失败。

在您的第三次尝试中，此行将无法编译：

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

您的第四次和第五次尝试也可以。

当我遇到这样的问题时，我总是使用 SQL Profiler 来检查 SQL Server 执行的语句。