Tony Gardner
asked on
I Want My View and Update Too!
I realize this might sound strange, but I've built several forms that contain both a single record with multiple text boxes, etc. AND a DataGridView. In fact, they share the same BindingNavigator. What's really odd is that the DataSource for everything on the form comes from an Access Query (thus a VIEW in VS2013).
It doesn't take a genius to see that the TableAdapter for the View simply doesn't support Inserts, Update and Deletes, but I am hoping to find a way to keep my beautiful forms, and learn how to write some code to manually do the inserts, updates and deletes on the corresponding TABLE.
For example, let's say that I have a VIEW called RosterView, and it has columns for everything in the TABLE Roster, plus some additional columns compliments of the joined tables (for example, the Teams table so I can show the player's Team Name next to the Team ID). All the fields on RosterView can be added to the top half of the form using the Designer, and a simple DataGridView added to the bottom half. Both share the same BindingNavigator and everything points to RosterView as the Data Source.
The obvious drawback here is that I am not able to do any inserts, deletes or updates since the TableAdapterManager won't invite Views to the party. A previous version of my form used the Roster TABLE for the top half and the RosterView for the DGV at the bottom. That worked okay, but I had to write a lot of code to keep everything in sync, so I went back to toying with this idea.
I'm willing to go back, but wanted to check in first with the Experts to see if anyone might have some creative ideas how to best approach this.
It doesn't take a genius to see that the TableAdapter for the View simply doesn't support Inserts, Update and Deletes, but I am hoping to find a way to keep my beautiful forms, and learn how to write some code to manually do the inserts, updates and deletes on the corresponding TABLE.
For example, let's say that I have a VIEW called RosterView, and it has columns for everything in the TABLE Roster, plus some additional columns compliments of the joined tables (for example, the Teams table so I can show the player's Team Name next to the Team ID). All the fields on RosterView can be added to the top half of the form using the Designer, and a simple DataGridView added to the bottom half. Both share the same BindingNavigator and everything points to RosterView as the Data Source.
The obvious drawback here is that I am not able to do any inserts, deletes or updates since the TableAdapterManager won't invite Views to the party. A previous version of my form used the Roster TABLE for the top half and the RosterView for the DGV at the bottom. That worked okay, but I had to write a lot of code to keep everything in sync, so I went back to toying with this idea.
I'm willing to go back, but wanted to check in first with the Experts to see if anyone might have some creative ideas how to best approach this.
ASKER
Thanks for jumping in, Paul. Not sure what a BE is, but I don't think I would be able to answer that question anyway since I don't have SQL Server available for this project (that is, our production SQL server is off limits for my training).
I was able to open the Query in Access and update a field, save it, close it, then open the same table and see that the change was applied.
I would be very happy if there was just a way for me to have a form with databound textboxes pointing to both the table (for example, Roster) and the Query (for example, RosterView). Please understand that I don't expect to be able to update the values in the textboxes pointing to RosterView -- these would be read-only. I would only want to be able to update the text boxes pointing to Roster.
I was able to open the Query in Access and update a field, save it, close it, then open the same table and see that the change was applied.
I would be very happy if there was just a way for me to have a form with databound textboxes pointing to both the table (for example, Roster) and the Query (for example, RosterView). Please understand that I don't expect to be able to update the values in the textboxes pointing to RosterView -- these would be read-only. I would only want to be able to update the text boxes pointing to Roster.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This Question was waiting for a VB.NET Expert who could understand my description well enough to explain what I now know, and have documented as the "Best" answer.
If you open the Access database and open the queyr in DS view, can you add/change records?