<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
Sub Page_Load(Sender As Object, E As EventArgs)
Dim strConnection As String
Dim strSQL As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter
' strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
' "Data Source=C:\Northwind.mdb"
strConnection = "Provider=sqloledb;Data Source=whsql-v08.prod.mesa1.secureserver.net;Initial Catalog=DB_49907;User ID=rntsoftuser;Password=password;"
strSQL = "SELECT EmployeeID, FirstName, LastName FROM Employees;"
objConnection = New OledbConnection(strConnection)
objAdapter = New OledbDataAdapter(strSQL, objConnection)
objAdapter.Fill(objDataSet, "Employees")
dgNameList1.DataSource = objDataSet.Tables("Employees").DefaultView
dgNameList1.DataBind()
Dim objTable As DataTable
Dim objNewRow As DataRow
objTable = objDataSet.Tables("Employees")
objNewRow = objTable.NewRow()
objNewRow("FirstName") = "Norman"
objNewRow("LastName") = "Blake"
objTable.Rows.Add(objNewRow)
objNewRow = objTable.NewRow()
objNewRow("FirstName") = "Kasey"
objNewRow("LastName") = "Chambers"
objTable.Rows.Add(objNewRow)
dgNameList2.DataSource = objTable.DefaultView
dgNameList2.DataBind()
Dim objRow As DataRow
objRow = objTable.Rows(3)
objRow("FirstName") = "John"
objRow("LastName") = "Hartford"
' bind the data grid to the new data
dgNameList3.DataSource = objTable.DefaultView
dgNameList3.DataBind()
objTable.Rows(objTable.Rows.Count - 2).Delete()
dgNameList4.DataSource = objTable.DefaultView
dgNameList4.DataBind()
Dim objBuilder As OleDbCommandBuilder
objBuilder = New OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()
objAdapter.InsertCommand = objBuilder.GetInsertCommand()
objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()
objAdapter.Update(objDataSet, "Employees")
strSQL = "SELECT EmployeeID, FirstName, LastName FROM Employees"
objConnection.Open()
Dim objCmd As New OleDbCommand(strSQL, objConnection)
dgUpd.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
dgUpd.DataBind()
End Sub
Original Data
Data with new Row
Data with edited Row
Data with deleted Row
Data fetched from database after the update: