Click here to Skip to main content
14,266,139 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello Guys,
happy holiday and best wishes in 2016, below is my C# code where i want to return the aggregated sum of all the fields in three tables (tbl_HTC1,tbl_HTC2 and tbl_HTC3)

protected int SumSQL()
{
   string SQL = "Select Sum(a) from (";
       SQL +="Select CONVERT(INT,TOTALG1)+CONVERT(INT,SUBG1MA)+CONVERT(INT,G1MA1)+CONVERT(INT,G1MA1_4)+CONVERT(INT,G1MA5_9)+CONVERT(INT,G1MA10_14)+CONVERT(INT,G1MA15_19)+CONVERT(INT,G1MA20_24)+CONVERT(INT,G1MA25_49)";
       SQL +="+CONVERT(INT,G1MA50)+CONVERT(INT,SUBG1FE)+CONVERT(INT,G1FE1)+CONVERT(INT,G1FE1_4)+CONVERT(INT,G1FE5_9)+CONVERT(INT,G1FE10_14)+CONVERT(INT,G1FE15_19)+CONVERT(INT,G1FE20_24)+CONVERT(INT,G1FE25_49)+CONVERT(INT,G1FE50)";
       SQL +="+CONVERT(INT,TOTALG2)+CONVERT(INT,SUBG2M)+CONVERT(INT,G2MA1)+CONVERT(INT,G2MA1_4)+CONVERT(INT,G2MA5_9)+CONVERT(INT,G2MA10_14)+CONVERT(INT,G2MA15_19)+CONVERT(INT,G2MA20_24)+CONVERT(INT,G2MA25_49)+CONVERT(INT,G2MA50)";
       SQL +="+CONVERT(INT,SUBG2F)+CONVERT(INT,G2FE1)+CONVERT(INT,G2FE1_4)+CONVERT(INT,G2FE5_9)+CONVERT(INT,G2FE10_14)+CONVERT(INT,G2FE15_19)+CONVERT(INT,G2FE20_24)+CONVERT(INT,G2FE25_49)+CONVERT(INT,G2FE50)";
       SQL +="+CONVERT(INT,TOTALG3)+CONVERT(INT,SUBG3M)+CONVERT(INT,G3MA1)+CONVERT(INT,G3MA1_4)+CONVERT(INT,G3MA5_9)+CONVERT(INT,G3MA10_14)+CONVERT(INT,G3MA15_19)+CONVERT(INT,G3MA20_24)+CONVERT(INT,G3MA25_49)+CONVERT(INT,G3MA50)";
       SQL +="+CONVERT(INT,SUBG3F)+CONVERT(INT,G3FE1)+CONVERT(INT,G3FE1_4)+CONVERT(INT,G3FE5_9)+CONVERT(INT,G3FE10_14)+CONVERT(INT,G3FE15_19)+CONVERT(INT,G3FE20_24)+CONVERT(INT,G3FE25_49)+CONVERT(INT,G3FE50)+CONVERT(INT,TOTALG4)+CONVERT(INT,SUBG4P)";
       SQL +="+CONVERT(INT,G4P1)+CONVERT(INT,G4P1_4)+CONVERT(INT,G4P5_9)+CONVERT(INT,G4P10_14)+CONVERT(INT,G4P15_19)+CONVERT(INT,G4P20_24)+CONVERT(INT,G4P25_49)+CONVERT(INT,G4P50)+CONVERT(INT,SUB4N)+CONVERT(INT,G4N1)+CONVERT(INT,G4N1_4)";
       SQL +="+CONVERT(INT,G4N5_9)+CONVERT(INT,G4N10_14)+CONVERT(INT,G4N15_19)+CONVERT(INT,G4N20_24)+CONVERT(INT,G4N25_49)+CONVERT(INT,G4N50)+CONVERT(INT,TOTALG5)+CONVERT(INT,SUBG5M)+CONVERT(INT,G5MA1)+CONVERT(INT,G5M1_4)+CONVERT(INT,G5MA5_9)";
       SQL +="+CONVERT(INT,G5MA10_14)+CONVERT(INT,G5MA15_19)+CONVERT(INT,G5MA20_24)+CONVERT(INT,G5MA25_49)+CONVERT(INT,G5MA50)+CONVERT(INT,SUBG5F)+CONVERT(INT,G5FE1)+CONVERT(INT,G5FE1_4)+CONVERT(INT,G5FE5_9)+CONVERT(INT,G5FE10_14)+CONVERT(INT,G5FE15_19)";
       SQL +="+CONVERT(INT,G5FE20_24)+CONVERT(INT,G5FE25_49)+CONVERT(INT,G5FE50)+CONVERT(INT,TOTALG6)+CONVERT(INT,SUBG6M)+CONVERT(INT,G6MA1)+CONVERT(INT,G6M1_4)+CONVERT(INT,G6MA5_9)+CONVERT(INT,G6MA10_14)+CONVERT(INT,G6MA15_19)+CONVERT(INT,G6MA20_24)";
       SQL +="+CONVERT(INT,G6MA25_49)+CONVERT(INT,G6MA50)+CONVERT(INT,SUBG6F)+CONVERT(INT,G6FE1)+CONVERT(INT,G6FE1_4)+CONVERT(INT,G6FE5_9)+CONVERT(INT,G6FE10_14)+CONVERT(INT,G6FE15_19)+CONVERT(INT,G6FE20_24)+CONVERT(INT,G6FE25_49)+CONVERT(INT,G6FE50)";
       SQL +="+CONVERT(INT,TOTALG7)+CONVERT(INT,SUBG7M)+CONVERT(INT,G7MA1)+CONVERT(INT,G7M1_4)+CONVERT(INT,G7MA5_9)+CONVERT(INT,G7MA10_14)+CONVERT(INT,G7MA15_19)+CONVERT(INT,G7MA20_24)+CONVERT(INT,G7MA25_49)+CONVERT(INT,G7MA50)+CONVERT(INT,SUBG7F)";
       SQL +="+CONVERT(INT,G7FE1)+CONVERT(INT,G7FE1_4)+CONVERT(INT,G7FE5_9)+CONVERT(INT,G7FE10_14)+CONVERT(INT,G7FE15_19)+CONVERT(INT,G7FE20_24)+CONVERT(INT,G7FE25_49)+CONVERT(INT,G7FE50)+CONVERT(INT,TOTALG8)+CONVERT(INT,SUBG8M)+CONVERT(INT,G8MA10_14)";
       SQL +="+CONVERT(INT,G8M15_19)+CONVERT(INT,G8MA20_24)+CONVERT(INT,G8MA25_49)+CONVERT(INT,G8MA50)+CONVERT(INT,SUBG8P)as a from tbl_htc1";
       SQL +="union all";
       SQL +="Select CONVERT(INT,TOTALG9)+CONVERT(INT,SUBG9M)+CONVERT(INT,G9MA1) +CONVERT(INT,G9MA1_4)+CONVERT(INT,G9MA5_9)+CONVERT(INT,G9MA10_14)+CONVERT(INT,G9MA15_19)+CONVERT(INT,G9MA20_24)+CONVERT(INT,G9MA25_49)+CONVERT(INT,G9MA50)+CONVERT(INT,SUBG9F)";
       SQL +="+CONVERT(INT,G9FE1)+CONVERT(INT,G9FE5_9)+CONVERT(INT,G9FE10_14)+CONVERT(INT,G9FE15_19)+CONVERT(INT,G9FE20_24)+CONVERT(INT,G9FE25_49)+CONVERT(INT,G9FE50)+CONVERT(INT,TOTALG10)+CONVERT(INT,SUBG10P)+CONVERT(INT,G10P1)+CONVERT(INT,G10P1_4)";
       SQL +="+CONVERT(INT,G10P5_9)+CONVERT(INT,G10P10_14)+CONVERT(INT,G10P15_19)+CONVERT(INT,G10P20_24)+CONVERT(INT,G10P25_49)+CONVERT(INT,G10P50)+CONVERT(INT,SUBG10N)+CONVERT(INT,G10N1)+CONVERT(INT,G10N1_4)+CONVERT(INT,G10N5_9)+CONVERT(INT,G10N10_14)";
       SQL +="+CONVERT(INT,G10N15_19)+CONVERT(INT,G10N20_24)+CONVERT(INT,G10N25_49)+CONVERT(INT,G10N50)+CONVERT(INT,TOTALG11)+CONVERT(INT,SUBG11M)+CONVERT(INT,G11MA1)+CONVERT(INT,G11MA1_4)+CONVERT(INT,G11MA10_14)+CONVERT(INT,G11MA15_19)+CONVERT(INT,G11MA20_24)";
       SQL +="+CONVERT(INT,G11MA25_49)+CONVERT(INT,G11MA50)+CONVERT(INT,SUBG11F)+CONVERT(INT,G11FE1)+CONVERT(INT,G11FE1_4)+CONVERT(INT,G11FE5_9)+CONVERT(INT,G11FE10_14)+CONVERT(INT,G11FE15_19)+CONVERT(INT,G11FE20_24)+CONVERT(INT,G11FE25_49)+CONVERT(INT,G11FE50)";
       SQL +="+CONVERT(INT,TOTALG12)+CONVERT(INT,SUBG12M)+CONVERT(INT,G12MA1)+CONVERT(INT,G12MA1_4)+CONVERT(INT,G12MA5_9)+CONVERT(INT,G12MA10_14)+CONVERT(INT,G12MA15_19)+CONVERT(INT,G12MA20_24)+CONVERT(INT,G12MA25_49)+CONVERT(INT,G12MA50)+CONVERT(INT,SUBG12F)";
       SQL +="+CONVERT(INT,G12FE1)+CONVERT(INT,G12FE1_4)+CONVERT(INT,G12FE5_9)+CONVERT(INT,G12FE10_14)+CONVERT(INT,G12FE15_19)+CONVERT(INT,G12FE20_24)+CONVERT(INT,G12FE25_49)+CONVERT(INT,G12FE50)+CONVERT(INT,TOTALG13)+CONVERT(INT,SUBG13M)+CONVERT(INT,G13MA1)";
       SQL +="+CONVERT(INT,G13MA1_4)+CONVERT(INT,G13MA5_9)+CONVERT(INT,G13MA10_14)+CONVERT(INT,G13MA15_19)+CONVERT(INT,G13MA20_24)+CONVERT(INT,G13MA25_49)+CONVERT(INT,G13MA50)+CONVERT(INT,SUBG13F)+CONVERT(INT,G13FE1)+CONVERT(INT,G13FE1_4)+CONVERT(INT,G13FE5_9)";
       SQL +="+CONVERT(INT,G13FE10_14)+CONVERT(INT,G13FE15_19)+CONVERT(INT,G13FE20_24)+CONVERT(INT,G13FE25_49)+CONVERT(INT,G13FE50)+CONVERT(INT,TOTALG14)+CONVERT(INT,SUBG14M)+CONVERT(INT,G14MA1)+CONVERT(INT,G14MA1_4)+CONVERT(INT,G14MA5_9)+CONVERT(INT,G14MA10_14)";
       SQL +="+CONVERT(INT,G14MA15_19)+CONVERT(INT,G14MA20_24)+CONVERT(INT,G14MA25_49)+CONVERT(INT,G14MA50)+CONVERT(INT,SUBG14F)+CONVERT(INT,G14FE1)+CONVERT(INT,G14FE1_4)+CONVERT(INT,G14FE5_9)+CONVERT(INT,G14FE10_14)+CONVERT(INT,G14FE15_19)+CONVERT(INT,G14FE20_24)";
       SQL +="+CONVERT(INT,G14FE50)+CONVERT(INT,TOTALG15)+CONVERT(INT,SUBG15M)+CONVERT(INT,G15MA15_19)+CONVERT(INT,G15MA20_24)+CONVERT(INT,G15MS25_49)+CONVERT(INT,G15MA50)+CONVERT(INT,SUBG15F)+CONVERT(INT,G15FE15_19)+CONVERT(INT,G15FE20_24)+CONVERT(INT,G15FE25_49)";
       SQL +="+CONVERT(INT,G15FE50)+CONVERT(INT,TOTALG16)+CONVERT(INT,SUBG16M)+CONVERT(INT,G16MA15_19)+CONVERT(INT,G16MS25_49)+CONVERT(INT,G16MA50)+CONVERT(INT,SUBG16F)+CONVERT(INT,G16FE15_19)+CONVERT(INT,G16FE20_24)+CONVERT(INT,G16FE25_49)+CONVERT(INT,G16FE50)as a from tbl_htc2";
       SQL +="union all";
       SQL +="Select CONVERT(INT,TOTALG17)+CONVERT(INT,SUBG17M)+CONVERT(INT,G17MA1)+CONVERT(INT,G17MA1_4)+CONVERT(INT,G17MA5_9)+CONVERT(INT,G17MA10_14)+CONVERT(INT,G17MA15_19)+CONVERT(INT,G17MA20_24)+CONVERT(INT,G17MA25_49)+CONVERT(INT,G17MA50)+CONVERT(INT,SUBG17F)+CONVERT(INT,G17FE1) ";
       SQL +="+CONVERT(INT,G17FE1_4)+CONVERT(INT,G17FE5_9)+CONVERT(INT,G17FE10_14)+CONVERT(INT,G17FE15_19)+CONVERT(INT,G17FE20_24)+CONVERT(INT,G17FE25_49)+CONVERT(INT,G17FE50)+CONVERT(INT,TOTALG18)+CONVERT(INT,SUBG18M)+CONVERT(INT,G18MA1)+CONVERT(INT,G18MA1_4)+CONVERT(INT,G18MA5_9)+CONVERT(INT,G18MA10_14)";
       SQL +="+CONVERT(INT,G18MA15_19)+CONVERT(INT,G18MA20_24)+CONVERT(INT,G18MA25_49)+CONVERT(INT,G18MA50)+CONVERT(INT,SUBG18F)+CONVERT(INT,G18FE1)+CONVERT(INT,G18FE1_4)+CONVERT(INT,G18FE5_9)+CONVERT(INT,G18FE10_14)+CONVERT(INT,G18FE15_19)+CONVERT(INT,G18FE20_24)";
       SQL +="+CONVERT(INT,G18FE25_49)+CONVERT(INT,G18FE50)+CONVERT(INT,TOTALG19)+CONVERT(INT,SUBG19M)+CONVERT(INT,G19MA1)+CONVERT(INT,G19MA1_4)+CONVERT(INT,G98MA5_9)+CONVERT(INT,G19MA10_14)+CONVERT(INT,G19MA15_19)+CONVERT(INT,G19MA20_24)+CONVERT(INT,G19MA25_49)";
       SQL +="+CONVERT(INT,G19MA50)+CONVERT(INT,SUBG19F)+CONVERT(INT,G19FE1)+CONVERT(INT,G19FE1_4)+CONVERT(INT,G19FE5_9)+CONVERT(INT,G19FE10_14)+CONVERT(INT,G19FE15_19)+CONVERT(INT,G19FE20_24)+CONVERT(INT,G19FE25_49)+CONVERT(INT,G19FE50)+CONVERT(INT,TOTALG20)";
       SQL +="+CONVERT(INT,SUBG0M)+CONVERT(INT,G20MA1) +CONVERT(INT,G20MA1_4)+CONVERT(INT,G20MA5_9)+CONVERT(INT,G20MA10_14)+CONVERT(INT,G20MA15_19)+CONVERT(INT,G20MA20_24)+CONVERT(INT,G20MA25_49)+CONVERT(INT,G20MA50)+CONVERT(INT,SUBG20F)+CONVERT(INT,G20FE1)";
       SQL +="+CONVERT(INT,G20FE1_4)+CONVERT(INT,G20FE5_9)+CONVERT(INT,G20FE10_14)+CONVERT(INT,G20FE15_19)+CONVERT(INT,G20FE20_24)+CONVERT(INT,G20FE25_49)+CONVERT(INT,G20FE50)+CONVERT(INT,TOTALG21)+CONVERT(INT,SUBG21M)+CONVERT(INT,G21MA1)+CONVERT(INT,G21MA1_4)";
       SQL +="+CONVERT(INT,G21MA5_9)+CONVERT(INT,G21MA10_14)+CONVERT(INT,G21MA15_19)+CONVERT(INT,G21MA20_24)+CONVERT(INT,G21MA25_49)+CONVERT(INT,G21MA50)+CONVERT(INT,SUBG21F)+CONVERT(INT,G21FE1)+CONVERT(INT,G21FE1_4)+CONVERT(INT,G21FE5_9)+CONVERT(INT,G21FE10_14)";
       SQL +="+CONVERT(INT,G21FE15_19)+CONVERT(INT,G21FE20_24)+CONVERT(INT,G21FE25_49)+CONVERT(INT,G21FE50)+CONVERT(INT,TOTALG22)+CONVERT(INT,SUBG22M)+CONVERT(INT,G22MA1)+CONVERT(INT,G22MA1_4)+CONVERT(INT,G22MA5_9)+CONVERT(INT,G22MA10_14)+CONVERT(INT,G22MA15_19)";
       SQL +="+CONVERT(INT,G22MA20_24)+CONVERT(INT,G22MA25_49)+CONVERT(INT,G22MA50)+CONVERT(INT,SUBG22F)+CONVERT(INT,G22FE1)+CONVERT(INT,G22FE1_4)+CONVERT(INT,G22FE5_9)+CONVERT(INT,G22FE10_14)+CONVERT(INT,G22FE15_19)+CONVERT(INT,G22FE20_24)+CONVERT(INT,G22FE25_49)";
       SQL +="+CONVERT(INT,G22FE50)+CONVERT(INT,G23_1)+CONVERT(INT,G23_2)+CONVERT(INT,TOTALG24)+CONVERT(INT,SUBG24M)+CONVERT(INT,G24MA15_19)+CONVERT(INT,G24MA20_24)+CONVERT(INT,G24MA25_49)+CONVERT(INT,G24MA50)+CONVERT(INT,SUBG24F)+CONVERT(INT,G24FE15_19)";
       SQL +="+CONVERT(INT,G24FE20_24)+CONVERT(INT,G24FE25_49)+CONVERT(INT,G24FE50)+CONVERT(INT,TOTALG25)+CONVERT(INT,SUBG25M)+CONVERT(INT,G25MA15_19)+CONVERT(INT,G25MA20_24)+CONVERT(INT,G25MA25_49)+CONVERT(INT,G25MA50)+CONVERT(INT,SUBG25F)+CONVERT(INT,G25FE15_19)";
       SQL +="+CONVERT(INT,G25FE20_24)+CONVERT(INT,G25FE25_49)+CONVERT(INT,G25FE50)+CONVERT(INT,TOTALG26)+CONVERT(INT,G26MA1)+CONVERT(INT,G26FE1)as a from tbl_htc3)as TOTAL ";
    try
    {
        int Total = 0;
        SqlConnection cn = new SqlConnection(ConnectAll.ConnectMe());
        if (cn.State != ConnectionState.Open )
        {
            cn.Open();
        }

        SqlCommand cmd = new SqlCommand(SQL, cn);
        SqlDataReader rd = cmd.ExecuteReader();
        while (rd.Read())
        {
            Total = Convert.ToInt32( rd["TOTAL"].ToString());
        }
        return Total;
    }
    catch (Exception ex)
    {
        ClientScript.RegisterStartupScript(this.GetType(), "alert", "'"+ex.Message.ToString()+"'", true);
        return 0;
    }
}

TextBox3.Text = Convert.ToString(Sum());


the script worked fine in SQL Management studio, trying to use it with C# code it throwing exception "Incorrect syntax near the keyword 'CONVERT'".

i need your recommendation do i use stored procedure? or is their any thing wrong, sample code will help alot.
as this is urgent.

thanks Guys
Posted
Updated 29-Dec-15 0:26am
v3
Comments
Raje_ 29-Dec-15 6:37am
   
It is always good to use stored procedure.
Dave Kreskowiak 29-Dec-15 10:40am
   
Why the hell are you using CONVERT on every single field in the query? Did someone make the massive mistake of storing numbers as strings?

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

First of all, this problem is only urgent to you, not to anyone else here at CodeProject.

For the functionality you want I would use a stored function that returns the total sum.
Then use ExecuteScalar in order to get the result.

The benefits are that it is easier for you to debug the SQL statement and you hide the implementation details of the SQL query from the client.
The client is only interested in the final result, the total sum, Right?

[UPDATE]
You can do something like this:
CREATE FUNCTION dbo.CalculateTotalSum()
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE varResult INT;

    -- Add your calculation here
    SET varResult = ???

    -- Return the result of the calculation
    RETURN varResult;

END


Then in C# do something similar to this:
using (SqlConnection con = new SqlConnection(connectionString))
{
    SqlCommand com = new SqlCommand("Execute dbo.CalculateTotalSum", con);
    int totalSum = (int)com.ExecuteScalar();
}



Happy New Year.
   
v2
Comments
AbrahamOlatubosun1973 29-Dec-15 7:49am
   
Thanks George, I also thought of using stored procedure but how to implement it is my little challenge, any help or sample. thank you and sorry for any missed used english
George Jonsson 29-Dec-15 9:04am
   
See my updated answer.
AbrahamOlatubosun1973 29-Dec-15 15:51pm
   
Hi George thank you for all your support, but i want to you to kindly help once more because i did exactly as recommended but i keep having the following error "
Procedure or function 'HTCSUM' expects parameter '@Totals', which was not supplied."
Below is my STored Procedure code:

ALTER PROCEDURE HTCSUM
@Totals INT OUTPUT
AS
BEGIN

Select Sum(a) from (
Select CONVERT(INT,TOTALG1)+CONVERT(INT,SUBG1MA)+CONVERT(INT,G1MA1)+CONVERT(INT,G1MA1_4)+CONVERT(INT,G1MA5_9)+CONVERT(INT,G1MA10_14)+CONVERT(INT,G1MA15_19) +CONVERT(INT,G1MA20_24)+CONVERT(INT,G1MA25_49)
+CONVERT(INT,G1MA50)+CONVERT(INT,SUBG1FE)+CONVERT(INT,G1FE1)+CONVERT(INT,G1FE1_4)+CONVERT(INT,G1FE5_9)+CONVERT(INT,G1FE10_14)+CONVERT(INT,G1FE15_19)+CONVERT(INT,G1FE20_24)+CONVERT(INT,G1FE25_49)+CONVERT(INT,G1FE50)
+CONVERT(INT,TOTALG2)+CONVERT(INT,SUBG2M)+CONVERT(INT,G2MA1)+CONVERT(INT,G2MA1_4)+CONVERT(INT,G2MA5_9)+CONVERT(INT,G2MA10_14)+CONVERT(INT,G2MA15_19)+CONVERT(INT,G2MA20_24)+CONVERT(INT,G2MA25_49)+CONVERT(INT,G2MA50)
+CONVERT(INT,SUBG2F)+CONVERT(INT,G2FE1)+CONVERT(INT,G2FE1_4)+CONVERT(INT,G2FE5_9)+CONVERT(INT,G2FE10_14)+CONVERT(INT,G2FE15_19)+CONVERT(INT,G2FE20_24)+CONVERT(INT,G2FE25_49)+CONVERT(INT,G2FE50)
+CONVERT(INT,TOTALG3)+CONVERT(INT,SUBG3M)+CONVERT(INT,G3MA1)+CONVERT(INT,G3MA1_4)+CONVERT(INT,G3MA5_9)+CONVERT(INT,G3MA10_14)+CONVERT(INT,G3MA15_19)+CONVERT(INT,G3MA20_24)+CONVERT(INT,G3MA25_49)+CONVERT(INT,G3MA50)
+CONVERT(INT,SUBG3F)+CONVERT(INT,G3FE1)+CONVERT(INT,G3FE1_4)+CONVERT(INT,G3FE5_9)+CONVERT(INT,G3FE10_14)+CONVERT(INT,G3FE15_19)+CONVERT(INT,G3FE20_24)+CONVERT(INT,G3FE25_49)+CONVERT(INT,G3FE50)+CONVERT(INT,TOTALG4)+CONVERT(INT,SUBG4P)
+CONVERT(INT,G4P1)+CONVERT(INT,G4P1_4)+CONVERT(INT,G4P5_9)+CONVERT(INT,G4P10_14)+CONVERT(INT,G4P15_19)+CONVERT(INT,G4P20_24)+CONVERT(INT,G4P25_49)+CONVERT(INT,G4P50)+CONVERT(INT,SUB4N)+CONVERT(INT,G4N1)+CONVERT(INT,G4N1_4)
+CONVERT(INT,G4N5_9)+CONVERT(INT,G4N10_14)+CONVERT(INT,G4N15_19)+CONVERT(INT,G4N20_24)+CONVERT(INT,G4N25_49)+CONVERT(INT,G4N50)+CONVERT(INT,TOTALG5)+CONVERT(INT,SUBG5M)+CONVERT(INT,G5MA1)+CONVERT(INT,G5M1_4)+CONVERT(INT,G5MA5_9)
+CONVERT(INT,G5MA10_14)+CONVERT(INT,G5MA15_19)+CONVERT(INT,G5MA20_24)+CONVERT(INT,G5MA25_49)+CONVERT(INT,G5MA50)+CONVERT(INT,SUBG5F)+CONVERT(INT,G5FE1)+CONVERT(INT,G5FE1_4)+CONVERT(INT,G5FE5_9)+CONVERT(INT,G5FE10_14)+CONVERT(INT,G5FE15_19)
+CONVERT(INT,G5FE20_24)+CONVERT(INT,G5FE25_49)+CONVERT(INT,G5FE50)+CONVERT(INT,TOTALG6)+CONVERT(INT,SUBG6M)+CONVERT(INT,G6MA1)+CONVERT(INT,G6M1_4)+CONVERT(INT,G6MA5_9)+CONVERT(INT,G6MA10_14)+CONVERT(INT,G6MA15_19)+CONVERT(INT,G6MA20_24)
+CONVERT(INT,G6MA25_49)+CONVERT(INT,G6MA50)+CONVERT(INT,SUBG6F)+CONVERT(INT,G6FE1)+CONVERT(INT,G6FE1_4)+CONVERT(INT,G6FE5_9)+CONVERT(INT,G6FE10_14)+CONVERT(INT,G6FE15_19)+CONVERT(INT,G6FE20_24)+CONVERT(INT,G6FE25_49)+CONVERT(INT,G6FE50)
+CONVERT(INT,TOTALG7)+CONVERT(INT,SUBG7M)+CONVERT(INT,G7MA1)+CONVERT(INT,G7M1_4)+CONVERT(INT,G7MA5_9)+CONVERT(INT,G7MA10_14)+CONVERT(INT,G7MA15_19)+CONVERT(INT,G7MA20_24)+CONVERT(INT,G7MA25_49)+CONVERT(INT,G7MA50)+CONVERT(INT,SUBG7F)
+CONVERT(INT,G7FE1)+CONVERT(INT,G7FE1_4)+CONVERT(INT,G7FE5_9)+CONVERT(INT,G7FE10_14)+CONVERT(INT,G7FE15_19)+CONVERT(INT,G7FE20_24)+CONVERT(INT,G7FE25_49)+CONVERT(INT,G7FE50)+CONVERT(INT,TOTALG8)+CONVERT(INT,SUBG8M)+CONVERT(INT,G8MA10_14)
+CONVERT(INT,G8M15_19)+CONVERT(INT,G8MA20_24)+CONVERT(INT,G8MA25_49)+CONVERT(INT,G8MA50)+CONVERT(INT,SUBG8P)as a from tbl_htc1
union all
Select CONVERT(INT,TOTALG9)+CONVERT(INT,SUBG9M)+CONVERT(INT,G9MA1) +CONVERT(INT,G9MA1_4)+CONVERT(INT,G9MA5_9)+CONVERT(INT,G9MA10_14)+CONVERT(INT,G9MA15_19)+CONVERT(INT,G9MA20_24)+CONVERT(INT,G9MA25_49)+CONVERT(INT,G9MA50)+CONVERT(INT,SUBG9F)
+CONVERT(INT,G9FE1)+CONVERT(INT,G9FE5_9)+CONVERT(INT,G9FE10_14)+CONVERT(INT,G9FE15_19)+CONVERT(INT,G9FE20_24)+CONVERT(INT,G9FE25_49)+CONVERT(INT,G9FE50)+CONVERT(INT,TOTALG10)+CONVERT(INT,SUBG10P)+CONVERT(INT,G10P1)+CONVERT(INT,G10P1_4)
+CONVERT(INT,G10P5_9)+CONVERT(INT,G10P10_14)+CONVERT(INT,G10P15_19)+CONVERT(INT,G10P20_24)+CONVERT(INT,G10P25_
George Jonsson 29-Dec-15 18:07pm
   
Well, you didn't do exactly as I said. You changed from a FUNCTION with a return value to a PROCEDURE with an output value. Hence, you need to provide a variable in your c# call.
See Using a Stored Procedure with Output Parameters[^]
(You don't like to Google, do you?)
AbrahamOlatubosun1973 30-Dec-15 6:46am
   
thank you very much George, i really appreciate all your support. i did google around, different suggestion but codeproject is where i got the best support.

i did place the statement in SQL FUNCTION, within the Function it has no error but when i called it from C# using the following code:
int totalSum = 0;
try
{
using (SqlConnection con = new SqlConnection(ConnectAll.ConnectMe()))
{
con.Open();
SqlCommand cmd = new SqlCommand("Execute dbo.fn_HTCSUM", con);
totalSum =(int) cmd.ExecuteScalar();
}
return totalSum;
}
catch (Exception ex)
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "'"+ex.Message.ToString()+"'", true);
return 0;
}
at this point "totalSum =(int) cmd.ExecuteScalar();" the error is "Object reference not set to an instance of an object."
but when i use Convert.ToInt32(cmd.ExecuteScalar()); it clear the error but return zero.

please kindly assist me, i need your support.

thank you
AbrahamOlatubosun1973 30-Dec-15 7:30am
   
Thanks George i modify the C# a little and all is working now,
the statement "SqlCommand cmd = new SqlCommand("Execute dbo.fn_HTCSUM", con);" should be "SqlCommand cmd = new SqlCommand("Select dbo.fn_HTCSUM", con);" this worked for me.

thanks i really appreciate all your effort.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100