0

What is the right way to avoid errors on INSERT and UPDATES with a SqlDataAdapter/SqlCommandbuilder when the SQL Statement used to SELECT has a computed column as one of the return fields?

I get the error now that "The column Amount cannot be modified because it is either a computed column or is the result of a UNION operator".

UPDATE: I fixed the issue by using a query like this:

SELECT *, PercentRating * 500 AS CoreValue FROM ValueListings

And ditching the computed column. Now it works. How is that SqlCommandBuilder realizes to NOT build the CoreValue field into the UPDATE and INSERT statements? Anybody know how this works internally?

BuddyJoe
  • 69,735
  • 114
  • 291
  • 466
  • Good news with your fix, shame your using the dreaded select star notiation! –  Jun 22 '09 at 19:40
  • What's wrong with the select * notation? I'm trying to keep the SQL simple because these statements are embedded in a DSL. – BuddyJoe Jul 06 '09 at 14:23
  • The problem with using select * is that it might affect performance, depending in the number of records and columns that this table has. For example, if you only need 3 columns out of a table with 8 columns it is better to call select column1, column2, column3 instead of select * as it will return a lot of data that you don't need. – Ricardo Sanchez Nov 20 '09 at 15:58
  • But I need all the columns! so I'm still baffled by Chalkey's remark. – BuddyJoe Nov 20 '09 at 19:16

2 Answers2

1

Use The Sentence:

SELECT * FROM ValueListings

Then After Fill the DataTable, add a Computed Column to it:

  Dim Dt as new DataTable
  Da.Fill(Dt)
  Dt.columns.add("CoreValue", GetType(Double), "PercentRating * 500")
x77
  • 737
  • 1
  • 4
  • 12
0

If you can avoid using the * it WILL save you trouble later. With * if you change the schema your code may break. If named fields, you're good.

Chris Woodruff
  • 1