Fill datagridview with query using a Join

[gate7cy]

I have an application in vb.net with database in access 2007. I have created a query in vb which takes data from two tables (services, customers). The query works fine. Now i want my datagrid to fill using this query. Nothing works. Anyone has any suggestions? In preview data my columns and the respected data are shown normally. In adding them is the problem. The query works fine. The code to fill the datagrid is :


Code:
Me.AllServicesTableAdapter.addd(Me.Database2DataSet.AllServices)I am using vb and access 2007. Thanks you all for your time.

 

[Stonkie]

The way I have found to work best for this is to create a new DataTable in the dataset designer and setting it's main query to the join query. You may have to add aliases directly in the SQL so the column bindings have meaningful names.

This will give you a strongly typed DataTable that actually has all the columns from both tables included in the JOIN clause! It is not too good at updating this data though, so you should either manually set the UPDATE/DELETE/INSERT queries or skip the update part and use it as read only data.

And of course, the data returned by the JOIN is "denormalized", so you will have copies of the associated data from the child table for every parent row. If you need to save changes to the child data, you will have to take this into account... And don't forget it's pretty confusing to the user to edit referenced data in many fields at a time. What I came up with last time I went through this was to lock the data from the child table and only save changes for the parent table.

 

[cjard]

Either your datagrid is not bound to the datatable youre filling, or it has no columns and your auto generate columns is set to false

 

[gate7cy]

My datagrid is bounded to one of the tables I use in the Join query. It has columns but as far as the autio generate columns option I cannot find it in the properties. Any suggestions. Thanks for your reply.
Special thanks cjard as your advise is always helpfull.

 

[cjard]

its not a property that appears in the grid, try code view!

Also did you know, in the debugger, if you point to a datatable or dataset and then click the little magnifying glass in the tool tip you get a browser to see whats in your table

 

[MattP]

in the debugger, if you point to a datatable or dataset and then click the little magnifying glass in the tool tip you get a browser to see whats in your table

Excellent advice. I use this feature all the time as a quick check to make sure I'm getting good data.

 

[gate7cy]

thanks cjard. The autogenerate did it. How do I arrange the columns position in code? In the preview the columns are placed where I want them but at runtime they appear at the end. Any tip on changing that?Thanks for all the replies

 

[Stonkie]

That's what I don't like about the auto generate solution. And have you looked at the column headers? By using the dataset, you can use the form designer to order your columns and set localized column headers for your datagridview (the "localized" keyword was the deal breaker in my situation).

The way you are going, you could still add columns in the designer and set the correct DataPropertyName property for each column so they bind to the right column in DataTable. But you will have to add aliases in the SQL to have a meaningful name to bind to. This way you don't need to use the Dataset Designer at all but you keep the advantages of working with the Form Designer.

Still, a third even more quick and dirty solution would be to manually sort the columns in code. From what I remember, the auto generate function won't replace columns that already have a DataPropertyName set to the column it is trying to add. This means you should be able to reorder the columns as you wish (set the header text, and various properties as well) and they will be kept between refreshes (if you reset the DataSource property or otherwise play with it).

The DisplayIndex property of the column is settable so you should be fine to use it to set your order. You can use the index of the column to determine where it should go or go a little more fancy and use the DataPropertyName which should be set to the name of the column in the database.

You can access the column properties like this :

myDataGridView.Columns(0).DataPropertyName
myDataGridView.Columns(0).DisplayIndex

 

[cjard]

thanks cjard. The autogenerate did it. How do I arrange the columns position in code? In the preview the columns are placed where I want them but at runtime they appear at the end. Any tip on changing that?Thanks for all the replies

You can select them in a different order from the database - AutoGen simply generates columns in the grid view, if there is no grid view column for the data column encountered.

If youre saying "Theyre at the end" that implies there are already columns in the grid, so just add some more columns to your grid in design mode

Make sure you understadn the difference between a DataGridView which is a viewer component, and a DataTable which holds data

 

[gate7cy]

thanks for the replies. I repeat that I used a join query to add columns from two tables. cjard I do understand the difference between datagridview and datatable. The columns I want to move in the datagridview are the ones added through the join query from the other table. So I cannot see them in design mode. I cannot arrange them through the datagridview options. They become visible only when I run the application and can then be re-arranged but that still is going to be temporary. I my join query I place the added columns in the place I want them to appear. As I am in the dataset designer and I preview my query the columns appear correctly where I want them to be.

 

[MattP]

Me.yourDataGridViewName.Columns(columnIndex).DisplayIndex = newDisplayIndex

 

[cjard]

Delete the grid from the form and drag it on again from the Data Sources window

Note: your datatable must have the extra columns for this to work
You can also add them all manually to the Columns colelcton (propoerty grid), but that's a longer operation

 

[gate7cy]

cheers matt. That did the trick.