Hello,

 I've been trying to get a fully functional DataGridView going for more hours than I care to admit so I decided to post and hopefully there is a simple solution.

My columns are Row ID, Position Title, and Active Row.  I can bring in all the data and add rows or edit existing rows, but Active Row is a boolean and comes in as True/False text instead of a check box.  If I change my code around a little it will show a check box, but will not save the data.  I have samples listed below...

1)  The code below works, but no checkbox and I have not been able to create one on the fly with working code.  Note that changing header text is no problem but I left out a few details to make this as simple as possible.

private PositionTitlesDomCollection _ptcd = new PositionTitlesDomCollection(); 

private void LoaddgvPositionTitle()
        {
            try
            {
                this.dgvPositionTitle.ReadOnly = false;
                _ptcd.Query.Select
                (
                    _ptcd.Query.Id,
                    _ptcd.Query.Description,
                    _ptcd.Query.ActiveRow
                );
                _ptcd.Query.OrderBy(_ptcd.Query.Description.Ascending);
                _ptcd.Query.Load();
                _ptcd.AllowDelete = false;
                _ptcd.AllowNew = true;
                _ptcd.AllowEdit = true;
                this.bndPositionTitlesDom.DataSource = _ptcd;
                this.bndPositionTitlesDom.AllowNew = true;
                this.dgvPositionTitle.AutoGenerateColumns = true;
                this.dgvPositionTitle.DataSource = bndPositionTitlesDom;
                this.dgvPositionTitle.AllowUserToAddRows = true;
                this.dgvPositionTitle.AllowUserToDeleteRows = false;
                this.bndPositionTitlesDom.ResetBindings(false);
                this.dgvPositionTitle.Refresh();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

private void btnSave_Click(object sender, EventArgs e)
        {
            if (this.Validate())
            {
                try
                {
                    this.bndPositionTitlesDom.EndEdit();
                    if (_ptcd.IsDirty)
                    {
                        int count = _ptcd.Count;
                        _ptcd.Save();
                    }
                   
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    this.Close();
                }
            }           
        }

2) This code produces a check box in the DataGridView, but will not save back to the database.

_ptcd.Query.Select
                (
                    _ptcd.Query.Id.As("Row ID"),
                    _ptcd.Query.Description.As("Position Title"),
                    _ptcd.Query.ActiveRow.As("Active Row")
                );

 
To recap briefly, my database is SQL2005 and I'm using the latest EntitySpaces 2007 I believe.

Thanks,
Bill
 

If you already see the EntitySpaces collection classes in your Toolbox, you can skip the first few steps.

• Make sure Design View is closed.
• Rebuild All.
• Open Design View for your Form.
• Open the Toolbox. All your EntitySpaces collections will be at the top, with blue gear icons.
• Drag the one you want onto the Form. In the example below I renamed it "ptcdColl".
• Click your BindingSource.
• Set its DataSource property to "ptcdColl".
• Click your DataGridView.
• Set its DataSource property to "bndPositionTitlesDom". Your collection's columns will now appear in the DGV.
• Set its "AllowUserToDeleteRows" property to False.
• Click its Columns property.
• Click the ellipses "..." to the right of "Collection".

Within this dialog, you set your DGV's column properties, Remove any columns you are not returning in our Load. Set the column's Header Text, rather than aliasing the properties in the query, as you you are doing in your 2nd code example. For the ActiveRow column, set its ColumnType property to "DataGridViewCheckBoxColumn". For the Id column, (assuming it is an identity column and should not be entered by the user) set the ReadOnly property to True.

Now your code looks like below. Everything else is either the default, or handled in the Designer. You are not going to AutoGenerateColumns, if you want the CheckBox.

private void LoaddgvPositionTitle()
{
    ptcdColl = new PositionTitlesDomCollection(); 
    ptcdColl.Query.Select
    (
        ptcdColl.Query.Id,
        ptcdColl.Query.Description,
        ptcdColl.Query.ActiveRow
    );
    ptcdColl.Query.OrderBy(
        ptcdColl.Query.Description.Ascending);
    ptcdColl.Query.Load();
    
    bndPositionTitlesDom.DataSource = ptcdColl;
    dgvPositionTitle.DataSource = bndPositionTitlesDom;
} 

private void btnSave_Click(object sender, EventArgs e)
{
    if (this.Validate())
    {
        try
        {
            bndPositionTitlesDom.EndEdit();
            if (ptcdColl.IsDirty)
            {
                int count = ptcdColl.Count;
                ptcdColl.Save();
            }
            
        }
        finally
        {
            Close();
        }
    }            
}

David,

Thanks for the help.  It took me a few minutes to decipher your instructions, but my DataGridView is working perfectly in less than an hour.  As I said earlier, you don't want to know, or I won't admit how long I've been trying to get this to work

 Best Regards,

Bill
 

We've all been there.

If you don't mind, which points were un-clear? I 'll edit and try to clarify them, in case someone else has a similar issue, and finds this thread.

Hi David,

Thanks for your help...  I would have offered some suggestions on clearing up your explanation but after making many successful DataGridViews I have forgotten why it confused me originally.

Now I have a new problem and it concerns DataViewGrids as well.  How do I populate a grid with values from a lookup table when the original column in the collection has an integer key?  The query code below produces a "jdColl.Query.es.LastQuery" query that brings back the data I want in the SQL query analyzer, but the collection does not load my column in the DataViewGrid, it just leaves it blank.  If I do not join, but use the column as an interger key, the column populates with the key data.

1    jdColl = new JobDescriptionsCollection();
2    JobDescriptionsQuery jd = new JobDescriptionsQuery("j");
3    PositionTitlesDomQuery pt = new PositionTitlesDomQuery("p");
4    
5    jd.Select(jd.Id, pt.Description, jd.DivisionId, jd.DepartmentId, jd.PayGradeId, jd.ActiveRow);
6    jd.InnerJoin(pt).On(pt.Id == jd.PositionTitleId);
7    jd.OrderBy(pt.Description.Ascending);
8    jdColl.Load(jd);
9    this.bindingSource1.DataSource = jdColl;
10   this.dataGridView1.DataSource = this.bindingSource1;
11   
12   // SQL produced...
13   
14   SELECT j.[id],p.[description] AS 'Description',j.[division_id],j.[department_id],j.[pay_grade_id],j.[active_row]  
15   FROM [job_descriptions] j 
16   INNER JOIN [position_titles_dom] p ON p.[id] = j.[position_title_id] 
17   ORDER BY p.[description] ASC

Try making

this.bindingSource1.DataSource = jdColl;

this

this.bindingSource1.DataSource = jdColl.LowLevelBind();

When it comes to Joins you are dealing with Virtual Columns. There are two design approaches you can take. I used Northwind Orders and Customers below in the examples.

The simple way:

Drop a DGV on your form.
Drop a BindingSource on your form.
Place the following in your form Load event:

OrdersQuery oq = new OrdersQuery("o");
CustomersQuery cq = new CustomersQuery("c");

oq.Select(
    oq.OrderID,
    cq.CompanyName,
    oq.EmployeeID);
oq.InnerJoin(cq).On(
    oq.CustomerID == cq.CustomerID);
oq.OrderBy(cq.CompanyName.Ascending);

OrdersCollection ordColl = new OrdersCollection();
ordColl.Load(oq);

bindingSource1.DataSource = ordColl;
dataGridView1.DataSource = bindingSource1;

That's it. No dropping an EntitySpaces OrdersCollection control on the form. No setting the DataSources in Design View. No customizing the DGV's Columns collection. Your code above should work with one change. You need to fully instantiate your collection.

JobDescriptionsCollection jdColl = new JobDescriptionsCollection();

Only the columns in the Select are auto-generated, and they are fully populated, including the CompanyName virtual column. All the steps in my previous post were only necessary because you needed a custom DGV columns collection rather than an auto-generated one. You can still take that approach, but it will require some extra code to make the Virtual Column available at design time.

The custom design approach:

Add the following to the Orders.cs custom class:

protected override List<esPropertyDescriptor> GetLocalBindingProperties()
{
    List<esPropertyDescriptor> props = new List<esPropertyDescriptor>();

    props.Add(new esPropertyDescriptor(this, "CompanyName", typeof(string)));

    return props;
}

public string CompanyName
{
    get { return (string)this.GetColumn("CompanyName"); }
}

Drop a DGV on your form.
Drop a BindingSource on your form.
Drop an OrdersCollection control on your form.
Rename it to ordColl.
Set the BindingSource DataSource to ordColl.
Set the DGV DataSoure to bindingSource1.

Edit the DGV columns collection and remove columns not brought back in the Select. Notice that CompanyName is now one of your choices, and should not be removed, in this case. It can be re-ordered and HeaderText changed, if you want.

The form Load event barely changes:

OrdersCollection ordColl = new OrdersCollection();

becomes:

ordColl = new OrdersCollection();

The CompanyName virtual column is populated by GetColumn in the property added to the custom entity class. If the column is empty, check the spelling and case of the column name.

David,

You tips worked pretty well for me.  My code is pasted below to see how I loaded the grid and set a few properties.

Thanks again for your help.

 If you look at my code you will see a couple of comments about not being able to concatenate columns.  That is something else that I would like to be able to do, but have not figured it out
 

1    // ToDo - Change this collection to include data behind ID numbers from lookup tables. "<[LastName] + ', ' + [FirstName] as FullName>"  jd.DepartmentId.As("Department"),
2            private void LoaddataGridView1()
3            {
4                try{
5                    JobDescriptionsQuery jd = new JobDescriptionsQuery("j");
6                    PositionTitlesDomQuery pt = new PositionTitlesDomQuery("p");
7                    DivisionsDomQuery dd = new DivisionsDomQuery("d");
8                    DepartmentsDomQuery ddq = new DepartmentsDomQuery("dq");
9                    PayGradesDomQuery pg = new PayGradesDomQuery("pq");
10   
11                   jd.Select(jd.Id.As("Row ID"), pt.Description.As("Job Description"),
12                       dd.ShortDescription.As("Division"),  ddq.ShortDescription.As("Department"),
13                       //"<ddq.ShortDescription + ' - ' + ddq.Description as Department>",
14                       pg.Description.As("Pay Grade"), jd.ActiveRow.As("Active Row"));
15                   jd.InnerJoin(pt).On(pt.Id == jd.PositionTitleId);
16                   jd.InnerJoin(dd).On(dd.Id == jd.DivisionId);
17                   jd.InnerJoin(ddq).On(ddq.Id == jd.DepartmentId);
18                   jd.InnerJoin(pg).On(pg.Id == jd.PayGradeId);
19                   jd.Where(jd.ActiveRow == true);
20                   jd.OrderBy(pt.Description.Ascending);
21   
22                   JobDescriptionsCollection jdColl = new JobDescriptionsCollection();
23                   jdColl.Load(jd);
24                   jdColl.AllowEdit = false;
25                   this.bindingSource1.DataSource = jdColl;
26                   this.dataGridView1.DataSource = this.bindingSource1;
27                   this.dataGridView1.MultiSelect = false;
28                   this.dataGridView1.Columns[0].Width = 75;
29                   this.dataGridView1.Columns[1].Width = 200;
30                   this.dataGridView1.Columns[4].Width = 125;
31               }
32               catch (Exception ex)
33               {
34                   throw ex;
35               }
36           }

If you are using the latest ES2007, then expression support can eliminate a lot of in-line raw SQL:

OrdersQuery oq = new OrdersQuery("o");
EmployeesQuery eq = new EmployeesQuery("e");

oq.Select(
    oq.OrderID,
    oq.CustomerID,
    (eq.LastName + ", " + eq.FirstName).As("EmployeeName"));
oq.InnerJoin(eq).On(
    oq.EmployeeID == eq.EmployeeID);

OrdersCollection collection = new OrdersCollection();
collection.Load(oq);

If you do need in-line raw SQL, remember to use your assigned query alias "e", not the instance name "eq".

OrdersQuery oq = new OrdersQuery("o");
EmployeesQuery eq = new EmployeesQuery("e");

oq.Select(
    oq.OrderID,
    oq.CustomerID,
    "<e.[LastName] + ', ' + e.[FirstName] AS 'EmployeeName'>");
oq.InnerJoin(eq).On(
    oq.EmployeeID == eq.EmployeeID);

OrdersCollection collection = new OrdersCollection();
collection.Load(oq);

Hi there.

 I have been trying your first example for the last few hours and am having no success whatsover.  Can someone with better eyes have a look and tell me what I'm missing??? (I'm sure I'm just overlooking something small).

 No matter what I try, I cannot get the "virtual" columns to show in the DataGridView.  If I use the "lastquery" feature and run it in mysql, all the columns are correctly populated, so I know the query is functional.  The grid returns the expected number of rows, but it seems to reflect only the original collection columns and is not displaying the additional "virtual" columns.  What am I missing???

 Thanks for any assistance.

1    Dim pwas As New ProductsWithAttributesStockQuery("pwas")
2    		Dim pwasa As New ProductsWithAttributesStockAttributesQuery("pwasa")
3    		Dim pa As New ProductsAttributesQuery("pa")
4    		Dim po As New ProductsOptionsQuery("po")
5    		Dim pov As New ProductsOptionsValuesQuery("pov")
6    
7    		With pwas
8    			.Select(pwas.StockId, pwas.Quantity, pwasa.ProductsAttributesId, pov.ProductsOptionsValuesName, po.ProductsOptionsName)
9    			.InnerJoin(pwasa).On(pwas.StockId.Equal(pwasa.StockId))
10   			.InnerJoin(pa).On(pwasa.ProductsAttributesId.Equal(pa.ProductsAttributesId))
11   			.InnerJoin(po).On(pa.OptionsId.Equal(po.ProductsOptionsId))
12   			.InnerJoin(pov).On(pa.OptionsValuesId.Equal(pov.ProductsOptionsValuesId))
13   			.Where(pwas.ProductsId.Equal(Me.combobox_Products.SelectedValue), pwas.Quantity.GreaterThan(0))
14   			.OrderBy(pov.ProductsOptionsValuesName.Ascending, pwas.Quantity.Ascending)
15   		End With
16   
17   		Dim obj1 As New ProductsWithAttributesStockCollection
18   		obj1.Load(pwas)
19   
20   		Me.bindingSource_Stock.DataSource = obj1
21   		Me.dataGridView_Stock.DataSource = Me.bindingSource_Stock
22   

I do not see any obvious problems with the posted code. Which version of EntitySpaces are you using?

I'm not exactly sure which "first example" you are referring to. If you want AutoGenerateColumns to work with virtual columns with ES 2007.1.1210.0, then you need to follow "The simple way" in post 8801. Make sure, in Design View, that no DataSources are set for the BindingSource or DataGridView.

Thanks very much!

 That was exactly the issue.  I did have data sources set and once removed, it all functioned as advertised!

 Thanks very much for your time!!