473,554 Members | 2,935 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Operation must use an updateable query ..

I've been using an example out of a book to be able to edit the rows in a
database. I am getting the following error:

=============== =============== =============== ===========
=============== =============== =============== ===========

Server Error in '/' Application.
--------------------------------------------------------------------------------
Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.Ole Db.OleDbExcepti on: Operation must use an
updateable query.

Source Error:

Line 74:
Line 75: objConnection.O pen()
Line 76: adapter.Update( ds, "TMaster")
Line 77:
Line 78: objConnection.C lose()

Source File: E:\hhsinternal\ tests\editing\e diting.aspx.vb Line: 76

Stack Trace:

[OleDbException (0x80004005): Operation must use an updateable query.]
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atusErrors(RowU pdatedEventArgs
rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount)
+1303846
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atus(RowUpdated EventArgs
rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount) +46
System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows,
DataTableMappin g tableMapping) +1750
System.Data.Com mon.DbDataAdapt er.UpdateFromDa taTable(DataTab le dataTable,
DataTableMappin g tableMapping) +40
System.Data.Com mon.DbDataAdapt er.Update(DataS et dataSet, String srcTable)
+180
tests_editing_e diting.Update(I nt32 PK, String FirstName) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 76
tests_editing_e diting.UpdateRe cord(Object Sender,
DataGridCommand EventArgs E) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 39
System.Web.UI.W ebControls.Data Grid.OnUpdateCo mmand(DataGridC ommandEventArgs
e) +105
System.Web.UI.W ebControls.Data Grid.OnBubbleEv ent(Object source, EventArgs
e) +679
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args) +35
System.Web.UI.W ebControls.Data GridItem.OnBubb leEvent(Object source,
EventArgs e) +117
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args) +35
System.Web.UI.W ebControls.Link Button.OnComman d(CommandEventA rgs e) +115
System.Web.UI.W ebControls.Link Button.RaisePos tBackEvent(Stri ng
eventArgument) +134
System.Web.UI.W ebControls.Link Button.System.W eb.UI.IPostBack EventHandler.Ra isePostBackEven t(String
eventArgument) +7
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +11
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +180
System.Web.UI.P age.ProcessRequ estMain(Boolean
includeStagesBe foreAsyncPoint, Boolean includeStagesAf terAsyncPoint) +5670

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.502 15.44; ASP.NET
Version:2.0.502 15.44

=============== =============== =============== ===========
=============== =============== =============== ===========

I can pretty much follow the code, but I don't understand what updateably
query it is referring to.

Line 75: adapter.Update( ds, "TMaster")

I have a single MS Access 2003 DB with a single table TMaster and Four
columns (PK, FirstName, LastName, EmpID). I am only pulling the data for the
PK and FirstName for my testing purposes (See SQL Statement in code).

This is my code (editing.aspx.v b), the first Imports statement starts as
Line 1. TIA, Jim

Imports System.Data
Imports System.Data.Ole Db

Partial Class tests_editing_e diting
Inherits System.Web.UI.P age
Public Sub EditRecord(ByVa l Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = E.Item.ItemInde x
LoadGrid()
End Sub
Public Sub CancelRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = -1
LoadGrid()
End Sub

Public Sub UpdateRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

'Retrieve the field values in the edited row
Dim PK As Int32 = Convert.ToInt32 (E.Item.Cells(0 ).Text)
Dim FirstNameTextBo x As TextBox = CType(E.Item.Ce lls(1).Controls (0),
TextBox)
Dim FirstName As String = Convert.ToStrin g(FirstNameText Box.Text)

employees.EditI temIndex = -1
Update(PK, FirstName)

employees.DataS ource = ds.tables("TMas ter")
employees.DataB ind()

End Sub

Public Sub Update(ByVal PK As Int32, ByVal FirstName As String)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()
Dim tbl As DataTable = ds.Tables("TMas ter")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("PK ") _
}
Dim row As DataRow = tbl.Rows.Find(P K)
row.Item("First Name") = FirstName

Dim cb As New OleDbCommandBui lder(adapter)

objConnection.O pen()
adapter.Update( ds, "TMaster")

objConnection.C lose()
End Sub

Private Sub LoadGrid()
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If

Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

With employees
.DataSource = ds.Tables("TMas ter")
.DataBind()
End With

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
If Not Page.IsPostBack Then
LoadGrid()
End If
End Sub
End Class

=============== =============== =============
=============== =============== =============
Below is the editing.aspx code if needed ..
=============== =============== =============
=============== =============== =============

<%@ Page Language="VB" AutoEventWireup ="false" CodeFile="editi ng.aspx.vb"
Inherits="tests _editing_editin g" %>

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="employees" runat="server" CellPadding="5"
AutoGenerateCol umns="false" OnEditCommand=" EditRecord"
OnCancelCommand ="CancelReco rd"
OnUpdateCommand ="UpdateRecord" >
<Columns>
<asp:BoundColum n DataField="PK" ReadOnly="true" Visible="false" />
<asp:BoundColum n DataField="Firs tName" HeaderText="Fir st Name"
ReadOnly="False " />
<asp:EditComman dColumn ButtonType="Lin kButton" UpdateText="Sav e"
CancelText="Can cel" EditText="Edit" />
</Columns>
</asp:DataGrid>
</div>
</form>
</body>
</html>

Dec 12 '05 #1
8 2996
You didn't define an update query for the adapter to use. It doesn't know
how to do the update on its own.

You either have to supply the update command, or you have to use an
OleDBCommandBui lder object. If can do some research on both these techniques
for more information.

"Jim in Arizona" <ti*******@hotm ail.com> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...
I've been using an example out of a book to be able to edit the rows in a
database. I am getting the following error:

=============== =============== =============== ===========
=============== =============== =============== ===========

Server Error in '/' Application.
--------------------------------------------------------------------------------
Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.Ole Db.OleDbExcepti on: Operation must use an
updateable query.

Source Error:

Line 74:
Line 75: objConnection.O pen()
Line 76: adapter.Update( ds, "TMaster")
Line 77:
Line 78: objConnection.C lose()

Source File: E:\hhsinternal\ tests\editing\e diting.aspx.vb Line: 76

Stack Trace:

[OleDbException (0x80004005): Operation must use an updateable query.]

System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atusErrors(RowU pdatedEventArgs
rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount)
+1303846
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atus(RowUpdated EventArgs
rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount) +46
System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows,
DataTableMappin g tableMapping) +1750
System.Data.Com mon.DbDataAdapt er.UpdateFromDa taTable(DataTab le
dataTable, DataTableMappin g tableMapping) +40
System.Data.Com mon.DbDataAdapt er.Update(DataS et dataSet, String
srcTable) +180
tests_editing_e diting.Update(I nt32 PK, String FirstName) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 76
tests_editing_e diting.UpdateRe cord(Object Sender,
DataGridCommand EventArgs E) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 39

System.Web.UI.W ebControls.Data Grid.OnUpdateCo mmand(DataGridC ommandEventArgs
e) +105
System.Web.UI.W ebControls.Data Grid.OnBubbleEv ent(Object source,
EventArgs e) +679
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args)
+35
System.Web.UI.W ebControls.Data GridItem.OnBubb leEvent(Object source,
EventArgs e) +117
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args)
+35
System.Web.UI.W ebControls.Link Button.OnComman d(CommandEventA rgs e) +115
System.Web.UI.W ebControls.Link Button.RaisePos tBackEvent(Stri ng
eventArgument) +134

System.Web.UI.W ebControls.Link Button.System.W eb.UI.IPostBack EventHandler.Ra isePostBackEven t(String
eventArgument) +7
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +11
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +180
System.Web.UI.P age.ProcessRequ estMain(Boolean
includeStagesBe foreAsyncPoint, Boolean includeStagesAf terAsyncPoint) +5670

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.502 15.44;
ASP.NET Version:2.0.502 15.44

=============== =============== =============== ===========
=============== =============== =============== ===========

I can pretty much follow the code, but I don't understand what updateably
query it is referring to.

Line 75: adapter.Update( ds, "TMaster")

I have a single MS Access 2003 DB with a single table TMaster and Four
columns (PK, FirstName, LastName, EmpID). I am only pulling the data for
the PK and FirstName for my testing purposes (See SQL Statement in code).

This is my code (editing.aspx.v b), the first Imports statement starts as
Line 1. TIA, Jim

Imports System.Data
Imports System.Data.Ole Db

Partial Class tests_editing_e diting
Inherits System.Web.UI.P age
Public Sub EditRecord(ByVa l Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = E.Item.ItemInde x
LoadGrid()
End Sub
Public Sub CancelRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = -1
LoadGrid()
End Sub

Public Sub UpdateRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

'Retrieve the field values in the edited row
Dim PK As Int32 = Convert.ToInt32 (E.Item.Cells(0 ).Text)
Dim FirstNameTextBo x As TextBox =
CType(E.Item.Ce lls(1).Controls (0), TextBox)
Dim FirstName As String = Convert.ToStrin g(FirstNameText Box.Text)

employees.EditI temIndex = -1
Update(PK, FirstName)

employees.DataS ource = ds.tables("TMas ter")
employees.DataB ind()

End Sub

Public Sub Update(ByVal PK As Int32, ByVal FirstName As String)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()
Dim tbl As DataTable = ds.Tables("TMas ter")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("PK ") _
}
Dim row As DataRow = tbl.Rows.Find(P K)
row.Item("First Name") = FirstName

Dim cb As New OleDbCommandBui lder(adapter)

objConnection.O pen()
adapter.Update( ds, "TMaster")

objConnection.C lose()
End Sub

Private Sub LoadGrid()
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If

Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

With employees
.DataSource = ds.Tables("TMas ter")
.DataBind()
End With

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
If Not Page.IsPostBack Then
LoadGrid()
End If
End Sub
End Class

=============== =============== =============
=============== =============== =============
Below is the editing.aspx code if needed ..
=============== =============== =============
=============== =============== =============

<%@ Page Language="VB" AutoEventWireup ="false" CodeFile="editi ng.aspx.vb"
Inherits="tests _editing_editin g" %>

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="employees" runat="server" CellPadding="5"
AutoGenerateCol umns="false" OnEditCommand=" EditRecord"
OnCancelCommand ="CancelReco rd"
OnUpdateCommand ="UpdateRecord" >
<Columns>
<asp:BoundColum n DataField="PK" ReadOnly="true" Visible="false" />
<asp:BoundColum n DataField="Firs tName" HeaderText="Fir st Name"
ReadOnly="False " />
<asp:EditComman dColumn ButtonType="Lin kButton" UpdateText="Sav e"
CancelText="Can cel" EditText="Edit" />
</Columns>
</asp:DataGrid>
</div>
</form>
</body>
</html>


Dec 12 '05 #2
The most common problem asociated with the
"Operation must use an updateable query" error message is that the
account you are using doesn't have read/write permissions to the
directory and/or Access database file which you are trying to modify.

Juan T. Llibre
ASP.NET MVP
============
"Jim in Arizona" <ti*******@hotm ail.com> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...
I've been using an example out of a book to be able to edit the rows in a database. I am getting
the following error:

=============== =============== =============== ===========
=============== =============== =============== ===========

Server Error in '/' Application.
--------------------------------------------------------------------------------
Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the current web request.
Please review the stack trace for more information about the error and where it originated in the
code.

Exception Details: System.Data.Ole Db.OleDbExcepti on: Operation must use an updateable query.

Source Error:

Line 74:
Line 75: objConnection.O pen()
Line 76: adapter.Update( ds, "TMaster")
Line 77:
Line 78: objConnection.C lose()

Source File: E:\hhsinternal\ tests\editing\e diting.aspx.vb Line: 76

Stack Trace:

[OleDbException (0x80004005): Operation must use an updateable query.]
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atusErrors(RowU pdatedEventArgs rowUpdatedEvent ,
BatchCommandInf o[] batchCommands, Int32 commandCount) +1303846
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atus(RowUpdated EventArgs rowUpdatedEvent ,
BatchCommandInf o[] batchCommands, Int32 commandCount) +46
System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows, DataTableMappin g tableMapping) +1750
System.Data.Com mon.DbDataAdapt er.UpdateFromDa taTable(DataTab le dataTable, DataTableMappin g
tableMapping) +40
System.Data.Com mon.DbDataAdapt er.Update(DataS et dataSet, String srcTable) +180
tests_editing_e diting.Update(I nt32 PK, String FirstName) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 76
tests_editing_e diting.UpdateRe cord(Object Sender, DataGridCommand EventArgs E) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 39
System.Web.UI.W ebControls.Data Grid.OnUpdateCo mmand(DataGridC ommandEventArgs e) +105
System.Web.UI.W ebControls.Data Grid.OnBubbleEv ent(Object source, EventArgs e) +679
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args) +35
System.Web.UI.W ebControls.Data GridItem.OnBubb leEvent(Object source, EventArgs e) +117
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args) +35
System.Web.UI.W ebControls.Link Button.OnComman d(CommandEventA rgs e) +115
System.Web.UI.W ebControls.Link Button.RaisePos tBackEvent(Stri ng eventArgument) +134

System.Web.UI.W ebControls.Link Button.System.W eb.UI.IPostBack EventHandler.Ra isePostBackEven t(String
eventArgument) +7
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler sourceControl, String eventArgument)
+11
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +180
System.Web.UI.P age.ProcessRequ estMain(Boolean includeStagesBe foreAsyncPoint, Boolean
includeStagesAf terAsyncPoint) +5670

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.502 15.44; ASP.NET Version:2.0.502 15.44

=============== =============== =============== ===========
=============== =============== =============== ===========

I can pretty much follow the code, but I don't understand what updateably query it is referring
to.

Line 75: adapter.Update( ds, "TMaster")

I have a single MS Access 2003 DB with a single table TMaster and Four columns (PK, FirstName,
LastName, EmpID). I am only pulling the data for the PK and FirstName for my testing purposes (See
SQL Statement in code).

This is my code (editing.aspx.v b), the first Imports statement starts as Line 1. TIA, Jim

Imports System.Data
Imports System.Data.Ole Db

Partial Class tests_editing_e diting
Inherits System.Web.UI.P age
Public Sub EditRecord(ByVa l Sender As Object, ByVal E As DataGridCommand EventArgs)
employees.EditI temIndex = E.Item.ItemInde x
LoadGrid()
End Sub
Public Sub CancelRecord(By Val Sender As Object, ByVal E As DataGridCommand EventArgs)
employees.EditI temIndex = -1
LoadGrid()
End Sub

Public Sub UpdateRecord(By Val Sender As Object, ByVal E As DataGridCommand EventArgs)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

'Retrieve the field values in the edited row
Dim PK As Int32 = Convert.ToInt32 (E.Item.Cells(0 ).Text)
Dim FirstNameTextBo x As TextBox = CType(E.Item.Ce lls(1).Controls (0), TextBox)
Dim FirstName As String = Convert.ToStrin g(FirstNameText Box.Text)

employees.EditI temIndex = -1
Update(PK, FirstName)

employees.DataS ource = ds.tables("TMas ter")
employees.DataB ind()

End Sub

Public Sub Update(ByVal PK As Int32, ByVal FirstName As String)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()
Dim tbl As DataTable = ds.Tables("TMas ter")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("PK ") _
}
Dim row As DataRow = tbl.Rows.Find(P K)
row.Item("First Name") = FirstName

Dim cb As New OleDbCommandBui lder(adapter)

objConnection.O pen()
adapter.Update( ds, "TMaster")

objConnection.C lose()
End Sub

Private Sub LoadGrid()
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If

Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

With employees
.DataSource = ds.Tables("TMas ter")
.DataBind()
End With

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArg s) Handles Me.Load
If Not Page.IsPostBack Then
LoadGrid()
End If
End Sub
End Class

=============== =============== =============
=============== =============== =============
Below is the editing.aspx code if needed ..
=============== =============== =============
=============== =============== =============

<%@ Page Language="VB" AutoEventWireup ="false" CodeFile="editi ng.aspx.vb"
Inherits="tests _editing_editin g" %>

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="employees" runat="server" CellPadding="5"
AutoGenerateCol umns="false" OnEditCommand=" EditRecord" OnCancelCommand ="CancelReco rd"
OnUpdateCommand ="UpdateRecord" >
<Columns>
<asp:BoundColum n DataField="PK" ReadOnly="true" Visible="false" />
<asp:BoundColum n DataField="Firs tName" HeaderText="Fir st Name" ReadOnly="False " />
<asp:EditComman dColumn ButtonType="Lin kButton" UpdateText="Sav e" CancelText="Can cel"
EditText="Edit" />
</Columns>
</asp:DataGrid>
</div>
</form>
</body>
</html>


Dec 12 '05 #3
Jim i blogged about that error at:-
http://spaces.msn.com/members/naijacoder/
Look for the Error and read on.
Hope that helps
PAtrick
"Jim in Arizona" <ti*******@hotm ail.com> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...
I've been using an example out of a book to be able to edit the rows in a
database. I am getting the following error:

=============== =============== =============== ===========
=============== =============== =============== ===========

Server Error in '/' Application.
-------------------------------------------------------------------------- ------ Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.Ole Db.OleDbExcepti on: Operation must use an
updateable query.

Source Error:

Line 74:
Line 75: objConnection.O pen()
Line 76: adapter.Update( ds, "TMaster")
Line 77:
Line 78: objConnection.C lose()

Source File: E:\hhsinternal\ tests\editing\e diting.aspx.vb Line: 76

Stack Trace:

[OleDbException (0x80004005): Operation must use an updateable query.]
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atusErrors(RowU pdatedEventArgs rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount)
+1303846
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atus(RowUpdated EventArgs
rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount) +46
System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows,
DataTableMappin g tableMapping) +1750
System.Data.Com mon.DbDataAdapt er.UpdateFromDa taTable(DataTab le dataTable, DataTableMappin g tableMapping) +40
System.Data.Com mon.DbDataAdapt er.Update(DataS et dataSet, String srcTable) +180
tests_editing_e diting.Update(I nt32 PK, String FirstName) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 76
tests_editing_e diting.UpdateRe cord(Object Sender,
DataGridCommand EventArgs E) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 39
System.Web.UI.W ebControls.Data Grid.OnUpdateCo mmand(DataGridC ommandEventArgs e) +105
System.Web.UI.W ebControls.Data Grid.OnBubbleEv ent(Object source, EventArgs e) +679
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args) +35 System.Web.UI.W ebControls.Data GridItem.OnBubb leEvent(Object source,
EventArgs e) +117
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args) +35 System.Web.UI.W ebControls.Link Button.OnComman d(CommandEventA rgs e) +115
System.Web.UI.W ebControls.Link Button.RaisePos tBackEvent(Stri ng
eventArgument) +134
System.Web.UI.W ebControls.Link Button.System.W eb.UI.IPostBack EventHandler.Ra i
sePostBackEvent (String eventArgument) +7
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +11
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData) +180 System.Web.UI.P age.ProcessRequ estMain(Boolean
includeStagesBe foreAsyncPoint, Boolean includeStagesAf terAsyncPoint) +5670

-------------------------------------------------------------------------- ------ Version Information: Microsoft .NET Framework Version:2.0.502 15.44; ASP.NET Version:2.0.502 15.44

=============== =============== =============== ===========
=============== =============== =============== ===========

I can pretty much follow the code, but I don't understand what updateably
query it is referring to.

Line 75: adapter.Update( ds, "TMaster")

I have a single MS Access 2003 DB with a single table TMaster and Four
columns (PK, FirstName, LastName, EmpID). I am only pulling the data for the PK and FirstName for my testing purposes (See SQL Statement in code).

This is my code (editing.aspx.v b), the first Imports statement starts as
Line 1. TIA, Jim

Imports System.Data
Imports System.Data.Ole Db

Partial Class tests_editing_e diting
Inherits System.Web.UI.P age
Public Sub EditRecord(ByVa l Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = E.Item.ItemInde x
LoadGrid()
End Sub
Public Sub CancelRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = -1
LoadGrid()
End Sub

Public Sub UpdateRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

'Retrieve the field values in the edited row
Dim PK As Int32 = Convert.ToInt32 (E.Item.Cells(0 ).Text)
Dim FirstNameTextBo x As TextBox = CType(E.Item.Ce lls(1).Controls (0), TextBox)
Dim FirstName As String = Convert.ToStrin g(FirstNameText Box.Text)

employees.EditI temIndex = -1
Update(PK, FirstName)

employees.DataS ource = ds.tables("TMas ter")
employees.DataB ind()

End Sub

Public Sub Update(ByVal PK As Int32, ByVal FirstName As String)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()
Dim tbl As DataTable = ds.Tables("TMas ter")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("PK ") _
}
Dim row As DataRow = tbl.Rows.Find(P K)
row.Item("First Name") = FirstName

Dim cb As New OleDbCommandBui lder(adapter)

objConnection.O pen()
adapter.Update( ds, "TMaster")

objConnection.C lose()
End Sub

Private Sub LoadGrid()
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If

Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

With employees
.DataSource = ds.Tables("TMas ter")
.DataBind()
End With

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
If Not Page.IsPostBack Then
LoadGrid()
End If
End Sub
End Class

=============== =============== =============
=============== =============== =============
Below is the editing.aspx code if needed ..
=============== =============== =============
=============== =============== =============

<%@ Page Language="VB" AutoEventWireup ="false" CodeFile="editi ng.aspx.vb"
Inherits="tests _editing_editin g" %>

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="employees" runat="server" CellPadding="5"
AutoGenerateCol umns="false" OnEditCommand=" EditRecord"
OnCancelCommand ="CancelReco rd"
OnUpdateCommand ="UpdateRecord" >
<Columns>
<asp:BoundColum n DataField="PK" ReadOnly="true" Visible="false" />
<asp:BoundColum n DataField="Firs tName" HeaderText="Fir st Name"
ReadOnly="False " />
<asp:EditComman dColumn ButtonType="Lin kButton" UpdateText="Sav e"
CancelText="Can cel" EditText="Edit" />
</Columns>
</asp:DataGrid>
</div>
</form>
</body>
</html>


Dec 13 '05 #4

"Patrick.O. Ige" <na********@hot mail.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..
Jim i blogged about that error at:-
http://spaces.msn.com/members/naijacoder/
Look for the Error and read on.
Hope that helps
PAtrick

I remember having such issues when I was working with classic ASP and in
most cases it was security permissions on the access DB. Because of that
prior experience, I made sure to check the permissions before I posted and
security permissions are full control for all auth users on our network, and
I'm an admin so have full control anyway. I double check again this morning
to make sure and I still get the same error. I'm at a loss. "Jim in Arizona" <ti*******@hotm ail.com> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...
I've been using an example out of a book to be able to edit the rows in a
database. I am getting the following error:

=============== =============== =============== ===========
=============== =============== =============== ===========

Server Error in '/' Application.
--------------------------------------------------------------------------

------
Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.Ole Db.OleDbExcepti on: Operation must use
an
updateable query.

Source Error:

Line 74:
Line 75: objConnection.O pen()
Line 76: adapter.Update( ds, "TMaster")
Line 77:
Line 78: objConnection.C lose()

Source File: E:\hhsinternal\ tests\editing\e diting.aspx.vb Line: 76

Stack Trace:

[OleDbException (0x80004005): Operation must use an updateable query.]

System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atusErrors(RowU pdatedEventArgs
rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount)
+1303846
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atus(RowUpdated EventArgs
rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount)
+46
System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows,
DataTableMappin g tableMapping) +1750
System.Data.Com mon.DbDataAdapt er.UpdateFromDa taTable(DataTab le

dataTable,
DataTableMappin g tableMapping) +40
System.Data.Com mon.DbDataAdapt er.Update(DataS et dataSet, String

srcTable)
+180
tests_editing_e diting.Update(I nt32 PK, String FirstName) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 76
tests_editing_e diting.UpdateRe cord(Object Sender,
DataGridCommand EventArgs E) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 39

System.Web.UI.W ebControls.Data Grid.OnUpdateCo mmand(DataGridC ommandEventArgs
e) +105
System.Web.UI.W ebControls.Data Grid.OnBubbleEv ent(Object source,

EventArgs
e) +679
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args)

+35
System.Web.UI.W ebControls.Data GridItem.OnBubb leEvent(Object source,
EventArgs e) +117
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args)

+35
System.Web.UI.W ebControls.Link Button.OnComman d(CommandEventA rgs e)
+115
System.Web.UI.W ebControls.Link Button.RaisePos tBackEvent(Stri ng
eventArgument) +134

System.Web.UI.W ebControls.Link Button.System.W eb.UI.IPostBack EventHandler.Ra i
sePostBackEvent (String
eventArgument) +7
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +11
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData)

+180
System.Web.UI.P age.ProcessRequ estMain(Boolean
includeStagesBe foreAsyncPoint, Boolean includeStagesAf terAsyncPoint)
+5670

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

------
Version Information: Microsoft .NET Framework Version:2.0.502 15.44;

ASP.NET
Version:2.0.502 15.44

=============== =============== =============== ===========
=============== =============== =============== ===========

I can pretty much follow the code, but I don't understand what updateably
query it is referring to.

Line 75: adapter.Update( ds, "TMaster")

I have a single MS Access 2003 DB with a single table TMaster and Four
columns (PK, FirstName, LastName, EmpID). I am only pulling the data for

the
PK and FirstName for my testing purposes (See SQL Statement in code).

This is my code (editing.aspx.v b), the first Imports statement starts as
Line 1. TIA, Jim

Imports System.Data
Imports System.Data.Ole Db

Partial Class tests_editing_e diting
Inherits System.Web.UI.P age
Public Sub EditRecord(ByVa l Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = E.Item.ItemInde x
LoadGrid()
End Sub
Public Sub CancelRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = -1
LoadGrid()
End Sub

Public Sub UpdateRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

'Retrieve the field values in the edited row
Dim PK As Int32 = Convert.ToInt32 (E.Item.Cells(0 ).Text)
Dim FirstNameTextBo x As TextBox =

CType(E.Item.Ce lls(1).Controls (0),
TextBox)
Dim FirstName As String = Convert.ToStrin g(FirstNameText Box.Text)

employees.EditI temIndex = -1
Update(PK, FirstName)

employees.DataS ource = ds.tables("TMas ter")
employees.DataB ind()

End Sub

Public Sub Update(ByVal PK As Int32, ByVal FirstName As String)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()
Dim tbl As DataTable = ds.Tables("TMas ter")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("PK ") _
}
Dim row As DataRow = tbl.Rows.Find(P K)
row.Item("First Name") = FirstName

Dim cb As New OleDbCommandBui lder(adapter)

objConnection.O pen()
adapter.Update( ds, "TMaster")

objConnection.C lose()
End Sub

Private Sub LoadGrid()
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If

Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

With employees
.DataSource = ds.Tables("TMas ter")
.DataBind()
End With

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
If Not Page.IsPostBack Then
LoadGrid()
End If
End Sub
End Class

=============== =============== =============
=============== =============== =============
Below is the editing.aspx code if needed ..
=============== =============== =============
=============== =============== =============

<%@ Page Language="VB" AutoEventWireup ="false" CodeFile="editi ng.aspx.vb"
Inherits="tests _editing_editin g" %>

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="employees" runat="server" CellPadding="5"
AutoGenerateCol umns="false" OnEditCommand=" EditRecord"
OnCancelCommand ="CancelReco rd"
OnUpdateCommand ="UpdateRecord" >
<Columns>
<asp:BoundColum n DataField="PK" ReadOnly="true" Visible="false" />
<asp:BoundColum n DataField="Firs tName" HeaderText="Fir st Name"
ReadOnly="False " />
<asp:EditComman dColumn ButtonType="Lin kButton" UpdateText="Sav e"
CancelText="Can cel" EditText="Edit" />
</Columns>
</asp:DataGrid>
</div>
</form>
</body>
</html>



Dec 13 '05 #5

"Marina" <so*****@nospam .com> wrote in message
news:%2******** *******@TK2MSFT NGP12.phx.gbl.. .
You didn't define an update query for the adapter to use. It doesn't know
how to do the update on its own.

You either have to supply the update command, or you have to use an
OleDBCommandBui lder object. If can do some research on both these
techniques for more information.


Hi Marina.

Under the Update subroutine:

Public Sub Update(ByVal PK As Int32, ByVal FirstName As String)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()
Dim tbl As DataTable = ds.Tables("TMas ter")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("PK ") _
}
Dim row As DataRow = tbl.Rows.Find(P K)
row.Item("First Name") = FirstName

Dim cb As New OleDbCommandBui lder(adapter)

objConnection.O pen()
adapter.Update( ds, "TMaster")

objConnection.C lose()
End Sub

You can see that I have the cb OleDbCommandBui lder object, although I don't
know if it is being used correctly. Is there more needed to that?
Dec 13 '05 #6

"Marina" <so*****@nospam .com> wrote in message
news:%2******** *******@TK2MSFT NGP12.phx.gbl.. .
You didn't define an update query for the adapter to use. It doesn't know
how to do the update on its own.

You either have to supply the update command, or you have to use an
OleDBCommandBui lder object. If can do some research on both these
techniques for more information.


Hi Marina.

Under the Update subroutine:

Public Sub Update(ByVal PK As Int32, ByVal FirstName As String)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"

Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection)
Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()
Dim tbl As DataTable = ds.Tables("TMas ter")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("PK ") _
}
Dim row As DataRow = tbl.Rows.Find(P K)
row.Item("First Name") = FirstName

Dim cb As New OleDbCommandBui lder(adapter)

objConnection.O pen()
adapter.Update( ds, "TMaster")

objConnection.C lose()
End Sub

You can see that I have the cb OleDbCommandBui lder object, although I don't
know if it is being used correctly. Is there more needed to that?
Dec 13 '05 #7
Jim are you giving the ASPNET acct the right permissions?
Patrick

"Jim in Arizona" <ti*******@hotm ail.com> wrote in message
news:#a******** ******@TK2MSFTN GP12.phx.gbl...

"Patrick.O. Ige" <na********@hot mail.com> wrote in message
news:%2******** ********@tk2msf tngp13.phx.gbl. ..
Jim i blogged about that error at:-
http://spaces.msn.com/members/naijacoder/
Look for the Error and read on.
Hope that helps
PAtrick

I remember having such issues when I was working with classic ASP and in
most cases it was security permissions on the access DB. Because of that
prior experience, I made sure to check the permissions before I posted and
security permissions are full control for all auth users on our network,

and I'm an admin so have full control anyway. I double check again this morning to make sure and I still get the same error. I'm at a loss.
"Jim in Arizona" <ti*******@hotm ail.com> wrote in message
news:ex******** ******@TK2MSFTN GP09.phx.gbl...
I've been using an example out of a book to be able to edit the rows in a database. I am getting the following error:

=============== =============== =============== ===========
=============== =============== =============== ===========

Server Error in '/' Application.
------------------------------------------------------------------------- - ------
Operation must use an updateable query.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.Data.Ole Db.OleDbExcepti on: Operation must use
an
updateable query.

Source Error:

Line 74:
Line 75: objConnection.O pen()
Line 76: adapter.Update( ds, "TMaster")
Line 77:
Line 78: objConnection.C lose()

Source File: E:\hhsinternal\ tests\editing\e diting.aspx.vb Line: 76

Stack Trace:

[OleDbException (0x80004005): Operation must use an updateable query.]

System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atusErrors(RowU pdatedEventArgs
rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount)
+1303846
System.Data.Com mon.DbDataAdapt er.UpdatedRowSt atus(RowUpdated EventArgs rowUpdatedEvent , BatchCommandInf o[] batchCommands, Int32 commandCount)
+46
System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows,
DataTableMappin g tableMapping) +1750
System.Data.Com mon.DbDataAdapt er.UpdateFromDa taTable(DataTab le

dataTable,
DataTableMappin g tableMapping) +40
System.Data.Com mon.DbDataAdapt er.Update(DataS et dataSet, String

srcTable)
+180
tests_editing_e diting.Update(I nt32 PK, String FirstName) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 76
tests_editing_e diting.UpdateRe cord(Object Sender,
DataGridCommand EventArgs E) in
E:\hhsinternal\ tests\editing\e diting.aspx.vb: 39

System.Web.UI.W ebControls.Data Grid.OnUpdateCo mmand(DataGridC ommandEventArgs
e) +105
System.Web.UI.W ebControls.Data Grid.OnBubbleEv ent(Object source,

EventArgs
e) +679
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs args) +35
System.Web.UI.W ebControls.Data GridItem.OnBubb leEvent(Object source,
EventArgs e) +117
System.Web.UI.C ontrol.RaiseBub bleEvent(Object source, EventArgs
args) +35
System.Web.UI.W ebControls.Link Button.OnComman d(CommandEventA rgs e)
+115
System.Web.UI.W ebControls.Link Button.RaisePos tBackEvent(Stri ng
eventArgument) +134

System.Web.UI.W ebControls.Link Button.System.W eb.UI.IPostBack EventHandler.Ra i sePostBackEvent (String
eventArgument) +7
System.Web.UI.P age.RaisePostBa ckEvent(IPostBa ckEventHandler
sourceControl, String eventArgument) +11
System.Web.UI.P age.RaisePostBa ckEvent(NameVal ueCollection postData)

+180
System.Web.UI.P age.ProcessRequ estMain(Boolean
includeStagesBe foreAsyncPoint, Boolean includeStagesAf terAsyncPoint)
+5670
-------------------------------------------------------------------------

- ------
Version Information: Microsoft .NET Framework Version:2.0.502 15.44;

ASP.NET
Version:2.0.502 15.44

=============== =============== =============== ===========
=============== =============== =============== ===========

I can pretty much follow the code, but I don't understand what
updateably query it is referring to.

Line 75: adapter.Update( ds, "TMaster")

I have a single MS Access 2003 DB with a single table TMaster and Four
columns (PK, FirstName, LastName, EmpID). I am only pulling the data for the
PK and FirstName for my testing purposes (See SQL Statement in code).

This is my code (editing.aspx.v b), the first Imports statement starts

as Line 1. TIA, Jim

Imports System.Data
Imports System.Data.Ole Db

Partial Class tests_editing_e diting
Inherits System.Web.UI.P age
Public Sub EditRecord(ByVa l Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = E.Item.ItemInde x
LoadGrid()
End Sub
Public Sub CancelRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
employees.EditI temIndex = -1
LoadGrid()
End Sub

Public Sub UpdateRecord(By Val Sender As Object, ByVal E As
DataGridCommand EventArgs)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"
Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection) Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

'Retrieve the field values in the edited row
Dim PK As Int32 = Convert.ToInt32 (E.Item.Cells(0 ).Text)
Dim FirstNameTextBo x As TextBox =

CType(E.Item.Ce lls(1).Controls (0),
TextBox)
Dim FirstName As String = Convert.ToStrin g(FirstNameText Box.Text)
employees.EditI temIndex = -1
Update(PK, FirstName)

employees.DataS ource = ds.tables("TMas ter")
employees.DataB ind()

End Sub

Public Sub Update(ByVal PK As Int32, ByVal FirstName As String)
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If
Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"
Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection) Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()
Dim tbl As DataTable = ds.Tables("TMas ter")
tbl.PrimaryKey = New DataColumn() _
{ _
tbl.Columns("PK ") _
}
Dim row As DataRow = tbl.Rows.Find(P K)
row.Item("First Name") = FirstName

Dim cb As New OleDbCommandBui lder(adapter)

objConnection.O pen()
adapter.Update( ds, "TMaster")

objConnection.C lose()
End Sub

Private Sub LoadGrid()
Dim objConnection As OleDbConnection
Dim strConnection = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=e:\hhsin ternal\tests\ed iting\editing.m db"

If objConnection Is Nothing Then
objConnection = New OleDbConnection (strConnection)
End If

If objConnection.S tate = ConnectionState .Closed Then
objConnection.O pen()
End If

Dim strSQLSelect As String = "SELECT PK, FirstName FROM TMaster"
Dim adapter As New OleDbDataAdapte r(strSQLSelect, objConnection) Dim ds As New DataSet()
adapter.Fill(ds , "TMaster")
objConnection.C lose()

With employees
.DataSource = ds.Tables("TMas ter")
.DataBind()
End With

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArg s) Handles Me.Load
If Not Page.IsPostBack Then
LoadGrid()
End If
End Sub
End Class

=============== =============== =============
=============== =============== =============
Below is the editing.aspx code if needed ..
=============== =============== =============
=============== =============== =============

<%@ Page Language="VB" AutoEventWireup ="false" CodeFile="editi ng.aspx.vb" Inherits="tests _editing_editin g" %>

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitl ed Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataGrid ID="employees" runat="server" CellPadding="5"
AutoGenerateCol umns="false" OnEditCommand=" EditRecord"
OnCancelCommand ="CancelReco rd"
OnUpdateCommand ="UpdateRecord" >
<Columns>
<asp:BoundColum n DataField="PK" ReadOnly="true" Visible="false" />
<asp:BoundColum n DataField="Firs tName" HeaderText="Fir st Name"
ReadOnly="False " />
<asp:EditComman dColumn ButtonType="Lin kButton" UpdateText="Sav e"
CancelText="Can cel" EditText="Edit" />
</Columns>
</asp:DataGrid>
</div>
</form>
</body>
</html>




Dec 13 '05 #8
"Patrick.O. Ige" <na********@tou ghguy.net> wrote in message
news:%2******** ********@TK2MSF TNGP11.phx.gbl. ..
Jim are you giving the ASPNET acct the right permissions?
Patrick


Well I'll be ...
This is the first time I've actually messed with an access DB and ASP.NET so
I never thought I had to verifiy ASPNET account permissions. That was it
exactly.
Most of my DB programming thus far in ASP.NET has been with SQL.

Thanks Patrick.
Dec 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
902
by: Tom wilson | last post by:
This is driving me nuts. I'm trying to update an Excel spreadsheet using ADO.Net and Oledb in VB.Net. The connection is open, the adapter is connected and the dataset is loaded. Here's the code in question: myDataRow = myDataSet.Tables(0).Rows(RowNum) myDataRow(ColumnCount) = Ailment Adapter.UpdateCommand = New OleDbCommand("UPDATE SET...
6
7550
by: ano1optimist | last post by:
I have been running these queries in Access 2000 with no problems. This week, I had to install Access 2003 to create some runtime versions for another application, and now I keep getting "operation must be an updateable query" messages when I try to run the same old queries. Here is a sample of one that is no longer working: UPDATE...
4
6495
by: MDW | last post by:
Hey all. I'm confused. I'm trying to add a single record into an Access 2000 database using ASP.Net. Here is the code: objConn = New OleDbConnection(strConnect) objConn.Open objCommand = New OleDbCommand("INSERT INTO LOGIN_MASTER (LOGIN_ID, PWD, F_NAME, L_NAME, TYPE_ID) VALUES ('" & strEmail & "','" & strPwd & "','" &
606
14377
by: Neil Zanella | last post by:
Hello, I am trying to update an MS access database from ASP.NET. I am using IIS on Windows XP Pro. I can issue SELECT statements from ASP.NET using ADO.NET but I cannot seem to be able to carry out INSERT statements. Here is the error which I am getting: Exception Details: System.Data.OleDb.OleDbException: Operation must use an...
2
7505
by: SheryMich | last post by:
Hi - I am having a bit of a problem with the insert into a database. When I go to insert a record into an un-keyed, single table Access database, I get the aforementioned ''Operation Must Use an Updateable Query' error. Points: - The database is in my local temp directory, not inetpub - The directory has read/write permissions - The...
1
3574
by: Muskito | last post by:
HELP!!! Hello All, I'm using VB.net 2003 and trying to update data in Excel worksheet. The program selects data from the excel, updates something in the MSSQL DB and then tries to update something back to the excel worksheet. My problem is that i'm having this annoying exception: "Operation must
11
3938
by: Arpan | last post by:
I have always been working with SQL Server 2005 for ASP.NET apps but due to some reasons, had to revert back to MS-Access 2000. When I try to insert/update a MS-Access DB table (MDB), ASP.NET generates the following error: Operation must use an updateable query. pointing to a line that says
1
2746
by: pavya | last post by:
Hi, I have developed one Web application. At that time my system had a FAT file system on it and this application worked properly. But now i have converted FAT file system to NTFS file system and whenever i am trying to run this application then it through the exception. I have used MS Access as a database. The error is as follows... Server...
0
2465
by: rickmedlin | last post by:
I know this has been posted on elsewhere but I'm stuck. I'm using the following append query to copy an Access query to Excel: INSERT INTO . SELECT * FROM Test; This isn't the real table name or workbook name, but the logic is the same. This method is preferable to me because it's efficient and there's much less chance for user error. ...
0
7506
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7782
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8018
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7541
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6123
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5423
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3533
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2006
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
823
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.