Question How can I code so records filtered between two dates in DataGridView display in the Textboxs?

[Godis]

I am working on a project where I want records filtered between two given dates in the datagridview display in the form textboxes accordingly.

I have tried all I could but it is not working. I have also tried to place a label control as alternative to total up the filtered records on the datagridview but I couldn't code to make it successful.

Please I appreciate if someone can help me out on these two problems.

I hereby attach my filter button code for correction.

Thanks as I look forward for correction

 private void btnSearch_Click(object sender, EventArgs e)
        {
            
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
            con.Open();

            SqlDataAdapter da = new SqlDataAdapter("Select * From FarmHistory Where Date Between '"+fromDateTimePicker.Value.ToString()+"' And '"+toDateTimePicker.Value.ToString()+"'", con);
            DataTable dt = new DataTable();
            da.Fill(dt);       
            farmHistoryDataGridView.DataSource = dt;                         
          }

 

[Skydiver]

This is not a C# question, but rather a SQL question. The main issue is that the dates are likely not formatted properly for the current SQL settings. You will need to pass in the date strings as 'yyyy-mm-dd' or whatever your current SQL schema settings are for dates. The use of .ToString() will result in a date formatted for the current UI culture of the user.

To make this into a C# question instead of a SQL question, use a parameterized query and pass in the two DateTimes as parameters. The SqlCommand will take care of doing the right thing of passing the dates to the SQL server without have to worry about the current UI culture, and the current SQL settings.

 

[jmcilhinney]

As suggested, you should be using parameters. NEVER use string concatenation to insert values into SQL code like that. To learn more about why what you're doing is bad and what you should be doing instead, check out this blog post of mine.

I've also learned since writing that that there are good reasons not to use the AddWithValue method. Instead, use Add and specify the data type and, optionally, size of the data, then set the Value property of the parameter, e.g.

parameters.AddWithValue("@FirstName", this.firstNameField.Text);

would be better written:

parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = this.firstNameField.Text;

 

[Godis]

This is not a C# question, but rather a SQL question. The main issue is that the dates are likely not formatted properly for the current SQL settings. You will need to pass in the date strings as 'yyyy-mm-dd' or whatever your current SQL schema settings are for dates. The use of .ToString() will result in a date formatted for the current UI culture of the user.

To make this into a C# question instead of a SQL question, use a parameterized query and pass in the two DateTimes as parameters. The SqlCommand will take care of doing the right thing of passing the dates to the SQL server without have to worry about the current UI culture, and the current SQL settings.

Thanks to your usual assistance.

I did it but still it is giving me an error message that "The SelectCommand property has not been initialized before calling "Fill" ".

In fact am confused without knowing what to do. I wish to attach the parameters code for correction.

private void btnSearch_Click(object sender, EventArgs e)
        {
             try
            {
          
            con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
            con.Open();
        
           using( DataTable dt = new DataTable("FarmHistory"))
           {
             using(SqlCommand cmd = new SqlCommand("SELECT ID, Date, Month, Year, FarmLocation, CropType, BatchNo, Action, ActionDate, DateNursed, FirstHarvestDate, Comments FROM dbo.FarmHistory WHERE Date BETWEEN @fromDateTimePicker AND @toDateTimePicker", con))     
               {         
                //Add Values
                cmd.Parameters.AddWithValue("@fromDate", fromDateTimePicker.Value);
                cmd.Parameters.AddWithValue("toDate", toDateTimePicker.Value);
                //Fill Data to DataTable
                da.Fill(dt);
                //Add DataSource
                farmHistoryDataGridView.DataSource = dt;           
               }
           }
       }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
             con.Close();

 

[Skydiver]

The parameter names on lines 14 and 15 need to match the parameter names in your SQL command on line 11.

 

[Skydiver]

Then on line 17, you seem to have pulled presumably as SqlDataAdapter named da out of thin air. How did you connect the SqlCommand to this SqlDataAdapter ?

 

[Godis]

Then on line 17, you seem to have pulled presumably as SqlDataAdapter named da out of thin air. How did you connect the SqlCommand to this SqlDataAdapter ?

Please I have tried to create the projects newly and differently by using the attached code but it is still giving me an error message; this time differently. I still need a guideline to fix my error. Attached is the new code I am using and the error message also attached.

 private void btnSearch_Click(object sender, EventArgs e)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            SqlConnection con = new SqlConnection();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlParameter param = new SqlParameter();

            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
                con.Open();             

                cmd = new SqlCommand("SELECT ID, Date, Month, Year FROM dbo.SearchDate WHERE Date BETWEEN @fromDate AND @toDate");
              
                dt = new DataTable("SearchDate");

                string fromDate = cmd.ExecuteScalar().ToString();
                   cmd.Parameters.AddWithValue("@fromDate", fromDate);                 
                   cmd.Parameters.AddWithValue("@toDateDate", toDate);

                   this.searchDateTableAdapter.Fill(this.dateFilterDataSet.SearchDate);
                   da.Fill(dt);
                   searchDateDataGridView.DataSource = dt;
                                
            }
            catch (Exception ex)
            {
                
               MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
              
            }
            con.Close();

 

[Skydiver]

The error seems pretty self-explanatory. To fix it, set the Connection property.

 

[JohnH]

cmd = new SqlCommand("the query", con)

 

[Godis]

The error seems pretty self-explanatory. To fix it, set the Connection property.

Thanks.
However, after setting the connection property there is another error requesting "Must declare the scalar variable "@fromDate".

Please I will appreciate an example of declaring scalar variable @fromDate.

Attached are my code page and the error message.

 

[Skydiver]

The problem is likely that you are calling ExecuteScalar() before setting the parameters. Eg. You are executing line 18, but you add your parameters on lines 19 and 20.

It does not look like your attachments got attached.

 

[Skydiver]

And then later you'll likely run into the problem that ExecuteScalar() is supposed to return a single scalar value, but you could potentially have many rows be in between the dates.

You don't really need to execute the query. You need to give the SqlDataAdapter your SqlCommand object so that it will execute the query and put the results into a DataSet.

 

[Godis]

And then later you'll likely run into the problem that ExecuteScalar() is supposed to return a single scalar value, but you could potentially have many rows be in between the dates.

You don't really need to execute the query. You need to give the SqlDataAdapter your SqlCommand object so that it will execute the query and put the results into a DataSet.

I decided to remove the scalar stuff I could succeed in the date filter but when I run the application still there come another error message that "The SelectCommand property has not been initialized before calling 'Fill' ".

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

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

        }

        private void searchDateBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.searchDateBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.dateFilterDataSet);

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'dateFilterDataSet.SearchDate' table. You can move, or remove it, as needed.
            this.searchDateTableAdapter.Fill(this.dateFilterDataSet.SearchDate);
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            SqlDataAdapter da = new SqlDataAdapter();
            SqlConnection con = new SqlConnection();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand();
            SqlParameter param = new SqlParameter();

            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
                con.Open();

                cmd = new SqlCommand("SELECT ID, Date, Month, Year FROM dbo.SearchDate WHERE Date BETWEEN @fromDate AND @toDate", con);

                cmd.Parameters.AddWithValue("@fromDate", fromDate);
                cmd.Parameters.AddWithValue("@toDateDate", toDate);
        da.Fill(dt);
                this.searchDateTableAdapter.Fill(this.dateFilterDataSet.SearchDate, dateDateTimePicker.Value);

            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            con.Close();
        }
    }
}

Sorry my previous attachment could not successful.

Please I want to attach the whole code view. I wish it could be edited for me.

Best regards.

 

[JohnH]

Nothing in your code is associated with the da data adapter, you just create an instance and call Fill. For the Fill to be able to do its thing you must assign a select command to it, either by passing it to the constructor or later assigning it to the SelectCommand property.

SqlDataAdapter Class (System.Data.SqlClient)

Represents a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database. This class cannot be inherited.

docs.microsoft.com

 

[Godis]

Thanks so much for such a great assistance. I believe am now close to the solution.
I have modified the code as you advised. Yet still there is an error message.
How do I initialize the connection property of the the scalar?

I have attached the error message and the whole code for advise.

Thanks.

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

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

        }

        private void searchDateBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.searchDateBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.dateFilterDataSet);

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'dateFilterDataSet.SearchDate' table. You can move, or remove it, as needed.
            this.searchDateTableAdapter.Fill(this.dateFilterDataSet.SearchDate);
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            SqlDataAdapter searchDateTableAdapter = new SqlDataAdapter();
            SqlConnection con = new SqlConnection();
            DataTable dt = new DataTable("searchDate");
            SqlCommand cmd = new SqlCommand();
            DataSet dateFilterDataSet = new DataSet();
            SqlParameter param = new SqlParameter();

            try
            {
                con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
                con.Open();
              
              
                searchDateTableAdapter.SelectCommand = new SqlCommand("SELECT ID, Date, Month, Year FROM dbo.SearchDate WHERE Date BETWEEN @fromDate AND @toDate", con);

                string fromDate = cmd.ExecuteScalar().ToString();
                cmd.Parameters.AddWithValue("@fromDate", fromDate);
                cmd.Parameters.AddWithValue("@toDateDate", toDate);
                searchDateTableAdapter.Fill(this.dateFilterDataSet);
                return;

            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }
            con.Close();
        }
    }
}