Display data only from search parameters

[rattlerrFx]

Hi, I have a small program that is for my own use that has a small database. In my program i have a DataGrid to display the information. Right now when i search i have to search via columns, what i want is to search for data contained in the column rows and only display the data searched. Below is the code i have that will return information when a "Column" is entered into the textbox "tbSearch" but I need to be able to search the column rows. I have nine columns that will contain data and i need to search those column rows.

private void button_Click(object sender, RoutedEventArgs e)
{

        using (SqlConnection conn = new SqlConnection())
        {
            int result = 0;
            string searchOut = tbSearch.Text;
            //using (SqlConnection con = new SqlConnection())
            conn.ConnectionString = " Data Source=(LocalDB)\\\\MSSQLLocalDB;Database=cloudyhamdb.mdf;Trusted\_Connection=True;AttachDbFilename =|DataDirectory|cloudyhamdb.mdf; Integrated Security = True;";
            
            SqlCommand MyCommand = new SqlCommand("INSERT INTO clouddata " + " (First\_Name, Last\_Name, Grid\_Square, Country, State, Call\_Sign, Date\_Time, Mode, Power)" + " Values (@First\_Name, @Last\_Name, @Grid\_Square, @Country, @State, @Call\_Sign, @Date\_Time, @Mode, @Power)", conn);
            MyCommand.Parameters.Add("@First\_Name", System.Data.SqlDbType.Text);
            MyCommand.Parameters.Add("@Last\_Name", System.Data.SqlDbType.Text);
            MyCommand.Parameters.Add("@Grid\_Square", System.Data.SqlDbType.Text);
            MyCommand.Parameters.Add("@Country", System.Data.SqlDbType.Text);
            MyCommand.Parameters.Add("@State", System.Data.SqlDbType.Text);
            MyCommand.Parameters.Add("@Call\_Sign", System.Data.SqlDbType.Text);
            MyCommand.Parameters.Add("@Date\_Time", System.Data.SqlDbType.SmallDateTime);
            MyCommand.Parameters.Add("@Power", System.Data.SqlDbType.Text);
            MyCommand.Parameters.Add("@Mode", System.Data.SqlDbType.Text);

            MyCommand.Parameters\["@First\_Name"\].Value = Convert.ToString(tbFirstName.Text);
            MyCommand.Parameters\["@Last\_Name"\].Value = Convert.ToString(tbLastName.Text);
            MyCommand.Parameters\["@Grid\_Square"\].Value = Convert.ToString(tbGridSquare.Text);
            MyCommand.Parameters\["@Country"\].Value = Convert.ToString(tbCountry.Text);
            MyCommand.Paramete

[CHill60]

I may have missed something, but that is an INSERT statement. Surely a search would involve a SELECT from a pre-existing table. Something along the lines of

SqlCommand MyCommand = new SqlCommand("SELECT First_Name, Last_Name, Grid_Square, Country, State, Call_Sign, Date_Time, Mode, Power FROM clouddata WHERE First_Name = @First_Name", conn);
MyCommand.Parameters.AddWithValue("@First_Name", searchOut);

If the column you are going to search on can differ then you will need two textboxes or similar, one to name the column to search on and one to give the value to search on.

[rattlerrFx]

Can you provide an example for using 2 textboxes?

[CHill60]

Here is an example using a ComboBox and a TextBox. I've used a ComboBox with fixed entries for the user to choose from. I don't want them to enter the column name for a few reasons: They might misspell it, they might introduce other errors and because I'm using string concatenation to get the column name (only the name, NOT the value) it provides a further protection against SQL Injection.

  private void button1\_Click(object sender, EventArgs e)
    {
        if (cmbColumns.SelectedIndex == -1)
        {
            MessageBox.Show("You must select a column");
            return;
        }
        if (string.IsNullOrEmpty(tbSearch.Text))
        {
            MessageBox.Show("You must enter a value to search for");
            return;
        }

        // This bit would NOT normally be within the UI layer
        var conString = ConfigurationManager.ConnectionStrings\["ConnectToDB"\].ConnectionString;
        using (SqlConnection conn = new SqlConnection(conString))
        {
            conn.Open();
            using (var myCommand = new SqlCommand())
            {
                myCommand.Connection = conn;
                var sb = new StringBuilder(
                    "SELECT First\_Name, Last\_Name, Grid\_Square, Country, State, Call\_Sign, Date\_Time, Mode, Power ");
                sb.Append("FROM clouddata WHERE ");
                sb.Append(cmbColumns.SelectedItem);
                sb.Append("=@searchValue");
                myCommand.CommandText = sb.ToString();
                myCommand.Parameters.AddWithValue("@searchValue", tbSearch.Text);

                using (var adapter = new SqlDataAdapter(myCommand))
                {
                    var myTable = new DataTable();
                    adapter.Fill(myTable);
                    dataGridView1.DataSource = myTable;
                }
            }
        }
    }