CODEWITHSUNDEEP
c#sqlvariablesscalar
gymcode
gymcode

Reputation: 4623

SQL Query Must Declare the Scalar Variable

I have a class file where I declare readonly string of my query to be used in a method. I met the error of

Must declare the scalar variable "@DBID"

May I know if I declare my variables wrongly?

Below are the code snippets:

Class file:

private static readonly string QUERY_GETMATCHEDRECORD = "SELECT [Title], [ItemLink], [RecordDocID] FROM [ERMS].[dbo].[Records] WHERE [ID] = @DBID AND [V1RecordID] = @recID AND [V1RecordDocID] = @recDocID";

public DataTable GetMatchedRecord(string DBID, string recID, string recDocID)
{
    string Method = System.Reflection.MethodBase.GetCurrentMethod().Name;
    DataTable dt = new DataTable();
    try
    {
        using (DB db = new DB(_datasource, _initialCatalog))
        {
            db.OpenConnection();
            using (SqlCommand command = new SqlCommand())
            {
                string commandText = QUERY_GETMATCHEDRECORD .FormatWith(DBID,recID,recDocID);
                _log.LogDebug(Method, "Command|{0}".FormatWith(commandText));
                command.CommandText = commandText;
                dt = db.ExecuteDataTable(command);
            }
            db.CloseConnection();
        }
    }
    catch (Exception ex)
    {
        _log.LogError(Method, "Error while retrieving matching records |{0}".FormatWith(ex.Message));
        _log.LogError(ex);
    }
    return dt;
}

Program .cs file:

MatchedRecords = oDB.GetMatchedRecord(DBID, RecID, RecDocID);

Upvotes: 1

Views: 854

Answers (1)

Sancho Panza
Sancho Panza

Reputation: 759

using '@'-notated variables will only work if you add the parameters to the commands parameter-collection.

try the following:

    using (DB db = new DB(_datasource, _initialCatalog))
    {
        db.OpenConnection();
        using (SqlCommand command = new SqlCommand())
        {
            command.CommandText = QUERY_GETMATCHEDRECORD;
            command.Parameters.AddWithValue("@DBID", DBID);
            command.Parameters.AddWithValue("@recID", recID);
            command.Parameters.AddWithValue("@recDocID",recDocID);
            dt = db.ExecuteDataTable(command);
        }
        db.CloseConnection();
    }

Upvotes: 2

Related Questions