By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

How to create a new column in a datatable from two existing column

P: n/a
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?

Sep 29 '07 #1
Share this Question
Share on Google+
13 Replies


P: n/a
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?
Sep 29 '07 #2

P: n/a
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?

Oct 1 '07 #3

P: n/a
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?
>
>
>
Oct 1 '07 #4

P: n/a
Hi Charlie,

The datatable is the one returned from this method:
http://msdn2.microsoft.com/en-us/lib...tasources.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?

Oct 1 '07 #5

P: n/a
"Peter" <Pe***@discussions.microsoft.comschrieb
Hi Charlie,

The datatable is the one returned from this method:
http://msdn2.microsoft.com/en-us/lib...tasources.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

Oct 1 '07 #6

P: n/a
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.

"Peter" wrote:
Hi Charlie,

The datatable is the one returned from this method:
http://msdn2.microsoft.com/en-us/lib...tasources.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?
>
>
>
Oct 1 '07 #7

P: n/a
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.

"Armin Zingler" wrote:
"Peter" <Pe***@discussions.microsoft.comschrieb
Hi Charlie,

The datatable is the one returned from this method:
http://msdn2.microsoft.com/en-us/lib...tasources.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

Oct 1 '07 #8

P: n/a
"Peter" <Pe***@discussions.microsoft.comschrieb
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
Oct 2 '07 #9

P: n/a
"Peter" <Pe***@discussions.microsoft.comschrieb
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" <Pe***@discussions.microsoft.comschrieb
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
Oct 2 '07 #10

P: n/a
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().

"Armin Zingler" wrote:
"Peter" <Pe***@discussions.microsoft.comschrieb
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
Oct 2 '07 #11

P: n/a
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.

"Armin Zingler" wrote:
"Peter" <Pe***@discussions.microsoft.comschrieb
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" <Pe***@discussions.microsoft.comschrieb
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
Oct 2 '07 #12

P: n/a
"Peter" <Pe***@discussions.microsoft.comschrieb
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.
"Armin Zingler" wrote:
"Peter" <Pe***@discussions.microsoft.comschrieb
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" <Pe***@discussions.microsoft.comschrieb
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
Oct 2 '07 #13

P: n/a
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

Oct 2 '07 #14

This discussion thread is closed

Replies have been disabled for this discussion.