DataGridView is not showing the records from a query in Access Database

L

Lin100

Well-known member
Joined
Dec 12, 2022
Messages
69
Programming Experience
10+
I want a DataGridView to obtain records from a query resided in an Access database.
The program run without error. The header name of the fields are displayed but not the records.

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

MS Access query Name: Q_SHOW_ALL_UNITS_AND_ITS_STATUS

SELECT DISTINCTROW Unit.Property_Name, Unit.Unit_Number, Unit.Bedroom, Unit.Rental_Cost, Unit.Security_Deposit, Unit.Bathroom, Unit.Square_Feet, Unit.Status, Tenant.Date_Lease_Expired, Tenant.Late_On_Payment, Tenant.Days_Delinquent
FROM Unit LEFT JOIN Tenant ON (Unit.Unit_Number = Tenant.Unit_Number) AND (Unit.Property_Name = Tenant.Property_Name)
ORDER BY Unit.Property_Name, Unit.Unit_Number, Unit.Bedroom, Unit.Rental_Cost;

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

dataGridView Name: dataGridView1
right click dataGridView1 --> Properties --> DataSource = qSHOWALLUNITSANDITSSTATUSBindingSource

qSHOWALLUNITSANDITSSTATUSBindingSource --> Q_SHOW_ALL_UNITS_AND_ITS_STATUS

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

Form1.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 Data_Grid_View_And_Access
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

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

Form1.Designer.cs: 
namespace Data_Grid_View_And_Access
{
    partial class Form1
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>

        private void InitializeComponent()
        {
            this.components = new System.ComponentModel.Container();
            this.dataGridView1 = new System.Windows.Forms.DataGridView();
            this.aMS_2007DataSet = new Data_Grid_View_And_Access.AMS_2007DataSet();
            this.qSHOWALLUNITSANDITSSTATUSBindingSource = new System.Windows.Forms.BindingSource(this.components);
            this.q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter = new Data_Grid_View_And_Access.AMS_2007DataSetTableAdapters.Q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter();
            this.propertyNameDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.unitNumberDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.bedroomDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.rentalCostDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.securityDepositDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.bathroomDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.squareFeetDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.statusDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.dateLeaseExpiredDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            this.lateOnPaymentDataGridViewCheckBoxColumn = new System.Windows.Forms.DataGridViewCheckBoxColumn();
            this.daysDelinquentDataGridViewTextBoxColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.aMS_2007DataSet)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.qSHOWALLUNITSANDITSSTATUSBindingSource)).BeginInit();
            this.SuspendLayout();
            //
            // dataGridView1
            //
            this.dataGridView1.AutoGenerateColumns = false;
            this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
            this.propertyNameDataGridViewTextBoxColumn,
            this.unitNumberDataGridViewTextBoxColumn,
            this.bedroomDataGridViewTextBoxColumn,
            this.rentalCostDataGridViewTextBoxColumn,
            this.securityDepositDataGridViewTextBoxColumn,
            this.bathroomDataGridViewTextBoxColumn,
            this.squareFeetDataGridViewTextBoxColumn,
            this.statusDataGridViewTextBoxColumn,
            this.dateLeaseExpiredDataGridViewTextBoxColumn,
            this.lateOnPaymentDataGridViewCheckBoxColumn,
            this.daysDelinquentDataGridViewTextBoxColumn});
            this.dataGridView1.DataSource = this.qSHOWALLUNITSANDITSSTATUSBindingSource;
            this.dataGridView1.Location = new System.Drawing.Point(78, 88);
            this.dataGridView1.Name = "dataGridView1";
            this.dataGridView1.RowHeadersWidth = 51;
            this.dataGridView1.RowTemplate.Height = 24;
            this.dataGridView1.Size = new System.Drawing.Size(665, 306);
            this.dataGridView1.TabIndex = 0;
            this.dataGridView1.DataSourceChanged += new System.EventHandler(this.Form1_Load);
            //
            // aMS_2007DataSet
            //
            this.aMS_2007DataSet.DataSetName = "AMS_2007DataSet";
            this.aMS_2007DataSet.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
            //
            // qSHOWALLUNITSANDITSSTATUSBindingSource
            //
            this.qSHOWALLUNITSANDITSSTATUSBindingSource.DataMember = "Q_SHOW_ALL_UNITS_AND_ITS_STATUS";
            this.qSHOWALLUNITSANDITSSTATUSBindingSource.DataSource = this.aMS_2007DataSet;
            //
            // q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter
            //
            this.q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter.ClearBeforeFill = true;
            //
            // propertyNameDataGridViewTextBoxColumn
            //
            this.propertyNameDataGridViewTextBoxColumn.DataPropertyName = "Property_Name";
            this.propertyNameDataGridViewTextBoxColumn.HeaderText = "Property_Name";
            this.propertyNameDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.propertyNameDataGridViewTextBoxColumn.Name = "propertyNameDataGridViewTextBoxColumn";
            this.propertyNameDataGridViewTextBoxColumn.Width = 125;
            //
            // unitNumberDataGridViewTextBoxColumn
            //
            this.unitNumberDataGridViewTextBoxColumn.DataPropertyName = "Unit_Number";
            this.unitNumberDataGridViewTextBoxColumn.HeaderText = "Unit_Number";
            this.unitNumberDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.unitNumberDataGridViewTextBoxColumn.Name = "unitNumberDataGridViewTextBoxColumn";
            this.unitNumberDataGridViewTextBoxColumn.Width = 125;
            //
            // bedroomDataGridViewTextBoxColumn
            //
            this.bedroomDataGridViewTextBoxColumn.DataPropertyName = "Bedroom";
            this.bedroomDataGridViewTextBoxColumn.HeaderText = "Bedroom";
            this.bedroomDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.bedroomDataGridViewTextBoxColumn.Name = "bedroomDataGridViewTextBoxColumn";
            this.bedroomDataGridViewTextBoxColumn.Width = 125;
            //
            // rentalCostDataGridViewTextBoxColumn
            //
            this.rentalCostDataGridViewTextBoxColumn.DataPropertyName = "Rental_Cost";
            this.rentalCostDataGridViewTextBoxColumn.HeaderText = "Rental_Cost";
            this.rentalCostDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.rentalCostDataGridViewTextBoxColumn.Name = "rentalCostDataGridViewTextBoxColumn";
            this.rentalCostDataGridViewTextBoxColumn.Width = 125;
            //
            // securityDepositDataGridViewTextBoxColumn
            //
            this.securityDepositDataGridViewTextBoxColumn.DataPropertyName = "Security_Deposit";
            this.securityDepositDataGridViewTextBoxColumn.HeaderText = "Security_Deposit";
            this.securityDepositDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.securityDepositDataGridViewTextBoxColumn.Name = "securityDepositDataGridViewTextBoxColumn";
            this.securityDepositDataGridViewTextBoxColumn.Width = 125;
            //
            // bathroomDataGridViewTextBoxColumn
            //
            this.bathroomDataGridViewTextBoxColumn.DataPropertyName = "Bathroom";
            this.bathroomDataGridViewTextBoxColumn.HeaderText = "Bathroom";
            this.bathroomDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.bathroomDataGridViewTextBoxColumn.Name = "bathroomDataGridViewTextBoxColumn";
            this.bathroomDataGridViewTextBoxColumn.Width = 125;
            //
            // squareFeetDataGridViewTextBoxColumn
            //
            this.squareFeetDataGridViewTextBoxColumn.DataPropertyName = "Square_Feet";
            this.squareFeetDataGridViewTextBoxColumn.HeaderText = "Square_Feet";
            this.squareFeetDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.squareFeetDataGridViewTextBoxColumn.Name = "squareFeetDataGridViewTextBoxColumn";
            this.squareFeetDataGridViewTextBoxColumn.Width = 125;
            //
            // statusDataGridViewTextBoxColumn
            //
            this.statusDataGridViewTextBoxColumn.DataPropertyName = "Status";
            this.statusDataGridViewTextBoxColumn.HeaderText = "Status";
            this.statusDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.statusDataGridViewTextBoxColumn.Name = "statusDataGridViewTextBoxColumn";
            this.statusDataGridViewTextBoxColumn.Width = 125;
            //
            // dateLeaseExpiredDataGridViewTextBoxColumn
            //
            this.dateLeaseExpiredDataGridViewTextBoxColumn.DataPropertyName = "Date_Lease_Expired";
            this.dateLeaseExpiredDataGridViewTextBoxColumn.HeaderText = "Date_Lease_Expired";
            this.dateLeaseExpiredDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.dateLeaseExpiredDataGridViewTextBoxColumn.Name = "dateLeaseExpiredDataGridViewTextBoxColumn";
            this.dateLeaseExpiredDataGridViewTextBoxColumn.Width = 125;
            //
            // lateOnPaymentDataGridViewCheckBoxColumn
            //
            this.lateOnPaymentDataGridViewCheckBoxColumn.DataPropertyName = "Late_On_Payment";
            this.lateOnPaymentDataGridViewCheckBoxColumn.HeaderText = "Late_On_Payment";
            this.lateOnPaymentDataGridViewCheckBoxColumn.MinimumWidth = 6;
            this.lateOnPaymentDataGridViewCheckBoxColumn.Name = "lateOnPaymentDataGridViewCheckBoxColumn";
            this.lateOnPaymentDataGridViewCheckBoxColumn.Width = 125;
            //
            // daysDelinquentDataGridViewTextBoxColumn
            //
            this.daysDelinquentDataGridViewTextBoxColumn.DataPropertyName = "Days_Delinquent";
            this.daysDelinquentDataGridViewTextBoxColumn.HeaderText = "Days_Delinquent";
            this.daysDelinquentDataGridViewTextBoxColumn.MinimumWidth = 6;
            this.daysDelinquentDataGridViewTextBoxColumn.Name = "daysDelinquentDataGridViewTextBoxColumn";
            this.daysDelinquentDataGridViewTextBoxColumn.Width = 125;
            //
            // Form1
            //
            this.AutoScaleDimensions = new System.Drawing.SizeF(8F, 16F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(800, 450);
            this.Controls.Add(this.dataGridView1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.Form1_Load);
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.aMS_2007DataSet)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.qSHOWALLUNITSANDITSSTATUSBindingSource)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.DataGridView dataGridView1;
        private AMS_2007DataSet aMS_2007DataSet;
        private System.Windows.Forms.BindingSource qSHOWALLUNITSANDITSSTATUSBindingSource;
        private AMS_2007DataSetTableAdapters.Q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter;
        private System.Windows.Forms.DataGridViewTextBoxColumn propertyNameDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn unitNumberDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn bedroomDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn rentalCostDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn securityDepositDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn bathroomDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn squareFeetDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn statusDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn dateLeaseExpiredDataGridViewTextBoxColumn;
        private System.Windows.Forms.DataGridViewCheckBoxColumn lateOnPaymentDataGridViewCheckBoxColumn;
        private System.Windows.Forms.DataGridViewTextBoxColumn daysDelinquentDataGridViewTextBoxColumn;
    }
}

program.cs: 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Data_Grid_View_And_Access
{
    internal static class Program
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

app.config: 
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Data_Grid_View_And_Access.Properties.Settings.AMS_2007ConnectionString"
            connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\AMS_2007.accdb"
            providerName="System.Data.OleDb" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
</configuration>
 
Sort by date Sort by votes
jmcilhinney said:
I already specified it. You're already calling it. Read what I wrote. Please tell me I don't need to explain how to use the return value of a function.
Click to expand...
Here what I did. When The program run, the window appear and there are column header but no records. In addition, The messagebox did not pop up; and therefore I don't know how many records were returned.

Showing the number of records returned: 
using System;
using System.CodeDom;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

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

        private void Form1_Load(object sender, EventArgs e)
        {
            int rows_number = 0;
            this.q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter.Fill(this.aMS_2007DataSet.Q_SHOW_ALL_UNITS_AND_ITS_STATUS);
            rows_number = this.q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter.Fill(this.aMS_2007DataSet.Q_SHOW_ALL_UNITS_AND_ITS_STATUS);
            MessageBox.Show(rows_number.ToString());
        }
    }
}
 
Upvote 0 Downvote
Firstly, that code is way more verbose than it needs to be. Why call Fill twice? Why assign zero to a variable that you're about to assign something else to immediately afterwards.

Secondly, don't use message boxes to debug. Use the debugger. You only needed this:
C#: 
var rowCount = this.q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter.Fill(this.aMS_2007DataSet.Q_SHOW_ALL_UNITS_AND_ITS_STATUS);
You could then put a breakpoint on that line and, when it gets hit, step to the next line and look at the value of rowCount.

Most importantly though, if that message box is not being displayed then that suggests that that code is not being executed, so no wonder there's no data. If you had debugged properly, i.e. with a breakpoint, then you'd already know whether it was being executed or not by whether the breakpoint was hit or not.

My guess would be that that method is not actually handling the Load event of the form. Open the form in the designer, open the Properties window, click the Events button and then check that that method is selected as the handler for that event.
 
Upvote 0 Downvote
jmcilhinney said:
Firstly, that code is way more verbose than it needs to be. Why call Fill twice? Why assign zero to a variable that you're about to assign something else to immediately afterwards.

Secondly, don't use message boxes to debug. Use the debugger. You only needed this:
C#: 
var rowCount = this.q_SHOW_ALL_UNITS_AND_ITS_STATUSTableAdapter.Fill(this.aMS_2007DataSet.Q_SHOW_ALL_UNITS_AND_ITS_STATUS);
You could then put a breakpoint on that line and, when it gets hit, step to the next line and look at the value of rowCount.

Most importantly though, if that message box is not being displayed then that suggests that that code is not being executed, so no wonder there's no data. If you had debugged properly, i.e. with a breakpoint, then you'd already know whether it was being executed or not by whether the breakpoint was hit or not.

My guess would be that that method is not actually handling the Load event of the form. Open the form in the designer, open the Properties window, click the Events button and then check that that method is selected as the handler for that event.
Click to expand...

I highlighted the DataGridView1 --> Right click --> Select properties --> and clicked the Events button. I did not see any events call Form1_Load. This is fine. To fix the problem I performed the action below. After I did this, when it ran, the DataGridView displayed all of the records as well as the messagebox with number 65. The Events does not need to call the procedure private void Form1_Load(object sender, EventArgs e)
in order for Form_Load to activate.

SOLUTION TO SHOW ALL RECORDS IN DATAGRIDVIEW1

View Menu --> Solution Explorer --> Right click on project name Data_Grid_View_And_Access --> Properties --> Build TAB
1) uncheck box "Prefer 32-bit"
2) Platform target: Select X64
 
Upvote 0 Downvote
Lin100 said:
I highlighted the DataGridView1
Click to expand...
Why? You're not trying to handle an event of the grid. Look at the name of the method: Form1_Load. What has that got to do with the grid? Read the words I wrote:
My guess would be that that method is not actually handling the Load event of the form.
Click to expand...
It's frustrating when people ask for help and then don't pay attention when it's provided.
 
Upvote 0 Downvote

Similar threads

J
  • Question
help with running the code
Replies
1
Views
1K
Skydiver
Skydiver

Share this page

Latest posts

Back
Top Bottom