Error - There is already an open DataReader associated with Command which must be closed first

[Lin100]

When I click on listBox1, I have 2 error messages

ERROR:
There is already an open DataReader associated with Command which must be closed first
this.unitTableAdapter1.FillBy(this.dataSet_Property_Name_And_Unit_List.Unit); Line 49

ERROR:
System.ArgumentNullException: 'Value cannot be null. Parameter name: key'
catch (System.Exception ex) Line 51

///////////////////////////////////////

DataSet_Property_Name_And_Unit_List.xsd --> Right Click - -> Open --> UnitTableAdapter --> Right Click --> Configure

SELECT Property_Name, Unit_Number FROM Unit

///////////////////////////////////////

DataSet_Unit_Detail.xsd --> Right Click - -> Open --> UnitTableAdapter --> Right Click --> Configure

SELECT Unit_Number, Property_Name, Rental_Cost, Security_Deposit, Bedroom, Bathroom, Square_Feet, Status
FROM Unit WHERE (Property_Name = '@Property_Name')

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

namespace Retrieve_Data_From_Access_With_Navigation_Button
{
    public partial class Form3 : Form
    {
        public Form3()
        {
           InitializeComponent();
        }

        private void unitBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
           this.Validate();
           this.unitBindingSource.EndEdit();
           this.tableAdapterManager.UpdateAll(this.dataSet_Unit_Detail);
        }

        private void Form3_Load(object sender, EventArgs e)
        {
           this.unitTableAdapter1.Fill(this.dataSet_Property_Name_And_Unit_List.Unit);
           this.unitTableAdapter.Fill(this.dataSet_Unit_Detail.Unit);
        }

        private void fillByToolStripButton_Click(object sender, EventArgs e)
        {
            try
            {
                this.unitTableAdapter1.FillBy(this.dataSet_Property_Name_And_Unit_List.Unit);
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
              this.unitTableAdapter1.FillBy(this.dataSet_Property_Name_And_Unit_List.Unit);
            }
            catch (System.Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
            }
        }
    }
}

 

[Lin100]

If I recall corectly, your column names have spaces in them. Do yourself a favour and remove the spaces.. Always a terrible idea to use characters that mean column names need quoting

I do not use spaces in the column names because I know that it is a bad idea. Instead, I used underscore as in Unit_Number. If there is a specific column name that you think have spaces in it, then specify it because I cannot find any that does.

 

[cjard]

Your query works for me on the database you gave:

GIVE THE FILL METHOD A SENSIBLE NAME:

 

[cjard]

BTW, i do not recommend to make the main query on a table adapter one that selects by something other than the primary key

Make the main query one that selects ID:

You can add additonal queries tothe same TA for other things:

Making the main query select all columns by the PK is useful for loading related data in a dataset that has relations; you can load eg 100 child records then enumerate them loading the parent records. You can also have a secondary query on a child tableadapter that loads by the parent ID (if youre loading parent records first)

 

[cjard]

I use just a ? and I got Error CS7036 Line 4
There is no argument given that corresponds to the required formal parameter
'Property_Name' of 'UnitTableAdapter.Fill(DataSet_Unit_Detail.UnitDataTable, string)'
Retrieve_Data_From_Access_With_Navigation_Button

private void Form3_Load(object sender, EventArgs e)
 {
   this.unitTableAdapter.Fill(this.dataSet_Unit_Detail.Unit);   <----- ERROR CS7036
 }

When you write a query with a ? in it, you have to provide two arguments to FillByXXX; one is the data set, the other is what you want the ? to be replaced with

When you have two ? in a query you will have to provide three arguments to FillByXXX. When you write a query with N ? symbols, you have to provide N+1 arguments to FillByXxx


See the edited post 15 and the follow on posts for further advice

 

[Lin100]

When you write a query with a ? in it, you have to provide two arguments to FillByXXX; one is the data set, the other is what you want the ? to be replaced with

View attachment 2630

When you have two ? in a query you will have to provide three arguments to FillByXXX. When you write a query with N ? symbols, you have to provide N+1 arguments to FillByXxx


See the edited post 15 and the follow on posts for further advice

Can you upload the entire project so I can run it and look at it ?

 

[cjard]

Here you go. I've fixed up your database so it's easier to work with; done away with the property name being a primary key and made property table have an autonumber ID PK and unit the same, and also a number column in Unit that relates to Property. This is how most engineers would create related tables rather than using data that could change. I've left the update query that fixed the relationship up inside the DB if you want to read it. The basic process was "add autonumber columns to each table, add number column called PropertyId to Unit table, use update query to set Unit.PropertyId to the related parent property, based on existing property_name column, delete unit.property_name, and make ID columns good to go in both tables"

-

In the app I've created a form that shows you how to fill data using parameters, how to create tableadapters sensibly (the main query selects by ID, then other queries add utility by e.g. selecting by name, or by parent id so that related data can be loaded easily), and how one might load related data - I suggest you put %i% in the "fill by name" box, so that Addison and Sakowitz are loaded as parents, and then the related child records are loaded for them, and you can select one or the other parent and see the left child grid of related data change.

Pay attention to how the datasource and datamember properties are used on the grids and bindingsources - this is critical to understanding working with bound, related data

Also, and the bit that I've been banging on about being most important; look at the naming of everything, and think about how confusing as heck it would be if I'd just called everything "tableAdapter1", "tableAdapter2", "Fill3", "Fill4" etc.. This code doesn't really need the comments I've added because from the names alone it reads like a book, but the comments help explain why some of the code is the way it is. This app has been done as an exercise to help you learn, so it would have been a terrible thing to do, to fill it up with poor or misleading names. I realize I'm going on about it a lot, but always strive to render this same courtesy to other developers when you ask their help; give them code that is easy to read, and that explains itself quickly so they don't have to jump through hoops to figure out what is going on. Unless you're paying them, of course, in which case the harder you make it, the more you'll have to pay so yay for them

*The only thing I didn't rename was the block of nested stuff the designer created - datagridview columns and bindingnav widgets.. but I don't interact with them in any way in this code. If I did interact with them, I would rename them too

 

[Lin100]

Here you go. I've fixed up your database so it's easier to work with; done away with the property name being a primary key and made property table have an autonumber ID PK and unit the same, and also a number column in Unit that relates to Property. This is how most engineers would create related tables rather than using data that could change. I've left the update query that fixed the relationship up inside the DB if you want to read it. The basic process was "add autonumber columns to each table, add number column called PropertyId to Unit table, use update query to set Unit.PropertyId to the related parent property, based on existing property_name column, delete unit.property_name, and make ID columns good to go in both tables"

-

In the app I've created a form that shows you how to fill data using parameters, how to create tableadapters sensibly (the main query selects by ID, then other queries add utility by e.g. selecting by name, or by parent id so that related data can be loaded easily), and how one might load related data - I suggest you put %i% in the "fill by name" box, so that Addison and Sakowitz are loaded as parents, and then the related child records are loaded for them, and you can select one or the other parent and see the left child grid of related data change.

Pay attention to how the datasource and datamember properties are used on the grids and bindingsources - this is critical to understanding working with bound, related data

Also, and the bit that I've been banging on about being most important; look at the naming of everything, and think about how confusing as heck it would be if I'd just called everything "tableAdapter1", "tableAdapter2", "Fill3", "Fill4" etc.. This code doesn't really need the comments I've added because from the names alone it reads like a book, but the comments help explain why some of the code is the way it is. This app has been done as an exercise to help you learn, so it would have been a terrible thing to do, to fill it up with poor or misleading names. I realize I'm going on about it a lot, but always strive to render this same courtesy to other developers when you ask their help; give them code that is easy to read, and that explains itself quickly so they don't have to jump through hoops to figure out what is going on. Unless you're paying them, of course, in which case the harder you make it, the more you'll have to pay so yay for them

*The only thing I didn't rename was the block of nested stuff the designer created - datagridview columns and bindingnav widgets.. but I don't interact with them in any way in this code. If I did interact with them, I would rename them too

Two issues that I have when running the Lin100App that you had attached.

1) When I run it a window would pop up as shown in the attachment. What do I do
when this window pop up ?

2) I can see the code for PropertyUnitForm.cs but I cannot open the form where I
could see the textbox, and the listview on a form. The form that holds these control
would not open when I right-click and open.

 

[cjard]

@Skydiver can you sense check that for me? On my machine I just extract the zip, open the SLN and click play, and it Just Works (tm).. If it's wonky for you let me know md I'll double check/remake the zip

 

[Skydiver]

I had to unblock the .ZIP file to remove the Mark of the Web before I extracted the files out to a directory:

otherwise I was getting build errors.

After that, the code builds and runs:

and I could also get to the form

and it's code:

 

[Lin100]

I had to unblock the .ZIP file to remove the Mark of the Web before I extracted the files out to a directory:
View attachment 2637
otherwise I was getting build errors.

After that, the code builds and runs:
View attachment 2638

and I could also get to the form
View attachment 2639
and it's code:
View attachment 2640

1) I did unblock and then unzip. It did run but no records was displayed.
The access database AMS_2007.accdb is in the folder Lin100App.

2) Furthermore, the PropertyID has a -1 for new record. In the table Property,
the PropertyID goes from 1 to 6 and there is no PropertyID that has the value
-1.

 

[cjard]

1) you have you put something in the TextBox and click the Fill button if you want to load records. I did say this both in comments in the code, and in the text of the post. Please read everything I wrote

2) It's an autonumber. That is normal. The Id is calculated by the database when the record is saved. None of what I wrote discusses saving or its mechanics; this project demonstrates a variety of topics mentioned in the post (loading data, related data, setting up data binding easily) but saving related data isn't one of them. Some small tweaks to the data relation and the tableadapter config may be required to make life easier when saving but we aren't there yet - we're still on with reading data, not writing it