Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to use the edit section of a grid view control

Posted on 2009-05-09
13
Medium Priority
?
270 Views
Last Modified: 2012-05-06
Hello Guys! I am in the process of filtering records and then displaying them in a grid view control. The data displays just fine. I have the edit button in the side. I wanted to know what code should be written there, that would let the user update the data to the database. Thanks in advance. I'll paste the code that i'm using to diplay the data to the grid view.
Dim con As New System.Data.OleDb.OleDbConnection
        Dim dreader As System.Data.OleDb.OleDbDataReader
        Dim myCommand As New System.Data.OleDb.OleDbCommand
        Dim myPath As String
 
        myPath = Server.MapPath("hayat.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
        myCommand.CommandText = "select * from empdetails where empid between " & TextBox1.Text & " AND " & TextBox2.Text & " order by empid"
        myCommand.Connection = con
        Try
            con.Open()
            dreader = myCommand.ExecuteReader
            GridView1.DataSource = dreader
            GridView1.DataBind()
            dreader.Close()
        Catch ex As Exception
            lblerror.Text = "There is an error" + ex.ToString
        Finally
            con.Close()
        End Try

Open in new window

0
Comment
Question by:preethamonline
  • 7
  • 5
13 Comments
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24344108
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

Open in new window

0
 

Author Comment

by:preethamonline
ID: 24344290
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?
0
 
LVL 23

Expert Comment

by:Saqib Khan
ID: 24344408
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/

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:preethamonline
ID: 24344433
thanks guys. Will check it out and will let you know.
0
 
LVL 11

Accepted Solution

by:
Muhammad Ousama Ghazali earned 2000 total points
ID: 24346057
My earlier answer was based more upon the theoretical knowledge and was not tested as my VS was crashing at the time. Please consider it partially flawed and check the new snippet I am attaching.

Although, adilkhan has pointed to a good resource, you can still check out these links for better understanding of GridView:

http://www.asp.net/learn/data-access/
http://msdn.microsoft.com/en-us/magazine/cc163933.aspx

However, I am posting a quick solution to your question. You can keep either your version of code with OleDbDataReader or use my earlier code with DataSet to display the records. The code snippet assumes that:
  1. You have a table named EMPLOYEES with ID, Name, and Description fields
  2. You have set DataKeyNames properyt of the GridView1 control to ID field.
  3. You have set AutoGenerateColumns property to False.
  4. There is a lable control on page named lblMessage.
For successful incorporation of "Edit, Update, Cancel" command button of GridView you need to code in at least three events of the GridView:
  • RowCancelingEdit
  • RowEditing
  • RowUpdating
My code may not be perfect but I have tested it for example purposes. Hope this helps.
Dim cnnTemp As OleDbConnection = Nothing
Dim cmdTemp As OleDbCommand = Nothing
 
Dim strDbPath As String = Server.MapPath("hayat.mdb")
Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & strDbPath & "; User Id=Admin;Password=;"
Dim strQuery As String = String.Empty
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
 
	If IsPostBack Then Return
	LoadEmployeeDetail()
 
End Sub
 
Private Sub LoadEmployeeDetail()
 
	lblMessage.ForeColor = Drawing.Color.Green
	'Change it as per your own table
	strQuery = "SELECT ID, Name, Description FROM EMPLOYEES"
 
	Try
		cnnTemp = New OleDbConnection(strConnectionString)
		cmdTemp = New OleDbCommand(strQuery, cnnTemp)
 
		cnnTemp.Open()
 
		GridView1.DataSource = cmdTemp.ExecuteReader()
		GridView1.DataBind()
 
	Catch ex As Exception
		lblMessage.ForeColor = Drawing.Color.Red
		lblMessage.Text = "There is an error" + ex.ToString()
 
	Finally
		cmdTemp = Nothing
		cnnTemp = Nothing
 
	End Try
 
End Sub
 
Private Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView1.RowCancelingEdit
 
	GridView1.EditIndex = -1
	LoadEmployeeDetail()
 
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
	LoadEmployeeDetail()
 
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
 
	'Change it as per your own table
	strQuery = "UPDATE EMPLOYEES SET Name = '" & strName & "', Description = '" & strDescription & "' WHERE ID = " & strId
 
	Try
		cnnTemp = New OleDbConnection(strConnectionString)
		cmdTemp = New OleDbCommand(strQuery, cnnTemp)
 
		cnnTemp.Open()
		cmdTemp.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
		cnnTemp.Close()
		cmdTemp = Nothing
		cnnTemp = Nothing
 
	End Try
 
	GridView1.EditIndex = -1 'Must set this to get out of editing mode
 
	LoadEmployeeDetail() 'Reload all records
 
End Sub

Open in new window

0
 

Author Comment

by:preethamonline
ID: 24352970
Dear Mogha,
I'm not able to update the database. I get the error saying that the file is already in use.
0
 

Author Comment

by:preethamonline
ID: 24353019
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
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24355394
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.
0
 

Author Comment

by:preethamonline
ID: 24403463
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
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24403795
It was un-intentional mistake. Please update
GridView1.DataKeys(e.RowIndex).Value
with
GridView1.DataKeys(0).Value
0
 

Author Comment

by:preethamonline
ID: 24405428
Sorry dear. Still the same mistake.
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24406124
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.
0
 

Author Comment

by:preethamonline
ID: 24406596
You're right. i missed out on that. Thanks for reminding me. I will try and let you know. Thanks for your patience.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lots of people ask this question on how to extend the “MembershipProvider” to make use of custom authentication like using existing database or make use of some other way of authentication. Many blogs show you how to extend the membership provider c…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question