Exforsys.com
 
Home Tutorials VB.NET 2005
 

Data Manipulation with ADO.NET

 

Editing Data 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:


(a) Update Child table – Delete records. The code sample is given below


(b) Dim DeletedChildRecords as DataTable = _


(c) DSet.Table.GetChanges(DataRowState.Deleted) DataAdapter.Update(DeletedChildRecords)


(d) Parent Table – Insert, Update, and delete records


(e) Child Table – Insert and update records.


Adding Data

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


Typed data set

Dim newCustomersRow as DataSetClass.Customers.CustomersRow


newCustomersRow = DataSetInstance.Customers.NewCustomersRow()


newCustomersRow.CustomerID = "ALFKI"


newCustomersRow.CompanyName = "Alfreds Futterkiste"


DataSetInstance.Customers.Rows.Add(newCustomersRow)


Untyped data set

Dim newCustomersRow As DataRow = DataSet1.Tables("Customers").NewRow()


newCustomersRow("CustomerID") = "ALFKI"


newCustomersRow("CompanyName") = "Alfreds Futterkiste"


DataSet1.Tables("Customers").Rows.Add(newCustomersRow)


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).


The following line of code is used to delete nth row.


DataSetXX.TableYYY.Rows(n).Delete()


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.


Click here to view sample code



Read Next: Using XML Data



 

 

Comments


sha said:

  by using databinding , with text boxes how to edit a record and also how to delete a record from the
database in VB.Net
August 5, 2006, 10:33 pm

hana said:

  it was usefull :)
September 11, 2006, 7:11 am

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, 6:26 am

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, 12:18 am

samad said:

  i am new to ado.net 2005.please tell me how can i wite a professional ado.net programm?
June 19, 2007, 4:24 pm

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, 5:32 am

Ashokkakollu said:

  can u give me an idea about those two objects?
August 7, 2007, 4:59 am

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, 6:20 am

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, 7:50 am

obaid said:

  brilliant way to explain and summarize the complexities of the database handling, great tutorial !
December 4, 2007, 9:33 am

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 8, 2007, 2:48 pm

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, 11:30 pm

raghul said:

  sir,

i am working with Datagridview .But I want Display the value Dynamically.If i use where in SQL query it repaces the data..
But the data is stored in DB as in given order
Here is the code

Dim cmd2 As New SqlCommand("select * from Intime where Intime='" Label3.Text "'", con)
cmd2.ExecuteNonQuery()

Dim ad2 As New SqlDataAdapter(cmd2)
Dim ds As New DataSet()
ad2.Fill(ds, "Intime")

Else : exists = False
MessageBox.Show("INVALID NUMBER")
TextBox1.Clear()
End If

Sorry for my BAD ENGLISH
September 30, 2008, 7:42 am

Albert Maria Joseph said:

  Kindly help me to update a table in access using datagrid view. That is, I displayed all the records in the access database in a datagridview. From this datagridview i want to insert data, edit data, update data, delete data to the access table. Pls help me with vb.net 2005 coding.
With regards
albert
August 25, 2009, 5:38 am

Post Your Comment:

Members Please Login
Your Name:*
e-mail ID:(required for notification)*
Image Verification: 
 
 Subscribe    

Sponsored Links

 

Subscribe via RSS


Get Daily Updates via Subscribe to Exforsys Free Training via email


Get Latest Free Training Updates delivered directly to your Inbox...

Enter your email address:


 

Subscribe to Exforsys Free Training via RSS
 

 
Partners -  Privacy and Legal Policy -  Site News -  Contact   Sitemap  

Copyright © 2000 - 2010 exforsys.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape