Can I use MS Access "Calculated" column in visual studio?!

[Sadiq6210]

Hi,

I have a database in MS Access with some calculated columns, for example:
column1: a number (normal)
column2: a number (normal)
column3: column1 * column2 (calculated)

Then, I linked the database with visual studio 2013 but I am not able to import new data using VB application, when I try to fill new row in datagridview and click (save), I got this error: "field is not updateable"
I tried to remove column3 (calculated column) and the problem solved.

My question: Isn't possible to use Access calculated fields in VB?

[jmcilhinney]

You've left out a lot of important information. Firstly, did you use the wizard to create a Data Source or did you write all your ADO.NET code yourself? If it's the former then open your DataSet in the designer, select that column and then open the Properties window. You need to make sure that the Expression property of that column is set to basically the same as you used in Access in order to calculate the appropriate values. You'll then also need to edit the commands of the associated table adapter to make sure that you're not trying to insert nay values into either the DataColumn in your app or the database column.

[Sadiq6210]

You've left out a lot of important information. Firstly, did you use the wizard to create a Data Source or did you write all your ADO.NET code yourself? If it's the former then open your DataSet in the designer, select that column and then open the Properties window. You need to make sure that the Expression property of that column is set to basically the same as you used in Access in order to calculate the appropriate values. You'll then also need to edit the commands of the associated table adapter to make sure that you're not trying to insert nay values into either the DataColumn in your app or the database column.

I didn't write the code for database in VS, I added the database then I added the (datagridview) by drag the table into the form and the code for datagridview was automatically generated.

I uploaded a sample program in the attachments. Could you have a look please?
Just run the program and try to add new data in field1 & field2 only (field3 is defined as calculated from access "Filed1*Filed2"). Click save and you will get the error.

I think I missed many things but I didn't find and tutorial related to this.

[jmcilhinney]

Have you done what I already suggested?

[Sadiq6210]

Have you done what I already suggested?

Yes, I put this value in Field3 expression ([Field1]*[Field2]) exactly same in access
I got this error

You'll then also need to edit the commands of the associated table adapter to make sure that you're not trying to insert nay values into either the DataColumn in your app or the database column.

Could you explain more about this step?

[jmcilhinney]

Have you opened the DataSet in the designer and looked at the table adapter? The four commands are listed in the Properties window with the other properties. You don't want to retrieve data from that column in the database because the values will be calculated automatically by the DataTable and you don't want to save data to that column in the database because it will calculate the values itself.

[Sadiq6210]

Have you opened the DataSet in the designer and looked at the table adapter? The four commands are listed in the Properties window with the other properties. You don't want to retrieve data from that column in the database because the values will be calculated automatically by the DataTable and you don't want to save data to that column in the database because it will calculate the values itself.

Thanks dear

Let me summarize what I did.

1. I created new project, I added the database using the wizard, then I added the datagridview.

2. From DataSet Designer, I changed the expression for the calculated field to the same expression in access which is [Field1]*[Field2]



3. From DataSet Designer, I remove (field3) from the four commands



But still it is not working
Did I miss anything?

[jmcilhinney]

I'll have to create a project from scratch and test it for myself, which I'm not able to do right now. If you still don't have a solution by the time I'm able, I'll see what I can come up with.

[Sadiq6210]

I'll have to create a project from scratch and test it for myself, which I'm not able to do right now. If you still don't have a solution by the time I'm able, I'll see what I can come up with.

I will appreciate that.
I tried manytimes but still I am not able to do it

[jmcilhinney]

I just tested and basically you have to create your own column independent of the database column. When you create the Data Source, you're given the choice of which database objects to include. At that point, you can expand that table and select the columns other than that one to exclude it from the DataSet. If you've already created the DataSet, you can edit the SelectCommand of the table adapter and remove that column from the SQL code. When prompted, tell it to refresh the action commands accordingly. That will remove that column from the DataTable too and I think that that is where you're already at.

Now, you can create a local DataColumn that behaves in the same way as the database column but isn't connected directly to it. Just right-click the DataTable and select Add > Column. You can then configure that column appropriately. You can get most of the property values you need by examining the existing columns. You'll want to set the Name and Caption properties but leave the Source property blank. You'll need to set the Expression property to basically the same value as in the database and also set the DataType based on the type of data that expression produces. You can then use that DataTable exactly as you would any other. That column will be populated only be evaluation of the Expression rather than from the database and nothing will be saved from it to the database.

[Sadiq6210]

I just tested and basically you have to create your own column independent of the database column. When you create the Data Source, you're given the choice of which database objects to include. At that point, you can expand that table and select the columns other than that one to exclude it from the DataSet. If you've already created the DataSet, you can edit the SelectCommand of the table adapter and remove that column from the SQL code. When prompted, tell it to refresh the action commands accordingly. That will remove that column from the DataTable too and I think that that is where you're already at.

Now, you can create a local DataColumn that behaves in the same way as the database column but isn't connected directly to it. Just right-click the DataTable and select Add > Column. You can then configure that column appropriately. You can get most of the property values you need by examining the existing columns. You'll want to set the Name and Caption properties but leave the Source property blank. You'll need to set the Expression property to basically the same value as in the database and also set the DataType based on the type of data that expression produces. You can then use that DataTable exactly as you would any other. That column will be populated only be evaluation of the Expression rather than from the database and nothing will be saved from it to the database.

Genius idea =)
This is working perfect
Thank you very much dear, this will help me a lot in my project

You must spread some Reputation around before giving it to jmcilhinney again.