ADO.net query from coming from APP.config not working

Question

Below is my sql query written in APP.Config

<add key="query" value="Select *
     from POManage po 
     Where po.VendorID = @manufacturerid
     and po.order_date between @StartDate and @EndDate order by PONumber asc"/>

Here is my Program .cs code

 int manufacturerid = 32;     
 DateTime StartDate = DateTime.Now.AddDays(-1);
 DateTime EndDate = DateTime.Now;
 SqlCommand cmd = new SqlCommand(query, con);
 cmd.Parameters.Add("@manufacturerid", SqlDbType.BigInt,manufacturerid);
 cmd.Parameters.AddWithValue("@StartDate", StartDate);
 cmd.Parameters.AddWithValue("@EndDate", EndDate);
 cmd.CommandType = CommandType.Text;
 SqlDataAdapter ad = new SqlDataAdapter(query, con);
 ad.Fill(ds);

But it is showing error for @manufacturerid Must declare the scalar variable.

Similar issue is coming for StartDate and EndDate.

Answer 1

firstly, It's a very bad idea to keep the command query in the appconfig

You have already created a command with query. You need pass the command to Adapter. not query.

 int manufacturerid = 32;     
 StartDate = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd");
 EndDate = DateTime.Now.ToString("yyyy-MM-dd");
 SqlCommand cmd = new SqlCommand(query, con);
 cmd.Parameters.Add("@manufacturerid", SqlDbType.BigInt,manufacturerid);
 cmd.Parameters.AddWithValue("@StartDate", StartDate);
 cmd.Parameters.AddWithValue("@EndDate", EndDate);
 cmd.CommandType = CommandType.Text;
 SqlDataAdapter ad = new SqlDataAdapter(cmd);
 ad.Fill(ds);

and change Where po.VendorID = '@manufacturerid' to Where po.VendorID = @manufacturerid in your query

update:

        int manufacturerid = 32;
        var query = @"Select * from POManage po
            Where po.VendorID = @manufacturerid
            and CONVERT(DATE, po.order_date) between @StartDate and @EndDate 
            order by PONumber asc";
        var startDate = DateTime.Now.AddDays(-1);
        var endDate = DateTime.Now;
        var connectionString = "your connection string";
        var dataTable = new DataTable();
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(query, con))
            {
                adapter.SelectCommand.Parameters.AddWithValue("@manufacturerid", manufacturerid);
                adapter.SelectCommand.Parameters.AddWithValue("@StartDate", startDate);
                adapter.SelectCommand.Parameters.AddWithValue("@EndDate", endDate);
                adapter.Fill(dataTable);
            }
        }