MDB Database won't update

Discussion in 'ASP .Net' started by Jonathan Upright, Oct 5, 2005.

  1. Greetings to anyone who can help:

    I'm using WebMatrix to make ASP.NET pages, and I chose the "Editable
    DataGrid" at the project selector screen. As you may know, it defaults
    to the Microsoft SQL database "pubs". I've followed the instructions in
    the comments and also changed everything pertaining to SQL over to
    OLEDB. (i.e. Changed SqlDbType. to OleDbType.) I also changed the
    datafield names and variable names accordingly. The page loads, the
    datagrid displays, when I click "Edit" the row of data becomes TextBoxes
    as they are supposed to. However, when I alter the data and click
    "Update" the changes do not take. Since I am a newbie to ASP.NET (but I
    did take 2 semesters of VB in college) I do not understand why this is
    happening. I need some help on this one. I am attaching the ASPX file
    for your review.

    Thanx in advance,

    Jonathan

    <%@ Page Language="VB" %>
    <%@ import Namespace="System.Data" %>
    <%@ import Namespace="System.Data.OleDb" %>
    <script runat="server">

    ' TODO: update the ConnectionString and Command values for your application

    Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data source=specialorders.mdb"
    Dim SelectCommand As String = "SELECT PurchaseOrderNumber, LastName, FirstName from [Purchase Orders]"

    Dim isEditing As Boolean = False

    Sub Page_Load(Sender As Object, E As EventArgs)

    If Not Page.IsPostBack Then

    ' Databind the data grid on the first request only
    ' (on postback, bind only in editing, paging and sorting commands)

    BindGrid()

    End If

    End Sub

    ' ---------------------------------------------------------------
    '
    ' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
    '

    Sub DataGrid_ItemCommand(Sender As Object, E As DataGridCommandEventArgs)

    ' this event fires prior to all of the other commands
    ' use it to provide a more graceful transition out of edit mode

    CheckIsEditing(e.CommandName)

    End Sub

    Sub CheckIsEditing(commandName As String)

    If DataGrid1.EditItemIndex <> -1 Then

    ' we are currently editing a row
    If commandName <> "Cancel" And commandName <> "Update" Then

    ' user's edit changes (If any) will not be committed
    Message.Text = "Your changes have not been saved yet. Please press update to save your changes, or cancel to discard your changes, before selecting another item."
    isEditing = True

    End If

    End If

    End Sub

    Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)

    ' turn on editing for the selected row

    If Not isEditing Then

    DataGrid1.EditItemIndex = e.Item.ItemIndex
    BindGrid()

    End If

    End Sub

    Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

    ' update the database with the new values

    ' get the edit text boxes
    Dim PurchaseOrderNumber As Long = CType(e.Item.Cells(2).Controls(0), TextBox).Text
    Dim LastName As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
    Dim FirstName As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text

    ' TODO: update the Command value for your application
    Dim myConnection As New OleDbConnection(ConnectionString)
    Dim UpdateCommand As New OleDbCommand()
    UpdateCommand.Connection = myConnection

    If AddingNew = True Then
    UpdateCommand.CommandText = "INSERT INTO [Purchase Orders](PurchaseOrderNumber, LastName, FirstName) VALUES (@PurchaseOrderNumber, @LastName, @FirstName)"
    Else
    UpdateCommand.CommandText = "UPDATE [Purchase Orders] SET LastName = @LastName, FirstName = @FirstName WHERE PurchaseOrderNumber = @PurchaseOrderNumber"
    End If

    UpdateCommand.Parameters.Add("@PurchaseOrderNumber", OleDbType.BigInt).Value = PurchaseOrderNumber
    UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar, 15).Value = LastName
    UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 12).Value = FirstName

    ' execute the command
    Try
    myConnection.Open()
    UpdateCommand.ExecuteNonQuery()

    Catch ex as Exception
    Message.Text = ex.ToString()

    Finally
    myConnection.Close()

    End Try

    ' Resort the grid for new records
    If AddingNew = True Then
    DataGrid1.CurrentPageIndex = 0
    AddingNew = False
    End If

    ' rebind the grid
    DataGrid1.EditItemIndex = -1
    BindGrid()

    End Sub

    Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)

    ' cancel editing

    DataGrid1.EditItemIndex = -1
    BindGrid()

    AddingNew = False

    End Sub

    Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)

    ' delete the selected row

    If Not isEditing Then

    ' the key value for this row is in the DataKeys collection
    Dim keyValue As Long = CLng(DataGrid1.DataKeys(e.Item.ItemIndex))

    ' TODO: update the Command value for your application
    Dim myConnection As New OleDbConnection(ConnectionString)
    Dim DeleteCommand As New OleDbCommand("DELETE from [Purchase Orders] where PurchaseOrderNumber='" & keyValue & "'", myConnection)

    ' execute the command
    myConnection.Open()
    DeleteCommand.ExecuteNonQuery()
    myConnection.Close()

    ' rebind the grid
    DataGrid1.CurrentPageIndex = 0
    DataGrid1.EditItemIndex = -1
    BindGrid()

    End If

    End Sub

    Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)

    ' display a new page of data

    If Not isEditing Then

    DataGrid1.EditItemIndex = -1
    DataGrid1.CurrentPageIndex = e.NewPageIndex
    BindGrid()

    End If

    End Sub

    Sub AddNew_Click(Sender As Object, E As EventArgs)

    ' add a new row to the end of the data, and set editing mode 'on'

    CheckIsEditing("")

    If Not isEditing = True Then

    ' set the flag so we know to do an insert at Update time
    AddingNew = True

    ' add new row to the end of the dataset after binding

    ' first get the data
    Dim myConnection As New OleDbConnection(ConnectionString)
    Dim myCommand As New OleDbDataAdapter(SelectCommand, myConnection)

    Dim ds As New DataSet()
    myCommand.Fill(ds)

    ' add a new blank row to the end of the data
    Dim rowValues As Object() = {"", "", ""}
    ds.Tables(0).Rows.Add(rowValues)

    ' figure out the EditItemIndex, last record on last page
    Dim recordCount As Integer = ds.Tables(0).Rows.Count

    If recordCount > 1 Then

    recordCount -= 1
    DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
    DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize

    End If

    ' databind
    DataGrid1.DataSource = ds
    DataGrid1.DataBind()

    End If


    End Sub

    ' ---------------------------------------------------------------
    '
    ' Helpers Methods:
    '

    ' property to keep track of whether we are adding a new record,
    ' and save it in viewstate between postbacks

    Property AddingNew() As Boolean

    Get
    Dim o As Object = ViewState("AddingNew")
    If o Is Nothing Then
    Return False
    End If
    Return CBool(o)
    End Get

    Set(ByVal Value As Boolean)
    ViewState("AddingNew") = Value
    End Set

    End Property

    Sub BindGrid()

    Dim myConnection As New OleDbConnection(ConnectionString)
    Dim myCommand As New OleDbDataAdapter(SelectCommand, myConnection)

    Dim ds As New DataSet()
    myCommand.Fill(ds)

    DataGrid1.DataSource = ds
    DataGrid1.DataBind()

    End Sub

    </script>
    <html>
    <head>
    </head>
    <body style="FONT-FAMILY: arial">
    <h2>Editable Data Grid
    </h2>
    <hr size="1" />
    <form runat="server">
    <asp:datagrid id="DataGrid1" runat="server" HorizontalAlign="Center" DataKeyField="PurchaseOrderNumber" OnItemCommand="DataGrid_ItemCommand" OnEditCommand="DataGrid_Edit" OnUpdateCommand="DataGrid_Update" OnCancelCommand="DataGrid_Cancel" OnDeleteCommand="DataGrid_Delete" AllowPaging="True" PageSize="6" OnPageIndexChanged="DataGrid_Page" ForeColor="Black" BackColor="White" CellPadding="3" CellSpacing="1" width="100%">
    <FooterStyle backcolor="#C6C3C6"></FooterStyle>
    <ItemStyle backcolor="#DEDFDE"></ItemStyle>
    <HeaderStyle font-bold="True" forecolor="White" backcolor="#4A3C8C"></HeaderStyle>
    <Columns>
    <asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit">
    <ItemStyle font-size="Smaller" width="10%"></ItemStyle>
    </asp:EditCommandColumn>
    <asp:ButtonColumn Text="Delete" ButtonType="PushButton" CommandName="Delete">
    <ItemStyle font-size="Smaller" width="10%"></ItemStyle>
    </asp:ButtonColumn>
    </Columns>
    <PagerStyle font-size="Smaller" horizontalalign="Right" backcolor="#C6C3C6" mode="NumericPages"></PagerStyle>
    </asp:datagrid>
    <br />
    <asp:LinkButton id="LinkButton1" onclick="AddNew_Click" runat="server" Text="Add new item" Font-Size="smaller"></asp:LinkButton>
    <br />
    <br />
    <asp:Label id="Message" runat="server" width="80%" enableviewstate="false" forecolor="red"></asp:Label>
    </form>
    </body>
    </html>
     
    Jonathan Upright, Oct 5, 2005
    #1
    1. Advertising

  2. Jonathan Upright

    Jon Guest

    Hi.
    You are creating a command but not inserting any data into it.

    Create a DataAdapter
    Set the Update command of that.
    Call dataadapter.update()

    "Jonathan Upright" <> wrote in message
    news:HiG0f.26325$...
    > Greetings to anyone who can help:
    >
    > I'm using WebMatrix to make ASP.NET pages, and I chose the "Editable
    > DataGrid" at the project selector screen. As you may know, it defaults
    > to the Microsoft SQL database "pubs". I've followed the instructions in
    > the comments and also changed everything pertaining to SQL over to
    > OLEDB. (i.e. Changed SqlDbType. to OleDbType.) I also changed the
    > datafield names and variable names accordingly. The page loads, the
    > datagrid displays, when I click "Edit" the row of data becomes TextBoxes
    > as they are supposed to. However, when I alter the data and click
    > "Update" the changes do not take. Since I am a newbie to ASP.NET (but I
    > did take 2 semesters of VB in college) I do not understand why this is
    > happening. I need some help on this one. I am attaching the ASPX file
    > for your review.
    >
    > Thanx in advance,
    >
    > Jonathan
    >



    --------------------------------------------------------------------------------


    > <%@ Page Language="VB" %>
    > <%@ import Namespace="System.Data" %>
    > <%@ import Namespace="System.Data.OleDb" %>
    > <script runat="server">
    >
    > ' TODO: update the ConnectionString and Command values for your
    > application
    >
    > Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data
    > source=specialorders.mdb"
    > Dim SelectCommand As String = "SELECT PurchaseOrderNumber, LastName,
    > FirstName from [Purchase Orders]"
    >
    > Dim isEditing As Boolean = False
    >
    > Sub Page_Load(Sender As Object, E As EventArgs)
    >
    > If Not Page.IsPostBack Then
    >
    > ' Databind the data grid on the first request only
    > ' (on postback, bind only in editing, paging and sorting
    > commands)
    >
    > BindGrid()
    >
    > End If
    >
    > End Sub
    >
    > ' ---------------------------------------------------------------
    > '
    > ' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
    > '
    >
    > Sub DataGrid_ItemCommand(Sender As Object, E As
    > DataGridCommandEventArgs)
    >
    > ' this event fires prior to all of the other commands
    > ' use it to provide a more graceful transition out of edit mode
    >
    > CheckIsEditing(e.CommandName)
    >
    > End Sub
    >
    > Sub CheckIsEditing(commandName As String)
    >
    > If DataGrid1.EditItemIndex <> -1 Then
    >
    > ' we are currently editing a row
    > If commandName <> "Cancel" And commandName <> "Update" Then
    >
    > ' user's edit changes (If any) will not be committed
    > Message.Text = "Your changes have not been saved yet.
    > Please press update to save your changes, or cancel to discard your
    > changes, before selecting another item."
    > isEditing = True
    >
    > End If
    >
    > End If
    >
    > End Sub
    >
    > Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
    >
    > ' turn on editing for the selected row
    >
    > If Not isEditing Then
    >
    > DataGrid1.EditItemIndex = e.Item.ItemIndex
    > BindGrid()
    >
    > End If
    >
    > End Sub
    >
    > Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
    >
    > ' update the database with the new values
    >
    > ' get the edit text boxes
    > Dim PurchaseOrderNumber As Long =
    > CType(e.Item.Cells(2).Controls(0), TextBox).Text
    > Dim LastName As String = CType(e.Item.Cells(3).Controls(0),
    > TextBox).Text
    > Dim FirstName As String = CType(e.Item.Cells(4).Controls(0),
    > TextBox).Text
    >
    > ' TODO: update the Command value for your application
    > Dim myConnection As New OleDbConnection(ConnectionString)
    > Dim UpdateCommand As New OleDbCommand()
    > UpdateCommand.Connection = myConnection
    >
    > If AddingNew = True Then
    > UpdateCommand.CommandText = "INSERT INTO [Purchase
    > Orders](PurchaseOrderNumber, LastName, FirstName) VALUES
    > (@PurchaseOrderNumber, @LastName, @FirstName)"
    > Else
    > UpdateCommand.CommandText = "UPDATE [Purchase Orders] SET
    > LastName = @LastName, FirstName = @FirstName WHERE PurchaseOrderNumber =
    > @PurchaseOrderNumber"
    > End If
    >
    > UpdateCommand.Parameters.Add("@PurchaseOrderNumber",
    > OleDbType.BigInt).Value = PurchaseOrderNumber
    > UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar,
    > 15).Value = LastName
    > UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar,
    > 12).Value = FirstName
    >
    > ' execute the command
    > Try
    > myConnection.Open()
    > UpdateCommand.ExecuteNonQuery()
    >
    > Catch ex as Exception
    > Message.Text = ex.ToString()
    >
    > Finally
    > myConnection.Close()
    >
    > End Try
    >
    > ' Resort the grid for new records
    > If AddingNew = True Then
    > DataGrid1.CurrentPageIndex = 0
    > AddingNew = False
    > End If
    >
    > ' rebind the grid
    > DataGrid1.EditItemIndex = -1
    > BindGrid()
    >
    > End Sub
    >
    > Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
    >
    > ' cancel editing
    >
    > DataGrid1.EditItemIndex = -1
    > BindGrid()
    >
    > AddingNew = False
    >
    > End Sub
    >
    > Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)
    >
    > ' delete the selected row
    >
    > If Not isEditing Then
    >
    > ' the key value for this row is in the DataKeys collection
    > Dim keyValue As Long =
    > CLng(DataGrid1.DataKeys(e.Item.ItemIndex))
    >
    > ' TODO: update the Command value for your application
    > Dim myConnection As New OleDbConnection(ConnectionString)
    > Dim DeleteCommand As New OleDbCommand("DELETE from [Purchase
    > Orders] where PurchaseOrderNumber='" & keyValue & "'", myConnection)
    >
    > ' execute the command
    > myConnection.Open()
    > DeleteCommand.ExecuteNonQuery()
    > myConnection.Close()
    >
    > ' rebind the grid
    > DataGrid1.CurrentPageIndex = 0
    > DataGrid1.EditItemIndex = -1
    > BindGrid()
    >
    > End If
    >
    > End Sub
    >
    > Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
    >
    > ' display a new page of data
    >
    > If Not isEditing Then
    >
    > DataGrid1.EditItemIndex = -1
    > DataGrid1.CurrentPageIndex = e.NewPageIndex
    > BindGrid()
    >
    > End If
    >
    > End Sub
    >
    > Sub AddNew_Click(Sender As Object, E As EventArgs)
    >
    > ' add a new row to the end of the data, and set editing mode 'on'
    >
    > CheckIsEditing("")
    >
    > If Not isEditing = True Then
    >
    > ' set the flag so we know to do an insert at Update time
    > AddingNew = True
    >
    > ' add new row to the end of the dataset after binding
    >
    > ' first get the data
    > Dim myConnection As New OleDbConnection(ConnectionString)
    > Dim myCommand As New OleDbDataAdapter(SelectCommand,
    > myConnection)
    >
    > Dim ds As New DataSet()
    > myCommand.Fill(ds)
    >
    > ' add a new blank row to the end of the data
    > Dim rowValues As Object() = {"", "", ""}
    > ds.Tables(0).Rows.Add(rowValues)
    >
    > ' figure out the EditItemIndex, last record on last page
    > Dim recordCount As Integer = ds.Tables(0).Rows.Count
    >
    > If recordCount > 1 Then
    >
    > recordCount -= 1
    > DataGrid1.CurrentPageIndex = recordCount \
    > DataGrid1.PageSize
    > DataGrid1.EditItemIndex = recordCount Mod
    > DataGrid1.PageSize
    >
    > End If
    >
    > ' databind
    > DataGrid1.DataSource = ds
    > DataGrid1.DataBind()
    >
    > End If
    >
    >
    > End Sub
    >
    > ' ---------------------------------------------------------------
    > '
    > ' Helpers Methods:
    > '
    >
    > ' property to keep track of whether we are adding a new record,
    > ' and save it in viewstate between postbacks
    >
    > Property AddingNew() As Boolean
    >
    > Get
    > Dim o As Object = ViewState("AddingNew")
    > If o Is Nothing Then
    > Return False
    > End If
    > Return CBool(o)
    > End Get
    >
    > Set(ByVal Value As Boolean)
    > ViewState("AddingNew") = Value
    > End Set
    >
    > End Property
    >
    > Sub BindGrid()
    >
    > Dim myConnection As New OleDbConnection(ConnectionString)
    > Dim myCommand As New OleDbDataAdapter(SelectCommand, myConnection)
    >
    > Dim ds As New DataSet()
    > myCommand.Fill(ds)
    >
    > DataGrid1.DataSource = ds
    > DataGrid1.DataBind()
    >
    > End Sub
    >
    > </script>
    > <html>
    > <head>
    > </head>
    > <body style="FONT-FAMILY: arial">
    > <h2>Editable Data Grid
    > </h2>
    > <hr size="1" />
    > <form runat="server">
    > <asp:datagrid id="DataGrid1" runat="server"
    > HorizontalAlign="Center" DataKeyField="PurchaseOrderNumber"
    > OnItemCommand="DataGrid_ItemCommand" OnEditCommand="DataGrid_Edit"
    > OnUpdateCommand="DataGrid_Update" OnCancelCommand="DataGrid_Cancel"
    > OnDeleteCommand="DataGrid_Delete" AllowPaging="True" PageSize="6"
    > OnPageIndexChanged="DataGrid_Page" ForeColor="Black" BackColor="White"
    > CellPadding="3" CellSpacing="1" width="100%">
    > <FooterStyle backcolor="#C6C3C6"></FooterStyle>
    > <ItemStyle backcolor="#DEDFDE"></ItemStyle>
    > <HeaderStyle font-bold="True" forecolor="White"
    > backcolor="#4A3C8C"></HeaderStyle>
    > <Columns>
    > <asp:EditCommandColumn ButtonType="PushButton"
    > UpdateText="Update" CancelText="Cancel" EditText="Edit">
    > <ItemStyle font-size="Smaller" width="10%"></ItemStyle>
    > </asp:EditCommandColumn>
    > <asp:ButtonColumn Text="Delete" ButtonType="PushButton"
    > CommandName="Delete">
    > <ItemStyle font-size="Smaller" width="10%"></ItemStyle>
    > </asp:ButtonColumn>
    > </Columns>
    > <PagerStyle font-size="Smaller" horizontalalign="Right"
    > backcolor="#C6C3C6" mode="NumericPages"></PagerStyle>
    > </asp:datagrid>
    > <br />
    > <asp:LinkButton id="LinkButton1" onclick="AddNew_Click"
    > runat="server" Text="Add new item" Font-Size="smaller"></asp:LinkButton>
    > <br />
    > <br />
    > <asp:Label id="Message" runat="server" width="80%"
    > enableviewstate="false" forecolor="red"></asp:Label>
    > </form>
    > </body>
    > </html>
    >
     
    Jon, Oct 5, 2005
    #2
    1. Advertising

  3. Would I have to use the "regular" DataAdapter or OleDbDataAdapter?

    Thanx,

    Jonathan

    Jon wrote:
    > Hi.
    > You are creating a command but not inserting any data into it.
    >
    > Create a DataAdapter
    > Set the Update command of that.
    > Call dataadapter.update()
    >
    > "Jonathan Upright" <> wrote in message
    > news:HiG0f.26325$...
    >
    >>Greetings to anyone who can help:
    >>
    >>I'm using WebMatrix to make ASP.NET pages, and I chose the "Editable
    >>DataGrid" at the project selector screen. As you may know, it defaults
    >>to the Microsoft SQL database "pubs". I've followed the instructions in
    >>the comments and also changed everything pertaining to SQL over to
    >>OLEDB. (i.e. Changed SqlDbType. to OleDbType.) I also changed the
    >>datafield names and variable names accordingly. The page loads, the
    >>datagrid displays, when I click "Edit" the row of data becomes TextBoxes
    >>as they are supposed to. However, when I alter the data and click
    >>"Update" the changes do not take. Since I am a newbie to ASP.NET (but I
    >>did take 2 semesters of VB in college) I do not understand why this is
    >>happening. I need some help on this one. I am attaching the ASPX file
    >>for your review.
    >>
    >>Thanx in advance,
    >>
    >>Jonathan
    >>

    >
    >
    >
    > --------------------------------------------------------------------------------
    >
    >
    >
    >><%@ Page Language="VB" %>
    >><%@ import Namespace="System.Data" %>
    >><%@ import Namespace="System.Data.OleDb" %>
    >><script runat="server">
    >>
    >> ' TODO: update the ConnectionString and Command values for your
    >>application
    >>
    >> Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;data
    >>source=specialorders.mdb"
    >> Dim SelectCommand As String = "SELECT PurchaseOrderNumber, LastName,
    >>FirstName from [Purchase Orders]"
    >>
    >> Dim isEditing As Boolean = False
    >>
    >> Sub Page_Load(Sender As Object, E As EventArgs)
    >>
    >> If Not Page.IsPostBack Then
    >>
    >> ' Databind the data grid on the first request only
    >> ' (on postback, bind only in editing, paging and sorting
    >>commands)
    >>
    >> BindGrid()
    >>
    >> End If
    >>
    >> End Sub
    >>
    >> ' ---------------------------------------------------------------
    >> '
    >> ' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
    >> '
    >>
    >> Sub DataGrid_ItemCommand(Sender As Object, E As
    >>DataGridCommandEventArgs)
    >>
    >> ' this event fires prior to all of the other commands
    >> ' use it to provide a more graceful transition out of edit mode
    >>
    >> CheckIsEditing(e.CommandName)
    >>
    >> End Sub
    >>
    >> Sub CheckIsEditing(commandName As String)
    >>
    >> If DataGrid1.EditItemIndex <> -1 Then
    >>
    >> ' we are currently editing a row
    >> If commandName <> "Cancel" And commandName <> "Update" Then
    >>
    >> ' user's edit changes (If any) will not be committed
    >> Message.Text = "Your changes have not been saved yet.
    >>Please press update to save your changes, or cancel to discard your
    >>changes, before selecting another item."
    >> isEditing = True
    >>
    >> End If
    >>
    >> End If
    >>
    >> End Sub
    >>
    >> Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
    >>
    >> ' turn on editing for the selected row
    >>
    >> If Not isEditing Then
    >>
    >> DataGrid1.EditItemIndex = e.Item.ItemIndex
    >> BindGrid()
    >>
    >> End If
    >>
    >> End Sub
    >>
    >> Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
    >>
    >> ' update the database with the new values
    >>
    >> ' get the edit text boxes
    >> Dim PurchaseOrderNumber As Long =
    >>CType(e.Item.Cells(2).Controls(0), TextBox).Text
    >> Dim LastName As String = CType(e.Item.Cells(3).Controls(0),
    >>TextBox).Text
    >> Dim FirstName As String = CType(e.Item.Cells(4).Controls(0),
    >>TextBox).Text
    >>
    >> ' TODO: update the Command value for your application
    >> Dim myConnection As New OleDbConnection(ConnectionString)
    >> Dim UpdateCommand As New OleDbCommand()
    >> UpdateCommand.Connection = myConnection
    >>
    >> If AddingNew = True Then
    >> UpdateCommand.CommandText = "INSERT INTO [Purchase
    >>Orders](PurchaseOrderNumber, LastName, FirstName) VALUES
    >>(@PurchaseOrderNumber, @LastName, @FirstName)"
    >> Else
    >> UpdateCommand.CommandText = "UPDATE [Purchase Orders] SET
    >>LastName = @LastName, FirstName = @FirstName WHERE PurchaseOrderNumber =
    >>@PurchaseOrderNumber"
    >> End If
    >>
    >> UpdateCommand.Parameters.Add("@PurchaseOrderNumber",
    >>OleDbType.BigInt).Value = PurchaseOrderNumber
    >> UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar,
    >>15).Value = LastName
    >> UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar,
    >>12).Value = FirstName
    >>
    >> ' execute the command
    >> Try
    >> myConnection.Open()
    >> UpdateCommand.ExecuteNonQuery()
    >>
    >> Catch ex as Exception
    >> Message.Text = ex.ToString()
    >>
    >> Finally
    >> myConnection.Close()
    >>
    >> End Try
    >>
    >> ' Resort the grid for new records
    >> If AddingNew = True Then
    >> DataGrid1.CurrentPageIndex = 0
    >> AddingNew = False
    >> End If
    >>
    >> ' rebind the grid
    >> DataGrid1.EditItemIndex = -1
    >> BindGrid()
    >>
    >> End Sub
    >>
    >> Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
    >>
    >> ' cancel editing
    >>
    >> DataGrid1.EditItemIndex = -1
    >> BindGrid()
    >>
    >> AddingNew = False
    >>
    >> End Sub
    >>
    >> Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)
    >>
    >> ' delete the selected row
    >>
    >> If Not isEditing Then
    >>
    >> ' the key value for this row is in the DataKeys collection
    >> Dim keyValue As Long =
    >>CLng(DataGrid1.DataKeys(e.Item.ItemIndex))
    >>
    >> ' TODO: update the Command value for your application
    >> Dim myConnection As New OleDbConnection(ConnectionString)
    >> Dim DeleteCommand As New OleDbCommand("DELETE from [Purchase
    >>Orders] where PurchaseOrderNumber='" & keyValue & "'", myConnection)
    >>
    >> ' execute the command
    >> myConnection.Open()
    >> DeleteCommand.ExecuteNonQuery()
    >> myConnection.Close()
    >>
    >> ' rebind the grid
    >> DataGrid1.CurrentPageIndex = 0
    >> DataGrid1.EditItemIndex = -1
    >> BindGrid()
    >>
    >> End If
    >>
    >> End Sub
    >>
    >> Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
    >>
    >> ' display a new page of data
    >>
    >> If Not isEditing Then
    >>
    >> DataGrid1.EditItemIndex = -1
    >> DataGrid1.CurrentPageIndex = e.NewPageIndex
    >> BindGrid()
    >>
    >> End If
    >>
    >> End Sub
    >>
    >> Sub AddNew_Click(Sender As Object, E As EventArgs)
    >>
    >> ' add a new row to the end of the data, and set editing mode 'on'
    >>
    >> CheckIsEditing("")
    >>
    >> If Not isEditing = True Then
    >>
    >> ' set the flag so we know to do an insert at Update time
    >> AddingNew = True
    >>
    >> ' add new row to the end of the dataset after binding
    >>
    >> ' first get the data
    >> Dim myConnection As New OleDbConnection(ConnectionString)
    >> Dim myCommand As New OleDbDataAdapter(SelectCommand,
    >>myConnection)
    >>
    >> Dim ds As New DataSet()
    >> myCommand.Fill(ds)
    >>
    >> ' add a new blank row to the end of the data
    >> Dim rowValues As Object() = {"", "", ""}
    >> ds.Tables(0).Rows.Add(rowValues)
    >>
    >> ' figure out the EditItemIndex, last record on last page
    >> Dim recordCount As Integer = ds.Tables(0).Rows.Count
    >>
    >> If recordCount > 1 Then
    >>
    >> recordCount -= 1
    >> DataGrid1.CurrentPageIndex = recordCount \
    >>DataGrid1.PageSize
    >> DataGrid1.EditItemIndex = recordCount Mod
    >>DataGrid1.PageSize
    >>
    >> End If
    >>
    >> ' databind
    >> DataGrid1.DataSource = ds
    >> DataGrid1.DataBind()
    >>
    >> End If
    >>
    >>
    >> End Sub
    >>
    >> ' ---------------------------------------------------------------
    >> '
    >> ' Helpers Methods:
    >> '
    >>
    >> ' property to keep track of whether we are adding a new record,
    >> ' and save it in viewstate between postbacks
    >>
    >> Property AddingNew() As Boolean
    >>
    >> Get
    >> Dim o As Object = ViewState("AddingNew")
    >> If o Is Nothing Then
    >> Return False
    >> End If
    >> Return CBool(o)
    >> End Get
    >>
    >> Set(ByVal Value As Boolean)
    >> ViewState("AddingNew") = Value
    >> End Set
    >>
    >> End Property
    >>
    >> Sub BindGrid()
    >>
    >> Dim myConnection As New OleDbConnection(ConnectionString)
    >> Dim myCommand As New OleDbDataAdapter(SelectCommand, myConnection)
    >>
    >> Dim ds As New DataSet()
    >> myCommand.Fill(ds)
    >>
    >> DataGrid1.DataSource = ds
    >> DataGrid1.DataBind()
    >>
    >> End Sub
    >>
    >></script>
    >><html>
    >><head>
    >></head>
    >><body style="FONT-FAMILY: arial">
    >> <h2>Editable Data Grid
    >> </h2>
    >> <hr size="1" />
    >> <form runat="server">
    >> <asp:datagrid id="DataGrid1" runat="server"
    >>HorizontalAlign="Center" DataKeyField="PurchaseOrderNumber"
    >>OnItemCommand="DataGrid_ItemCommand" OnEditCommand="DataGrid_Edit"
    >>OnUpdateCommand="DataGrid_Update" OnCancelCommand="DataGrid_Cancel"
    >>OnDeleteCommand="DataGrid_Delete" AllowPaging="True" PageSize="6"
    >>OnPageIndexChanged="DataGrid_Page" ForeColor="Black" BackColor="White"
    >>CellPadding="3" CellSpacing="1" width="100%">
    >> <FooterStyle backcolor="#C6C3C6"></FooterStyle>
    >> <ItemStyle backcolor="#DEDFDE"></ItemStyle>
    >> <HeaderStyle font-bold="True" forecolor="White"
    >>backcolor="#4A3C8C"></HeaderStyle>
    >> <Columns>
    >> <asp:EditCommandColumn ButtonType="PushButton"
    >>UpdateText="Update" CancelText="Cancel" EditText="Edit">
    >> <ItemStyle font-size="Smaller" width="10%"></ItemStyle>
    >> </asp:EditCommandColumn>
    >> <asp:ButtonColumn Text="Delete" ButtonType="PushButton"
    >>CommandName="Delete">
    >> <ItemStyle font-size="Smaller" width="10%"></ItemStyle>
    >> </asp:ButtonColumn>
    >> </Columns>
    >> <PagerStyle font-size="Smaller" horizontalalign="Right"
    >>backcolor="#C6C3C6" mode="NumericPages"></PagerStyle>
    >> </asp:datagrid>
    >> <br />
    >> <asp:LinkButton id="LinkButton1" onclick="AddNew_Click"
    >>runat="server" Text="Add new item" Font-Size="smaller"></asp:LinkButton>
    >> <br />
    >> <br />
    >> <asp:Label id="Message" runat="server" width="80%"
    >>enableviewstate="false" forecolor="red"></asp:Label>
    >> </form>
    >></body>
    >></html>
    >>

    >
    >
     
    Jonathan Upright, Oct 5, 2005
    #3
  4. Jonathan Upright

    Jon Guest

    OleDataAdapter


    "Jonathan Upright" <> wrote in message
    news:cqH0f.84425$...
    > Would I have to use the "regular" DataAdapter or OleDbDataAdapter?
    >
    > Thanx,
    >
    > Jonathan
    >
    > Jon wrote:
    >> Hi.
    >> You are creating a command but not inserting any data into it.
    >>
    >> Create a DataAdapter
    >> Set the Update command of that.
    >> Call dataadapter.update()
    >>
    >> "Jonathan Upright" <> wrote in message
    >> news:HiG0f.26325$...
    >>
    >>>Greetings to anyone who can help:
    >>>
    >>>I'm using WebMatrix to make ASP.NET pages, and I chose the "Editable
    >>>DataGrid" at the project selector screen. As you may know, it defaults
    >>>to the Microsoft SQL database "pubs". I've followed the instructions in
    >>>the comments and also changed everything pertaining to SQL over to
    >>>OLEDB. (i.e. Changed SqlDbType. to OleDbType.) I also changed the
    >>>datafield names and variable names accordingly. The page loads, the
    >>>datagrid displays, when I click "Edit" the row of data becomes TextBoxes
    >>>as they are supposed to. However, when I alter the data and click
    >>>"Update" the changes do not take. Since I am a newbie to ASP.NET (but I
    >>>did take 2 semesters of VB in college) I do not understand why this is
    >>>happening. I need some help on this one. I am attaching the ASPX file
    >>>for your review.
    >>>
    >>>Thanx in advance,
    >>>
    >>>Jonathan
    >>>

    >>
    >>
    >>
    >> --------------------------------------------------------------------------------
    >>
    >>
    >>
    >>><%@ Page Language="VB" %>
    >>><%@ import Namespace="System.Data" %>
    >>><%@ import Namespace="System.Data.OleDb" %>
    >>><script runat="server">
    >>>
    >>> ' TODO: update the ConnectionString and Command values for your
    >>> application
    >>>
    >>> Dim ConnectionString As String =
    >>> "Provider=Microsoft.Jet.OLEDB.4.0;data source=specialorders.mdb"
    >>> Dim SelectCommand As String = "SELECT PurchaseOrderNumber, LastName,
    >>> FirstName from [Purchase Orders]"
    >>>
    >>> Dim isEditing As Boolean = False
    >>>
    >>> Sub Page_Load(Sender As Object, E As EventArgs)
    >>>
    >>> If Not Page.IsPostBack Then
    >>>
    >>> ' Databind the data grid on the first request only
    >>> ' (on postback, bind only in editing, paging and sorting
    >>> commands)
    >>>
    >>> BindGrid()
    >>>
    >>> End If
    >>>
    >>> End Sub
    >>>
    >>> ' ---------------------------------------------------------------
    >>> '
    >>> ' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
    >>> '
    >>>
    >>> Sub DataGrid_ItemCommand(Sender As Object, E As
    >>> DataGridCommandEventArgs)
    >>>
    >>> ' this event fires prior to all of the other commands
    >>> ' use it to provide a more graceful transition out of edit mode
    >>>
    >>> CheckIsEditing(e.CommandName)
    >>>
    >>> End Sub
    >>>
    >>> Sub CheckIsEditing(commandName As String)
    >>>
    >>> If DataGrid1.EditItemIndex <> -1 Then
    >>>
    >>> ' we are currently editing a row
    >>> If commandName <> "Cancel" And commandName <> "Update" Then
    >>>
    >>> ' user's edit changes (If any) will not be committed
    >>> Message.Text = "Your changes have not been saved yet.
    >>> Please press update to save your changes, or cancel to discard your
    >>> changes, before selecting another item."
    >>> isEditing = True
    >>>
    >>> End If
    >>>
    >>> End If
    >>>
    >>> End Sub
    >>>
    >>> Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
    >>>
    >>> ' turn on editing for the selected row
    >>>
    >>> If Not isEditing Then
    >>>
    >>> DataGrid1.EditItemIndex = e.Item.ItemIndex
    >>> BindGrid()
    >>>
    >>> End If
    >>>
    >>> End Sub
    >>>
    >>> Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
    >>>
    >>> ' update the database with the new values
    >>>
    >>> ' get the edit text boxes
    >>> Dim PurchaseOrderNumber As Long =
    >>> CType(e.Item.Cells(2).Controls(0), TextBox).Text
    >>> Dim LastName As String = CType(e.Item.Cells(3).Controls(0),
    >>> TextBox).Text
    >>> Dim FirstName As String = CType(e.Item.Cells(4).Controls(0),
    >>> TextBox).Text
    >>>
    >>> ' TODO: update the Command value for your application
    >>> Dim myConnection As New OleDbConnection(ConnectionString)
    >>> Dim UpdateCommand As New OleDbCommand()
    >>> UpdateCommand.Connection = myConnection
    >>>
    >>> If AddingNew = True Then
    >>> UpdateCommand.CommandText = "INSERT INTO [Purchase
    >>> Orders](PurchaseOrderNumber, LastName, FirstName) VALUES
    >>> (@PurchaseOrderNumber, @LastName, @FirstName)"
    >>> Else
    >>> UpdateCommand.CommandText = "UPDATE [Purchase Orders] SET
    >>> LastName = @LastName, FirstName = @FirstName WHERE PurchaseOrderNumber =
    >>> @PurchaseOrderNumber"
    >>> End If
    >>>
    >>> UpdateCommand.Parameters.Add("@PurchaseOrderNumber",
    >>> OleDbType.BigInt).Value = PurchaseOrderNumber
    >>> UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar,
    >>> 15).Value = LastName
    >>> UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar,
    >>> 12).Value = FirstName
    >>>
    >>> ' execute the command
    >>> Try
    >>> myConnection.Open()
    >>> UpdateCommand.ExecuteNonQuery()
    >>>
    >>> Catch ex as Exception
    >>> Message.Text = ex.ToString()
    >>>
    >>> Finally
    >>> myConnection.Close()
    >>>
    >>> End Try
    >>>
    >>> ' Resort the grid for new records
    >>> If AddingNew = True Then
    >>> DataGrid1.CurrentPageIndex = 0
    >>> AddingNew = False
    >>> End If
    >>>
    >>> ' rebind the grid
    >>> DataGrid1.EditItemIndex = -1
    >>> BindGrid()
    >>>
    >>> End Sub
    >>>
    >>> Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
    >>>
    >>> ' cancel editing
    >>>
    >>> DataGrid1.EditItemIndex = -1
    >>> BindGrid()
    >>>
    >>> AddingNew = False
    >>>
    >>> End Sub
    >>>
    >>> Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)
    >>>
    >>> ' delete the selected row
    >>>
    >>> If Not isEditing Then
    >>>
    >>> ' the key value for this row is in the DataKeys collection
    >>> Dim keyValue As Long =
    >>> CLng(DataGrid1.DataKeys(e.Item.ItemIndex))
    >>>
    >>> ' TODO: update the Command value for your application
    >>> Dim myConnection As New OleDbConnection(ConnectionString)
    >>> Dim DeleteCommand As New OleDbCommand("DELETE from [Purchase
    >>> Orders] where PurchaseOrderNumber='" & keyValue & "'", myConnection)
    >>>
    >>> ' execute the command
    >>> myConnection.Open()
    >>> DeleteCommand.ExecuteNonQuery()
    >>> myConnection.Close()
    >>>
    >>> ' rebind the grid
    >>> DataGrid1.CurrentPageIndex = 0
    >>> DataGrid1.EditItemIndex = -1
    >>> BindGrid()
    >>>
    >>> End If
    >>>
    >>> End Sub
    >>>
    >>> Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
    >>>
    >>> ' display a new page of data
    >>>
    >>> If Not isEditing Then
    >>>
    >>> DataGrid1.EditItemIndex = -1
    >>> DataGrid1.CurrentPageIndex = e.NewPageIndex
    >>> BindGrid()
    >>>
    >>> End If
    >>>
    >>> End Sub
    >>>
    >>> Sub AddNew_Click(Sender As Object, E As EventArgs)
    >>>
    >>> ' add a new row to the end of the data, and set editing mode 'on'
    >>>
    >>> CheckIsEditing("")
    >>>
    >>> If Not isEditing = True Then
    >>>
    >>> ' set the flag so we know to do an insert at Update time
    >>> AddingNew = True
    >>>
    >>> ' add new row to the end of the dataset after binding
    >>>
    >>> ' first get the data
    >>> Dim myConnection As New OleDbConnection(ConnectionString)
    >>> Dim myCommand As New OleDbDataAdapter(SelectCommand,
    >>> myConnection)
    >>>
    >>> Dim ds As New DataSet()
    >>> myCommand.Fill(ds)
    >>>
    >>> ' add a new blank row to the end of the data
    >>> Dim rowValues As Object() = {"", "", ""}
    >>> ds.Tables(0).Rows.Add(rowValues)
    >>>
    >>> ' figure out the EditItemIndex, last record on last page
    >>> Dim recordCount As Integer = ds.Tables(0).Rows.Count
    >>>
    >>> If recordCount > 1 Then
    >>>
    >>> recordCount -= 1
    >>> DataGrid1.CurrentPageIndex = recordCount \
    >>> DataGrid1.PageSize
    >>> DataGrid1.EditItemIndex = recordCount Mod
    >>> DataGrid1.PageSize
    >>>
    >>> End If
    >>>
    >>> ' databind
    >>> DataGrid1.DataSource = ds
    >>> DataGrid1.DataBind()
    >>>
    >>> End If
    >>>
    >>>
    >>> End Sub
    >>>
    >>> ' ---------------------------------------------------------------
    >>> '
    >>> ' Helpers Methods:
    >>> '
    >>>
    >>> ' property to keep track of whether we are adding a new record,
    >>> ' and save it in viewstate between postbacks
    >>>
    >>> Property AddingNew() As Boolean
    >>>
    >>> Get
    >>> Dim o As Object = ViewState("AddingNew")
    >>> If o Is Nothing Then
    >>> Return False
    >>> End If
    >>> Return CBool(o)
    >>> End Get
    >>>
    >>> Set(ByVal Value As Boolean)
    >>> ViewState("AddingNew") = Value
    >>> End Set
    >>>
    >>> End Property
    >>>
    >>> Sub BindGrid()
    >>>
    >>> Dim myConnection As New OleDbConnection(ConnectionString)
    >>> Dim myCommand As New OleDbDataAdapter(SelectCommand, myConnection)
    >>>
    >>> Dim ds As New DataSet()
    >>> myCommand.Fill(ds)
    >>>
    >>> DataGrid1.DataSource = ds
    >>> DataGrid1.DataBind()
    >>>
    >>> End Sub
    >>>
    >>></script>
    >>><html>
    >>><head>
    >>></head>
    >>><body style="FONT-FAMILY: arial">
    >>> <h2>Editable Data Grid
    >>> </h2>
    >>> <hr size="1" />
    >>> <form runat="server">
    >>> <asp:datagrid id="DataGrid1" runat="server"
    >>> HorizontalAlign="Center" DataKeyField="PurchaseOrderNumber"
    >>> OnItemCommand="DataGrid_ItemCommand" OnEditCommand="DataGrid_Edit"
    >>> OnUpdateCommand="DataGrid_Update" OnCancelCommand="DataGrid_Cancel"
    >>> OnDeleteCommand="DataGrid_Delete" AllowPaging="True" PageSize="6"
    >>> OnPageIndexChanged="DataGrid_Page" ForeColor="Black" BackColor="White"
    >>> CellPadding="3" CellSpacing="1" width="100%">
    >>> <FooterStyle backcolor="#C6C3C6"></FooterStyle>
    >>> <ItemStyle backcolor="#DEDFDE"></ItemStyle>
    >>> <HeaderStyle font-bold="True" forecolor="White"
    >>> backcolor="#4A3C8C"></HeaderStyle>
    >>> <Columns>
    >>> <asp:EditCommandColumn ButtonType="PushButton"
    >>> UpdateText="Update" CancelText="Cancel" EditText="Edit">
    >>> <ItemStyle font-size="Smaller"
    >>> width="10%"></ItemStyle>
    >>> </asp:EditCommandColumn>
    >>> <asp:ButtonColumn Text="Delete" ButtonType="PushButton"
    >>> CommandName="Delete">
    >>> <ItemStyle font-size="Smaller"
    >>> width="10%"></ItemStyle>
    >>> </asp:ButtonColumn>
    >>> </Columns>
    >>> <PagerStyle font-size="Smaller" horizontalalign="Right"
    >>> backcolor="#C6C3C6" mode="NumericPages"></PagerStyle>
    >>> </asp:datagrid>
    >>> <br />
    >>> <asp:LinkButton id="LinkButton1" onclick="AddNew_Click"
    >>> runat="server" Text="Add new item" Font-Size="smaller"></asp:LinkButton>
    >>> <br />
    >>> <br />
    >>> <asp:Label id="Message" runat="server" width="80%"
    >>> enableviewstate="false" forecolor="red"></asp:Label>
    >>> </form>
    >>></body>
    >>></html>
    >>>

    >>
     
    Jon, Oct 5, 2005
    #4
  5. Jonathan Upright

    Patirck Ige Guest

    Just remember its neither OleDB or SQL DataAdapter
    Patrick

    "Jonathan Upright" <> wrote in message
    news:cqH0f.84425$...
    > Would I have to use the "regular" DataAdapter or OleDbDataAdapter?
    >
    > Thanx,
    >
    > Jonathan
    >
    > Jon wrote:
    > > Hi.
    > > You are creating a command but not inserting any data into it.
    > >
    > > Create a DataAdapter
    > > Set the Update command of that.
    > > Call dataadapter.update()
    > >
    > > "Jonathan Upright" <> wrote in message
    > > news:HiG0f.26325$...
    > >
    > >>Greetings to anyone who can help:
    > >>
    > >>I'm using WebMatrix to make ASP.NET pages, and I chose the "Editable
    > >>DataGrid" at the project selector screen. As you may know, it defaults
    > >>to the Microsoft SQL database "pubs". I've followed the instructions in
    > >>the comments and also changed everything pertaining to SQL over to
    > >>OLEDB. (i.e. Changed SqlDbType. to OleDbType.) I also changed the
    > >>datafield names and variable names accordingly. The page loads, the
    > >>datagrid displays, when I click "Edit" the row of data becomes TextBoxes
    > >>as they are supposed to. However, when I alter the data and click
    > >>"Update" the changes do not take. Since I am a newbie to ASP.NET (but I
    > >>did take 2 semesters of VB in college) I do not understand why this is
    > >>happening. I need some help on this one. I am attaching the ASPX file
    > >>for your review.
    > >>
    > >>Thanx in advance,
    > >>
    > >>Jonathan
    > >>

    > >
    > >
    > >

    >
    > --------------------------------------------------------------------------

    ------
    > >
    > >
    > >
    > >><%@ Page Language="VB" %>
    > >><%@ import Namespace="System.Data" %>
    > >><%@ import Namespace="System.Data.OleDb" %>
    > >><script runat="server">
    > >>
    > >> ' TODO: update the ConnectionString and Command values for your
    > >>application
    > >>
    > >> Dim ConnectionString As String =

    "Provider=Microsoft.Jet.OLEDB.4.0;data
    > >>source=specialorders.mdb"
    > >> Dim SelectCommand As String = "SELECT PurchaseOrderNumber, LastName,
    > >>FirstName from [Purchase Orders]"
    > >>
    > >> Dim isEditing As Boolean = False
    > >>
    > >> Sub Page_Load(Sender As Object, E As EventArgs)
    > >>
    > >> If Not Page.IsPostBack Then
    > >>
    > >> ' Databind the data grid on the first request only
    > >> ' (on postback, bind only in editing, paging and sorting
    > >>commands)
    > >>
    > >> BindGrid()
    > >>
    > >> End If
    > >>
    > >> End Sub
    > >>
    > >> ' ---------------------------------------------------------------
    > >> '
    > >> ' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
    > >> '
    > >>
    > >> Sub DataGrid_ItemCommand(Sender As Object, E As
    > >>DataGridCommandEventArgs)
    > >>
    > >> ' this event fires prior to all of the other commands
    > >> ' use it to provide a more graceful transition out of edit mode
    > >>
    > >> CheckIsEditing(e.CommandName)
    > >>
    > >> End Sub
    > >>
    > >> Sub CheckIsEditing(commandName As String)
    > >>
    > >> If DataGrid1.EditItemIndex <> -1 Then
    > >>
    > >> ' we are currently editing a row
    > >> If commandName <> "Cancel" And commandName <> "Update" Then
    > >>
    > >> ' user's edit changes (If any) will not be committed
    > >> Message.Text = "Your changes have not been saved yet.
    > >>Please press update to save your changes, or cancel to discard your
    > >>changes, before selecting another item."
    > >> isEditing = True
    > >>
    > >> End If
    > >>
    > >> End If
    > >>
    > >> End Sub
    > >>
    > >> Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)
    > >>
    > >> ' turn on editing for the selected row
    > >>
    > >> If Not isEditing Then
    > >>
    > >> DataGrid1.EditItemIndex = e.Item.ItemIndex
    > >> BindGrid()
    > >>
    > >> End If
    > >>
    > >> End Sub
    > >>
    > >> Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)
    > >>
    > >> ' update the database with the new values
    > >>
    > >> ' get the edit text boxes
    > >> Dim PurchaseOrderNumber As Long =
    > >>CType(e.Item.Cells(2).Controls(0), TextBox).Text
    > >> Dim LastName As String = CType(e.Item.Cells(3).Controls(0),
    > >>TextBox).Text
    > >> Dim FirstName As String = CType(e.Item.Cells(4).Controls(0),
    > >>TextBox).Text
    > >>
    > >> ' TODO: update the Command value for your application
    > >> Dim myConnection As New OleDbConnection(ConnectionString)
    > >> Dim UpdateCommand As New OleDbCommand()
    > >> UpdateCommand.Connection = myConnection
    > >>
    > >> If AddingNew = True Then
    > >> UpdateCommand.CommandText = "INSERT INTO [Purchase
    > >>Orders](PurchaseOrderNumber, LastName, FirstName) VALUES
    > >>(@PurchaseOrderNumber, @LastName, @FirstName)"
    > >> Else
    > >> UpdateCommand.CommandText = "UPDATE [Purchase Orders] SET
    > >>LastName = @LastName, FirstName = @FirstName WHERE PurchaseOrderNumber =
    > >>@PurchaseOrderNumber"
    > >> End If
    > >>
    > >> UpdateCommand.Parameters.Add("@PurchaseOrderNumber",
    > >>OleDbType.BigInt).Value = PurchaseOrderNumber
    > >> UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar,
    > >>15).Value = LastName
    > >> UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar,
    > >>12).Value = FirstName
    > >>
    > >> ' execute the command
    > >> Try
    > >> myConnection.Open()
    > >> UpdateCommand.ExecuteNonQuery()
    > >>
    > >> Catch ex as Exception
    > >> Message.Text = ex.ToString()
    > >>
    > >> Finally
    > >> myConnection.Close()
    > >>
    > >> End Try
    > >>
    > >> ' Resort the grid for new records
    > >> If AddingNew = True Then
    > >> DataGrid1.CurrentPageIndex = 0
    > >> AddingNew = False
    > >> End If
    > >>
    > >> ' rebind the grid
    > >> DataGrid1.EditItemIndex = -1
    > >> BindGrid()
    > >>
    > >> End Sub
    > >>
    > >> Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)
    > >>
    > >> ' cancel editing
    > >>
    > >> DataGrid1.EditItemIndex = -1
    > >> BindGrid()
    > >>
    > >> AddingNew = False
    > >>
    > >> End Sub
    > >>
    > >> Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)
    > >>
    > >> ' delete the selected row
    > >>
    > >> If Not isEditing Then
    > >>
    > >> ' the key value for this row is in the DataKeys collection
    > >> Dim keyValue As Long =
    > >>CLng(DataGrid1.DataKeys(e.Item.ItemIndex))
    > >>
    > >> ' TODO: update the Command value for your application
    > >> Dim myConnection As New OleDbConnection(ConnectionString)
    > >> Dim DeleteCommand As New OleDbCommand("DELETE from [Purchase
    > >>Orders] where PurchaseOrderNumber='" & keyValue & "'", myConnection)
    > >>
    > >> ' execute the command
    > >> myConnection.Open()
    > >> DeleteCommand.ExecuteNonQuery()
    > >> myConnection.Close()
    > >>
    > >> ' rebind the grid
    > >> DataGrid1.CurrentPageIndex = 0
    > >> DataGrid1.EditItemIndex = -1
    > >> BindGrid()
    > >>
    > >> End If
    > >>
    > >> End Sub
    > >>
    > >> Sub DataGrid_Page(Sender As Object, E As

    DataGridPageChangedEventArgs)
    > >>
    > >> ' display a new page of data
    > >>
    > >> If Not isEditing Then
    > >>
    > >> DataGrid1.EditItemIndex = -1
    > >> DataGrid1.CurrentPageIndex = e.NewPageIndex
    > >> BindGrid()
    > >>
    > >> End If
    > >>
    > >> End Sub
    > >>
    > >> Sub AddNew_Click(Sender As Object, E As EventArgs)
    > >>
    > >> ' add a new row to the end of the data, and set editing mode 'on'
    > >>
    > >> CheckIsEditing("")
    > >>
    > >> If Not isEditing = True Then
    > >>
    > >> ' set the flag so we know to do an insert at Update time
    > >> AddingNew = True
    > >>
    > >> ' add new row to the end of the dataset after binding
    > >>
    > >> ' first get the data
    > >> Dim myConnection As New OleDbConnection(ConnectionString)
    > >> Dim myCommand As New OleDbDataAdapter(SelectCommand,
    > >>myConnection)
    > >>
    > >> Dim ds As New DataSet()
    > >> myCommand.Fill(ds)
    > >>
    > >> ' add a new blank row to the end of the data
    > >> Dim rowValues As Object() = {"", "", ""}
    > >> ds.Tables(0).Rows.Add(rowValues)
    > >>
    > >> ' figure out the EditItemIndex, last record on last page
    > >> Dim recordCount As Integer = ds.Tables(0).Rows.Count
    > >>
    > >> If recordCount > 1 Then
    > >>
    > >> recordCount -= 1
    > >> DataGrid1.CurrentPageIndex = recordCount \
    > >>DataGrid1.PageSize
    > >> DataGrid1.EditItemIndex = recordCount Mod
    > >>DataGrid1.PageSize
    > >>
    > >> End If
    > >>
    > >> ' databind
    > >> DataGrid1.DataSource = ds
    > >> DataGrid1.DataBind()
    > >>
    > >> End If
    > >>
    > >>
    > >> End Sub
    > >>
    > >> ' ---------------------------------------------------------------
    > >> '
    > >> ' Helpers Methods:
    > >> '
    > >>
    > >> ' property to keep track of whether we are adding a new record,
    > >> ' and save it in viewstate between postbacks
    > >>
    > >> Property AddingNew() As Boolean
    > >>
    > >> Get
    > >> Dim o As Object = ViewState("AddingNew")
    > >> If o Is Nothing Then
    > >> Return False
    > >> End If
    > >> Return CBool(o)
    > >> End Get
    > >>
    > >> Set(ByVal Value As Boolean)
    > >> ViewState("AddingNew") = Value
    > >> End Set
    > >>
    > >> End Property
    > >>
    > >> Sub BindGrid()
    > >>
    > >> Dim myConnection As New OleDbConnection(ConnectionString)
    > >> Dim myCommand As New OleDbDataAdapter(SelectCommand,

    myConnection)
    > >>
    > >> Dim ds As New DataSet()
    > >> myCommand.Fill(ds)
    > >>
    > >> DataGrid1.DataSource = ds
    > >> DataGrid1.DataBind()
    > >>
    > >> End Sub
    > >>
    > >></script>
    > >><html>
    > >><head>
    > >></head>
    > >><body style="FONT-FAMILY: arial">
    > >> <h2>Editable Data Grid
    > >> </h2>
    > >> <hr size="1" />
    > >> <form runat="server">
    > >> <asp:datagrid id="DataGrid1" runat="server"
    > >>HorizontalAlign="Center" DataKeyField="PurchaseOrderNumber"
    > >>OnItemCommand="DataGrid_ItemCommand" OnEditCommand="DataGrid_Edit"
    > >>OnUpdateCommand="DataGrid_Update" OnCancelCommand="DataGrid_Cancel"
    > >>OnDeleteCommand="DataGrid_Delete" AllowPaging="True" PageSize="6"
    > >>OnPageIndexChanged="DataGrid_Page" ForeColor="Black" BackColor="White"
    > >>CellPadding="3" CellSpacing="1" width="100%">
    > >> <FooterStyle backcolor="#C6C3C6"></FooterStyle>
    > >> <ItemStyle backcolor="#DEDFDE"></ItemStyle>
    > >> <HeaderStyle font-bold="True" forecolor="White"
    > >>backcolor="#4A3C8C"></HeaderStyle>
    > >> <Columns>
    > >> <asp:EditCommandColumn ButtonType="PushButton"
    > >>UpdateText="Update" CancelText="Cancel" EditText="Edit">
    > >> <ItemStyle font-size="Smaller"

    width="10%"></ItemStyle>
    > >> </asp:EditCommandColumn>
    > >> <asp:ButtonColumn Text="Delete" ButtonType="PushButton"
    > >>CommandName="Delete">
    > >> <ItemStyle font-size="Smaller"

    width="10%"></ItemStyle>
    > >> </asp:ButtonColumn>
    > >> </Columns>
    > >> <PagerStyle font-size="Smaller" horizontalalign="Right"
    > >>backcolor="#C6C3C6" mode="NumericPages"></PagerStyle>
    > >> </asp:datagrid>
    > >> <br />
    > >> <asp:LinkButton id="LinkButton1" onclick="AddNew_Click"
    > >>runat="server" Text="Add new item" Font-Size="smaller"></asp:LinkButton>
    > >> <br />
    > >> <br />
    > >> <asp:Label id="Message" runat="server" width="80%"
    > >>enableviewstate="false" forecolor="red"></asp:Label>
    > >> </form>
    > >></body>
    > >></html>
    > >>

    > >
    > >
     
    Patirck Ige, Oct 6, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dave
    Replies:
    1
    Views:
    543
    Anthony Williams
    Sep 10, 2003
  2. =?Utf-8?B?UGhpbA==?=

    Faster access to MDB? MS Access MDB and ASP

    =?Utf-8?B?UGhpbA==?=, Jan 26, 2005, in forum: ASP .Net
    Replies:
    8
    Views:
    477
    Kevin Spencer
    Jan 27, 2005
  3. John  Schult

    MDB to MDB Event Notification

    John Schult, May 4, 2005, in forum: Java
    Replies:
    0
    Views:
    480
    John Schult
    May 4, 2005
  4. Larry Bud

    Formview update--one field won't update

    Larry Bud, Jul 11, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    396
    Larry Bud
    Jul 11, 2007
  5. Dave

    Matrix generated Access mdb Update function not working

    Dave, Sep 1, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    1
    Views:
    179
    Anthony Williams
    Sep 10, 2003
Loading...

Share This Page