Updating Grid view

Question

kgenn 0 Light Poster

13 Years Ago

hello :)

I just want to ask a favor regarding the code on how to Update the edited data from the gridview. thanks.

here's the code.

<%@ Page Language="VB"%> <%--AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default"--%> 
<%@ Import Namespace="System.Data.Oledb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    Private Sub Page_Load()
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
        connection.Open()
        
        Dim command As OleDbCommand
        command = New OleDbCommand("Select * from tblLupon_C", connection)
        Dim DataReader As OleDbDataReader
        DataReader = command.ExecuteReader()
        GridView1.DataSource = DataReader
        GridView1.DataBind()
        
        connection.Close()
       
    End Sub
    
    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        Call Page_Load()
    End Sub

    Protected Sub Gridview1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdatedEventArgs)
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
        connection.Open()

        Dim command As OleDbCommand
        command = New OleDbCommand("UPDATE tblLUPON_C SET REGION_C = '& GridView1.Item(0, GridView1.CurrentRow.Index).[Value] &', PROVINCE_C = '& GridView1.Item(1, GridView1.CurrentRow.Index).[Value] &', CITYMUN_C = '& GridView1.Item(2, GridView1.CurrentRow.Index).[Value] &', BARANGAY_C = '& GridView1.Item(3, GridView1.CurrentRow.Index).[Value] &', LAST_M = '& GridView1.Item(4, GridView1.CurrentRow.Index).[Value] &', FIRST_M = '& GridView1.Item(5, GridView1.CurrentRow.Index).[Value] &', MIDDLE_M = '& GridView1.Item(6, GridView1.CurrentRow.Index).[Value] &', SUFFIX_M = '& GridView1.Item(7, GridView1.CurrentRow.Index).[Value] &', POSITION_C = '& GridView1.Item(8, GridView1.CurrentRow.Index).[Value] &', SEX_C = '& GridView1.Item(9, GridView1.CurrentRow.Index).[Value] &'", connection)
        Dim DataReader As OleDbDataReader
        DataReader = command.ExecuteReader()
        GridView1.DataSource = DataReader
        GridView1.DataBind()
        connection.Close()
    End Sub
    
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    
    <asp:GridView ID="GridView1" runat="server" EmptyDataText="No record found" 
            AutoGenerateEditButton="True"
                OnRowUpdating="Gridview1_RowUpdating"
                OnRowEditing= "GridView1_RowEditing">
    </asp:GridView>
    </div>
</form>
</body>
</html>

The Gridview1_RowUpdating was the error.

your help will be highly appreciated :D
thank you. thank you.

asp.net

2 Contributors
11 Replies
132 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by kgenn

kvprajapati 1,826 Posting Genius

13 Years Ago

Try this,

<%@ Page Language="VB"%> <%--AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default"--%> 
<%@ Import Namespace="System.Data.Oledb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    Private Sub Page_Load()
        If Not IsPostBack Then
            BindGrid()
        End If
    End Sub
    
    Sub BindGrid()
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
        connection.Open()
        
        Dim command As OleDbCommand
        command = New OleDbCommand("Select * from tblLupon_C", connection)
        Dim DataReader As OleDbDataReader
        DataReader = command.ExecuteReader()
        GridView1.DataSource = DataReader
        GridView1.DataBind()
        
        connection.Close()
    End Sub
    
    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        Call BindGrid()
    End Sub

    Protected Sub Gridview1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
        
        Dim tx1, tx2, tx3 As TextBox
        
        Dim row As GridViewRow = GridView1.Rows(e.RowIndex)
        tx1 = CType(row.Cells(0).Controls(0), TextBox)
        tx2 = CType(row.Cells(1).Controls(0), TextBox)
        tx3 = CType(row.Cells(2).Controls(0), TextBox)
        .....

                
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
       
      
        
        Dim command As OleDbCommand
        command = New OleDbCommand("UPDATE tblLUPON_C SET REGION_C =@p1, PROVINCE_C =@p2 where myid=@p3", connection)
        command.Parameters.AddWithValue("@p1", tx1.Text)
        command.Parameters.AddWithValue("@p2", tx2.Text)
        command.Parameters.AddWithValue("@p3", tx3.Text)
        
        connection.Open()

        command.ExecuteNonQuery()
        connection.Close()
        
        GridView1.EditIndex = -1
        DataBind()
    End Sub
    
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    
    <asp:GridView ID="GridView1" runat="server" EmptyDataText="No record found" 
            AutoGenerateEditButton="True"
                OnRowUpdating="Gridview1_RowUpdating"
                OnRowEditing= "GridView1_RowEditing">
    </asp:GridView>
    </div>
</form>
</body>
</html>

kvprajapati 1,826 Posting Genius

13 Years Ago

1. Update sql statement must have where clause otherwise all rows of a table will be updated with given values. Add/Choose unique or primary key with where clause.

2. Remember that the total count of Parameter objects is equals to defined parameters.

In

//there are six defined parameters in update statement
command = New OleDbCommand("UPDATE tblLUPON_C SET LAST_M =@LAST_M, FIRST_M =@FIRST_M, MIDDLE_M =@MIDDLE_M, SUFFIX_M=@SUFFIX_M, SEX_C=@SEX_M, POSITION_C =@POSITION_M", connection)

//below code adds four parameters - You have to add two more parameters for SEX_M and POSITIION_M.
 
command.Parameters.AddWithValue("@LAST_M", tx1.Text)
command.Parameters.AddWithValue("@FIRST_M", tx2.Text)
command.Parameters.AddWithValue("@MIDDLE_M", tx3.Text)
command.Parameters.AddWithValue("@SUFFIX_M", tx4.Text)

PS: You can attach a copy of your project with your post if you can't figure out.

kvprajapati 1,826 Posting Genius

13 Years Ago

Which is better to do, to add, edit and delete on the gridview or I should select one and put the data on the textboxes and their is the time I can edit some data?

I say both. It is all about design and placement of UI elements.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

kgenn 0 Light Poster · Answer 1 · 2011-01-25T09:58:07+00:00

hello guys :)

I just want to ask about the Row Update on grid view..

what should I used to get the value of the columns.

here's the sample:

UPDATE LUPON SET FIRST_M = '@FIRST_M' WHERE MYID = '@MYID'

please help me :D

kgenn 0 Light Poster · Answer 2 · 2011-01-25T11:56:16+00:00

Sir Thanks for your reply :)

but I encounter some problems :(

the fields in the table were here:
REGION_C
PROVINCE_C
CITYMUN_C
BARANGAY_C
LAST_M
FIRST_M
MIDDLE_M
SUFFIX_M
SEX_C -- in dropdown form in datagrid
POSITION_C -- in dropdown form in datagrid also

I follow the code that you right but the command.ExecuteNonQuery() was the error.

Is primary key required sir in updating those data?
my database is located at App_Data. Is that alright?

<%@ Page Language="VB"%> <%--AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default"--%> 
<%@ Import Namespace="System.Data.Oledb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    Private Sub Page_Load()
        If Not IsPostBack Then
            BindGrid()
        End If
    End Sub
    
    Sub BindGrid()
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
        connection.Open()
        
        Dim command As OleDbCommand
        command = New OleDbCommand("Select * from tblLupon_C", connection)
        Dim DataReader As OleDbDataReader
        DataReader = command.ExecuteReader()
        GridView1.DataSource = DataReader
        GridView1.DataBind()
        
        connection.Close()
    End Sub
    
    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        Call BindGrid()
    End Sub

    Protected Sub Gridview1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
        
        Dim tx1, tx2, tx3, tx4 As TextBox
        
        Dim row As GridViewRow = GridView1.Rows(e.RowIndex)
        tx1 = CType(row.Cells(4).Controls(0), TextBox)
        tx2 = CType(row.Cells(5).Controls(0), TextBox)
        tx3 = CType(row.Cells(6).Controls(0), TextBox)
        tx4 = CType(row.Cells(7).Controls(0), TextBox)

                
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
       
      
        
        Dim command As OleDbCommand
        command = New OleDbCommand("UPDATE tblLUPON_C SET LAST_M =@LAST_M, FIRST_M =@FIRST_M, MIDDLE_M =@MIDDLE_M, SUFFIX_M=@SUFFIX_M, SEX_C=@SEX_M, POSITION_C =@POSITION_M", connection)
        command.Parameters.AddWithValue("@LAST_M", tx1.Text)
        command.Parameters.AddWithValue("@FIRST_M", tx2.Text)
        command.Parameters.AddWithValue("@MIDDLE_M", tx3.Text)
        command.Parameters.AddWithValue("@SUFFIX_M", tx4.Text)
        
        connection.Open()

        command.ExecuteNonQuery()
        connection.Close()
        
        GridView1.EditIndex = -1
        DataBind()
    End Sub
    
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    
    <asp:GridView ID="GridView1" runat="server" EmptyDataText="No record found" 
            AutoGenerateEditButton="True"
                OnRowUpdating="Gridview1_RowUpdating"
                OnRowEditing= "GridView1_RowEditing">
    </asp:GridView>
    </div>
</form>
</body>
</html>

thanks for answering sir ;)
I hope you will post back again :)
your help is very much appreciated :)
please sir ;)

thank you and God bless sir :)

kgenn 0 Light Poster · Answer 3 · 2011-01-25T12:45:19+00:00

I follow the code that you right but the command.ExecuteNonQuery() was the error.

sir i mean write. :) not right. wee. I can't edit my post.

kgenn 0 Light Poster · Answer 4 · 2011-01-26T08:16:28+00:00

thanks sir. :)

I tried that the FIRST_M only that I will edit but it doesn't work.

sir, can I ask your suggestion regarding this matter?

Which is better to do, to add, edit and delete on the gridview or I should select one and put the data on the textboxes and their is the time I can edit some data?

sir thanks for the time :)
badly need sir. ;)

hope you won't be tired to help me. thanks sir :)

kgenn 0 Light Poster · Answer 5 · 2011-01-26T11:21:39+00:00

thanks sir :)

I will try again the code that you advice :)

thanks for answering my questions :D

kgenn 0 Light Poster · Answer 6 · 2011-01-31T07:05:21+00:00

sir, I made a sample of the code you have, it is updating already :)

but there are certain problems that I encounter :(

1. the Primary key is editable, how can I set the Unique_C (Primary Key) to be uneditable?

2. after I clicked the Update on the gridview, the table doesn't appear anymore.

here's the code.

<%@ Page Language="VB"%> <%--AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default"--%> 
<%@ Import Namespace="System.Data.Oledb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    Private Sub Page_Load()
        If Not IsPostBack Then
            BindGrid()
        End If
    End Sub
    
    Sub BindGrid()
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
        connection.Open()
        
        Dim command As OleDbCommand
        command = New OleDbCommand("Select LAST_M, UNIQUE_C from tblLupon_C", connection)
        Dim DataReader As OleDbDataReader
        DataReader = command.ExecuteReader()
        GridView1.DataSource = DataReader
        GridView1.DataBind()
        connection.Close()
    End Sub
    
    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        Call BindGrid()
    End Sub

    Protected Sub Gridview1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
        
        Dim tx1, tx2 As TextBox
     
        Dim row As GridViewRow = GridView1.Rows(e.RowIndex)
        tx1 = CType(row.Cells(1).Controls(0), TextBox)
        tx2 = CType(row.Cells(2).Controls(0), TextBox)
         
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
        
        
        Dim command As OleDbCommand
        command = New OleDbCommand("UPDATE tblLUPON_C SET LAST_M = @LAST_M WHERE UNIQUE_C =@UNIQUE_C", connection)
        command.Parameters.AddWithValue("@LAST_M", tx1.Text)
        command.Parameters.AddWithValue("@UNIQUE_C", tx2.Text)

        'Try
        connection.Open()

        command.ExecuteNonQuery()
        '  Catch ex As Exception
        'MsgBox(ex.ToString)
        ' Finally
        connection.Close()
        
        GridView1.EditIndex = -1
        DataBind()
        'End Try
    End Sub
    
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    
    <asp:GridView ID="GridView1" runat="server" 
            AutoGenerateEditButton="True"
                OnRowUpdating="Gridview1_RowUpdating"
                OnRowEditing= "GridView1_RowEditing">
    </asp:GridView>
    </div>
</form>
</body>
</html>

I hope you can help me sir :)
I will wait for your reply sir ;)
thanks! :)

kgenn 0 Light Poster · Answer 7 · 2011-01-31T07:13:28+00:00

Hi sir :)

I made a sample of the code you gave to me :) and it's right :) I can update the record. but I encounter some problems.

1. The primary key(UNIQUE_C) is editable. How can I set that to be uneditable?
2. After I update the data, the gridview does not appear anymore. What should I do to view the table again?

Once I run again the project, the informations that I update were updated :)

here's the code sir.

<%@ Page Language="VB"%> <%--AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default"--%> 
<%@ Import Namespace="System.Data.Oledb" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    Private Sub Page_Load()
        If Not IsPostBack Then
            BindGrid()
        End If
    End Sub
    
    Sub BindGrid()
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
        connection.Open()
        
        Dim command As OleDbCommand
        command = New OleDbCommand("Select LAST_M, UNIQUE_C from tblLupon_C", connection)
        Dim DataReader As OleDbDataReader
        DataReader = command.ExecuteReader()
        GridView1.DataSource = DataReader
        GridView1.DataBind()
        connection.Close()
    End Sub
    
    Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
        GridView1.EditIndex = e.NewEditIndex
        Call BindGrid()
    End Sub

    Protected Sub Gridview1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
        
        Dim tx1, tx2 As TextBox
     
        Dim row As GridViewRow = GridView1.Rows(e.RowIndex)
        tx1 = CType(row.Cells(1).Controls(0), TextBox)
        tx2 = CType(row.Cells(2).Controls(0), TextBox)
         
        Dim connection As OleDbConnection
        connection = New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=C:\Users\emong\Documents\Visual Studio 2008\WebSite3\App_Data/dbLupon.mdb")
        
        
        Dim command As OleDbCommand
        command = New OleDbCommand("UPDATE tblLUPON_C SET LAST_M = @LAST_M WHERE UNIQUE_C =@UNIQUE_C", connection)
        command.Parameters.AddWithValue("@LAST_M", tx1.Text)
        command.Parameters.AddWithValue("@UNIQUE_C", tx2.Text)

        'Try
        connection.Open()

        command.ExecuteNonQuery()
        '  Catch ex As Exception
        'MsgBox(ex.ToString)
        ' Finally
        connection.Close()
        
        GridView1.EditIndex = -1
        DataBind()
        'End Try
    End Sub
    
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
    
    <asp:GridView ID="GridView1" runat="server" 
            AutoGenerateEditButton="True"
                OnRowUpdating="Gridview1_RowUpdating"
                OnRowEditing= "GridView1_RowEditing">
    </asp:GridView>
    </div>
</form>
</body>
</html>

Sir, I also want to ask, if you can also help me regarding the primary, How can I increment the primary key, everytime there is a new record :)

thank you sir :)

I hope you understand what I write, sorry for wrong grammar.

thanks sir.
I hope you can reply :)
God bless :D

kgenn 0 Light Poster · Answer 8 · 2011-01-31T08:33:57+00:00

sir I found the error. it's the databind :)

1. the Primary key is editable, how can I set the Unique_C (Primary Key) to be uneditable?

it's my problem :(