vb.net expression


In the Visual Studio dataset designer, in a specific table, I added an expression to my SQL statement, just to get only part of a string
SELECT        soumissionNumber, sDate, sClient, sProjet, sMandat, sClientId, mid(soumissionNumber, 9, 11) AS Expr1
FROM            soumissions

Open in new window

Once I did this I can't update,save or delete records in the table anymore, I get the following error: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

My database is an access database

Thanks for the help!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Éric MoreauSenior .Net ConsultantCommented:
I have never used the designer but your error is that the column from the expression cannot be updated. You will need to create another command to update your table.
Jacques Bourgeois (James Burger)PresidentCommented:
Another solution is to add a calculated field (also called an expression column) in the DataTable that is created by your DataSet, thus having the DataTable do the job instead of the SELECT. Because the tool that generates the UPDATE does it from the SELECT, it won't consider that column and everything should go as if it was not there.

An other possible alternative, if you do not display the data automatically in a grid through DataBinding, is to perform the operation and send it to the display in your code every time the expression needs to be shown.
FCapoAuthor Commented:
Hi Jacques,

How would I do this with a datatable, I'm just looking to have a column with this expression : mid(soumissionNumber, 9, 11) AS Expr1
Jacques Bourgeois (James Burger)PresidentCommented:
Use code similar to the following:
Dim yourColumn As DataColumn = New DataColumn
With yourColumn
	.DataType = System.Type.GetType("System.String")
	.ColumnName = "yourName" 'If you need to address that column in your code later
	.Expression = "SUBSTRING(soumissionNumber, 9, 11)"
End With

Open in new window

I do not have time to test it, but it should be close if not right.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.