Must use updateable query?

J

Jim in Arizona

Continuing my lessons out of a book, I ran into a problem when trying for
the first time to update a datastore (access database in this case).

My Code:

Private Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim strConnection, strSQL As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\hhsinternal\comlog\testing.mdb"
strSQL = "SELECT tmasterid, firstname, lastname from tmaster;"

objConnection = New OleDbConnection(strConnection)
objAdapter = New OleDbDataAdapter(strSQL, objConnection)

objAdapter.Fill(objDataSet, "tmaster")

dglist1.DataSource = objDataSet.Tables("tmaster")
dglist1.DataBind()

Dim objTable As DataTable
Dim objNewRow As DataRow

objTable = objDataSet.Tables("tmaster")
objNewRow = objTable.NewRow()
objNewRow("FirstName") = "Pepsi"
objNewRow("LastName") = "Cola"
objTable.Rows.Add(objNewRow)

dglist2.DataSource = objTable.DefaultView
dglist2.DataBind()

Dim objRow As DataRow
objRow = objTable.Rows(3)
objRow("FirstName") = "Coca"
objRow("LastName") = "Cola"

dglist3.DataSource = objTable.DefaultView
dglist3.DataBind()

objTable.Rows(objTable.Rows.Count - 2).Delete()

dglist4.DataSource = objTable.DefaultView
dglist4.DataBind()

Dim objBuilder As OleDbCommandBuilder
objBuilder = New OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()
objAdapter.InsertCommand = objBuilder.GetInsertCommand()
objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()

objAdapter.Update(objDataSet, "tmaster")

strSQL = "SELECT tmasterid, firstname, lastname from tmaster;"
objConnection.Open()
Dim objCmd As New OleDbCommand(strSQL, objConnection)
dgUpd.DataSource =
objCmd.ExecuteReader(CommandBehavior.CloseConnection)
dgUpd.DataBind()

End Sub

And .. The Horrible Error!! Line 55 is where the error is.

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.OleDb.OleDbException: Operation must use an
updateable query.

Source Error:


Line 53: objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()
Line 54:
Line 55: objAdapter.Update(objDataSet, "tmaster")
Line 56:
Line 57: strSQL = "SELECT tmasterid, firstname, lastname from
tmaster;"


Source File: E:\hhsinternal\comlog\synch2.aspx.vb Line: 55

Stack Trace:


[OleDbException (0x80004005): Operation must use an updateable query.]
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +277
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +48
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1802
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable,
DataTableMapping tableMapping) +38
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
+166
synch2_aspx.Page_Load(Object sender, EventArgs e) in
E:\hhsinternal\comlog\synch2.aspx.vb:55
System.Web.UI.Control.OnLoad(EventArgs e) +102
System.Web.UI.Control.LoadRecursive() +45
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +952
 
K

Kevin Spencer

This error usually occurs when the folder containing the database file does
not have the proper file permissions to allow the ASP.Net user account to
change or write to the folder. Inserts, Updates, and Deletes change the
file.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

Jim in Arizona said:
Continuing my lessons out of a book, I ran into a problem when trying for
the first time to update a datastore (access database in this case).

My Code:

Private Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim strConnection, strSQL As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\hhsinternal\comlog\testing.mdb"
strSQL = "SELECT tmasterid, firstname, lastname from tmaster;"

objConnection = New OleDbConnection(strConnection)
objAdapter = New OleDbDataAdapter(strSQL, objConnection)

objAdapter.Fill(objDataSet, "tmaster")

dglist1.DataSource = objDataSet.Tables("tmaster")
dglist1.DataBind()

Dim objTable As DataTable
Dim objNewRow As DataRow

objTable = objDataSet.Tables("tmaster")
objNewRow = objTable.NewRow()
objNewRow("FirstName") = "Pepsi"
objNewRow("LastName") = "Cola"
objTable.Rows.Add(objNewRow)

dglist2.DataSource = objTable.DefaultView
dglist2.DataBind()

Dim objRow As DataRow
objRow = objTable.Rows(3)
objRow("FirstName") = "Coca"
objRow("LastName") = "Cola"

dglist3.DataSource = objTable.DefaultView
dglist3.DataBind()

objTable.Rows(objTable.Rows.Count - 2).Delete()

dglist4.DataSource = objTable.DefaultView
dglist4.DataBind()

Dim objBuilder As OleDbCommandBuilder
objBuilder = New OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()
objAdapter.InsertCommand = objBuilder.GetInsertCommand()
objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()

objAdapter.Update(objDataSet, "tmaster")

strSQL = "SELECT tmasterid, firstname, lastname from tmaster;"
objConnection.Open()
Dim objCmd As New OleDbCommand(strSQL, objConnection)
dgUpd.DataSource =
objCmd.ExecuteReader(CommandBehavior.CloseConnection)
dgUpd.DataBind()

End Sub

And .. The Horrible Error!! Line 55 is where the error is.

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.OleDb.OleDbException: Operation must use an
updateable query.

Source Error:


Line 53: objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()
Line 54:
Line 55: objAdapter.Update(objDataSet, "tmaster")
Line 56:
Line 57: strSQL = "SELECT tmasterid, firstname, lastname from
tmaster;"


Source File: E:\hhsinternal\comlog\synch2.aspx.vb Line: 55

Stack Trace:


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

System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
+277
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +48
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1802
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping) +38
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable) +166
synch2_aspx.Page_Load(Object sender, EventArgs e) in
E:\hhsinternal\comlog\synch2.aspx.vb:55
System.Web.UI.Control.OnLoad(EventArgs e) +102
System.Web.UI.Control.LoadRecursive() +45
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +952
Click to expand...
 
E

Elliot M. Rodriguez

At first pass, I'd check out this block:

Dim objRow As DataRow
objRow = objTable.Rows(3)
objRow("FirstName") = "Coca"
objRow("LastName") = "Cola"

and this:
objTable.Rows(objTable.Rows.Count - 2).Delete()

While I dont know how many rows you are expecting in the original query,
if the rowcount is less than the ordinal number of the row youre
trying to delete, then you'll get an error.


Continuing my lessons out of a book, I ran into a problem when trying for
the first time to update a datastore (access database in this case).

My Code:

Private Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim strConnection, strSQL As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\hhsinternal\comlog\testing.mdb"
strSQL = "SELECT tmasterid, firstname, lastname from tmaster;"

objConnection = New OleDbConnection(strConnection)
objAdapter = New OleDbDataAdapter(strSQL, objConnection)

objAdapter.Fill(objDataSet, "tmaster")

dglist1.DataSource = objDataSet.Tables("tmaster")
dglist1.DataBind()

Dim objTable As DataTable
Dim objNewRow As DataRow

objTable = objDataSet.Tables("tmaster")
objNewRow = objTable.NewRow()
objNewRow("FirstName") = "Pepsi"
objNewRow("LastName") = "Cola"
objTable.Rows.Add(objNewRow)

dglist2.DataSource = objTable.DefaultView
dglist2.DataBind()

Dim objRow As DataRow
objRow = objTable.Rows(3)
objRow("FirstName") = "Coca"
objRow("LastName") = "Cola"

dglist3.DataSource = objTable.DefaultView
dglist3.DataBind()

objTable.Rows(objTable.Rows.Count - 2).Delete()

dglist4.DataSource = objTable.DefaultView
dglist4.DataBind()

Dim objBuilder As OleDbCommandBuilder
objBuilder = New OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()
objAdapter.InsertCommand = objBuilder.GetInsertCommand()
objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()

objAdapter.Update(objDataSet, "tmaster")

strSQL = "SELECT tmasterid, firstname, lastname from tmaster;"
objConnection.Open()
Dim objCmd As New OleDbCommand(strSQL, objConnection)
dgUpd.DataSource =
objCmd.ExecuteReader(CommandBehavior.CloseConnection)
dgUpd.DataBind()

End Sub

And .. The Horrible Error!! Line 55 is where the error is.

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.OleDb.OleDbException: Operation must use an
updateable query.

Source Error:


Line 53: objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()
Line 54:
Line 55: objAdapter.Update(objDataSet, "tmaster")
Line 56:
Line 57: strSQL = "SELECT tmasterid, firstname, lastname from
tmaster;"


Source File: E:\hhsinternal\comlog\synch2.aspx.vb Line: 55

Stack Trace:


[OleDbException (0x80004005): Operation must use an updateable query.]
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +277
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +48
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1802
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable,
DataTableMapping tableMapping) +38
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
+166
synch2_aspx.Page_Load(Object sender, EventArgs e) in
E:\hhsinternal\comlog\synch2.aspx.vb:55
System.Web.UI.Control.OnLoad(EventArgs e) +102
System.Web.UI.Control.LoadRecursive() +45
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +952
Click to expand...
 
J

Jim in Arizona

Kevin Spencer said:
This error usually occurs when the folder containing the database file
does not have the proper file permissions to allow the ASP.Net user
account to change or write to the folder. Inserts, Updates, and Deletes
change the file.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Ambiguity has a certain quality to it.
Click to expand...

That appeard to be the problem. Thanks Kevin.
Jim
 
J

Jim in Arizona

That also occured to me so I double check it and found it to be ok.

Thanks Elliot.
Jim

Elliot M. Rodriguez said:
At first pass, I'd check out this block:

Dim objRow As DataRow
objRow = objTable.Rows(3)
objRow("FirstName") = "Coca"
objRow("LastName") = "Cola"

and this:
objTable.Rows(objTable.Rows.Count - 2).Delete()

While I dont know how many rows you are expecting in the original query,
if the rowcount is less than the ordinal number of the row youre trying to
delete, then you'll get an error.


Continuing my lessons out of a book, I ran into a problem when trying for
the first time to update a datastore (access database in this case).

My Code:

Private Sub Page_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Me.Load
Dim strConnection, strSQL As String
Dim objDataSet As New DataSet()
Dim objConnection As OleDbConnection
Dim objAdapter As OleDbDataAdapter

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\hhsinternal\comlog\testing.mdb"
strSQL = "SELECT tmasterid, firstname, lastname from tmaster;"

objConnection = New OleDbConnection(strConnection)
objAdapter = New OleDbDataAdapter(strSQL, objConnection)

objAdapter.Fill(objDataSet, "tmaster")

dglist1.DataSource = objDataSet.Tables("tmaster")
dglist1.DataBind()

Dim objTable As DataTable
Dim objNewRow As DataRow

objTable = objDataSet.Tables("tmaster")
objNewRow = objTable.NewRow()
objNewRow("FirstName") = "Pepsi"
objNewRow("LastName") = "Cola"
objTable.Rows.Add(objNewRow)

dglist2.DataSource = objTable.DefaultView
dglist2.DataBind()

Dim objRow As DataRow
objRow = objTable.Rows(3)
objRow("FirstName") = "Coca"
objRow("LastName") = "Cola"

dglist3.DataSource = objTable.DefaultView
dglist3.DataBind()

objTable.Rows(objTable.Rows.Count - 2).Delete()

dglist4.DataSource = objTable.DefaultView
dglist4.DataBind()

Dim objBuilder As OleDbCommandBuilder
objBuilder = New OleDbCommandBuilder(objAdapter)
objAdapter.UpdateCommand = objBuilder.GetUpdateCommand()
objAdapter.InsertCommand = objBuilder.GetInsertCommand()
objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()

objAdapter.Update(objDataSet, "tmaster")

strSQL = "SELECT tmasterid, firstname, lastname from tmaster;"
objConnection.Open()
Dim objCmd As New OleDbCommand(strSQL, objConnection)
dgUpd.DataSource =
objCmd.ExecuteReader(CommandBehavior.CloseConnection)
dgUpd.DataBind()

End Sub

And .. The Horrible Error!! Line 55 is where the error is.

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.OleDb.OleDbException: Operation must use
an updateable query.

Source Error:


Line 53: objAdapter.DeleteCommand = objBuilder.GetDeleteCommand()
Line 54:
Line 55: objAdapter.Update(objDataSet, "tmaster")
Line 56:
Line 57: strSQL = "SELECT tmasterid, firstname, lastname from
tmaster;"


Source File: E:\hhsinternal\comlog\synch2.aspx.vb Line: 55

Stack Trace:


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

System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
+277
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs
rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
+48
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1802
System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable
dataTable, DataTableMapping tableMapping) +38
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable) +166
synch2_aspx.Page_Load(Object sender, EventArgs e) in
E:\hhsinternal\comlog\synch2.aspx.vb:55
System.Web.UI.Control.OnLoad(EventArgs e) +102
System.Web.UI.Control.LoadRecursive() +45
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +952
Click to expand...
Click to expand...
 

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

Similar Threads

Operation must use an updateable query .. 8
Help inserting Records 2
UpdateCommand error 3
SortCommand in datagrid, dataview.... 1
System.Data.OleDb.OleDbException: Operation must use an updateable query. 1
Can't get datagrid sort object to work 1
Issues with OledbDataAdapter.Update() 1
OleDbConnection syntax error 3

Members online

Total: 21 (members: 1, guests: 20)
Robots: 232

Forum statistics

Threads
473,764
Messages
2,569,566
Members
45,041
Latest member
RomeoFarnh

Latest Threads

Top