none
Need solution for this error "Operation must use an updateable query" while running updating query RRS feed

  • Question

  • Hi

      I have Developed the win form solution to update the excel file data based on some condition, To achieve this i have written the

      following code.

       using (OleDbCommand cmd = new OleDbCommand())
            {
                    cmd.CommandText = "Update [" + sheetName + "] set [Count] = (Select Count(*) from [" + sheetName + "]  a where a.[FirstName] = [" + sheetName + "].[FirstName])";
                    cmd.Connection = con;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
            }

    My Data in excel like below

    FirstName LastName Items Count
    Sunil AM 10      0
    Sunil AM 10      0
    Sunil AM 10      0
    Ramesh L 20      0

    When i come to line executenonquery() I am getting the following error.

    "Operation must use an updateable query"

    plz suggest me to s=resolve this issue.

    --Regards

    Sunil

    Tuesday, September 6, 2016 5:28 PM

Answers

  • Hi Sunil A M,

    Sometimes you can edit data in the Datasheet View of a query to change the information in the underlying table. Other times, you can't. When you can't, the query is "non-updateable". When you try to create a recordset object based on a non-updateable query, the recordset becomes non-updateable.

    A recordset is never updateable when:
    1. It is based on a Crosstab query.
    2. It is based on a Union Query.
    3. It is an Aggregate Query that calculates a sum, average, count or other type of total on the values in a field.
    4. It is an Update Query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions
      Note: By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
    5. It is based on a Query that includes a linked ODBC table with no unique index.
    6. The database was opened as read-only or is located on a read-only drive.
    7. It is a SQL pass-through query.
    8. It is a query whose UniqueValues property is set to Yes. (That is, it is a query with a DISTINCT predicate.)
    9. Cartesian Joins (that is, a query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view.)
    10. Query based on three or more tables in which there is a many-to-one-to-many relationship.
      Note: Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
    11. Calculated fields. Even if the query itself is updateable, if a column in a query is based on a formula, the field cannot be updated. However, if the other fields in the formula are updated, the calculated field will automatically update.

    If you want to use update with Subquery, I would suggest that you could use SQL server database.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Sunil A M Tuesday, September 13, 2016 8:36 AM
    Wednesday, September 7, 2016 2:53 AM
    Moderator

All replies

  • Hi Sunil A M,

    Sometimes you can edit data in the Datasheet View of a query to change the information in the underlying table. Other times, you can't. When you can't, the query is "non-updateable". When you try to create a recordset object based on a non-updateable query, the recordset becomes non-updateable.

    A recordset is never updateable when:
    1. It is based on a Crosstab query.
    2. It is based on a Union Query.
    3. It is an Aggregate Query that calculates a sum, average, count or other type of total on the values in a field.
    4. It is an Update Query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions
      Note: By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
    5. It is based on a Query that includes a linked ODBC table with no unique index.
    6. The database was opened as read-only or is located on a read-only drive.
    7. It is a SQL pass-through query.
    8. It is a query whose UniqueValues property is set to Yes. (That is, it is a query with a DISTINCT predicate.)
    9. Cartesian Joins (that is, a query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view.)
    10. Query based on three or more tables in which there is a many-to-one-to-many relationship.
      Note: Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
    11. Calculated fields. Even if the query itself is updateable, if a column in a query is based on a formula, the field cannot be updated. However, if the other fields in the formula are updated, the calculated field will automatically update.

    If you want to use update with Subquery, I would suggest that you could use SQL server database.

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Sunil A M Tuesday, September 13, 2016 8:36 AM
    Wednesday, September 7, 2016 2:53 AM
    Moderator
  • In addition to Cole Wu's suggestions, make sure the read-only flag is not set on the file and also that there are no security issues with respect to updating the file. Also, as a test, remove the SELECT statement and see if you can update the file. It could be the SELECT sub query that is preventing the update.

    Keep in mind that Excel is not a standard database management system so there are some limitations when making changes through data access methods.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, September 7, 2016 12:03 PM