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

Lin100

Well-known member
Joined
Dec 12, 2022
Messages
69
Programming Experience
10+
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')

Code for Form3.cs:
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);
            }
        }
    }
}
 
Last edited:
Might need the entire project including DB posting to debug those, but this is wrong, by the way:
C#:
WHERE (Property_Name = '@Property_Name')

That's going to literally look for a string of "@Property_Name" I.e. you need a property called @Property_Name. It is not a database parameter if you enclose it in apostrophes

C#:
WHERE (Property_Name = @Property_Name)

This is a parameter. The tabkeadapter FillBy method will have multiple arguments

----

Fair warning: please read and heed the below paragraph if you want continued support from me on your code:

Please name your variables and controls properly and sensibly. This is for your sanity as well as mine. This extends to your Fill methods. A tableadapter can have multiple fill methods, so if you've made a tableadapter just based on "SELECT * FROM table" that might reasonably just be called Fill because it takes no parameters but anything else, like if you've made your tableadapter with a main query of "SELECT * FROM Properties WHERE PropertyId = @PropertyId", call the query "FillByPropertyId" / "GetByPropertyId". When you right click the table adapter and choose "add another query" and you put "SELECT * FROM Properties WHERE PropertyName = @PropertyName" call that query "FillByPropertyName"

I don't want to look at code that is like
C#:
tableAdapter3.FillBy7(dataSet1.Table1, textBox4.Text, listBox9.SelectedValue, checkBox23.Checked
and try and decipher it - is that that the one that fills by the tenant name, gender and retired status? Or was it the management office, contract length and expired contract query? Or is it the region, property type and is empty query? Or is it...

C#:
unitTableAdapter.FillByContractLengthAndExpiration(
  contractDataset.Properties,
  _managementOfficeNameTextBox.Text,
  _contractLengthListBox.SelectedValue,
  _isExpiredCheckBox.Checked
);

If you genuinely think the first code is better, more readable, more maintainable and nicer for other people to work with than the second, then I've no idea how to resolve that (but I genuinely cannot work with it)

"The first step to wisdom is by calling things proper names"
 
Last edited:
Might need the entire project including DB posting to debug those, but this is wrong, by the way:
C#:
WHERE (Property_Name = '@Property_Name')

That's going to literally look for a string of "@Property_Name" I.e. you need a property called @Property_Name. It is not a database parameter if you enclose it in apostrophes

C#:
WHERE (Property_Name = @Property_Name)

This is a parameter. The tabkeadapter FillBy method will have multiple arguments

----

Fair warning: please read and heed the below paragraph if you want continued support from me on your code:

Please name your variables and controls properly and sensibly. This is for your sanity as well as mine. This extends to your Fill methods. A tableadapter can have multiple fill methods, so if you've made a tableadapter just based on "SELECT * FROM table" that might reasonably just be called Fill because it takes no parameters but anything else, like if you've made your tableadapter with a main query of "SELECT * FROM Properties WHERE PropertyId = @PropertyId", call the query "FillByPropertyId" / "GetByPropertyId". When you right click the table adapter and choose "add another query" and you put "SELECT * FROM Properties WHERE PropertyName = @PropertyName" call that query "FillByPropertyName"

I don't want to look at code that is like
C#:
tableAdapter3.FillBy7(dataSet1.Table1, textBox4.Text, listBox9.SelectedValue, checkBox23.Checked
and try and decipher it - is that that the one that fills by the tenant name, gender and retired status? Or was it the management office, contract length and expired contract query? Or is it the region, property type and is empty query? Or is it...

C#:
unitTableAdapter.FillByContractLengthAndExpiration(
  contractDataset.Properties,
  _managementOfficeNameTextBox.Text,
  _contractLengthListBox.SelectedValue,
  _isExpiredCheckBox.Checked
);

If you genuinely think the first code is better, more readable, more maintainable and nicer for other people to work with than the second, then I've no idea how to resolve that (but I genuinely cannot work with it)

"The first step to wisdom is by calling things proper names"

WHERE (Property_Name = '@Property_Name')
C# automatically put in the single quote around @Property_Name. When I removed
the single quote, it put it right back in again. The attachment has a JPEG file that depict
it. C# did this in query builder in DataSet_Unit_Detail.xsd
DataSet_Unit_Detail.xsd --> Right Click - -> Open --> UnitTableAdapter --> Right Click --> Configure --> Query Builder
 

Attachments

  • Query Builder For DataSet_Unit_Detail.jpg
    Query Builder For DataSet_Unit_Detail.jpg
    179.9 KB · Views: 7
Last edited:
C# doesn't create those table adapters. Perhaps you meant Visual Studio?
 
WHERE (Property_Name = '@Property_Name')
C# automatically put in the single quote around @Property_Name. When I removed
the single quote, it put it right back in again. The attachment has a JPEG file that depict
it. C# did this in query builder in DataSet_Unit_Detail.xsd
DataSet_Unit_Detail.xsd --> Right Click - -> Open --> UnitTableAdapter --> Right Click --> Configure --> Query Builder

Yes. I meant Visual Studio 2022.
 
To cjard. You said
"If you genuinely think the first code is better, more readable, more maintainable and nicer for other people to work with than the second, then I've no idea how to resolve that (but I genuinely cannot work with it)"

The code that I originally posted for Form3.cs from line 1 to line 57 is created by Visual Studio. I did not write any of it. I did named thing with underscore for readability and that is all. namespace Retrieve_Data_From_Access_With_Navigation_Button <<-- readability
 
It's only more readable to Pythonistas. For C#, Java, JavaScript, C, and C++ programmers, the snake case is less readable.
 
Just because VisualStudio calls your new form Form3 (because you didnt rename form2 or form1) does not mean you have to persist with it. Here is where the rename option is:

1673629429216.png


Also F2 works, as does clicking on the name a second time when the file is already selected.

Here's where you rename a control:

1673629635743.png


in AZ order mode the prop grid has (Name) near the top. In Grouped mode the (Name) entry is in the Design group
 
WHERE (Property_Name = '@Property_Name')
C# automatically put in the single quote around @Property_Name. When I removed
You might need to put parameter placeholders in using ? marks instead; i'll check it. ODBC does understand @parameters but it doesn't treat them as named parameters. The design tools might not have the same understanding - but I'll refresh my memory of what happens with an access DB in the designer

Edit: yes; though ODBC will accept use of @params (but ignore the name and treat them as positional) the design tools don't understand it, and you open yourself up to a world of pain trying to force them past it with "ignore" - I'd recommend to just use e.g. select * from table where columnx like ? and columny in (?,?) and column z = ?
 
Last edited:
Actually I'd really recommend to use SQLServer localdb if you really want a file-based database. Forget all the 32/64 bit "must have the right version of office installed" nonsense..
 
Actually I'd really recommend to use SQLServer localdb if you really want a file-based database. Forget all the 32/64 bit "must have the right version of office installed" nonsense..
I did used the question mark as shown below and it still put quote around it
and it still gives the same error.
WHERE (Property_Name = '?Property_Name')
 
I suspect our OP doesn't want to "go naked" by jumping completely into his app as his only interface to his data living in SQLite or MySQL or localdb. I suspect that he wants to still be able to use Access to get to the data.

It's interesting at work where some database choices have been made to use relational databases with the justification "we want to do other queriees against the data to glean other insight later". But when later arrives, and it is time to do the other queries, there's interesting conversations about "you are not allowed to connect to the production database", or "thank you for granting us access to read from the production database, but we are not smart enough to do all the joins on the normalized data to get the information we want, can you dump the denormalized data as a flat .CSV file?", or "can you export all that into a data warehouse denormalized?"
 
I did used the question mark as shown below and it still put quote around it
and it still gives the same error.
WHERE (Property_Name = '?Property_Name')
If you look at the codes I posted, I don't use any kind of name after the ?

You just literally use a ? on its own, no names. The parameters are referred to positionally and must be repeated even if the value is the same. If the query contains 20 ? Symbols then the command must have 20 parameters

E.g.

C#:
//in a real database like SQLite, sqls, mysql, Postgres, oracle
"... WHERE name like @searchTerm OR address like @searchTerm";
cmd.Parameters.AddWithValue("@searchTerm", "smith%"); //add the parameter once

//in a basic database like access
"...WHERE name like ? OR address like ?";
cmd.Parameters.AddWithValue("@searchTerm", "smith%"); //for the first ? parameter
cmd.Parameters.AddWithValue("@searchTerm", "smith%"); //same value, for the second ? parameter

*illustration purposes only; avoid AddWithValue on databases where it causes performance problems, such as SQLserver
 
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

Form3.cs:
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);   <----- ERROR CS7036
 }
 

Attachments

  • Error CS7036.jpg
    Error CS7036.jpg
    148 KB · Views: 7
You've written a parameterized query but haven't then supplied a value for the parameter in the query, in the call to Fill

As I said earlier, when you write a parameterized query, call it FillBySomethingReleventHere - this helps remind you to provide an additional value

Only "SELECT * FROM table" queries should be called Fill - everything else that has a WHERE clause should be FillByXxx. You seldom want to write a query that loads all data into the client, so you should seldom have queries called Fill
 
Last edited:
Back
Top Bottom