Solved

How to use the edit section of a grid view control

Posted on 2009-05-09
13
256 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in JQuery 5 51
Gridview alignment 1 19
Open MVC5 View in new Window (not Tab) using HTML.ActionLink 6 26
transaction in asp.net, sql server 6 31
One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

813 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now