How to use the edit section of a grid view control

Instead of using OleDbDataReader and OleDbDataCommand you have to use DataSet and OleDbDataAdapter to utilise the GridView's built in Edit-Update features.

I am attaching a code snippet which may help you. Call the LoadEmployeeDetail() to display the data on page load or any other event and use UpdateEmployeeDetail() for updating any possible changes. Call the later function on click event of any button or other desired event.

Hope this helps.

Imports System.Data
Imports System.Data.OleDb
 
Dim cnnTemp As OleDbConnection = Nothing
Dim dsTemp As DataSet = Nothing
Dim daTemp As OleDbDataAdapter = Nothing
 
Private Sub LoadEmployeeDetail()
	Dim strDbPath As String = Server.MapPath("hayat.mdb")
	Dim strQuery As String = "select * from empdetails where empid between " & TextBox1.Text & " AND " & TextBox2.Text & " order by empid"
 
	Try
		cnnTemp = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & strDbPath & ";")
		daTemp = New OleDbDataAdapter(strQuery, cnnTemp)
		dsTemp = New DataSet("EmployeeDetails")
 
		daTemp.Fill(dsTemp)
 
		GridView1.DataSource = dsTemp
		GridView1.DataBind()
 
	Catch ex As Exception
		lblerror.Text = "There is an error" + ex.ToString
 
	Finally
		If cnnTemp.State = ConnectionState.Open Then cnnTemp.Close()
 
	End Try
 
End Sub
 
Private Sub UpdateEmployeeDetail()
 
	Dim dsChanges As DataSet = dsTemp.GetChanges()
	If dsChanges IsNot Nothing Then
		daTemp.Update(dsChanges)
		lblMessage.Text = "Changes were found and have been updated successfully."
 
	Else
		lblMessage.Text = "No changes were found."
 
	End If
 
End Sub

Select allOpen in new window

Thanks Moghazali. i will try it out. I guess i will be calling the LoadEmployeeDetail() on the click of  a buttton. Where should i call the UpdateEmployeeDetail(). Should i call it gridview1_rowediting or somewhere else?

I wrote a simple yet FULL Tutorial regarding GridView, see if it helps.

http://www.knowlegezone.com/documents/70/ASPNET-Formview--GridView-Coltrol-Insert-Update-Paging-And-Delete/

thanks guys. Will check it out and will let you know.

Dear Mogha,
I'm not able to update the database. I get the error saying that the file is already in use.

This is the other code that i have used.
Private Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit

        GridView1.EditIndex = -1

    End Sub

    Private Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView1.RowEditing

        GridView1.EditIndex = e.NewEditIndex

    End Sub
    Private Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating

        lblMessage.ForeColor = Drawing.Color.Green

        Dim strId As String = GridView1.DataKeys(e.RowIndex).Value
        Dim strName As String = CType(GridView1.Rows(e.RowIndex).Cells(0).Controls(0), TextBox).Text
        Dim strDescription As String = CType(GridView1.Rows(e.RowIndex).Cells(1).Controls(0), TextBox).Text


        Dim con As New System.Data.OleDb.OleDbConnection

        Dim myPath As String
        myPath = Server.MapPath("hayat.mdb")

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
        Dim myCommand As New System.Data.OleDb.OleDbCommand

        myCommand.CommandText = "UPDATE EMPLOYEES SET Name = '" & strName & "', Description = '" & strDescription & "' WHERE ID = " & strId
        myCommand.Connection = con
        Try
            con.Open()
            myCommand.ExecuteNonQuery()
            lblmessage.Text = "Changes were found and have been updated successfully."

        Catch ex As Exception
            lblmessage.ForeColor = Drawing.Color.Red
            lblmessage.Text = "There is an error" + ex.ToString()

        Finally
            con.Close()
            myCommand = Nothing
            con = Nothing

        End Try

        GridView1.EditIndex = -1 'Must set this to get out of editing mod

    End Sub

If it is on the development machine, make sure to close the Access Database file or Exit the Access altogether and re-run the code.

Also make sure that when you exit the Access or close the database, there is no Access lock file in addition to .MDB file in the folder where database file is located. If closing the Access or database file does not remove this file, it probably is locked with IIS or ASP.NET service. Run iisreset command to reset the IIS and related web applications and then re-test the web application.

I hope this time it works because I have tested the same code at my machine.

Sorry guys for the delay in responding. I'm still getting an error. dunno what i'm doing wrong. the reords load properly. when i select edit, and try to update a value, i get the following error.
error.JPG

It was un-intentional mistake. Please update
GridView1.DataKeys(e.RowIndex).Value
with
GridView1.DataKeys(0).Value

Sorry dear. Still the same mistake.

Did you do the point 2 as mentioned in my detailed post above. Copying a modified text here:
You have to set DataKeyNames property of the GridView1 control to ID field.

You're right. i missed out on that. Thanks for reminding me. I will try and let you know. Thanks for your patience.