Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > General .NET
|
General .NET For general discussion of MICROSOFT .NET topics that don't fall within any of the other .NET forum subcategories or .NET language forums.  If your question is specific to a language (C# or Visual Basic) or type of application (Windows Forms or ASP.Net) try an applicable forum category. ** PLEASE BE SPECIFIC WITH YOUR QUESTION ** When posting here, provide details regarding the Microsoft .NET language you are using and/or what type of application (Windows/Web Forms, etc) you are working in, if applicable to the question. This will help others answer the question without having to ask.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the General .NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 23rd, 2005, 02:23 PM
Registered User
 
Join Date: Aug 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Update Access Database with datagrid

I have successfully made a datagrid pulling data from an access database. However, when I try to update a field, it doesn't work properly, it just updates the record with what was previously in there. For example, if the record is "Tom" and I try to change it to "Frank" it doesn't work, it will update with "Tom". My question is, what is wrong with my code that is preventing me from updating with a new value?

Code:
<%@ Page Language="VB" Debug="true"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
    sub Page_Load
        BindData
    End sub

    public Sub BindData()
        Dim dbconn,sql,dbcomm,dbread
        dbconn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("content.mdb"))
        dbconn.Open()
        sql = "SELECT * FROM users"
        dbcomm = New OleDbCommand(sql,dbconn)
        dbread = dbcomm.ExecuteReader()
        usersInfo.DataSource = dbread
        usersInfo.DataBind()
        dbconn.Close()
    end sub

    Public Sub DataGrid_Edit(Source As Object, E As DataGridCommandEventArgs)
        usersInfo.EditItemIndex = E.Item.ItemIndex
        BindData
    End Sub 

    Public Sub DataGrid_Cancel(Source As Object, E As DataGridCommandEventArgs)
        usersInfo.EditItemIndex = -1
        BindData
    End Sub 

    Public Sub DataGrid_Delete(Source As Object, E As DataGridCommandEventArgs)
        Dim myConnection As OleDbConnection
        Dim myCommand As OleDbCommand
        Dim strUpdateStmt As String
        myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("content.mdb"))
        strUpdateStmt = "DELETE FROM users WHERE ID = " & E.Item.ItemIndex
        myCommand = New OleDbCommand(strUpdateStmt,myConnection)
        myConnection.Open()
        myCommand.ExecuteNonQuery()
        usersInfo.EditItemIndex = -1
        BindData()
    End Sub 

    Public Sub DataGrid_Update(Source As Object, E As DataGridCommandEventArgs)
        Dim myConnection As OleDbConnection
        Dim myCommand As OleDbCommand
        Dim txtFirstName As TextBox = E.Item.Cells(3).Controls(0)
        Dim strUpdateStmt As String
        myConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("content.mdb"))
        strUpdateStmt = "UPDATE users SET FirstName = '" & txtFirstName.Text & "' WHERE ID = " & E.Item.Cells(2).Text
        response.write(strUpdateStmt)
        myCommand = New OleDbCommand(strUpdateStmt,myConnection)
        myConnection.Open()
        myCommand.ExecuteNonQuery()
        usersInfo.EditItemIndex = -1
        BindData
    End Sub
</script>

<html>
<body>

    <form runat="server">
        <H3>Editing Records in the DataGrid</H3>
        <asp:DataGrid id="usersInfo" runat="server" AutoGenerateColumns="False" OnEditCommand="DataGrid_Edit" OnCancelCommand="DataGrid_Cancel" OnUpdateCommand="DataGrid_Update" OnDeleteCommand="DataGrid_Delete">
            <Columns>
                <asp:EditCommandColumn ButtonType="LinkButton" CancelText="Cancel" EditText="Edit" UpdateText="Update" />
                 <asp:ButtonColumn Text="Delete" CommandName="Delete" />
                <asp:BoundColumn DataField="ID" HeaderText="ID" ReadOnly="True" /> 
                <asp:BoundColumn DataField="FirstName" HeaderText="FirstName" />
            </Columns>
        </asp:DataGrid>
    </form>

</body>
</html>
Thanks,

Jeff M

 
Old January 23rd, 2005, 04:57 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi Jeff,

It's because you're calling BindData on PostBack as well. That clears all the values the user entered, and rebinds the control with the initial data from the database.

Check for PostBack in the Page_Load to avoid this:

Sub Page_Load
  If Not Page.IsPostBack Then
        BindData()
  End If
End Sub

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old May 19th, 2007, 10:44 AM
Registered User
 
Join Date: May 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

HI,
I tried this code.My main problem was in updation.But still wen i am facing a prob.
Its saying index out of bounds.Even though my data grid contains a cell(1) but then also its saying out of bounds.Any ideas wat the issue can be?
Thanks in advance:)






Similar Threads
Thread Thread Starter Forum Replies Last Post
datagrid, innertext, javascript, database update vsr ASP.NET 1.0 and 1.1 Professional 1 November 6th, 2007 05:54 PM
Please help, Update MS Access table from datagrid yzlin04 VB Databases Basics 1 August 6th, 2007 07:22 AM
update DataBase from DataGrid AnwarSadique C# 1 July 26th, 2006 09:03 AM
Problem with update database in datagrid annie_stwg ASP.NET 1.0 and 1.1 Basics 4 April 1st, 2006 12:10 AM
Datagrid update, dropdownlist database insertion herman ASP.NET 1.0 and 1.1 Professional 2 October 11th, 2003 01:31 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.