Easy Add/Edit/Delete form linked to database tables

[jharding08]

This is probably an easy question, but I wanted to know the best way to add/edit/delete records from a form. The form is based on one table, with many foreign tables linked.

There will be a listview where the user selects the record to view (primary key). Then there will be textboxes, comboboxes and listview for the resulting data. This will be view mode, then the user can edit that data via typing new info into the textboxes, selecting a different value with the combo box or selecting a different value int eh sub-listviews and save. They can also create a new record, which blanks all the controls and allows to save once new data is entered. User can also delete the data.

My question is, what is the best way to populate the controls when a record is selected in the primary list view? I used to work in access and you would just set the recordsource for the form and then the controls on the screen would be bound to that data.

I'm thinking about having add/edit/delete and then save/cancel buttons. Is there a good coding sample for this in VB2013?

[jmcilhinney]

The easiest way starts with not using a ListView. Use a DataGridView instead. In that case you will require almost no code. You simply populate a DataTable and bind it to the grid and all the columns and rows will be created automatically. The user simply hits the Delete key to delete the current row, adds a new row by typing into the bottom of the grid and edits in place. You save all the changes back to the database with a single call to Update on the same data adapter or table adapter that you used to Fill the DataTable in the first place. You can bind data to other controls, e.g. TextBoxes and ComboBoxes, as well and you can also automatically display related child data in another grid or other controls by selecting a parent record.

DO NOT use a ListView as a grid control. It isn't one and is not meant to be used as one. Think about Windows Explorer and that tells you how a ListView is to be used.

[jharding08]

The easiest way starts with not using a ListView. Use a DataGridView instead. In that case you will require almost no code. You simply populate a DataTable and bind it to the grid and all the columns and rows will be created automatically. The user simply hits the Delete key to delete the current row, adds a new row by typing into the bottom of the grid and edits in place. You save all the changes back to the database with a single call to Update on the same data adapter or table adapter that you used to Fill the DataTable in the first place. You can bind data to other controls, e.g. TextBoxes and ComboBoxes, as well and you can also automatically display related child data in another grid or other controls by selecting a parent record.

DO NOT use a ListView as a grid control. It isn't one and is not meant to be used as one. Think about Windows Explorer and that tells you how a ListView is to be used.

I definitely want to do a version of that. My dataset is about 4-5 tables. A main table and then some foreign keys to other "lookup" tables. I will use combo boxes when adding records for those other tables.

I'd like to have a list or grid that displays all the main data for that screen and then when selected it shows the related data in text/combo boxes. Is datagridview still the best for this list?

[jmcilhinney]

Yes, you should definitely use a DataGridView, unless you have a better grid control from a third party. Are you using a typed or untyped DataSet? If you're not sure, did you use the Data Source wizard to create the DataSet and accompanying table adapters or are you just creating a DataSet object in code and populating it with data adapters?

[jharding08]

Yes, you should definitely use a DataGridView, unless you have a better grid control from a third party. Are you using a typed or untyped DataSet? If you're not sure, did you use the Data Source wizard to create the DataSet and accompanying table adapters or are you just creating a DataSet object in code and populating it with data adapters?

I used the wizard to create the dataset. I can create a form and then put the objects in the form and it works, but my app is set up so that I have a main form with panels, I assign a user control to the main panel where the main work is being done. I did the same dataset for a usercontrol and it wont display the data. Are UCs different than WInForms?

[jmcilhinney]

A user control is like a form when you're designing the user control but it is like any other control when you're designing the form. You would probably have to add appropriate properties to the user control to allow you to pass in a DataSet and then do so in the form's Load event handler.

[jharding08]

A user control is like a form when you're designing the user control but it is like any other control when you're designing the form. You would probably have to add appropriate properties to the user control to allow you to pass in a DataSet and then do so in the form's Load event handler.

I guess i am wondering if there is an easier way to do the layout that I have than the way I am doing it.

I have a main form with panels, header panel, navigation panel

I have user controls that are the same size as my main panel and I set the main panel control to that user control when the associated button is clicked from the navigation panel.

Dim tempobject as New ucTemp
pnlMain.Controls.Clear()
pnlMain.Controls.Add(tempobject)

It was so easy to just add the data bound controls from the dataset to a form, and if it doesnt work the same with a user control, is there a different way to show forms in a container like the main panel?

I'd like to keep my container layout if possible

[jmcilhinney]

It DOES work the same way with a user control IF you do it in the designer for that user control, just as you would do it in the designer for a form. If you do that though, the user control will have its own DataSet, so the data it uses will have no direct connection to the data in the form itself or other controls. If that's OK then you're good to go as is. If it's not then you simply have to pass the data in from the outside, which is far from difficult.

[jharding08]

It DOES work the same way with a user control IF you do it in the designer for that user control, just as you would do it in the designer for a form. If you do that though, the user control will have its own DataSet, so the data it uses will have no direct connection to the data in the form itself or other controls. If that's OK then you're good to go as is. If it's not then you simply have to pass the data in from the outside, which is far from difficult.

I created a new UC, then created a new dataset. I set the dataset to data grid view and dragged it onto the UC which created the data grid with the columns.

When I go to run the app, where I log into the main form and then click the navigation button to open that UC in the main panel, it shows up with the data grid, but there isnt any data in it. There should be two records.

If I add a record, it allows it, and when I save it saves in the Db and is visible int he data grid view for that session. As soon as I exit out of that UC and go back in, the DGV is blank again

[jmcilhinney]

Do you have any code anywhere in the user control that actually retrieves data from the database? You're not going to see any data if you don't retrieve any data. You need to call Fill on a table adapter for that.

[jharding08]

Do you have any code anywhere in the user control that actually retrieves data from the database? You're not going to see any data if you don't retrieve any data. You need to call Fill on a table adapter for that.

I put the tableadapter.fill in the usercontrol.load event. Seems to populate the data now. Thank you

[jharding08]

So I got it working with textboxes, where I could change the info of a text box, that doesnt have a foreign key. But what if there is a foreign key to another table. My first example, I just used the one table and would enter Ids in the foreign key fields.

Now I have linked many other tables. I see the data populate into the textboxes with the right info, the actual value in stead of the key, but if I want to edit or add new, I need to have a combobox. Where the values in the dropdown are from the linked "lookup" table but the selected/displayed value is still bound to my overall dataset.

I see the combobox has 4 properties in data binding mode - Data source, display member, value member, selected value.

I use display member to be the human readable value in the lookup table and the value member to be the ID of that table, but shouldnt the data source be the overall dataset, not the lookup? not sure about "Selected Value"

If i have data soure as the overall dataset and the display member as the value ont he overall dataset, then I only get values that are int he overrall dataset, not all the lookup/available values.

[jharding08]

I think I have the combo box piece figured out. I was using a vw that joined three tables together as my data source, so I had all the fields in one data table. I believe its better to create a dataset with the three tables as their own data tables? I look in the designer of hte data set and my FK relationships are there, which means i should be able to pull data from all three tables and it will all link together?

How do I get data from all three tables in one datagridview? I pulled it in as details and the tables didnt link, when i stepped through records int eh main table,the FK tables didnt show the same data

[jharding08]

OK, so I read that I should be using stored procedures for Get, Insert, Update and Delete for my datasets. I can map the stored procedures for each function to the dataset, but I am back to my original issue, I have a query that has fields from 3 tables joined in a select stored procedure. How do I set up combo boxes so that I can keep the data bound to the data set, but populate all values for the field on the FK table>?

[jmcilhinney]

OK, so I read that I should be using stored procedures for Get, Insert, Update and Delete for my datasets.

Nope. You CAN use sprocs if you want to but you absolutely do not HAVE to. I rarely use sprocs at all.

[jmcilhinney]

How do I get data from all three tables in one datagridview?

Do you want to display the contents of one table in the grid proper and two other tables as drop-down lists in columns of the grid? If so, follow the CodeBank link in my signature below and check out my thread on putting a ComboBox Column In A DataGridView.

[jharding08]

Do you want to display the contents of one table in the grid proper and two other tables as drop-down lists in columns of the grid? If so, follow the CodeBank link in my signature below and check out my thread on putting a ComboBox Column In A DataGridView.

Thank you for that resource. I was able to get your example as well as my data tables working with combo boxes in the datagridview.

My next question is, how do I map the DataPropertyName of the main data binding to a combo box in detail view? I will have to create a new combo box and I can enter the data source, data member and value member, but where do I link it to the main data binding?

Thanks again. I'm starting to get these data controls.


***Nevermind, I figured it out. I tie it back to the main data binding with the Selected Value property*****

I would like to know the syntax to include more than one field in the Display Member output of the Combo Box

[jharding08]

Also, how do I use one function to change the user control that is displayed in my frmMain.pnlMain?

I was using:

Dim tempobject as new [usercontrol]
pnlMain.Controls.Clear()
pnlMain.Controls.Add(tempobject)

I had this on each button in the navigation panel and it would change user controls in pnlMain as called. I also want to call changes to the main panel from a user control that is already in the main panel (like drilling down to lookup tables). Instead of referencing the main form. main panel, could I just call my one function that would perform the above code, but the usercontrol would be passed in via parameter?




Something like this:

Public Sub GetUserControl(uc as System.Windows.Forms.Control)
Dim tempobject as new uc
pnlMain.Controls.Clear()
pnlMain.Controls.Add(tempobject)

That code isnt working, Getting error "value of type 'uc' cannot be converted to 'System.Windows.Forms.Control'

[jmcilhinney]

I would like to know the syntax to include more than one field in the Display Member output of the Combo Box

There is no such syntax. If you set the DisplayMember then you must set it to the name of one column or property. If you don't set it then you'll see the result of calling ToString on each item. That means that you can either override the ToString method to return a combination of properties, which is not an option if you're binding a DataTable, or else add a new column or property that combines the others and use it as a the DisplayMember. It's that second option that you'd use with a DataTable, adding a new column and setting its Expression property.

[jmcilhinney]

Also, how do I use one function to change the user control that is displayed in my frmMain.pnlMain?

I was using:

Dim tempobject as new [usercontrol]
pnlMain.Controls.Clear()
pnlMain.Controls.Add(tempobject)

I had this on each button in the navigation panel and it would change user controls in pnlMain as called. I also want to call changes to the main panel from a user control that is already in the main panel (like drilling down to lookup tables). Instead of referencing the main form. main panel, could I just call my one function that would perform the above code, but the usercontrol would be passed in via parameter?




Something like this:

Public Sub GetUserControl(uc as System.Windows.Forms.Control)
Dim tempobject as new uc
pnlMain.Controls.Clear()
pnlMain.Controls.Add(tempobject)

That code isnt working, Getting error "value of type 'uc' cannot be converted to 'System.Windows.Forms.Control'

That's going beyond the scope of the topic of this thread and should be posted in a new thread.

[jharding08]

There is no such syntax. If you set the DisplayMember then you must set it to the name of one column or property. If you don't set it then you'll see the result of calling ToString on each item. That means that you can either override the ToString method to return a combination of properties, which is not an option if you're binding a DataTable, or else add a new column or property that combines the others and use it as a the DisplayMember. It's that second option that you'd use with a DataTable, adding a new column and setting its Expression property.

I have my datatable tied to my datagridview and text/combo boxes and it is updating in the database. My next problem is filtering that data based on a global username variable that I have stored in a module on login.

I see where in the table adapter I can add a where clause. How do I link to that global username variable?

[jmcilhinney]

My next problem is filtering that data based on a global username variable that I have stored in a module on login.

Then you should ask us about that in your next thread, which has a title that indicates that that is the topic in question.