CODEWITHSUNDEEP
ado.netdbcommand
Ali Shahzeb
Ali Shahzeb

Reputation: 3

DbCommand , Must Declare a variable error

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;

Upvotes: 0

Views: 287

Answers (2)

Steve
Steve

Reputation: 216293

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

Upvotes: 1

ttaaoossuu
ttaaoossuu

Reputation: 7884

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

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

Upvotes: 0

Related Questions