|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Manipulating data on input w/ TableAdapter & Dataset.I know this is easier to do with a datareader and a listview, but can
it be done with a tableadapter and a dataset? I want to manipulate the data prior to it reaching the dataset, For instance, data is stored as Firstname and Lastname and sometimes there will be no firstname, so appending a comma in this instance looks stupid. So I need to check if firstname exists then return lastname & ", " & firstname or if not exist, just return lastname. I was trying to do this with an IIF statement in the SQL code but it wouldn't run saying it was an unrecognized command although it is described in the BOL documentation for SQL Server 2000. I also tried to do it with partial class of the dataset. First on RowChanging, e.row = "processed data", error = cannot change proposed value. Next on RowChanged, also met with error. Any ideas, or is this not meant to be? you need to take a look at the documentation for sql server. It has a lot of
sql function that can be used in the sql statement. One in particular is IsNull, which can allow you to do these concatenations safely. You can also have calculated columns in the database by setting the Expression property of a column What is valid in the expression is fully documented, and you should be able to construct an expression to create the correct string. Show quote "Mike Edgewood" <itm***@gmail.com> wrote in message news:1138371673.650991.152690@g47g2000cwa.googlegroups.com... >I know this is easier to do with a datareader and a listview, but can > it be done with a tableadapter and a dataset? > > I want to manipulate the data prior to it reaching the dataset, For > instance, data is stored as Firstname and Lastname and sometimes there > will be no firstname, so appending a comma in this instance looks > stupid. So I need to check if firstname exists then return lastname & > ", " & firstname or if not exist, just return lastname. > > I was trying to do this with an IIF statement in the SQL code but it > wouldn't run saying it was an unrecognized command although it is > described in the BOL documentation for SQL Server 2000. > > I also tried to do it with partial class of the dataset. First on > RowChanging, e.row = "processed data", error = cannot change proposed > value. Next on RowChanged, also met with error. > > Any ideas, or is this not meant to be? > I'm afraid I was unsuccessful with my previous attempts on a calculated
column. That is where I started with this mess, actually. Perhaps you can help shed some light where I am failing. How could you write an expression for the aforementioned problem? In the mean time, I'll try to find some better documentation on the calculated column. The documentation can be found very easily. I just searched 'expression
property datacolumn' on google and the right page was at the top of the result set: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp This completely documents what kind of functions are available in the expression. Show quote "Mike Edgewood" <itm***@gmail.com> wrote in message news:1138391408.664269.44850@g49g2000cwa.googlegroups.com... > I'm afraid I was unsuccessful with my previous attempts on a calculated > column. That is where I started with this mess, actually. > > Perhaps you can help shed some light where I am failing. How could you > write an expression for the aforementioned problem? In the mean time, > I'll try to find some better documentation on the calculated column. > I believe the resolution I'm needing goes beyond what an expression
column can handle. So I was wondering if there was another way to manipulate the data prior to it reaching a datatable. Partial classes, perhaps? Where I am working it is common (and allowed) to enter just a state and a zip code or perhaps just a zipcode. So when I fill a readonly table I want to know whether or not to append a comma to my citystate field. Data field possibilities city: "Parsippany" state "NJ" zip "07054" Desired output "Parsippany, NJ" city: "" State: "NJ" zip: "07054" Desired output - "NJ" (not ", NJ") city: state: zip: 07054 desired output - "" (not ",") Is it doable? Mike,
I had some misreadings in this message thread, I thought that you wanted to do it in your transaction SQL string before the datatable was filled. The solution as I now see Marina is given as well, but I misreaded it, is the same as I gave and is very simple for this problem.. See this sample. http://www.vb-tips.com/default.aspx?ID=e8fee3e3-3ca7-4bb0-81dc-f0b560f43e7c I hope this helps, Cor Mike,
If the solution from Marina is for whatever reason not the one for you, than you can use extra (expression) columns in your dataset/datatable. It is not what you ask, however full AdoNet, while your question is now more related to the database that you are using. The used SQL dialect should have a feature for what you ask. I hope this gives an idea. Cor  |
|||||||||||||||||||||||
Other interesting topics