title: How to pass value to sql command in c#
body: <p>I have a SQL command:</p>
  
  <pre><code>string keyProcesses = "SELECT distinct STUFF((SELECT ', '+ cn.name  from WMCCMCategories cn  INNER JOIN CategorySets uc ON uc.categoryId = cn.categoryID INNER JOIN KeyProcesses u ON u.categorySetId = uc.setId  INNER JOIN Companies c ON c.companyId = u.companyId  WHERE c.companyName = @companyName  ORDER BY cn.name FOR XML PATH('')), 1, 1, '') AS listStr FROM WMCCMCategories cnn Group by cnn.name";
  </code></pre>
  
  <p>And I have:</p>
  
  <pre><code>public SqlCommand sqlCmd = new SqlCommand();
  sqlCmd.CommandText = commandText; //where commandText is another SQL statement
  sqlCmd.Parameters.Clear();
  </code></pre>
  
  <p>Then I execute connection:</p>
  
  <pre><code>string connectionString = ConfigurationSettings.AppSettings["connectionString"];
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
      SqlCommand command = new SqlCommand(commandText, connection);
      try
      {
          connection.Open();
          SqlDataAdapter sdr = new SqlDataAdapter(sqlCmd.CommandText, connection);
          DataTable dt=new DataTable();
          sdr.Fill(dt)
  </code></pre>
  
  <p>After that I want to move to the next SQL command and add parameter to that command:</p>
  
  <pre><code>sqlCmd.CommandText = keyProcesses;
  sqlCmd.Parameters.AddWithValue("@companyName", compnyName);
  SqlDataAdapter sdr1 = new SqlDataAdapter(sqlCmd.CommandText, connection);
  DataTable dtb1 = new DataTable();
  sdr1.Fill(dtb1);
  </code></pre>
  
  <p>But it fail to execute this <code>SqlCommand</code>. I already make sure that the SQL key process can run properly with a fix parameter pre-added in. The <code>compnName</code> is not empty. The only suspect here is how to add value to sql parameter. I don't know what is wrong in my way of using it ?</p>
  
  <p>Edit: I update the whole part of code for the easy of investigating the issue:</p>
  
  <pre><code>string connectionString = ConfigurationSettings.AppSettings["connectionString"];
              using (SqlConnection connection = new SqlConnection(connectionString))
              {
                  SqlCommand command = new SqlCommand(commandText, connection);
  
                  try
                  {
                      connection.Open();
  
                      SqlDataAdapter sdr = new SqlDataAdapter(sqlCmd.CommandText, connection);
                      DataTable dt=new DataTable();
                      sdr.Fill(dt);
                          //To store the values of company names:
                      List&lt;CompanyModel&gt; companies = new List&lt;CompanyModel&gt;();
  
  
                          for(int i = 0; i &lt; dt.Rows.Count; i++)
                          {
                              companies.Add(new CompanyModel 
                              { 
                                  compnSN = i + 1,
                                  compnName =  dt.Rows[i]["companyName"].ToString(),
                                  compnAddress =  dt.Rows[i]["address"].ToString()
                              });
                          }
  
                          companyRepeater.DataSource = companies;
                          companyRepeater.DataBind();
  
                          string comName = dt.Rows[0]["companyName"].ToString();
                          var names = companies.Select(c =&gt; c.compnName);
  
                          string[] arr = names.ToArray();
                          foreach (string compnyName in arr)
                          {
                              //To write names to file for debugging purpose
                              using (StreamWriter _testData = new StreamWriter(Server.MapPath("~/File.txt"), true))
                              {
  
                                  _testData.WriteLine(compnyName); // Write the file.
                              }
  
  
                                //Get KeyProcesses
                                sqlCmd.CommandText = keyProcesses;
                                //sqlCmd.Parameters.AddWithValue("@companyName", compnyName);
                                sqlCmd.Parameters.Add("@companyName", SqlDbType.NVarChar);
                                sqlCmd.Parameters["@companyName"].Value = compnyName;
                               // SqlDataAdapter sdr1 = new SqlDataAdapter(sqlCmd.CommandText, connection);
                                DataTable dtb1 = new DataTable();
                                //sdr1.Fill(dtb1);
                                using (var reader = sqlCmd.ExecuteReader())
                                {
                                    dtb1.Load(reader);
                                }
  
                                companies.Add(new CompanyModel
                                {
                                    compnKeyProcesses = dtb1.Rows[0][0].ToString()
  
                                });
  </code></pre>