Data Manipulation with ADO.NET

In this tutorial you will learn about Editing Data With ADO .NET - Updating Data, Adding Data, Typed data set, Untyped data set, Deleting Data, Editing with a DataGrid.

Updating Data

The SqlDataAdapter’s update method is called whenever the changes made to a DataSet has to be applied to the underlying table. The SqlDataAdapter is instantiated using eh SELECT statement and the compiler generates the statements for UPDATE, INSERT and DELETE. The changes made to the DataSet are then applied to the Database. However considerations for automatic update will fail if there is no primary key assigned to the data in the Database. The update is also likely to fail if the AcceptChanges method of the DataSet is called before the Update method of the SqlDataAdapter is called. The dataset maintains the row versions like Deleted rows, modified rows, inserted row and so on. When the UPDATE method is called this version information is used to update the relevant changes to the database. The AcceptChanges method removes all the versions and hence the UPDATE will fail. AcceptChanges is usually called after Update method.

The Update method of the Adapter will be called within a Try Catch block. When two tables are updated in a dataset, the following sequence is advised as a best practice:

Adding Data

Adding new rows to the DataSet can be performed in any one of the following methods:

Typed data set

Untyped data set

Deleting Data

In order to retain the information that the dataset needs to send updates to the data source, the System.Data.DataRow.Delete method is used to remove rows in a data table. For example, if the application uses a TableAdapter (or DataAdapter), the adapter's Update method will delete rows in the database that have a RowState of DataRowState.Deleted.

If the application does not need to send updates back to a data source, then it is possible to remove records by directly accessing the data row collection DataRowCollection.Remove

To delete records from a data table call the DataRow.Delete method of a DataRow. This method does not physically remove the record; instead, it marks the record for deletion.

If the count property of a DataRowCollection is obtained, the resulting count includes records that have been marked for deletion. To get an accurate count of only records that are not marked for deletion, the user can loop through the collection looking at the RowState property of each record (records marked for deletion have a RowState of DataRowState.Deleted).

Editing with a DataGrid

A DataGrid can be edited using the BeginEdit and EndEdit methods of the DataGrid. The following illustration clarifies the concepts of editing a DataGrid.

1. Identify the current cell by its ColumnNumber and RowNumber properties and call the method BeginEdit of the DataGrid .
2. Create the DataTable and DataRow Objects.
3. Assign the edited values to the DataRow.
4. Accept changes by calling the AcceptChanges method of DataRow.
5. Call the EndEdit method of the DataGrid.

Vikramdeshkulkarni said:

thanks very much...
i got a idea of how to proceed upon with my problem with the steps given below.
please send some code to implement the logic, im working on windows based application, using vb.net

1. Identify the current cell by its ColumnNumber and RowNumber properties and call the method BeginEdit of the DataGrid .
2. Create the DataTable and DataRow Objects.
3. Assign the edited values to the DataRow.
4. Accept changes by calling the AcceptChanges method of DataRow.
5. Call the EndEdit method of the DataGrid.

January 11, 2007

sb said:

i am working in datarid
i need a update in my datatable in specific field by spicific criteria
Like i want to update the emp. leave having 3 type P_leave,C_leave,M_leave
Now first i have to check wheather what kind of leave is been update and than i a have to calculate days from date (From 29/05/2007 To 31/05/2007) and than update the specific Emp_code record
As i m new .net i am getting problem in updating datatable
Plz help me

May 30, 2007

samad said:

i am new to ado.net 2005.please tell me how can i wite a professional ado.net programm?

June 19, 2007

Atheeque Ahmed said:

Dear sir, I am developing project in vb.net I want to know about how to add & Update records using DataGridView component. Please let me help.

July 28, 2007

Ashokkakollu said:

can u give me an idea about those two objects?

August 07, 2007

sara abouhassan said:

dear sir,i need a help to insert a new row that has automated primary key
note: the table i need to insert in is a combination between texts & integers
thank you

August 13, 2007

Prashant K said:

Hi All
I have one prblem . I am updating the datgrid changes to database. I am using the
da.update(ds, "Table") to update to the database.

Code is working but in between it doesnt update the data of a particular Row in the table.

I am not able to understand why this code is not working for a particular row only.

i am sending u my code. Pls Suggest soem way to me.

Dim TagCommand As New OleDb.OleDbCommand
Dim reader As OleDb.OleDbDataReader

'for testing
Dim dsTagMap As New DataSet
Dim daTagMap As OleDb.OleDbDataAdapter
Dim connTag As OleDb.OleDbConnection

'Thsi is to laod the data to grid from table
Private Sub Loaddata()
Try

daTagMap = New OleDb.OleDbDataAdapter("select * from TagMap", modConnection.gConn)
daTagMap.Fill(dsTagMap, "TagMap")
dgvTagMapping.DataSource = dsTagMap.Tables("TagMap"

Catch ex As Exception
MsgBox(ex.Message)
End Try

'This is to update the data

Private Sub updatechange()
Try
dgvTagMapping.Refresh()
dgvTagMapping.Enabled = True

Dim cb As New OleDb.OleDbCommandBuilder(daTagMap)

Dim dsChanges As DataSet = dsTagMap.GetChanges()
If dsTagMap.IsInitialized Then
dsTagMap.EndInit()
End If

If dsTagMap.HasChanges Then
daTagMap.Update(dsTagMap, "TagMap")
End If

dgvTagMapping.EndEdit()
dsTagMap.AcceptChanges()

Catch ex As Exception
MsgBox(ex.Message)
End Try

End Sub

September 29, 2007

obaid said:

brilliant way to explain and summarize the complexities of the database handling, great tutorial !

December 04, 2007

Cliph barton said:

When i wanna creat an oledb adapter and when i give it a path of MDB (mdb is extension of microsoft access file) file there is a problem that when i press the TEST CONNECTION a massage box apears and written ''INVALID AUTHORIZATION SPECIFICATION'' and coonection habe been failed. what can i do?

December 08, 2007

Shullaymahl said:

Hey I have the same type of error working with the OleDBconnection in VB.NET pro 2005 under XP2. I have creating a new vb project as well as a new simple database with just one table and a couple of fields and it still will create a successful test. This is done after I create Oledbconnection object and then create the Connection String. When I connect using the other choice and Ole db 4 driver it works fine, but then the ds.tbl.addtblrow command lines throw a nullexception error. Anyone else other than us two have this problem?

December 23, 2007

Editing Data With ADO .NET