I am trying to parameterized Sql query but I am getting the error"'Must declare the scalar variable "

Question

I am trying to create a parameter for all those values that is mentioned below in the code itself.

Any help would be appreciated!

This is what I did:

public static void Main()
    {

        {
            string testEnv = ClaimQuery.ClaimQueryhelper.getTestEnv();
            // calls claimQueryhelper for the test environment
            var connectionString = ClaimQuery.ClaimQueryhelper.getConnection(testEnv);
            // if we are not calling helper then the environment is hard coded
            //string connectionString = ConfigurationManager.ConnectionStrings["aitf"].ConnectionString;
            //ConsoleWindows.Properties.Settings.Default.ConnectionString;

            using (SqlConnection con = connectionString)
            {

                ///Sql to get the data from the Dbase
                string sql = "SELECT TOP 3 claim.CLCL_MICRO_ID,cdml.LOBD_ID,cdml.IDCD_ID,CLMD_TYPE, " +
                    "* FROM CMC_CLCL_CLAIM claim INNER JOIN CMC_CDML_CL_LINE cdml ON claim.CLCL_ID = cdml.CLCL_ID" +
                    " INNER JOIN CMC_CLMD_DIAG clmd ON claim.CLCL_ID = clmd.CLCL_ID WHERE CLCL_CUR_STS =@CLCL_CUR_STS AND @CLCL_CL_SUB_TYPE AND @CLCL_RECD_DT and @CLMD_TYPE ";

                //connecting to the Sql server
                using (SqlCommand command = new SqlCommand(sql, con))
                 //Reading the Sql database
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    command.Parameters.Add(
                        new[]
                            {
                             new SqlParameter(@"CLCL_CUR_STS", SqlDbType.Int).Value = "01",
                             new SqlParameter(@"CLCL_CL_SUB_TYPE", SqlDbType.VarChar).Value = "M",
                              new SqlParameter(@"CLMD_TYPE", SqlDbType.VarChar).Value = "01",
                              new SqlParameter(@"CLCL_RECD_DT", SqlDbType.DateTime).Value = "2017-02-03 00:00:00.000",
                             });

Answer 1

The parameter values for SqlCommand query string (i.e. array of SqlParameter) must be iterated & assigned to the command before DataReader opens by using ExecuteReader. See this example below:

using (SqlConnection con = new SqlConnection(connectionString))
{
     // SQL query to get the data from the database
     string sql = @"[query string here]";
     con.Open();

     // connecting to the SQL Server
     using (SqlCommand command = new SqlCommand(sql, con))
     {
         SqlParameter[] parameters = new[]
         {
             new SqlParameter(@"CLCL_CUR_STS", SqlDbType.Int).Value = "01",
             new SqlParameter(@"CLCL_CL_SUB_TYPE", SqlDbType.VarChar).Value = "M",
             new SqlParameter(@"CLMD_TYPE", SqlDbType.VarChar).Value = "01",
             new SqlParameter(@"CLCL_RECD_DT", SqlDbType.DateTime).Value = "2017-02-03 00:00:00.000",
         };

         foreach (SqlParameter sqlParam in parameters)
         {
             command.Parameters.Add(sqlParam);
         }

         // Reading the SQL database
         using (SqlDataReader reader = command.ExecuteReader())
         {
             // fetch retrieved data
         }
     }

     // other stuff

     con.Close();
}

Must declare the scalar variable error obviously means that one or more of provided query parameters are not declared during query statement execution (they're wrongly declared after the DataReader has already open).