Must use updateable query?

  • Thread starter Thread starter Jim in Arizona
  • Start date Start date
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
 
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...
 
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...
 
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
 
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...
 
Back
Top