Dynamic Data Tables and Saving the Solution
- Create a new form named frmData. Add a tool strip to this form. Right-click on the tool strip and choose “Insert Standard Items”, which is going to create a set of standard buttons and separators on the tool strip. Except for the button that has the typical “Save” icon, right-click on each button or a separator and choose delete from the pop up menu (or click on each button or separator and click the delete button on your keyboard). Click on the save button (which now should be the only object on the tool strip). In the properties window, change the Display style to ImageAndText. Add a DataGridView object to the form and dock it in the parent container. Name this DataGridViewObject as dgvData. In the code page of frmData, import the library System.Data.OleDb above the class header for frmData. Then, inside the frmData Class, declare the public properties DS as DataSet, DA as OleDbDataAdaptor, DB as Database and TableName as string. Create a public sub called RefreshTable. In this sub, assign DS.Tables(TableName).DefaultView to the DataSource property of dgvData to display a table specified by TableName in the dataset DS in dgvData in default view mode.
- In the Database Class, create the public properties StaffDA, ClientDA, TaskDA and AssignmentDA, all of which are of OleDbDataAdapter type. In their respective functions (e.g. GetStaff) replace the local data adapter variables (e.g. Dim staffDA As OleDbDataAdapter = GetDataAdapter(“SELECT * FROM Staff”)) with these properties (e.g. StaffDA = GetDataAdapter(“SELECT * FROM Staff”)). Refer to the code from the lecture W07B-ADONET. Copy the UpdateDatabase sub (from the Database class of to the project for W07B lecture) to the Database class of your final project. Originally this sub does not have any parameters. Add the parameters DA as OleDbDataAdapter, DS as DataSet and TableName as String).
Inside this sub, replace all occurrences of arcsDA with the parameter DA, replace MyDataSet with the parameter DS, and replace “Arcs” with the parameter TableName. Go back to the design view of frmData and double-click on the save button on the tool strip to create its event handler. In this event handler, call the UpdateDatabase sub of the property DB with parameters being the DA, DS and TableName properties.
- Go to the code page of frmMain. In the sub UpdateFormSList, add a case for “Data” in the select-case structure; under this case, assign a new instance of frmData to FormSList(formName). In the sub InitializeFormSList, include a line to add the key “Data” with value Nothing inside the With structure.
- Go to the code page of frmDashboard. Add the string parameter tableName to the sub OpenTable (which originally does not have any parameters). In the sub OpenTable, delete the code under the Try block of the Try-Catch structure. Then, do the following under the Try block. Create a variable named dataForm as frmData type and call the GetForm function of the property MainForm with the parameter “Data” to assign to dataForm. Assign the parameter tableName to the TableName property of dataForm, assign tableName to the Text property of dataForm. Assign MyDataSet property of DB property (of the frmDashboard class) to the DB property of dataForm. Use a select-case structure for tableName and assign data adapters (e.g. DB.StaffDA) to the DA property of dataForm under their respective cases (e.g. Case “Staff”). Include a Case Else under which you throw an exception to ask the user the select a table first. After the select-case structure, call the RefreshTable, Show and BringToFront subs of dataForm. In the event handler of btnOpenTable.Click, set lstData.Text as the parameter of the call to OpenTable.
- In the design view of frmMain, add the top-level menu item “Tables”, under which add the menu items “Staff”, “Client”, “Task” and “Assignment”. Double-click on each of these menu items to create their event handlers. In each event handler, add an if-then structure to check if the call to DatabaseLoaded
function equals true. Inside the if-then structure, create a variable of type frmDashboard and call GetForm with parameter “Dashboard” to assign to this variable. Call OpenTable sub of this variable with their respective parameter (e.g. “Staff”). After all code is developed for frmData, all other form classes (i.e. frmStaff, frmClient, frmTask and frmAssignment) can be deleted.
- In the code page of frmDashboard, create a public sub called SaveSolution. In this sub, create a for- each loop over the AssignmentList property of frmDashboard class. In this loop, declare a string variable named queryStr and concatenate the string “StaffID = “, StaffID property of the Staff property of the loop variable, the string “ AND ClientID = “, and ClientID property of the Client property of the loop variable together to assign to queryStr. Declare an array called rows of DataRow type and assign to this array DB.MyDataSet.Tables(“Assignment”).Select(queryStr). Declare a variable called currRow as DataRow and assign rows(0) to it. Call sub BeginEdit of currRow, assign the Assigned property of the loop variable to the “Assigned” column of currRow, and finally call the EndEdit sub of currRow before the end of the for-each loop. After the loop, call UpdateDatabase sub of DB property of frmDashboard with the parameters DB.AssignmentDa, DB.MyDataSet and “Assignment”.
- In the design view of frmDashboard, create a button named btnSaveSolution with the Text property “Save Solution”. Double-click on this button to create its Click event handler. In this event handler call the sub SaveSolution. In the design view of frmMain, add a submenu called “Save Solution” below the “Optimize” submenu item under the “Solution” menu. Double click on “Save Solution” to create its event handler. In this event handler, add an if-then structure to check if the call to DatabaseLoaded function equals True. Inside the if-then structure, create a variable of type frmDashboard and call GetForm with parameter “Dashboard” to assign to this variable. Call BringToFront sub of this variable, and then call SaveSolution sub of this variable.
This assignment has been answered 4 times in private sessions.
Or buy a ready public solution below.
© 2021 Codify Tutor. All rights reserved