Create Dataset from Dataset

ssabc

Well-known member
Joined
Dec 11, 2017
Messages
63
Programming Experience
10+
I have a dataset that is defined in my project already.

I would like to populate my datagridview with this dataset, after being filtered through this query:

C#:
        private void updatedgv(DataGridView dgv)
        {
            // this.timeDataTableAdapter1.Fill(this.timeDataSet.TimeData);
            DataTable dt_TimeData = timeDataSet.Tables["TimeData"];


            var qryTimeDataSource = from a in dt_TimeData.AsEnumerable()
                                    orderby a.Field<String>("EmployeeNo")
                                    where (a.Field<String>("EmployeeNo") == txtEmployeeNumber.Text)
                                    && (a.Field<DateTime>("Date") == dateTimePicker1.Value)
                                    select a;


            // How do I convert the above query to what I need to fill the table adapter with?
            this.timeDataTableAdapter1.Fill("I believe a new table from the above query goes here...");
            
            // This is wrong...
            dgv.DataSource = qryTimeDataSource;

Questions contained within the above code.

Basically, are these steps correct?
  1. DataTable
  2. Query
  3. Fill Table Adapter with Table (derived from query)

Thanks!
 
You're going about this the wrong way. Either you want all the data or you don't. If you do, retrieve all the data and filter it locally using a BindingSource. If you don't want all the data then add a new query to your table adapter that does the filtering and then you can just retrieve the data you need. Either way, you can sort locally using the BindingSource.

If you have dragged a table from the DataSources window then you already have a BindingSource. Calling Fill on your table adapter in the Load event handler will get all the data from your database table into your DataTable. That DataTable will be bound to the BindingSource and the BindingSource will be bound to the DataGridView. If you want the data to be sorted by EmployeeNo then specify that in the BindingSource:
timeBindingSource.Sort = "EmployeeNo";

You can then filter like so:
timeBindingSource.Filter = $"EmployeeNo = '{txtEmployeeNumber.Text}' AND Date = #{dateTimePicker1.Value:M/dd/yyyy}#";

If you'd rather just retrieve the data you need when you need it, right-click your table adapter in the DataSet designer and add a new query. The SQL code would look something like this:
C#:
SELECT * FROM Time WHERE EmployeeNo = ? AND [Date] = ?
and you would name the corresponding methods FillByEmployeeNoAndDate and GetDataByEmployeeNoAndDate. When it come to retrieving data, you'd call FillByEmployeeNoAndDate instead of Fill and pass the filter values, i.e. txtEmployeeNumber.Text and dateTimePicker1.Value, as arguments.
 
Ok, this is good. And yes, I would prefer to have all the data populated up front, which is happening.

C#:
        private void updatedgv(DataGridView dgv)
        {
            this.timeDataTableAdapter1.Fill(this.timeDataSet.TimeData);
            DataTable dt_TimeData = timeDataSet.Tables["TimeData"];
            
            timeBindingSource.Filter = $"EmployeeNo = '{txtEmployeeNumber.Text}' AND Date = #{dateTimePicker1.Value:M/dd/yyyy}#";

            Debug.WriteLine(dateTimePicker1.Value.ToString("yyyy-MM-dd"));

        }

However, I am not sure the filter is behaving correctly, or if I am missing a step. Also, per one of my other posts I am still trying to populate datasource data in the combo boxes, and have the value be the record. The program gets stuck here, error below:

Screen Shot 06-25-18 at 03.29 PM.PNG

Thanks for your patience.
 
Your code is still not right. You should not be querying the database again when you want to filter. You already have all the data so all you need to set the Filter of the BindingSource. You query the database once only, probably in the Load event handler of the form. By default, all data will be displayed in the grid. If you want no data displayed by default then you can set the Filter of the BindingSource to "EmployeeNo = ''".

Fix that and see if your issue goes away. If it doesn't, we can look more closely.
 
If I changed the combobox values to textbox values in the datagridview, this would be solved. However, I am looking to populate pre-selected combobox values. Where is the query in my code, since the filter is not a query?
 
Without knowing everything about your application, here is what I would expect to be the sequence of events:

1. In the Load event handler of the form, you populate three DataTables by calling Fill on the appropriate table adapters. You would populate the two DataTables bound to your combo box columns first, then you would populate the DataTable bound to the grid.
2. The grid will display every record in the bound DataTable by default. If you want to display no data by default, you would set the Filter property of the BindingSource bound to the grid to a value that will exclude every record, e.g. "EmployeeNo = ''".
3. Write a single method that sets the Filter of the BindingSource bound to the grid based on all the filter controls. Call that method whenever the user makes a change in any of those filter controls.

That's basically all there is to it. I guess one question is whether you actually have bound those combo box columns to your DataSet. If you haven't you should. That would mean adding a BindingSource for each and then binding those to the DataSet (select the DataSet as the DataSource and the appropriate DataTable as the DataMember) and then bind the columns to the appropriate BindingSource (set the BindingSource as the DataSource, the column to display as the DisplayMember and the primary key column as the ValueMember).
 
If I changed the combobox values to textbox values in the datagridview, this would be solved. However, I am looking to populate pre-selected combobox values. Where is the query in my code, since the filter is not a query?
 
If you're just going to repeat your previous post and ignore what I posted then we can't be friends any more. I suggest that you learn how to use a combo box column in a DataGridView properly. I've told you what you should do. You can either do it or not.
 
Sorry, I did not know it was posted twice. It looked like it was not posted, and I thought I wrote it and forgot to hit send.

Thank you for your help!!!
 
jmc:

I have converted the dgv to all textboxes for now. I will deal with the cbo thing later. The grid is populating well.

Can you tell me, why I cannot fill the jobs table adapter (code below)?

Screen Shot 06-27-18 at 08.47 AM.PNG

Screen Shot 06-27-18 at 08.47 AM 001.PNG

Screen Shot 06-27-18 at 08.50 AM.PNG

Thanks again... I think its starting to make more sense!
 
Firstly, please don't post images of code or error messages. They are both text so there's no reason not to post them as text. I want to copy and paste your code and now I can't. Code should be posted inside formatting tags, i.e.


[xcode=c#]your code here[/xcode]

or, if you need extra formatting, e.g. bold or colour:

[code]your code here[/code]

while error messages should be posted as quotes.

As for the issue, the error message seems to suggest that you have two different typed DataSets defined in your project and you are using a table adapter from one with a DataTable from the other. The error message (which again I can't copy and paste because you posted an image) indicates that the DataTable you're using is part of a typed DataSet named Time while the table adapter is expecting one that is part of a typed DataSet named ResourcePlanningDataSet.
 
jcm:

There are two datasets in the project, but this table is only using the one called time, which is represented in the non-code picture of my last post.

The Jobs table exists in both datasets. If I click on it and run the fill query, it does point to the correct data.

By this logic, should I not be able to fill it?
C#:
this.jobsTableAdapter1.Fill(this.timeDataSet.Jobs);

The jobs table adapter filled with the jobs table in the time dataset??

Thanks!
 
Got It:

jcm:

Changes these two lines, and everything started working. Not sure how this got there to begin with, on the auto generation...


C#:
            //this.jobsTableAdapter1 = new ResourcePlanningC2.ResourcePlanningDataSetTableAdapters.JobsTableAdapter();
            this.jobsTableAdapter1 = new ResourcePlanningC2.TimeTableAdapters.JobsTableAdapter();
            //

            //private ResourcePlanningDataSetTableAdapters.JobsTableAdapter jobsTableAdapter1;
            private TimeTableAdapters.JobsTableAdapter jobsTableAdapter1;
            //

Thanks for the help though, as it got me in the right direction. Have a great weekend!
 
Back
Top Bottom