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();
                }
            }
        }
    }
}

new SqlDataAdapter(commandText should be new SqlDataAdapter(command — Conrad Frix
– Conrad Frix, Commented Jan 8, 2018 at 19:38
A much easier way is to load the DataTable with all Jersey number and just filter it as desired. Hitting the server repeatedly is rather inefficient — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Jan 8, 2018 at 19:40
I just realized it's more than that what I previously wrote. sqlDataAdap which fills the table isn't defined in your coding snippit so I have no idea what it's doing. — Conrad Frix
– Conrad Frix, Commented Jan 8, 2018 at 19:45
dataGridView1.Refresh() try that see if it refreshes the datagridview — MethodMan
– MethodMan, Commented Jan 8, 2018 at 19:53
First of all you sql appears to be just fine. The code however is crippling your ability to function. You have an anti-pattern I call try/squelch. Which is, you have a catch that does nothing expect capture any error and throw it away and pretend like nothing happened. Get that catch out of your code. — Sean Lange
– Sean Lange, Commented Jan 8, 2018 at 20:22

Conrad Frix · Accepted Answer · 2018-01-08 22:44:35Z

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

Conrad Frix + All. Thank you. It worked. For var dataAdapter = new SqlDataAdapter(command); Also, I made two mistakes. 1) I made a typo in the database name in my actual code. 2) I was impatient not waiting enough for debugger to complete as it pointed "1)" out this error. Again, thank you.

Ehsan Ullah Nazir · Accepted Answer · 2018-01-08 19:57:23Z

0

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....

answered Jan 8, 2018 at 19:57

Ehsan Ullah Nazir

1,9371 gold badge15 silver badges21 bronze badges

14 Comments

Sean Lange Over a year ago

you need to concatenate the wildcards and the parameter.

Ehsan Ullah Nazir Over a year ago

you mean like "SELECT * FROM JERSEY WHERE JERSEYNUMBER LIKE '%' " + @jnumber + " '%' ORDER BY ASSIGNMENT_DATE"; ?

Sean Lange Over a year ago

NO!!! That would circumvent using parameters. It would be like this. "SELECT * FROM JERSEY WHERE JERSEYNUMBER LIKE '%' + @jnumber + '%' ORDER BY ASSIGNMENT_DATE"

Ehsan Ullah Nazir Over a year ago

thats what he is already doing, please view link parameterized query

Sean Lange Over a year ago

Right and the sql they posted is correct. The issue is that the grid is empty.

|

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

14 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

14 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related