Populating Values For DataGridView from DataBase based on the condition

Question

Hi All,

I am creating an application where in I want to display the rows in DataGridView based on the values entered by the user in the textbox.

For Eg. If the User enters the BookName in the textbox all the details regarding that book should be displayed in a DataGridView.

I have used the following codings:

            SqlConnection objSqlConnection = new SqlConnection();
            string connectionStringSettings = "Data Source =.; Initial Catalog = LibrarySystemManagement;Integrated Security = SSPI";
            private void btnSearch_Click(object sender, EventArgs e)
            try
            {
                objSqlConnection.ConnectionString = connectionStringSettings;
                objSqlConnection.Open();

                if ((txtBookName.Text != "") || (txtCategory.Text != ""))
                {

                    SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter("select * from LIBRARYBOOKDETAILS where Title = '"+txtTitle.Text+"'", objSqlConnection);
                    SqlCommandBuilder objSqlCommandBuilder = new SqlCommandBuilder(objSqlDataAdapter);
                    DataTable objDataTable = new DataTable();
                    objSqlDataAdapter.Fill(objDataTable);
                    BindingSource objBindingSource = new BindingSource();
                    objBindingSource.DataSource = objDataTable;
                    dataGridView1.DataSource = objBindingSource;
                    objSqlDataAdapter.Update(objDataTable);
                    objSqlConnection.Close();
                }
            }
            catch (Exception e1)
            {
                MessageBox.Show(e1.Message + e1.Source);
            }

But the above code displays all the rows entered in the table. I mean the rows are not retrived based on the condition.

Can anyone help me out in finding the correct set of code snippet used to retrieve data based on the condition?

Please Help me out.

Thanks in advance.

Answer 1

You're opening yourself up to SQL injection by directly accepting user input, although that's a side issue. Why are you calling objSqlDataAdapter.Update(objDataTable); in this section?

Although everything else looks fine I would try 2 things:

1. Remove the objSqlDataAdapter.Update(objDataTable); call - nothing has been modified, so what is being updated? At this point you should only be selecting data. This is also modifying the table when it's updated, and the table acts as your BindingSource's datasource.

2. Change the select command to use parameters and see if this makes a difference. This step has the added benefit of preventing SQL injection.

So change this:

SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter("select * from LIBRARYBOOKDETAILS where Title = '"+txtTitle.Text+"'", objSqlConnection);

To this:

SqlCommand command = new SqlCommand("select * from LIBRARYBOOKDETAILS where Title = @Title", objSqlConnection);
command.Parameters.AddWithValue("@Title", txtTitle.Text);

SqlDataAdapter objSqlDataAdapter = new SqlDataAdapter(command);