How to display results in DataGridView using a text box as the input to a SQL query with parameters

Question

Using Visual Studio 2017, I'm trying to build a Windows Forms application that asks a user for specific value from a SQL Server Express database (footballteam).

User enters a value (@jnumber) in a text box (textBox1.Text) which corresponds to the "LIKE" for "JERSEYNUMBER".

Then, the query (commandText) is to be executed after clicking on button (button1_Click)

Results should display a DataGridView.

Build results were: "Build: 0 succeeded, 0 failed, 1 up-to-date, 0 skipped".

However, when running the application, user enters a number value for @jnumber in textBox.Text, then clicks on button (button1_Click), but dataGridView1 remains empty; no results.

Goal is also to avoid SQL injection. Appreciate your help.

Code is here:

// directives
using System;
using System.Data
using System.Windows.Forms;
using System.Data.SqlClient;

namespace displaydmlquery
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            // variable sql query
            var commandText = "SELECT * FROM JERSEY WHERE JERSEYNUMBER LIKE '%' + @jnumber+ '%' ORDER BY ASSIGNMENT_DATE";
            // variable connection string
            var connectionString = "Server=hostname\\SQLEXPRESS;Database=footballteam;User Id=userid;Password=password";

            // Create a connection instance
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // Create a SqlCommand instance
                SqlCommand command = new SqlCommand(commandText, connection);

                // Add the parameter to used in the text box input
                command.Parameters.Add("@jnumber", SqlDbType.NVarChar, 20).Value = textBox1.Text;

                // Execute query
                try
                {
                    // open connection
                    connection.Open();

                    // create a SqlDataAdapter to execute query
                    var dataAdapter = new SqlDataAdapter(commandText, connectionString);

                    // Create command builder
                    var commandBuilder = new SqlCommandBuilder(dataAdapter);

                    // Execute query reader
                    command.ExecuteReader();

                    // create a data table to hold query
                    DataTable dtRecord = new DataTable();

                    // fill in data tbale
                    sqlDataAdap.Fill(dtRecord);

                    // Display results in  DataGridView
                    dataGridView1.DataSource = dtRecord;
                }
                catch
                {
                    // Handle exception, future code
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}

Answer 1

I was trying to hint at what your problems were in the comments but I failed so I'm taking the time to give you a complete answer.

Below is what your code is doing. You'll notice that some of the lines have question marks. These are the troubling lines because they don't make any sense. It seems you're confusing what the different data objects are meant to do.

 1. set up a sql string
 2. set up a connection string
 3. create a connection Object from connection string from (2)
 4. create a command Object from the sql string and the connection object from (1) and (3) 
 5. set the value of the parameter on the command object from (4)
 6. open the connection Object from (3)
 7. create a DataAdapter object and a new connection Object (???) 
     and a new command Object (???) from (1) and (3)        
 8. create commandBuilder and generate INSERT UPDATE and DELETE commands Objects (???) from the data adapter (7)  
 9. execute the command object from (4). Disregard the results (???)
 10. create a new DataTable Object
 11. fill the DataTable from (10) with an unknown sqlDataAdap (does it have 
       connection, sql, or parameters associated ????)
 12. set the DataSource on the datagrid to the filled(?) datatable from (10)        
 13. throw away exceptions (???)
 14. close the connection
 15. dispose the connection

Here's some code that should work

using (SqlConnection connection = new SqlConnection(connectionString))
{

    // Create a SqlCommand instance
    SqlCommand command = new SqlCommand(commandText, connection);

    // Add the parameter to used in the text box input
    command.Parameters.Add("@jnumber", SqlDbType.NVarChar, 20).Value = textBox1.Text;


    // open connection
    connection.Open();

    // create a SqlDataAdapter using the command object with the parameters set
    var dataAdapter = new SqlDataAdapter(command, connectionString);

    // create a data table to hold query
    DataTable dtRecord = new DataTable();

    // fill in data table with the dataAdapater
    dataAdapter.Fill(dtRecord);

    // Display results in  DataGridView
    dataGridView1.DataSource = dtRecord;
} // Using will close the connection when it disposes it

Answer 2

Please try with updating your query

        // variable sql query
        var commandText = "SELECT * FROM JERSEY WHERE JERSEYNUMBER LIKE '%'  @jnumber '%' ORDER BY ASSIGNMENT_DATE";

and add parameter as you are already doing....