Chapter 5 Data Inserting with Visual Basic.NET

1. Chapter 5 Data Inserting with Visual Basic.NET In this chapter, we will concentrate on inserting data into the DataSet and the database. Insert data into the DataSet or exactly insert data into the data tables embedded in the DataSet is totally different with inserting data into the database or exactly inserting data into the data tables in the database. The former is only to insert data into the mapping of the data table in the DataSet, and this insertion has nothing to do with the real data tables in the database. In other words, the data inserted into the mapping data tables in the DataSet are not inserted into the data tables in the real database. The latter is to insert the data into the data tables in the real database.

2. Chapter 5 Data Inserting with Visual Basic.NET - 2 As you know, ADO.NET provided a disconnected working mode for the database access applications. The so-called disconnected mode means that your data-driven applications will not always keep the connection with your database, and this connection may be disconnected after you setup your DataSet and load all data from the data tables in your database into those data table mappings in your DataSet, and most time you are just working on the data between your applications and your data table mappings in your DataSet. The main reason of using this mode is to reduce the overhead of a large number of connections to the database and improve the efficiency of data transferring and implementations between the users’ applications and the data sources. In this chapter, we provide two parts to show readers how to insert data into the database; to insert data into the database using the Visual Basic.NET design tools and wizards is discussed in the first part, and inserting data to the database using the run-time object method is shown in the second part.

3. Chapter 5 Data Inserting with Visual Basic.NET - 3 In this chapter, you will: • Understand the working principle and structure on inserting data to the database using the Visual Basic.NET Design Tools and Wizards. • Understand the procedures in how to configure the TableAdapter object by using the TableAdapter Query Configuration Wizard and build the query to insert data into the database. • Design and develop special procedures to validate data before and after accessing the database. • Understand the working principle and structure on inserting data to the database using the run-time object method. • Design and build stored procedures to perform the data inserting.

4. PART I Data Insertion with Design Tools and Wizards We develop two methods to perform this data inserting: • Use the TableAdapter DBDirect method, TableAdapter.Insert(), to directly insert data into the database. • Insert data into the database by first adding new records into the DataSet, and then updating new records from the DataSet to the database using the TableAdapter.Update() method. Both methods utilize the TableAdapter’s direct and indirect methods to complete the data insertion. The database we try to use is the Microsoft Access database, CSE_DEPT.mdb, which was developed in Chapter 2. You can try to use any other databases such as Microsoft SQL Server 2005 or Oracle Database 10g XE. The only issue is that you need to connect the correct database to your Visual Basic.NET data-driven applications.

5. 5.1  Insert New Data Into a Database Generally there are many different ways to insert new data into the database in Visual Studio.NET. Regularly, however, three methods are widely utilized: 1. Using the TableAdapter's DBDirect methods, specifically such as the TableAdapter.Insert() method 2.  Using the TableAdapter’s Update() method to insert new records that have already been added into the DataTable in the DataSet 3.  Using the Command object combined with the ExecuteNonQuery() method In addition to inserting data into the database, method 2 can be used for other data operations such as update and delete data from the database. both methods 1 and 2 need method 3 to complete those data manipulations, which means that both methods need to execute the ExecuteNonQuery() method of the Command object to finish those data operations again the database.

6. 5.1.1  Insert Data into Database Using TableAdapter.Insert Method When you use this TableAdapter DBDirect method to perform data manipulations to a database, the main query must provide enough information in order for the DBDirect methods to be created correctly. • The so-called main query is the default or original query methods such as Fill() and GetData() when you first time open any TableAdapter by using the TableAdapter Configuration Wizard. • The enough information means that the data table must contain completed definitions. For example, if a TableAdapter is configured to query data from a table that does not have a primary key column defined, it does not generate DBDirect methods.

7. TableAdapter DBDirect Methods Table 5-1 lists three TableAdapter DBDirect methods. The TableAdapter.Update() method has two functionalities: one is to directly make all changes in the database based on the parameters contained in the Update() method, and another job is to update all changes made in the DataSet to the database based on the associated properties of the TableAdapter such as the InsertCommand, UpdateCommand and Delete-Command.

8. 5.1.2 Insert Data into Database Using TableAdapter.Update Method To use this method to insert data into the database, one needs to perform the following steps: 1. Add new records to the desired DataTable in DataSet by creating a new DataRow and adding it to the Rows collection. 2. After the new rows are added to the DataTable, call the TableAdapter.Update method. You can control the amount of data to be updated by passing an entire DataSet, a DataTable, an array of DataRows, or a single DataRow. In order to provide a detailed discussion and explanation how to use these two methods to insert new records into the database, a real example will be very helpful. Let’s first create a new Visual Basic.NET project to handle these issues.

9. 5.2 Insert Data into the Microsoft Access Database Using a Sample Project InsertWizard We have provided a very detailed introduction about the Design Tools and Wizards in Visual Basic.NET in section 4.2 in the last chapter, such as DataSet, BindingSource, TableAdapter, Data Source window, Data Source Configuration window and DataSet Designer. We need to use those staff to develop our data-inserting sample project based on the SampleWizards project developed in the last chapter. First let’s copy that project and do some modifications on that project to get our new project. The advantage of creating our new project in this way is that you don’t need to redo the data source connection and configuration since those jobs have been performed in the last chapter.

10. 5.2.1 Create New Project Based on SampleWizards Project Copy project SampleWizards to our new folder Chapter 5. Change the name of the solution and the project from SampleWizards to InsertWizard. Double click the InsertWizard Project.vbproj to open this project. Perform the following modifications to get our desired project: 1. Go to Project|InsertWizard Project Properties menu item to open the project’s property window. Change the Assembly name: from SampleWizards Project to InsertWizard Project and the Root namespace from SampleWizards_Project to InsertWizard_Project, respectively. 2. Click the Assembly Information button to open the Assembly Information dialog box, change the Title: and the Product: to InsertWizard Project. Click OK to close this dialog box. Go to File|Save All to save those modifications.

11. 5.2.2 Application User Interfaces As you know from the last chapter, six form windows work as the user interfaces for the SampleWizards project: LogIn, Selection, Faculty, Course, Student and Grid. Of all these six form windows, only three of them contain the Insert command button, and they are: Faculty, Course and Student. Therefore we only need to work on these three forms to perform the data insertion to our database. First let’s concentrate on the Faculty form to perform the data insertion into our Faculty table in the database. To insert a new record into the Faculty table, a separate user interface or form is needed to allow us to enter the new faculty information. Now let’s create another new form, Insert Faculty form.

12. 5.2.3  Create the Insert Faculty Form Window Add following items that are shown in Table 5-2 into this form.

13. 5.2.3 Create the Insert Faculty Form Window - 2 This form allows users to use two different methods to insert data into the database: either the TableAdapter.Insert() method or the TableAdapter.Update() method. The form also allows users to enter all pieces of information into the appropriate textboxes for the new inserted faculty. In addition to the Form’s properties shown in Table 5-2, the following properties of the form should be setup: 1. AcceptButton: cmdInsert 2. StartPosition: CenterScreen The checkbox chkPhoto is used to allow users to choose the inserting faculty photo, including the name and the location of that photo. By checking this checkbox, it indicates that a faculty photo will be included and displayed for this data insertion. Also the user needs to provide the photo’s name (txtPhotoName) and the location (txtPhotoLocation) in which the photo is stored if this checkbox is checked

14. 5.2.3Create the Insert Faculty Form Window - 3 The finished Insert Faculty form window is shown in Figure 5-1. From both Table 5-2 and Figure 5-1, it can be found that the textbox Photo Name and the textbox Photo Location are disabled (Enabled properties are False in Table 5-2 and gray color in Figure 5-1). The reason for this is that we want to use the checkbox Faculty Photo to control these two controls to make the project more professional.

15. 5.2.4 Validate Data Before the Data Insertion It is important to validate data before they can be inserted into the database since we want to make sure that the data inserted into the database are correct. The most popular validation mode is to make sure that each datum is not NULL and it contains a certain value. In this application, we try to validate that each piece of faculty information, which is stored in the associated textbox, is not an empty string unless the user intends to leave it as an empty datum. In that case, an NULL must be entered. To make this validation simple, we develop a control collection and add all of those textboxes into this collection. In this way, we don’t need to check each textbox, but instead we can use the For Each …Next loop to scan the whole collection to find the empty textbox.

16. 5.2.4.1 Visual Basic Collection and .NET Framework Collection class There are two kinds of collection classes available for the Visual Basic.NET applications: 1.  Visual Basic collection class 2. .NET Framework collection class The index of the Visual Basic collection class is 1-based, which means that the index starts from 1. The namespace for the Visual Basic collection class is Microsoft.VisualBasic. The index value in the .NET Framework collection class is 0-based, which means that the index starts from 0. The namespace for the .NET Framework collection class is System.Collections.Generic. A generic collection is useful when every item in the collection has the same data type.

17. Create a Microsoft Visual Basic Collection Object To create a Microsoft Visual Basic collection object newVBCollection, one can use the following declarations: Dim newVBCollection As New Microsoft.VisualBasic.Collection() Or Dim newVBCollection As New Collection() The first declaration uses the full name of the collection class, which means that both the class name and the namespace is included. The second declaration uses only the collection class name with the default namespace. To create a .NET Framework collection object newNETCollection, the following declaration can be used: Dim newNETCollection As New System.Collections.Generic.Dictionary(Of String, String) or Dim newNETCollection As New Dictionary(Of String, String) The first declaration uses the full class name and the second one only uses the class name with the default namespace.

18. 5.2.4.2 Validate Data Using the Generic Collection First we need to create the generic collection object for our Insert faculty form. Since this collection will be used by the different procedures in this form, so a form-level or a model-level object should be created. Enter the codes that are shown in Figure 5-2 into the form’s declaration section. In order to use the collection object to check all textboxes, one needs to add all textboxes into the collection object after the collection object FacultyCollection is created by using the Add() method. The data insertion will not be performed until all textboxes are non-empty in this application.

19. Adding and Removing TextBoxes from CollectionObject Based on these descriptions, we need to create two user-defined subroutines to perform this adding and removing textboxes from the collection object. The coding for the Insert button’s click event procedure is Two subroutines, CreateFacultyCollection and RemoveFacultyCollection, are included in this event procedure.

20. The Coding for Two Subroutines The coding are shown in Figures 5-4 and 5-5. Each textbox is added into the collection by using the Add() method with two parameters; the first one is the so-called Key parameter represented in a string format, and the second is the content of each textbox, which is considered as the Value parameter.

21. The Coding for the Checkbox chkPhoto Recall that when the project begins to run, the Photo Name and the Photo Location textboxes are disabled. As the user checked this checkbox, it means that the user wants to include a faculty photo to the data insertion. Both the Photo Name and the Photo Location boxes should be enabled and focused to allow users to enter the associated information about the faculty photo. Enter the codes that are shown in Figure 5-6 into this chkPhoto click event procedure.

22. 5.2.5 Initialization and Termination Coding for the Data Insertion This section we need to handle the coding for the following event procedures: • Coding for the Form_Load event procedure to initialize the combo box comboMethod to display two data insertion methods: TableAdapter.Insert() and TableAdapter.Update(). • Coding for the Cancel button event procedure to erase all contents from those textboxes that contained the faculty information. • Coding for the Back button event procedure to return the program to the Selection form window to allow users to select other data operations.

23. 5.2.5 Initialization and Termination Coding for the Data Insertion - 2 The coding for the Form_Load and Cancelbutton event procedure is shown below. The coding for the Back button is simple: Me.Close().

24. 5.2.6 Build the Insert Query 5.2.6.1 Configure the TableAdapter and Build the Data Inserting Query In order to use the TableAdapter.Insert() DBDirect method to access the database, we need first to configure the TableAdapter and build the Insert query. • Open the Data Source window by going to Data|Show Data Sources • Click the Edit the DataSet with Designer button • Right click on the bottom item from the Faculty table and select the Add Query item from the popup menu • Keep the default selection: Use SQL statements and then Next • Select and check the INSERT item, then Next • Click the Query Builder button to build our insert query • Modify the query function name from the default one to the InsertFaculty and click the Next to go to the last window. • Click the Finish button to complete this query building and close the wizard.

25. 5.2.6 Build the Insert Query - 2 The finished Query Builder window is shown in Figure 5-9. Seven question marks represents the seven input parameters that will be inserted into the database.

26. 5.2.7 Develop Codes to Insert Data Using the TableAdapter.Insert Method The coding for the Insert button click event procedure is shown in Figure 5-10.

27. Avoid Multiple Same Data Insertions It is a good way to avoid the multiple insertions of the same data into the database by disabling the Insert button after a data-insertion is successfully completed. A question arises: When and how this button can be enabled again to allow us to insert another new record if we want to do that later? The solution to this question is to develop another event procedure to handle this issue. If the txtID who stores the faculty_id (primary key) is changed, it means that a new record will be inserted.

28. 5.2.8 Develop Codes to Insert Data Using the TableAdapter.Update Method When a data-driven application uses DataSet to store data, as we did for this application by using the CSE_DEPTDataSet, one can use the TableAdapter.Update() method to insert or add a new record into the database. To insert a new record into the database using this method, two steps are needed: 1. Add new records to the desired data table in the DataSet. For example, in this application, the Faculty table in the DataSetCSE_DEPTDataSet. 2. Call the TableAdapter.Update() method to update new added records from the data table in the DataSet to the data table in the database. The amount of data to be updated can be controlled by passing the different argument in the Update() method, either an entire DataSet, a DataTable, an array of DataRow, or a single DataRow.

29. The Coding for the Second Data Insertion Method The old codes have been highlighted with gray color.

30. The Coding for the Function InsertFacultyRow The finished coding is shown in Figure 5-13. The point is that the input parameter is a DataRow object – FacultyRow which is passed by reference. In this way, it can be returned to the calling procedure. So this input parameter works as both input and output for this function. The functionality of this function is to assign data stored in all textboxes in the Faculty form to each associated column in the DataRow object.

31. Perform the Data Insertion At this point, we completed the coding for our data insertion by using two methods. Now let’s test our coding by running our project. You have two ways to test the project. . • Run the project starting from the LogIn form, Selection form and then the Faculty form. Then you can click the Insert button in the Faculty form to open the Insert faculty form window to test the data insertion. • Directly starting from the Insert Faculty form. To run the project in the first way, you need to add some codes (shown in Figure 5-14) into the Insert button event procedure in the Faculty form to direct the program to open the Insert Faculty form window and close the Faculty form window.

32. Testing the Data Insertion Start the project and enter data that are shown in Figure 5-15 into the Insert Faculty Form to test this insertion. Click the Insert button to perform this data insertion. You can open the Faculty table in our sample database CSE_DEPT.mdb to confirm this data insertion.

33. 5.2.9 Validate Data After the Data Insertion 5.2.9.1 Develop Codes to Retrieve Back the New Inserted Records Another way to confirm this data insertion is to use the Select button, exactly the Select button’s click event procedure in the Insert Faculty Form to do this job. Open that event procedure and enter the codes that are shown in Figure 5-17 into this event procedure.

34. 5.2.9.2 Data Binding for All Faculty-Information Related Textboxes Before we can run the project to test this data validation, we need first to perform data binding to connect each faculty-information related textbox to each data column in the DataSet. As we did in section 4.11 in Chapter 4, we need to set up a one-to-one relationship between each textbox that contains one piece of faculty information and each data column in the Faculty data table in the DataSet. We need to setup this relationship in the graphical user interface of the Insert Faculty form window. We need to setup one-to-one relationship for the following textboxes: Faculty ID textbox – txtID Faculty Title textbox – txtTitle Faculty Office textbox – txtOffice Faculty Phone textbox – txtPhone Faculty College textbox – txtCollege Faculty Email textbox – txtEmail

35. Binding Examples A Binding example between the Faculty ID textbox and the faculty_id column, between the Office textbox and the office column in the Faculty table, is shown in Figure 5-18.

36. Testing the Second Data Insertion Method Now let’s test to insert data using the TableAdapter.Update() method. To do that, select the TableAdapter Update from the combo box comboMethod, and then enter the following information into the associated textboxes as the new faculty information: A66789 Faculty ID textbox Williams Johnson Faculty Name textbox Associate Professor Title textbox MTC-229 Office textbox 750-330-5588 Phone textbox University of Toronto College textbox wjohnson@college.edu Email textbox Click the Insert button to insert this new record into the database. Exactly this new record will be first inserted into the Faculty table in the DataSet, and then it will be inserted into the Faculty table in the database when the TableAdapter.Update() method is executed.

37. Testing the Second Data Insertion Method - 2 Now let’s test our data validation functionality by clicking the Select button to retrieve back that new inserted data from the database, and re-display them in the associated textboxes. All information about the new inserted faculty record is displayed in the associated textboxes, which is shown in Figure 5-19.

38. 5.2.9.3Develop Codes to Display the New Inserted Faculty Photo Four controls used to display the new inserted faculty’s photo on this Insert Faculty form window are: • Faculty Photo checkbox • Photo Name textbox • Photo Location textbox • PhotoBox picture box The Faculty Photo checkbox is used to control the Photo Name and the Photo Location textboxes. When this checkbox is checked, it means that the user wants to display the faculty photo for this data insertion. The default location to store the faculty photo file is the folder in which our Visual Basic.NET executable file is located. When the default location is used, you do not need to provide the detailed path and the folder in which the photo file is located.

39. 5.2.9.3Develop Codes to Display the New Inserted Faculty Photo - 2 The coding should be done in the Select button event procedure during the data validation since the photo name and the photo location can be obtained from two textboxes: the Photo Name and the Photo Location. Now let’s develop the coding for this faculty photo displaying issue.

40. Testing the Data Insertion Method with Photo File After completing the login and selection process, on the opened Insert faculty form window, check the Faculty Photo checkbox and enter the following information into the associated textboxes: Click the Select button to confirm this data insertion.

41. 5.3 Insert Data into the SQL Server Database Using a Sample Project SQLInsertWizard Basically there is no significant difference between inserting data into the Microsoft Access or SQL Server databases. For both kinds of databases, one can use either method: TableAdapter.Insert() or the TableAdapter.Update(), to insert data into the database. A small difference between these two databases is that you cannot call the stored procedures to insert data into the Microsoft Access database by using the TableAdapter (you can do that using the run time object method by executing the ExecuteNonQuery() method), but you can use stored procedures to finish the data insertion for the SQL Server database by using the TableAdapter. In this section, first we concentrate on the data insertion using the two TableAdapter methods, and then we discuss how to insert data using the stored procedure method in the next section.

42. 5.3.1 Modify the Existing Project to a New Data Insertion project Open the Windows Explorer and create a new folder such as Chapter 5, and then browse to our project SelectWizard Project, copy this project to our new folder Chapter 5. Change the name of the solution and the project from SelectWizard to SQLInsertWizard, and then double click the SQLInsertWizard Project.vbproj to open this project. Perform the following modifications to get our desired project: • Go to Project|SQLInsertWizard Project Properties menu item to open the project’s property window. Change the Assembly name: from SelectWizard Project to SQLInsertWizard Project and the Root namespace from SelectWizard_Project to SQLInsertWizard_Project, respectively. • Click the Assembly Information button to open the Assembly Information dialog box, change the Title: and the Product: to SQLInsertWizard Project. Click OK to close this dialog box. Go to the File|Save All to save those modifications.

43. 5.3.2 Create a New Form Window to Insert Data for the Course Form Recall that in the project SelectWizard, the Course form window contains a button named Insert. We want to use that button to insert a new course record into the Course table in the database. To do that, we need to create a new form window named InsertCourseForm that should be triggered by the clicking of the Insert button from the Course form as the project runs. • When the user clicks the Insert button, the Insert Course form window will be shown up. This form allows users to use two different methods to insert data into the database. • The form also allows users to enter all pieces of new course information into the appropriate textboxes for the selected faculty based on the faculty name. • By clicking the Insert button, a new course record about a faculty is inserted into the database. • The Select button is used to validate this data insertion by retrieving back the new inserted data.

44. 5.3.2 Create a New Form Window to Insert Data for the Course Form - 2 Create a new Form and add the following items that are shown in Table 5-3 into this form.

45. 5.3.2 Create a New Form Window to Insert Data for the Course Form - 3 In addition to the Form’s properties shown in Table 5-3, the following properties of the form should be setup: • AcceptButton: cmdInsert • StartPosition: CenterScreen Your finished Insert Course form is shown in Figure 5-23.

46. 5.3.3 Project Initialization and Validate Data Before the Data Insertion Totally we have 6 pieces of course information, faculty_id, course_id, course schedule, classroom, credits and enrollment, and all of these pieces of information should be entered by the user into 6 textboxes as the project runs. Also the combo box Faculty Name should be initialized by adding all faculty members to allow users to make the selection from this box. The coding for the Form_Load event procedure of the Insert Course Form is shown in Figure 5-24.

47. Data Validation Before the Data insertion This data validation is to make sure that all textboxes that contain the course information are non-empty. Two subroutines are used to perform this data validation.

48. The Coding for the Back and Cancel Buttons Event Procedure When the Back button is clicked, the current form should be closed and the project is returned to the Selection form to allow users to perform other data actions. The coding for this button is easy. Open its event procedure and enter Me.Close(). Yes, that is! The functionality of the Cancel button is to clean up most textboxes’ contents to allow users to re-enter all course-related information into those textboxes. The coding for this event procedure is shown in Figure 5-27. All textboxes’ contents, except the Course ID, are cleaned up to allow users to re-enter new course information. We need to retrieve back the new inserted course record from the database based on the Course ID later.

49. 5.3.4 Configure the TableAdapter and Build the Data Insertion Query Recall that when we built our sample database CSE_DEPT in chapter 2, there is no faculty name column in the Course table, and the only relationship existed between the Faculty and the Course tables is the faculty_id, which is a primary key in the Faculty table but a foreign key in the Course table. As the project runs and the Insert Course form window is shown up, the user needs to insert new course data based on the faculty name, not the faculty ID. So we need to perform two queries with two tables: first we need to make a query to the Faculty table to get the faculty_id based on the faculty name selected by the user, and second we can insert a new course record based on the faculty_id we obtained from our first query. These two queries belong to two TableAdapters; the FacultyTableAdapter and the CourseTable-Adapter. Now let’s to create these two query functions under two TableAdapters.

50. 5.3.4 Configure the TableAdapter and Build the Data Insertion Query - 2 Recall that in section 4.14 in the last chapter, we have built a query function under the FacultyTableAdapter and the function is named FindFacultyIDByName(). So in this section, we don’t need to redo this job and we can use that query function directly from this project. What we need to do is to create the query function to insert new course record based on the selected faculty_id we obtained from the first query. Open the DataSet Designer Wizard. Right-click on the last line of the Course table and choose the Add|Query item to open the TableAdapter Query Configuration Wizard. Keep the default item Use SQL statements selected and click the Next button to go to the next window. Select the INSERT item by checking this radio button and click the Next again to continue. Click the Query Builder button on the next window to open the Query Builder dialog box, which is shown in Figure 5-28.