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 Visual Basic .NET or Microsoft Visual Studio .NET
2. In Server Explorer, expand the Servers, the name of the server, and the SQL Servers nodes. Expand the name of the server. If you see a database named CarInventory, fine. If not, To create a new database, right-click the name of the server and click New Database. Set the name of the database to CarInventory and click OK 
3. Expand the new CarInventory node. To create a new table, right-click the Tables node and click New Table
4. Complete the table as follows:
    

   Column Name	Data Type	Length	Allow Nulls	Properties
   CarID	int			Primary Key Identity: Yes
   TagNumber	varchar	20	Unchecked
   Make	varchar	40	Unchecked
   Model	varchar	40	Unchecked
   CarYear	varchar	20	Unchecked
   Category	varchar	40	Unchecked
   HasK7Player	bit
   HasCDPlayer	bit
   HasDVDPlayer	bit
   CarPicture	varchar	240
   IsAvailable	bit

5. Save the table as Cars and close it
6. On the main menu of Visual Studio .NET, click File -> New -> Project
7. In the Project Types list, select Microsoft Visual Basic Projects. In the Templates list, click Windows Forms Application
8. Set the Name to CarInventory2 and click OK
9. 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

10. In the Windows Forms section of the Toolbox, click the OpenFileDialog button and click the form
11. Set its Filter as Picture Files (*.bmp;*.gif;*.jpeg;*jpg)|*.bmp;*.gif;*.jpeg;*jpg
12. Set its Title to Select Car Picture
13. 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

14. From Server Explorer, open the server that holds the above database then expand the CarInventory database followed by the Tables node
15. Drag the Cars table and drop it on the form
16. On the main menu, click Data -> Generate Dataset...
17. While the New radio button is selected, change the name of the dataset to dsCarInventory and click OK
18. 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.SqlDataAdapter1.Fill(Me.DsCarInventory1) End Sub

19. In the Class Name combo box, select btnAdd
20. In the Method Name combo box, select Click
21. In the Class Name combo box, select btnClose
22. In the Method Name combo box, select Click
23. Implement the events as follows:
     

    Private Sub btnAddCar_Click(ByVal sender As 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.SqlDataAdapter1.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 Object, ByVal e As System.EventArgs) _ Handles btnClose.Click End End Sub

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