Must use updateable query?

Discussion in 'ASP .Net' started by Jim in Arizona, Jun 28, 2005.

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

    --------------------------------------------------------------------------------
    Version Information: Microsoft .NET Framework Version:2.0.40607.42; ASP.NET
    Version:2.0.40607.42
     
    Jim in Arizona, Jun 28, 2005
    #1
    1. Advertising

  2. 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" <> wrote in message
    news:...
    > 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
    >
    > --------------------------------------------------------------------------------
    > Version Information: Microsoft .NET Framework Version:2.0.40607.42;
    > ASP.NET Version:2.0.40607.42
    >
     
    Kevin Spencer, Jun 28, 2005
    #2
    1. Advertising

  3. 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.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
    >
    > --------------------------------------------------------------------------------
    > Version Information: Microsoft .NET Framework Version:2.0.40607.42; ASP.NET
    > Version:2.0.40607.42
    >
    >
     
    Elliot M. Rodriguez, Jun 28, 2005
    #3
  4. "Kevin Spencer" <> wrote in message
    news:...
    > 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, Jun 28, 2005
    #4
  5. 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:...
    > 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.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
    >>
    >> --------------------------------------------------------------------------------
    >> Version Information: Microsoft .NET Framework Version:2.0.40607.42;
    >> ASP.NET Version:2.0.40607.42
     
    Jim in Arizona, Jun 28, 2005
    #5
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. abdul haleem
    Replies:
    3
    Views:
    421
    Wim Hollebrandse
    Oct 19, 2003
  2. =?Utf-8?B?S2Vu?=

    Operation must use an updateable query Error!!!

    =?Utf-8?B?S2Vu?=, Feb 13, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    1,293
    Hans Kesting
    Feb 13, 2004
  3. =?Utf-8?B?S2Vu?=

    Operation must use an updateable query Error!!!

    =?Utf-8?B?S2Vu?=, Feb 13, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    332
    =?Utf-8?B?S2Vu?=
    Feb 13, 2004
  4. Alphonse Giambrone

    Re: Operation must use an updateable query.

    Alphonse Giambrone, Apr 28, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    3,023
    Pravin A. Sable
    Apr 29, 2004
  5. Kevin Spencer

    Re: Operation must use an updateable query.

    Kevin Spencer, Apr 28, 2004, in forum: ASP .Net
    Replies:
    3
    Views:
    618
    Alphonse Giambrone
    Apr 29, 2004
Loading...

Share This Page