DbCommand , Must Declare a variable error

Question

This is my code. I have added the db parameter too but it still shows me error (on execution). Must declare a scalar variable

       DbCommand command;
        StringBuilder query = new StringBuilder(
                                                        @"SELECT         isnull(UpsellService_OID,'') UpsellService_OID," + Environment.NewLine +
                                                        "       isnull(ServiceName,'') ServiceName," + Environment.NewLine +
                                                        "       isnull(ServiceDescription,'') ServiceDescription," + Environment.NewLine +
                                                        "       isnull(Create_By,'') Create_By," + Environment.NewLine +
                                                        "       isnull(Create_Date,'') Create_Date," + Environment.NewLine +
                                                        "       isnull(Modify_By,'') Modify_By," + Environment.NewLine +
                                                        "       isnull(Modify_Date,'') Modify_Date," + Environment.NewLine +
                                                        "       isnull(Active_f,'') Active_f" + Environment.NewLine +
                                                        "FROM   TRGPAYROLL.ZONG.UPSELLSERVICES   " + Environment.NewLine +
                                                        "WHERE  1 = 1");
        if (!string.IsNullOrEmpty(idObject.ServiceName))
        {
            query.Append(" AND ServiceName like '%'  @ServiceName  '%'");
        }

        command = db.GetSqlStringCommand(query.ToString());
        if (!string.IsNullOrEmpty(idObject.ServiceName))
        {
            db.AddInParameter(command, "ServiceName", DbType.String, idObject.ServiceName);
        }

        return command;

Steve · Answer 1 · 2014-03-06T08:12:16.080

I would rewrite the last part of your code in this way

    if (!string.IsNullOrEmpty(idObject.ServiceName))
    {
        query.Append(" AND ServiceName like @ServiceName");
    }

    command = db.GetSqlStringCommand(query.ToString());
    if (!string.IsNullOrEmpty(idObject.ServiceName))
    {
        db.AddInParameter(command, "@ServiceName", DbType.String, "%" + idObject.ServiceName + "%");
    }

The wildcard are added directly to the value of the parameter, while the placeholder of the parameter should be free from any string concatenations. However there are many details missing to be sure of the correctness of this answer. In particular Ican only assume the inner workings of the methods GetSqlStringCommand and AddInParameter

score 0 · Answer 2 · answered Mar 06 '14 at 08:01

0

@ServiceName variable is not declared in your SQL statement. Append to beggining of it something like

DECLARE @ServiceName AS nchar(32)
SET @ServiceName = ....

answered Mar 06 '14 at 08:01

ttaaoossuuuu

7,786
3
28
58

DbCommand , Must Declare a variable error

2 Answers2