在此上下文中不允许子查询。只允许标量表达式 [英] Subqueries are not allowed in this context. Only scalar expressions are allowed
问题描述
你好
有些人可以帮助我在我的应用程序中我有一个方法来更新数据库上的记录如果存在也插入如果记录不是
public static int updateValue（int cellnum，int returnID，double？value）
{
int num = 0;
使用（SqlConnection connection = new SqlConnection（clsGeneral.getConnectionString（）））
{
using（SqlCommand command = connection.CreateCommand （））
{
string str;
str =IF NOT NOT EXISTS（SELECT * FROM Valu） WHERE ReturnID =+ returnID +AND CellID =（SELECT CellID FROM [Cell] WHERE CellNum =+ cellnum +））;
if（value！ = 0.0）
{
str + =INSERT INTO Valu（CellID，ReturnID，Valu，Txt，FieldUsed，DteCreated，DteUpd）VALUES（（选择CellID FROM [Cell] ] WHERE CellNum =+ cellnum +），+返回ID +，CONVERT（FLOAT，REPLACE（'+ value +'，'，'，'。'）），''，1，getdate（），getdate（））;
str + =ELSE;
str + = string.Concat（new object [] {UPDATE [Valu] SET [Valu]。[Valu] = REPLACE（'，+ value，，'，'，'。'），[Valu] .FieldUsed = 1 WHERE [Valu] .ReturnID =，returnID，AND [Valu] .CellID =（SELECT CellID FROM [Cell] WHERE CellNum =，cellnum，）}）;
}
else
{
str + =INSERT INTO Valu（CellID，ReturnID，Valu，Txt，FieldUsed，DteCreated，DteUpd）VALUES（（选择CellID FROM [Cell] WHERE CellNum =+ cellnum +）， + returnID +，CONVERT（FLOAT，REPLACE（'+ value +'，'，'，'。'）），''，0，getdate（），getdate（））;
str + =ELSE;
str + = string.Concat（new object [] {UPDATE [Valu] SET [Valu]。[Valu ] = CONVERT（FLOAT，REPLACE（ '，+ value，'，'，'，'。'）），[Valu] .FieldUsed = 0 WHERE [Valu] .ReturnID =，returnID，AND [Valu] .CellID =（SELECT CellID FROM [ Cell] WHERE CellNum =，cellnum，）}）;
}
command.CommandType = CommandType.Text;
command.CommandText = str;
connection.Open（）;
num = command.ExecuteNonQuery（）;
}
}
返回num;
}
我在保存时收到以下错误，你可以帮我修补它。
不允许使用子查询在这种背景下。只允许标量表达
我尝试过：
Hello
Can some help me please in my application I have a method below to update the record on the database if exists also insert if the record is not
public static int updateValue(int cellnum, int returnID, double? value)
{
int num = 0;
using(SqlConnection connection = new SqlConnection(clsGeneral.getConnectionString()))
{
using(SqlCommand command = connection.CreateCommand())
{
string str;
str = "IF NOT EXISTS(SELECT * FROM Valu WHERE ReturnID = " + returnID + " AND CellID=(SELECT CellID FROM [Cell] WHERE CellNum=" + cellnum + "))";
if (value != 0.0)
{
str += " INSERT INTO Valu (CellID, ReturnID, Valu, Txt, FieldUsed, DteCreated, DteUpd) VALUES ((SELECT CellID FROM[Cell] WHERE CellNum = " + cellnum + ")," + returnID + ", CONVERT(FLOAT, REPLACE('" + value + "',',','.')),'',1,getdate(),getdate())";
str += " ELSE ";
str += string.Concat(new object[] { "UPDATE [Valu] SET [Valu].[Valu]=REPLACE('", + value , "',',','.'),[Valu].FieldUsed=1 WHERE [Valu].ReturnID=", returnID, " AND [Valu].CellID=(SELECT CellID FROM [Cell] WHERE CellNum=", cellnum, ")" });
}
else
{
str += " INSERT INTO Valu (CellID, ReturnID, Valu, Txt, FieldUsed, DteCreated, DteUpd) VALUES ((SELECT CellID FROM[Cell] WHERE CellNum = " + cellnum + ")," + returnID + ", CONVERT(FLOAT, REPLACE('" + value + "',',','.')),'',0,getdate(),getdate())";
str += " ELSE ";
str += string.Concat(new object[] { "UPDATE [Valu] SET [Valu].[Valu]=CONVERT(FLOAT, REPLACE('", +value, "',',','.')),[Valu].FieldUsed=0 WHERE [Valu].ReturnID=", returnID, " AND [Valu].CellID=(SELECT CellID FROM [Cell] WHERE CellNum=", cellnum, ")" });
}
command.CommandType = CommandType.Text;
command.CommandText = str;
connection.Open();
num = command.ExecuteNonQuery();
}
}
return num;
}
Am getting the below error when am saving, can you please help me hot fix it.
Subqueries are not allowed in this context. Only scalar expressions are allowed
What I have tried:
public static int updateValue(int cellnum, int returnID, double? value)
{
int num = 0;
using(SqlConnection connection = new SqlConnection(clsGeneral.getConnectionString()))
{
using(SqlCommand command = connection.CreateCommand())
{
string str;
str = "IF NOT EXISTS(SELECT * FROM Valu WHERE ReturnID = " + returnID + " AND CellID=(SELECT CellID FROM [Cell] WHERE CellNum=" + cellnum + "))";
if (value != 0.0)
{
str += " INSERT INTO Valu (CellID, ReturnID, Valu, Txt, FieldUsed, DteCreated, DteUpd) VALUES ((SELECT CellID FROM[Cell] WHERE CellNum = " + cellnum + ")," + returnID + ", CONVERT(FLOAT, REPLACE('" + value + "',',','.')),'',1,getdate(),getdate())";
str += " ELSE ";
str += string.Concat(new object[] { "UPDATE [Valu] SET [Valu].[Valu]=REPLACE('", + value , "',',','.'),[Valu].FieldUsed=1 WHERE [Valu].ReturnID=", returnID, " AND [Valu].CellID=(SELECT CellID FROM [Cell] WHERE CellNum=", cellnum, ")" });
}
else
{
str += " INSERT INTO Valu (CellID, ReturnID, Valu, Txt, FieldUsed, DteCreated, DteUpd) VALUES ((SELECT CellID FROM[Cell] WHERE CellNum = " + cellnum + ")," + returnID + ", CONVERT(FLOAT, REPLACE('" + value + "',',','.')),'',0,getdate(),getdate())";
str += " ELSE ";
str += string.Concat(new object[] { "UPDATE [Valu] SET [Valu].[Valu]=CONVERT(FLOAT, REPLACE('", +value, "',',','.')),[Valu].FieldUsed=0 WHERE [Valu].ReturnID=", returnID, " AND [Valu].CellID=(SELECT CellID FROM [Cell] WHERE CellNum=", cellnum, ")" });
}
command.CommandType = CommandType.Text;
command.CommandText = str;
connection.Open();
num = command.ExecuteNonQuery();
}
}
return num;
}
推荐答案
第一步是将sql移动到存储过程并传入参数。正如您现在所看到的，所有这些内联sql都很难调试和维护。
您应该将INSERT更改为类似
First step is to move your sql into a Stored Procedure and pass in parameters. All of this inline sql is difficult to debug and to maintain, as you can see now.
And you should change your INSERT to something like
INSERT INTO Value(CellID, ReturnID, Valu, Txt, FieldUsed, DteCreated, DteUpd)
SELECT CellID, @returnID, @value, '', 1, getDate(), getDate()
FROM [Cell]
WHERE CellNUm = @cellnum
你可以'使用VALUES语句时，有一个SELECT语句。所以，只需单独使用SELECT语句。
You can't have a SELECT statement when you use the VALUES statement. So, just use the SELECT statement by itself.
我注意到的第一件事就是这段代码很危险！通过将字符串拼接在一起将变量添加到SQL语句中是对SQL注入的邀请;这是有史以来十大漏洞之一。
第二件事就是把它变成更易于管理和更清洁的东西。只是试图解析正在进行的逻辑需要一点点。
这个解决方案的第1部分是将这个混乱转换为存储过程 。此脚本需要在SQL Server上运行一次，然后我们可以在C＃中按名称调用它，只需传递值
First thing I noticed is that this code is dangerous! Adding variables into an SQL statement by piecing strings together is an invitation to SQL Injection; which is one of the top 10 vulnerabilities of all time.
Second thing is to get this into something more manageable and cleaner looking. Just trying to parse the logic going on took a little bit.
Part 1 of this solution is to convert this mess into a Stored Procedure. This script will need to be run on the SQL Server once, and then we can call it by name within C# and just pass the values
CREATE PROCEDURE dbo.Valu_CreateOrUpate (
@ReturnID INT,
@CellNum INT,
@Value FLOAT
) AS
BEGIN
DECLARE @Now DATETIME = GetDate()
DECLARE @FieldUsed BIT = 0
DECLARE @CellID INT
IF (@Value <> 0) SET @FieldUsed = 1
SELECT @CellID = CellID FROM [Cell] WHERE CellNum = @CellNum
IF NOT EXISTS(SELECT 1 FROM Valu WHERE ReturnID = @ReturnID AND CellID = @CellID)) BEGIN
INSERT INTO Valu (CellID, ReturnID, Valu, Txt, FieldUsed, DteCreated, DteUpd)
VALUES ( @CellID, @ReturnID, @Value, '', @FieldUsed, @Now, @Now)
END; ELSE BEGIN
UPDATE [Valu]
SET [Valu].[Valu] = @Value
, [Valu].FieldUsed = @FieldUsed
WHERE [Valu].ReturnID = @ReturnID
AND [Valu].CellID = @CellID
END
END
GO
下一部分是更容易阅读C＃代码来调用它。 CommandText已更改为过程的名称，CommandType更改为StoredProcedure，并且通过 Parameters.AddWithValue（）方法将变量添加到命令中。此方法将根据您使用的变量类型自动键入值。它通常做得很好，
然而;如果你的一个变量是 null ，它将不会在查询中包含它，除非你明确地将它作为 DBNull.Value 传递。您需要决定如何处理代码中可以为空的值。
The next part is the much easier to read C# code to call this. The CommandText was changed to be the name of the procedure, the CommandType is changed to be StoredProcedure, and the variables are added into the command via the Parameters.AddWithValue() method. This method will "auto-type" the values based on what type of variable you are working with. It generally does a good job,
However; if one of your variables is null it will not include it in the query unless you pass it in explicitly as DBNull.Value. You will need to decide how to handle the nullable value you have in your code.
public static int updateValue(int cellnum, int returnID, double? value) {
int num = 0;
using (SqlConnection connection = new SqlConnection(clsGeneral.getConnectionString())) {
using (SqlCommand command = connection.CreateCommand()) {
command.CommandText = "dbo.Valu_CreateOrUpate";
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ReturnID", returnID);
command.Parameters.AddWithValue("@CellNum", cellnum);
command.Parameters.AddWithValue("@Value", value ); // ** watch for NULL **
connection.Open();
num = command.ExecuteNonQuery();
}
}
return num;
}
这篇关于在此上下文中不允许子查询。只允许标量表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
