Creating a Visual Basic 2008 dataset from an SQL Server 2008 data table that uses "Computed Column Specification" with formulas.
I am using Column properties in SQL Server 2008. I was all ready assisted with this in SQL and it works fine. Now I need to get it to work in a Visual Basic 2008 Data-Grid. I don’t necessarily need general help with “Add new data source” => Database => Connection String => Choose Table => Finish. I don’t claim to be a pro, but I have created many Datasets and they all work fine in Visual Basic 2008 Standard edition. This Dataset “PayrollDataSet.xsd” looks fine after debug, and has no errors until you try to add a record. There are two records in it that were created in SQL, and all fields look good. My problem is that I can not create any new records in the Visual Basic program, although I can in SQL.
The Culprit: 3 columns that have formulas. 1) is simply adding two columns. 2) and 3) are what I call identities or mirrors of another column. Example: formula column 2) The company’s Social Security contribution is equal to the employee’s Social Security contribution. SS_Co = SS This all works fine in SQL, but not in a VB 2008 Data Grid.
1) Column name: Total_PR_Liability - The formula => ([GrossPay]+[Total_Co_Taxe
2) Column name: SS_Co - The formula => ([SS])
3) Column name: Medicare_Co - The formula => ([Medicare])
The update fails and the error message is:
The column”SS_Co” can not be modified because it is either a computed column or is the result of a UNION operator.
There is an identical error message line like this for each of the three formulas columns.
The Culprit: 3 columns that have formulas. 1) is simply adding two columns. 2) and 3) are what I call identities or mirrors of another column. Example: formula column 2) The company’s Social Security contribution is equal to the employee’s Social Security contribution. SS_Co = SS This all works fine in SQL, but not in a VB 2008 Data Grid.
1) Column name: Total_PR_Liability - The formula => ([GrossPay]+[Total_Co_Taxe
2) Column name: SS_Co - The formula => ([SS])
3) Column name: Medicare_Co - The formula => ([Medicare])
The update fails and the error message is:
The column”SS_Co” can not be modified because it is either a computed column or is the result of a UNION operator.
There is an identical error message line like this for each of the three formulas columns.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The Update, Insert, and Delete commands which are automatically generated by the DataAdapter are treating these computer columns as normal columns and trying to insert values into these columns. You need to manually write your Insert, Update commands for the dataadapter.
Above mentioned is a valid solution but this is another alternative
http://www.go4answers.com/Example/different-ways-insert-update-delete-30116.aspx
Above mentioned is a valid solution but this is another alternative
http://www.go4answers.com/Example/different-ways-insert-update-delete-30116.aspx
ASKER
Thank you both MikeTool, and CodeCruiser. I am looking at both options.
Code Cruiser option looks a bit beyond my ability to transpose. It also looks like it might be in C#. I am using Visual Basic.
MikeTool I have removed the formulas from the computed columns. Are you saying that I can now write functional code (in the code view) to apply to these columns? Or Do I work in the Table adapter with a query?
Code Cruiser option looks a bit beyond my ability to transpose. It also looks like it might be in C#. I am using Visual Basic.
MikeTool I have removed the formulas from the computed columns. Are you saying that I can now write functional code (in the code view) to apply to these columns? Or Do I work in the Table adapter with a query?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK Code cruiser,
I am starting fresh. New data source, same data table without computed columns in a datagrid format in a Visual Basic windows form. Do I go to solution explorer to the dataset and configure the table adapter? Or do I work in SQL Server? I have a feelling, if this works it will help a lot of people who read this.
I am starting fresh. New data source, same data table without computed columns in a datagrid format in a Visual Basic windows form. Do I go to solution explorer to the dataset and configure the table adapter? Or do I work in SQL Server? I have a feelling, if this works it will help a lot of people who read this.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for quality TechSupport.
ASKER