| Groups | Blog | Home |
vb.net : How 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 &. [quoted text, click to view] "Peter" wrote:
> 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. [quoted text, click to view] "Charlie" wrote:
> 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 &. > > > "Peter" wrote: > > > 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? > > > >
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. [quoted text, click to view] "Peter" wrote:
> Hi Charlie, > > The datatable is returned from a .NET method so I have no control on the > SELECT statement. > > "Charlie" wrote: > > > 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 &. > > > > > > "Peter" wrote: > > > > > 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 the one returned from this method: http://msdn2.microsoft.com/en-us/library/system.data.common.dbdatasourceenumerator.getdatasources.aspx [quoted text, click to view] "Charlie" wrote:
> 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. > > "Peter" wrote: > > > Hi Charlie, > > > > The datatable is returned from a .NET method so I have no control on the > > SELECT statement. > > > > "Charlie" wrote: > > > > > 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 &. > > > > > > > > > "Peter" wrote: > > > > > > > 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? > > > > > > > >
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. [quoted text, click to view] "Peter" wrote:
> Hi Charlie, > > The datatable is the one returned from this method: > http://msdn2.microsoft.com/en-us/library/system.data.common.dbdatasourceenumerator.getdatasources.aspx > > > "Charlie" wrote: > > > 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. > > > > "Peter" wrote: > > > > > Hi Charlie, > > > > > > The datatable is returned from a .NET method so I have no control on the > > > SELECT statement. > > > > > > "Charlie" wrote: > > > > > > > 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 &. > > > > > > > > > > > > "Peter" wrote: > > > > > > > > > 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 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. [quoted text, click to view] "Armin Zingler" wrote:
> "Peter" <Peter@discussions.microsoft.com> schrieb > > Hi Charlie, > > > > The datatable is the one returned from this method: > > http://msdn2.microsoft.com/en-us/library/system.data.common.dbdatasourceenumerator.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,
What I mean is the value of the new column will have the combined value of existing columns I specified using datatable.columns.add(). [quoted text, click to view] "Armin Zingler" wrote:
> "Peter" <Peter@discussions.microsoft.com> schrieb > > 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
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. [quoted text, click to view] "Armin Zingler" wrote:
> "Peter" <Peter@discussions.microsoft.com> schrieb > > 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. > > > > "Armin Zingler" wrote: > > > >> "Peter" <Peter@discussions.microsoft.com> schrieb > >> > 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 > >>
"Peter" <Peter@discussions.microsoft.com> schrieb
[quoted text, click to view] > Hi Charlie, > > The datatable is the one returned from this method: > http://msdn2.microsoft.com/en-us/library/system.data.common.dbdatasourceenumerator.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
"Peter" <Peter@discussions.microsoft.com> schrieb
[quoted text, click to view] > 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. [quoted text, click to view] > "Armin Zingler" wrote:
> > > "Peter" <Peter@discussions.microsoft.com> schrieb > > > 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. > > > > > > > "Armin Zingler" wrote: > > > > > > > "Peter" <Peter@discussions.microsoft.com> schrieb > > > > > 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 > > > > > >
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
"Peter" <Peter@discussions.microsoft.com> schrieb
[quoted text, click to view] > 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"?
"Peter" <Peter@discussions.microsoft.com> schrieb
[quoted text, click to view] > 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. [quoted text, click to view] > "Armin Zingler" wrote:
> >> "Peter" <Peter@discussions.microsoft.com> schrieb >> > 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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
| home · blog · groups | Questions? Comments? Contact the dn |