VS 2013 Easy Add/Edit/Delete form linked to database tables-VBForums
Results 1 to 22 of 22

Thread: Easy Add/Edit/Delete form linked to database tables

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Post Easy Add/Edit/Delete form linked to database tables

    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?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jmcilhinney View Post
    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?

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    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?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jmcilhinney View Post
    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?

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jmcilhinney View Post
    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

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jmcilhinney View Post
    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

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jmcilhinney View Post
    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

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    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.

  13. #13

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    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

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    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>?

  15. #15
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jharding08 View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  16. #16
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jharding08 View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  17. #17

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jmcilhinney View Post
    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
    Last edited by jharding08; Nov 12th, 2015 at 02:47 PM.

  18. #18

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    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'

  19. #19
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jharding08 View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  20. #20
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jharding08 View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  21. #21

    Thread Starter
    Junior Member
    Join Date
    Jul 2012
    Posts
    17

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jmcilhinney View Post
    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?

  22. #22
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,108

    Re: Easy Add/Edit/Delete form linked to database tables

    Quote Originally Posted by jharding08 View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.