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