Data Entry With the DataRow

ADO.NET provides different types of support for data entry into a table. For example, you can use the classic INSERT INTO expression from the SQL. You can also create a stored procedure that internally creates a new record. Besides the INSERT expression or a stored procedure, to perform data entry, you can use a DataSet object.

The DataSet class is equipped with a Tables property, which is a collection of the tables that are part of the data set. The Tables property is an object of type DataTableCollection. As its name indicates, the DataTableCollection itself is a list of tables and each table can be located using an index, either by the numeric order of the table or its name. The DataTableCollection class supports this through an Item property which is based on the DataTable class. This relationship makes it possible to access the contents of a table. Fundamentally, a table is made of columns and records and each record is also called a row.

The records of a table are stored in a member of the DataTable called Rows. The Rows property is an object of type DataRowCollection. An individual record is an object of type DataRow. To represent each record, the DataRowCollection is equipped with an Item property which is of type DataRow. To perform data entry, you can assign a value to the name of each column of the desired table of your database. Each column is accessible through the Item property of the DataRow class. One of the overloaded versions of this property takes a String value as its index.

After assigning the right value to each DataRow.Item that corresponds to the desired column of a table, you can call the Update() method of the data adapter you were using. This means that, previously, you should have initialized a data adapter.

1. Start Microsoft Access and create a Blank Database named CarInventory1
2. In the Tables section of the Database window, double-click Create Table In Design View
3. Complete it as follows:
    

   Field Name	Data Type	Length	Properties
   CarID	AutoNumber		Primary Key
   TagNumber	Text	20	Indexed: Yes(No Duplicate)
   Make	Text	40
   Model	Text	40
   CarYear	Text	10
   Category	Text	40	Row Source Type: Value List Row Source: "Economy";"Compact";"Standard";"Full Size";"Mini Van";"SUV";"Truck";"Van"
   HasK7Player	Yes/No
   HasCDPlayer	Yes/No
   HasDVDPlayer	Yes/No
   CarPicture	Text	240
   IsAvailable	Yes/No

4. Save the table as Cars and close it
5. Close Microsoft Access
6. Start Microsoft Visual Basic .NET or Microsoft Visual Studio .NET and create a Windows Forms Application named CarInventory3
7. Design the form as follows: 
    

   Control Text Name Other Properties Label Text #     TextBox   txtTagNumber   Label Make:     TextBox   txtMake   Label Model:     TextBox   txtModel   Label Year:     TextBox   txtYear   Label Category:     ComboBox   cboCategory DropDownStyle: DropDownList Items: Economy Compact Standard Full Size Mini Van SUV Truck Van CheckBox Cassete Player chkK7Player CheckAlign: MiddleRight CheckBox DVD Player chkDVDPlayer CheckAlign: MiddleRight CheckBox CD Player chkCDPlayer CheckAlign: MiddleRight CheckBox Available chkAvailable CheckAlign: MiddleRight PictureBox   pctCar SizeMode: CenterImage Button Picture btnPicture   TextBox   txtPicture   Button Add Car btnAddCar   Button Close btnClose DialogResult: OK Form     AcceptButton: btnAddCar MaximizeBox: False StartPosition: CenterScreen

8. In the Windows Forms section of the Toolbox, click the OpenFileDialog button and click the form
9. Set its Filter as Picture Files (*.bmp;*.gif;*.jpeg;*jpg)|*.bmp;*.gif;*.jpeg;*jpg
10. Set its Title to Select Car Picture
11. On the form, double-click the Picture button and implement its event as follows:
     

    Private Sub btnPicture_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnPicture.Click If openFileDialog1.ShowDialog() = DialogResult.OK Then Me.txtPicture.Text = openFileDialog1.FileName Me.pctCar.Image = Bitmap.FromFile(openFileDialog1.FileName) End If End Sub

12. Return to the form
13. From the Data section of the Toolbox, click OleDbDataAdapter and click the form
14. In the first page of the Data Adapter Configuration Wizard, click Next
15. In the second page of the wizard, click New Connection
16. In the Data Link Properties, click the Provider property page
17. In the Provider tab, click Microsoft Jet 4.0 OLE DB Provider
     
    
18. Click Next
19. In the Connection page, click the ellipsis button on the right side of 1
20. Locate and select the CarInventory1 database you created above and click Open
21. Click Test Connection and click OK twice
22. In the second page of the wizard, click Next
23. In the third page, accept the first radio button (Use SQL Statement) and click Next
24. In the fourth page, click Query Builder
25. In the Add Table dialog box, as Cars is selected, click Add and click Close
26. In the list of fields, click the check box on the left of the * column
27. Click OK
     
    
28. Click Next and click Finish. For this exercise, if a message box comes up, click Don't Include Password
     
    
29. On the main menu, click Data -> Generate Dataset...
30. While the New radio button is selected, change the name of the dataset to dsCarInventory and click OK
31. Double-click an empty area of the form and change the Load event as follows:
     

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles MyBase.Load Me.OleDbDataAdapter1.Fill(Me.DsCarInventory1) End Sub

32. Return to the form and double-click the Add Car followed by the Close buttons
33. Implement the events as follows:
     

    Private Sub btnAddCar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnAddCar.Click Dim tblCars As DataTable = New DataTable tblCars = Me.DsCarInventory1.Tables("Cars") Dim rowNewCar As DataRow rowNewCar = tblCars.NewRow() rowNewCar("TagNumber") = Me.txtTagNumber.Text rowNewCar("Make") = Me.txtMake.Text rowNewCar("Model") = Me.txtModel.Text rowNewCar("CarYear") = Me.txtYear.Text rowNewCar("Category") = Me.cboCategory.Text rowNewCar("HasK7Player") = CStr(Me.chkK7Player.Checked) rowNewCar("HasCDPlayer") = CStr(Me.chkCDPlayer.Checked) rowNewCar("HasDVDPlayer") = CStr(Me.chkDVDPlayer.Checked) rowNewCar("CarPicture") = Me.txtPicture.Text rowNewCar("IsAvailable") = CStr(Me.chkAvailable.Checked) tblCars.Rows.Add(rowNewCar) Me.OleDbDataAdapter1.Update(Me.DsCarInventory1) Me.txtTagNumber.Text = "" Me.txtMake.Text = "" Me.txtModel.Text = "" Me.txtYear.Text = "" Me.cboCategory.SelectedIndex = -1 Me.chkK7Player.Checked = False Me.chkCDPlayer.Checked = False Me.chkDVDPlayer.Checked = False Me.txtPicture.Text = "" Me.chkAvailable.Checked = False Me.pctCar.Image = Nothing Me.txtTagNumber.Focus() End Sub Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles btnClose.Click End End Sub

34. Execute the application and create a record (you can first download a few pictures of cars):
     
    
35. Click Add Car to submit the record
36. Click Close to close the form