Question Problem Updating Database - Help Needed

[zendog1960]

First off let me state that I am learning the database areas of Winforms and how they all relate to each other so my knowledge is somewhat limited.

Here are the current conditions:

I created a database within the project which includes the following fields:
Database Name: Cigarettes.mdf
ItemID - Primary Key
SamCode - ordering code nothing special
FullName - Products Full Name
UPCnumber - UPC on product
OnHand - product actually in store
Reorderlevel - number set where is onhand is lower, it is time to order
OrderQty - Computed Column

The OrderQty has the following computation:

(case when ([ReorderLevel]-[OnHand])<(0) then (0) else [ReorderLevel]-[OnHand] end)

I also have a dataset named CigarettesDataSet with all fields included

In my form I have the following controls:
CigarettesDataGridView
CigarettesBindingNavigator
ComboBox1 - Populated by DataBound CigaretteSourceBinding1
Button1 - Sets Filter of grid to = current combobox1.text value
Button2 - Resets grid to show all records

The form works great for filtering and looking at all the records.

Here is the problem. When I update existing records or add new records then hit the save button on the CigarettesBindingNavigator the application throws this exception:

The column "OrderQty" cannot be modified because it is either a computed column or is the result of a UNION operator.

I know this is because the OrderQty is a computed column. can someone please help with a way to update the database and have the database compute the OrderQty as intended? Is this possible?

The code for the current update is:

    Private Sub CigarettesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CigarettesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.CigarettesBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.CigarettesDataSet)

    End Sub

Any help would be greatly appreciated
Thanks

 

[jmcilhinney]

Was your DataSet generated by the Data Source wizard? I'm surprised that it didn't handle that automatically if it was. Can you go into the DataSet designer and post a couple of things for us? First, the value of the Expression property of your OrderQty column. Next, the CommandText of the UpdateCommand of your TableAdapter.

 

[zendog1960]

Thanks for the reply

The OrderQty was set to int.

I was just using the pre-coded Navigation bar item.

    Private Sub CigarettesBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CigarettesBindingNavigatorSaveItem.Click
        Me.Validate()
        Me.CigarettesBindingSource.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.CigarettesDataSet)
    End Sub

Hopefully this is what you asked for....

I have temporarily made a work around by removing the computed Column expression from the database and handled it in a report but as stated that is just a temporary fix hopefully.

One thing I will be asking once this has been resolved will be how can I keep track of previous orders? More specific; each week a sheet will be printed out with all the cigarettes that fall below the reorder level will be faxed to our supplier. I would like to make it so we can track and build reports of those items that sold or didn't sell to better understand what we need to carry and what we do not.

but that is for another time I only mention it because it may have some bearing on the current setup.

 

[cjard]

If OrderQty is computed in the query, you should mark the column as readonly in the datatable
If an Expression on the datatable itself is doing the calc, the column should already be marked readonly

Either way; if you don't edit the orderqty column you shouldnt see this message


You can track cigarette sales at the time they are sold (by writing the sale contents to table) or at time of order (by writing the order to table), surely?

Remember that datatables do not have to be identical in structure to the database table they are assocaited with
Nothing stops you having a db table:

orders
orderid, product, qty
1, cigs, 20
1, lemons, 2

and a query that calculates what to order from the stock table..

thus you'll have a tableadapter for orders whose SELECT pulls stuff out of the stock table
(you should first make the TA using SELECT * FROM ORDERS, then change the query to SELECT someid, whateverproduct, qty FROM stock WHERE...
When VS asks you if you want to update the other queies because you have changed the main select, say NO
You now have a table that is filled from stock, and UPDATE/INSERT still inserts to ORDERS)