ansaurus

Question

How to make addition of two columns data for another column where those two columns are type of string ?

Answer 1

+1 A:

Use this:

dt.Columns.Add("fullname", typeof(string));
dt.Columns["fullname"].Expression = "lastname + ', ' + firstname";

For adding of value(Total,Amount and Surcharge are your existing columns, and are type string, you need to put CONVERT function in your column names so they will be casted to decimal(System.Decimal), if int use System.Int32 or System.Int64, etc):

dt.Columns["Total"].Expression = 
  "CONVERT(Amount,System.Decimal) + CONVERT(Surcharge,System.Decimal)";

[EDIT]

Just do it in your backend:

    select *, CONVERT(SERTAX(Rs), DECIMAL) 
        + CONVERT(STT(Rs), DECIMAL) AS brokerage 
    from tbl

If you have control in your database, modify the SERTAX and STT function so it can return decimal/currency/int, not string.

Then in your front-end, do this:

dt.ColumnChanging += (ds, de) =>
{
    if (de.Column.ColumnName == "Rs")
    {
        decimal serTaxResult = (decimal)new SqlCommand("select SERTAX(" + de.ProposedValue + ")").ExecuteScalar();
        decimal sttResult = (decimal)new SqlCommand("select STT(" + de.ProposedValue + ")").ExecuteScalar();

        // if your SERTAX AND STT really return string, use this:                        
        // decimal serTaxResult = decimal.Parse( (string) new SqlCommand("select SERTAX(" + de.ProposedValue + ")").ExecuteScalar() );
        // decimal sttResult = decimal.Parse( (string) new SqlCommand("select STT(" + de.ProposedValue + ")").ExecuteScalar() );


        de.Row["brokerage"] = serTaxResult + sttResult;
    }
};

[EDIT]

If all your columns are string type, you have to enclosed each of them with their own CONVERTs.

.Expression = string.Format("CONVERT({0},System.String)", 

      "CONVERT(" + serviceTaxClmnInCNote  + ", System.Int32)"
      + " + " 
      + "CONVERT(" + STTClmnInCNote + ", System.Int32)"

      );

Just change the System.Int32 to System.Decimal if the serviceTax and STT are money type.

Michael Buen 2010-05-07 07:26:02

@Michael Buen,Will this make addition or concatenate two strings ?

Harikrishna 2010-05-07 07:28:25

if any of the field is string, it will result to concatenation. but if they are both numbers, it will result to addition

Michael Buen 2010-05-07 07:30:45

@Micheal,These two columns are type of string not int and I can not change the datatype of them, because it gives error like can not change the datatype once data is inserted.

Harikrishna 2010-05-07 07:32:10

here are the list of possible expressions http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(VS.71).aspx

Michael Buen 2010-05-07 07:33:38

@Micheal, I have seen that, but can not change the datatype of the columns.

Harikrishna 2010-05-07 07:34:42

@Harikrishna: just omit the dt.Columns.Add, assign an expression to dt.Columns["yourExistingColumnHere"]'s Expression

Michael Buen 2010-05-07 07:35:10

@Harikrishna: i'll edit my answer

Michael Buen 2010-05-07 07:38:09

@Micheal,If instead of writing like "amount+Surcharge" I write `dt.columns["Amount"].Caption+dt.columns["SurCharge"].Caption;` it gives error.

Harikrishna 2010-05-07 07:39:55

@Micheal,Error is missing operator.

Harikrishna 2010-05-07 07:41:25

@Harikrishna: you cannot assign the columns caption, doing that will resolve things in compile time. just assign it like an ordinary expression

Michael Buen 2010-05-07 07:43:12

@Micheal, I have to do that because I can access the column name dynamically.

Harikrishna 2010-05-07 07:44:56

@Harikrishna: but caption is not integer, and caption is for data grid header purposes only. if you want to achieve adding of values on your existing column, just assign it to expression

Michael Buen 2010-05-07 07:48:08

@Micheal,It gives error like missing operator when I use expression only.

Harikrishna 2010-05-07 07:48:22

@Harikrishna: kindly put in your question what have you done. in verbatim, so i can see your code's intent

Michael Buen 2010-05-07 07:49:37

@Micheal,Ok I will edit my question. and sorry for that.

Harikrishna 2010-05-07 07:50:35

@Harikrishna: by the way, what happen when you do: `dt.Columns["Total"].Expression = "CONVERT(Amount,System.Decimal) + CONVERT(Surcharge,System.Decimal)";` ?

Michael Buen 2010-05-07 07:53:17

@Micheal,It gives error like `The expression contains undefined function call SERTAX().`

Harikrishna 2010-05-07 07:56:53

@Harikrishna: see my comment in your question

Michael Buen 2010-05-07 08:00:27

@Micheal,My data in the datatable is coming from the file by parsing it through html agility pack.

Harikrishna 2010-05-07 08:49:50

@Harikrishna: ok, now I know. where does the SERTAX and STT function come from? if from your C#, just change the `de.Row["brokerage"].Value` assignment. see my edited answer. ColumnChanging also triggers even while you are adding rows to your DataTable

Michael Buen 2010-05-07 09:12:22

@Micheal,Do you mean that when I inserting records in the datatable ?

Harikrishna 2010-05-07 09:16:39

@Harikrishna: Is the DataTable populated by you? or is it merely returned from HTML Agility Pack?

Michael Buen 2010-05-07 09:47:25

@Micheal,Ok I have put a condition that if these columns are coming in the table then it will type of int.Now my column names are assigned to string variable and I am doing like `TableExtractedFromFile.Columns[brokerageClmnInCNote].Expression ="'"+serviceTaxClmnInCNote+"'+'"+STTClmnInCNote+"'";` But problem is because of string variable it gives error that can not convert in int32.

Harikrishna 2010-05-07 09:54:22

if your brokerageClmnInCNote is string type. try: `.Expression = string.Format("CONVERT({0},System.String)", serviceTaxClmnInCNote + " + " + STTClmnInCNote)`

Michael Buen 2010-05-07 10:02:36

if all your columns are string type, i'll just put the answer in my edit. check my edit later

Michael Buen 2010-05-07 10:05:43

@Micheal,Name of Column **ServiceTax** is assigned to **serviceTaxClmnInCNote** variable and that column is type of int.And Name of column **STT** is assigned to **STTClmnInCNote** and type of int.

Harikrishna 2010-05-07 10:18:38

@Micheal,I have tried as you described in your edited answer but it gives error like : **The expression contains undefined function call SERTAX().**

Harikrishna 2010-05-07 10:21:10

where is SERTAX defined?

Michael Buen 2010-05-07 10:23:01

serviceTaxClmnInCNote is a string variable within which SERTAX is stored which is the column name.

Harikrishna 2010-05-07 10:25:00

if SERTAX is a C# function, forgot the Expression, it cannot process functions that are not built-in in Expression's own language. use the ColumnChanging event. for proof of concept code, try to work by piecemeal approach. first, make the ColumnChanging event assign values to computed column like this: `de.Columns["result"].Value = 7 * 6`. then replace it one-by-one with your expression/functions

Michael Buen 2010-05-07 10:27:48

@Micheal,Column Name is returned by html agility pack and I stored that column name in the string variable serviceTaxClmnInCNote.

Harikrishna 2010-05-07 10:28:36

@Micheal,Now it works but the result is string concatenate not addition.

Harikrishna 2010-05-07 10:48:30

@Micheal,I know the reason why it does not work,because column name contains special character.

Harikrishna 2010-05-07 11:15:38

Answer 2

A:

Extract the data from te two columns and call the string concat function. Refer the following link

http://www.1keydata.com/sql/sql-concatenate.html

Pavan Navali 2010-05-07 07:28:20

@Pavan,I have need of making addition of two columns data not concat.

Harikrishna 2010-05-07 07:30:12

ansaurus

tags:

views:

answers:

How to make addition of two columns data for another column where those two columns are type of string ?

related questions