MDB Database won't update

  • Thread starter Jonathan Upright
  • Start date
J

Jonathan Upright

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

Jon

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 said:
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>
 
J

Jonathan Upright

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

Thanx,

Jonathan
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()

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

Jon

OleDataAdapter


Jonathan Upright said:
Would I have to use the "regular" DataAdapter or OleDbDataAdapter?

Thanx,

Jonathan
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()

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

Patirck Ige

Just remember its neither OleDB or SQL DataAdapter
Patrick

Jonathan Upright said:
Would I have to use the "regular" DataAdapter or OleDbDataAdapter?

Thanx,

Jonathan
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()

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%"> said:
</asp:EditCommandColumn>
<asp:ButtonColumn Text="Delete" ButtonType="PushButton"
CommandName="Delete">
<ItemStyle font-size="Smaller"
width="10%"> said:
</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>
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top