title: Search string value from string array, if conditional then get sql database and display gridview or else show error
body: <p>I have tried several ways to do this but some reason I am not able to correctly implement the If statement.</p>
  
  <p>I am trying to acquire the <code>SelectedItem.Text</code> from <code>Drop Down List</code> and then search in the database for that row and store it in <code>DataSet</code> and then display it on a <code>GridView</code>. However, the database has 19 fixed columns with words, so if the user selects one drop down listing in the if statement the <code>DataSet</code> should get displayed on a <code>GridView</code> but if the data is not found in the database Else an error message should pop up saying "Data Not Found"</p>
  
  <p>I have tried physically inputting the 19 fixed <code>columns</code> in an <code>Array</code> and using the following:</p>
  
  <pre><code>protected void SearchProductButton_Click1(object sender, EventArgs e)
      {
          string Producttext = SearchProductDropDownList.SelectedItem.Text; 
          string[] Producttext2 = 
          {
              "Chem",
              "Drill",
              "Elect",
              "Products",
              "Environmental",
              "Instrument",
              "Lab",
              "Lift",
              "Tools",
              "Repair",
              "Pipes",
              "Portable",
              "Power",
              "Steel",
              "Testing",
              "Tooling",
              "Tubes",
              "Valves",
              "Systems",
              "Consumables",
          };
          using (var con = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True"))
          using (var daProduct = new SqlDataAdapter("SELECT [Serial_No], [Material_No], [Product_Line], [Product_Description], [Size], [UOM], [Supplier_Price], [Price_Date], [Selling_Price] FROM [ProductDB] WHERE [Product_Line] = @Product_Line", con))
          {
              foreach (string x in Producttext2)
              {
                  if (x.Contains(Producttext))                              
                  {
                      daProduct.SelectCommand.Parameters.Add("@Product_Line", SqlDbType.VarChar, 50).Value = Producttext;
                      DataSet dsProduct = new DataSet();
                      daProduct.Fill(dsProduct, "Product_Line");
                      SearchProductGridView.DataSource = dsProduct;
                      SearchProductGridView.DataBind();                        
                  }
                  else
                  {
                      MessageBox.Show("Data Not Found");
                  }
              }
  
          }
      }
  </code></pre>
  
  <p>In the SQL data I only have information filled for <code>Chem</code> &amp; <code>Elect</code> so all the other ones should show an error but due to the <code>Foreach</code> the <code>x</code>value` is not clearing and stays at the first run value so it ends up skipping until it goes through the whole list.</p>
  
  <p>I have modified the code after reading about the string to string array and then checking the <code>index</code> if it is <code>greater then -1</code> continue or <code>else</code> show the <code>error</code>.</p>
  
  <p>Here's the code:</p>
  
  <pre><code> protected void SearchProductButton_Click1(object sender, EventArgs e)
      {
          string Producttext = SearchProductDropDownList.SelectedItem.Text; 
          string[] Productarray = new string[20]; 
          Productarray[0] = "Chem";
          Productarray[1] = "Drill";
          Productarray[2] = "Elect";
          Productarray[3] = "Products";
          Productarray[4] = "Environmental";
          Productarray[5] = "Instrument";
          Productarray[6] = "Lab";
          Productarray[7] = "Lift";
          Productarray[8] = "Tools";
          Productarray[9] = "Repair";
          Productarray[10] = "Pipes";
          Productarray[11] = "Portable";
          Productarray[12] = "Power";
          Productarray[13] = "Steel";
          Productarray[14] = "Testing";
          Productarray[15] = "Tooling";
          Productarray[16] = "Tubes";
          Productarray[17] = "Valves";
          Productarray[18] = "Systems";
          Productarray[19] = "Consumables";
  
          using (var con = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True"))
          using (var daProduct = new SqlDataAdapter("SELECT [Serial_No], [Material_No], [Product_Line], [Product_Description], [Size], [UOM], [Supplier_Price], [Price_Date], [Selling_Price] FROM [ProductDB] WHERE [Product_Line] = @Product_Line", con))
          {
              //foreach (string x in Producttext2)
              //{
              int index1 = Array.IndexOf(Productarray, Producttext);
                  if (index1 !=-1)                             
                  {
                      daProduct.SelectCommand.Parameters.Add("@Product_Line", SqlDbType.VarChar, 50).Value = Producttext;
                      DataSet dsProduct = new DataSet();
                      daProduct.Fill(dsProduct, "Product_Line");
                      SearchProductGridView.DataSource = dsProduct;
                      SearchProductGridView.DataBind();                        
  
                  }
                  else
                  {
                      MessageBox.Show("Data Not Found");
                  }
              //}
  
          }
      }
  </code></pre>
  
  <p>The problem with the second approach is that it never goes to the else statement no matter what I try.</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I have decided to approach my requirement in a different way, instead of using <code>Textbox</code> I have sorted to using <code>ComboBox</code> from <code>AJAX ToolKit</code>. </p>
  
  <p>Here's the <strong>HTML</strong>:</p>
  
  <pre><code>&lt;ajaxToolkit:ComboBox ID="SearchMaterialComboBox" runat="server" 
          AutoCompleteMode="Append" AutoPostBack="false" CaseSensitive="false" 
          DataSourceID="ProductDBMaterialSqlDataSource" DataTextField="Material_No" 
          DataValueField="Material_No" DropDownStyle="DropDownList" Height="20px" 
          MaxLength="10" RenderMode="Block" style="display: inline-block;" Width="285px"&gt;
      &lt;/ajaxToolkit:ComboBox&gt;**strong text**
  </code></pre>
  
  <p><strong>Code</strong>:</p>
  
  <pre><code>protected void SearchMaterialButton_Click(object sender, EventArgs e)
      {
          //long Materialtextbox = Convert.ToInt64(SearchMaterialComboBox.Text);
          string Materialstr = SearchMaterialComboBox.Text;
          using (var con = new SqlConnection("Data Source=localhost;Initial Catalog=ROG;Integrated Security=True"))
          using (var daMaterial = new SqlDataAdapter("SELECT [Serial_No], [Material_No], [Product_Line], [Product_Description], [Size], [UOM], [Supplier_Price], [Price_Date], [Selling_Price] FROM [ProductDB] WHERE [Material_No] = @Material_No", con))
          {
              if (Materialstr != "")
              {
                  daMaterial.SelectCommand.Parameters.Add("@Material_No", SqlDbType.BigInt).Value = Materialstr;
                  DataSet dsMaterial = new DataSet();
                  daMaterial.Fill(dsMaterial, "Material_No");
                  SearchProductGridView.DataSource = dsMaterial;
                  SearchProductGridView.DataBind();
              }
              else
              {
  
              }
  
          }
      }       
  </code></pre>
  
  <p>I believe the last post mentioned by @Patashu had a valid point by inserting in the <code>HasRows</code> a <code>value</code> of <code>0</code>, if it is not <code>0</code> then continue else show an error message. However, the current approach has the <code>AutoComplete</code> option which is neat comparing to the conventional <code>TextBox</code> approach.</p>