HOW DO I REPRESENT THIS STATEMENT

Question

1.00/5 (3 votes)

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)

C#

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 28-Dec-15 23:48pm

AbrahamOlatubosun1973

Updated 29-Dec-15 0:26am

George Jonsson

v3

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

George Jonsson · Accepted Answer · 2015-12-29T00:32:00

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:

SQL

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:

C#

using (SqlConnection con = new SqlConnection(connectionString))
{
    SqlCommand com = new SqlCommand("Execute dbo.CalculateTotalSum", con);
    int totalSum = (int)com.ExecuteScalar();
}

Happy New Year.

Posted 29-Dec-15 0:32am

George Jonsson

Updated 29-Dec-15 3:04am

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.