 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / Languages / VB.NET / October 2007How to create a new column in a datatable from two existing column
I want to create a new column in a datatable from two existing columns. I have no problem to create the new column using the datatable.columns.add method. The problem is the value of the new column may become system.dbnull since one of the two existing columns may have system.dbnull. How can I fix it so the new column will get the value of the other column even the other column is system.dbnull? Assuming you are using a SELECT statement to get your data, you can combine the fields in the statement, and SQL will treat any null cells from a string type column as empty strings... SELECT FirstName & " " & LastName AS CustomerName FROM Customers... In this example, if a FirstName is null and there is only a last name for a given record, the CustomerName would be shown as only the last name. You would map your grid column to CustomerName. Also, this example assumes both columns are strings. You can also combine numeric columns in SQL, but use + rather than &. > I want to create a new column in a datatable from two existing columns. I > have no problem to create the new column using the datatable.columns.add > method. The problem is the value of the new column may become system.dbnull > since one of the two existing columns may have system.dbnull. How can I fix > it so the new column will get the value of the other column even the other > column is system.dbnull? Hi Charlie, The datatable is returned from a .NET method so I have no control on the SELECT statement. > Assuming you are using a SELECT statement to get your data, you can combine > the fields in the statement, and SQL will treat any null cells from a string [quoted text clipped - 14 lines] > > it so the new column will get the value of the other column even the other > > column is system.dbnull? I don't know which .net method you are referring to, but you should still be able to control the SELECT statement. The DataAdapter would be the most common .net object to use to get data from an Access or SQL database. The SELECT statement is a property of that object and can be passed as a parameter, or set as a property. In any case, as long as you are using a database that runs sql statements, you should be able to re-code in a way that lets you use a SELECT statement. > Hi Charlie, > [quoted text clipped - 19 lines] > > > it so the new column will get the value of the other column even the other > > > column is system.dbnull? Hi Charlie, The datatable is the one returned from this method: http://msdn2.microsoft.com/en-us/library/system.data.common.dbdatasourceenumerat or.getdatasources.aspx > I don't know which .net method you are referring to, but you should still be > able to control the SELECT statement. The DataAdapter would be the most [quoted text clipped - 28 lines] > > > > it so the new column will get the value of the other column even the other > > > > column is system.dbnull? > Hi Charlie, > > The datatable is the one returned from this method: > http://msdn2.microsoft.com/en-us/library/system.data.common.dbdatasourceenumerat or.getdatasources.aspx I do not completely understand the problem. I see, you are getting this datatable returned. Now, you want to add another column. You say, the problem is that there might be NULL values. Why is this a problem? What does the value of the new column have to be? Armin Hi Armin, I add the new column based on existing columns. If one of the based columns contains null (dbnull), the new column will contain null regardless the value in other based columns. > > Hi Charlie, > > [quoted text clipped - 7 lines] > > Armin > Hi Armin, > > I add the new column based on existing columns. If one of the based > columns contains null (dbnull), the new column will contain null > regardless the value in other based columns. What is a "column based on existing columns"? Armin Hi Armin, What I mean is the value of the new column will have the combined value of existing columns I specified using datatable.columns.add(). > > Hi Armin, > > [quoted text clipped - 5 lines] > > Armin > Hi Armin, > > What I mean is the value of the new column will have the combined > value of existing columns I specified using datatable.columns.add(). What's a "combined value"? Sorry for asking if that's too obvious. >> > Hi Armin, >> > [quoted text clipped - 5 lines] >> >> Armin The value of the new column will simply be the value of columnX + the value of columnY. This is simply done by issuing datatable.columns.add(). However, the value of the new column will be null if either one of the columns is null. > > Hi Armin, > > [quoted text clipped - 14 lines] > >> > >> Armin > The value of the new column will simply be the value of columnX + > the value of columnY. This is simply done by issuing > datatable.columns.add(). However, the value of the new column will > be null if either one of the columns is null. Ok, but how do you calculate the values in the column? The sum must be put into the rows at any time. > > > Hi Armin, > > > [quoted text clipped - 14 lines] > > > > > > > > Armin Peter, What Armin probably asks is. Show that piece of code that you are using. It seems that people is afraid to show that here, why we don't understand, because really you are not showing secrets or your own inventions with that. We are not forever asking direct questions here in this newsgroup, however I know that Armin often asks, "Show us a piece of your code". :-) Cor You could write a function that takes a single parameter--Datatable (the one returned by GetDataSources). In the function, create a new datatable (that will be the return value) and populate it in nested loops using the values from the GetDataSources datatable. In the second loop you would combine the columns you want to combine into a single column. That's where you could test for isdbnull. > Hi Charlie, > [quoted text clipped - 33 lines] > > > > > it so the new column will get the value of the other column even the other > > > > > column is system.dbnull? Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.