Click here to Skip to main content
15,126,317 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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;
}
Posted
Updated 4-Mar-19 11:19am

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
SQL
INSERT INTO Value(CellID, ReturnID, Valu, Txt, FieldUsed, DteCreated, DteUpd)
SELECT CellID, @returnID, @value, '', 1, getDate(), getDate()
FROM [Cell]
WHERE CellNUm = @cellnum


You can't have a SELECT statement when you use the VALUES statement. So, just use the SELECT statement by itself.
   
Comments
Member 14114251 4-Mar-19 9:11am
   
Can please assist in modifying this query the challenge am working on some's application and its currently on the production and am new in sql can you help me please to fix this.
Thanks in advance for your help.
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
SQL
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


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.
C#
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;
}
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900