To retrieve data based on field value selected from a combo box and search key

[shanboy]

I'm a beginner in .net programming environment.I am using c#.

i have a text box(txtSearchKey) for user to enter the text key.
i have a combo box(cmbSearchBy) to specify in which field of employee table ,search key should be used to retrieve data.

when i use the above code i get with all fields and a blank record,even when i tried with all fields.

Should i use cmd.ExecuteReader();,If so how?.

Plase help me out.

SqlCommand cmd = new SqlCommand("Select *  from employees where '"+cmbSearchBy.SelectedItem+"' = '"+txtSearchKey.Text+"' ", newcon);
                int result=Convert.ToInt32(cmd.ExecuteNonQuery());

                



                SqlDataAdapter da=new SqlDataAdapter(cmd);
                DataSet ds =new DataSet();

                da.Fill(ds);

                dgv1.DataSource=ds.Tables[0].DefaultView;

[jbisono]

First you do not need this line

int result=Convert.ToInt32(cmd.ExecuteNonQuery());

, in case you need it, you do not have to convert cmd.ExecuteNonQuery() to int because already return an integer value.

Second at the end of your code add this line

dgv1.DataBind();

if that does not work, i suggest that you verify your select statement and make sure is building what you expect. for example put in comment all your code and just display your statement string in another label or text to double check is right.

Regards.

[sknake]

You need to clean up the SQL classes you're instantiating as well. They implement IDisposable and should be disposed of:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace daniweb
{
  public partial class frmSqlSearch : Form
  {
    public frmSqlSearch()
    {
      InitializeComponent();
    }

    private static string GetText(List<string> Sql)
    {
      StringBuilder sb = new StringBuilder();
      for (int i1 = 0; i1 < Sql.Count; i1++)
        sb.AppendLine(Sql[i1]);
      return sb.ToString();
    }
    public static string BuildSqlNativeConnStr(string server, string database)
    {
      return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
    }
    private void button1_Click(object sender, EventArgs e)
    {
      string connStr = BuildSqlNativeConnStr("apex2006sql", "Leather");

      List<string> Sql = new List<string>();
      Sql.Add("Select *");
      Sql.Add("From [Employees]");
      Sql.Add(string.Format("Where [{0}] = @Value", cmbSearchBy.Text));

      using (SqlConnection conn = new SqlConnection(connStr))
      {
        conn.Open();
        string query = GetText(Sql);
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
          cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarChar)).Value = txtSearchKey.Text;
          using (SqlDataReader dr = cmd.ExecuteReader())
          {
            DataTable dt = new DataTable();
            dt.Load(dr);
            //dgv1.DataSource = dt;
            //dgv1.DataBind();
          }
        }
        conn.Close();
      }
    }
  }
}