Must use updateable query?

[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.CloseConnecti on)
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.UpdatedRowStatusE rrors(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.UpdateFromDataTab le(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

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.40607.42; ASP.NET
Version:2.0.40607.42

[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" <ti*******@hotmail.com> wrote in message
news:eH**************@TK2MSFTNGP09.phx.gbl...

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.CloseConnecti on)
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.UpdatedRowStatusE rrors(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.UpdateFromDataTab le(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

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.40607.42;
ASP.NET Version:2.0.40607.42

[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.

Jim in Arizona wrote:

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.CloseConnecti on)
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.UpdatedRowStatusE rrors(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.UpdateFromDataTab le(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

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.40607.42; ASP.NET
Version:2.0.40607.42

[Jim in Arizona]

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:uN**************@TK2MSFTNGP12.phx.gbl...

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.

That appeard to be the problem. Thanks Kevin.
Jim

[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" <"elliotrodriguez[spamminyspam]"@gmail.com> wrote in
message news:eF**************@TK2MSFTNGP12.phx.gbl...

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.

Jim in Arizona wrote:

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.CloseConnecti on)
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.UpdatedRowStatusE rrors(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.UpdateFromDataTab le(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

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.40607.42;
ASP.NET Version:2.0.40607.42