Solved

Creating a Visual Basic 2008 dataset from an SQL Server 2008 data table that uses "Computed Column Specification" with formulas.

Posted on 2010-11-19
8
937 Views
Last Modified: 2013-12-08
         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_Taxes])

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.
0
Comment
Question by:jampost
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 

Author Comment

by:jampost
ID: 34176076
One note I might add is that is that no computed column uses any other computed column. If it did, it wouldn't work in SQL Server 2008.  
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 167 total points
ID: 34179558
I believe that the automatically generated insert/update statements include the computed columns - which is why the errors occur. This looks like a similar issue/fix:
http://stackoverflow.com/questions/951539/sqlcommandbuilder-and-sql-server-computed-columns-error
Hope this helps.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34179626
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
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:jampost
ID: 34179762
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?
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 333 total points
ID: 34179777
The code sample was in C# but you need to change SQL queries not VB code.

You can add computed columns to the datatable but you would struggle with as well since you are using typed datasets.
0
 

Author Comment

by:jampost
ID: 34179824
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.
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 333 total points
ID: 34179837
You can change the queries in codebehind using

Adaptername. Insertcommand. Commandtext = "sql query here"

0
 

Author Closing Comment

by:jampost
ID: 34179847
Thank you for quality TechSupport.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Data organization issue 7 37
MS SQL Conditional WHERE clause 3 38
Google Chrome GPO Not Applying 5 32
Can a Trigger trigger a Trigger? 4 22
SSL stands for “Secure Sockets Layer” and an SSL certificate is a critical component to keeping your website safe, secured, and compliant. Any ecommerce website must have an SSL certificate to ensure the safe handling of sensitive information like…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate how nuggets on the Web are formatted by using Chrome Developer Tools. These tools would not only view the site's CSS but it can also modify it and save the CSS to use on your own site.
Shows how to create a shortcut to site-search Experts Exchange using Google in the Chrome browser. This eliminates the need to type out site:experts-exchange.com whenever you want to search the site. Launch the Search Engine Menu: In chrome, via you…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question