Select from table not working

Marius Vasile

I am trying to get data from table with query below, basically trying to get int val from Value field for each month as V1, V2, etc

SELECT 
	(CASE When Month = 'January' Then Value Else 0 End) AS V1, 
	(CASE When Month = 'February' Then Value Else 0 End) AS V2, 
	(CASE When Month = 'March' Then Value Else 0 End) AS V3, 
	(CASE When Month = 'April' Then Value Else 0 End) AS V4, 
	(CASE When Month = 'May' Then Value Else 0 End) AS V5, 
	(CASE When Month = 'June' Then Value Else 0 End) AS V6, 
	(CASE When Month = 'July' Then Value Else 0 End) AS V7, 
	(CASE When Month = 'August' Then Value Else 0 End) AS V8, 
	(CASE When Month = 'September' Then Value Else 0 End) AS V9, 
	(CASE When Month = 'October' Then Value Else 0 End) AS V10, 
	(CASE When Month = 'November' Then Value Else 0 End) AS V11, 
	(CASE When Month = 'December' Then Value Else 0 End) AS V12

	FROM tblOmOra WHERE Year=@CurrentYear;

What is wrong with this query?

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Brahma Prakash Shukla1y ago

Accepted answer

The issue seems to be caused by your stored procedure returning multiple result sets. You are executing two separate SELECT statements in your stored procedure, and both return results: one with columns M1, M2, etc., and another with columns V1, V2, etc. Since you are accessing the first row of the first result set in your C# code, you are trying to access V1 from the first result set, which does not contain V1.

To solve this, you need to handle multiple result sets in your C# code. You can achieve this by using SqlDataReader and its NextResult() method to iterate through each result set.

Please try below code -

protected void GetDataChartRALAM()
{
    int currentYear = DateTime.Now.Year;
    string spec = Session["Specialitate"].ToString();
    SqlConnection conn = new SqlConnection(connString);
    {
        using (SqlCommand cmd = new SqlCommand("RALAM", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CurrentYear", currentYear);
            cmd.Parameters.AddWithValue("@spec", spec);
            conn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                // Handle first result set (M1, M2, etc.)
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        double m1 = double.Parse(reader["M1"].ToString());
                        double m2 = double.Parse(reader["M2"].ToString());
                        double m3 = double.Parse(reader["M3"].ToString());
                        // Continue for M4, M5, etc.
                    }
                }

                // Move to the second result set (V1, V2, etc.)
                if (reader.NextResult() && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        double v1 = double.Parse(reader["V1"].ToString());
                        double v2 = double.Parse(reader["V2"].ToString());
                        double v3 = double.Parse(reader["V3"].ToString());
                        // Continue for V4, V5, etc.
                    }
                }
            }
        }
    }
}

Marius Vasile1y ago

Ok, I saw late your C#, it is working fine now I got the values for V1, V2...V12 as well but I have to do some math in C3 outside the reader if's

double[] yValues =
    {
        (acc1*200000)/val1,
        ((acc1+acc2)*200000)/(val1+val2),
        ((acc1+acc2+acc3)*200000)/(val1+val2+val3),
        ((acc1+acc2+acc3+acc4)*200000)/(val1+val2+val3+val4),
        ((acc1+acc2+acc3+acc4+acc5)*200000)/(val1+val2+val3+val4+val5),
        ((acc1+acc2+acc3+acc4+acc5+acc6)*200000)/(val1+val2+val3+val4+val5+val6),
        ((acc1+acc2+acc3+acc4+acc5+acc6+acc7)*200000)/(val1+val2+val3+val4+val5+val6+val7),
        ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8),
        ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8+acc9)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8+val9),
        ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8+acc9+acc10)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8+val9+val10),
        ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8+acc9+acc10+acc11)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8+val9+val10+val11),
        ((acc1+acc2+acc3+acc4+acc5+acc6+acc7+acc8+acc9+acc10+acc11+acc12)*200000)/(val1+val2+val3+val4+val5+val6+val7+val8+val9+val10+val11+val12)
    };

and having 2 if's is not working. Error The name 'acc1' does not exist in the current context

Brahma Prakash Shukla1y ago

Have you tried my code of c# ?

protected void GetDataChartRALAM()
{
    int currentYear = DateTime.Now.Year;
    string spec = Session["Specialitate"].ToString();
    string connString = "your_connection_string"; // Make sure to use your actual connection string

    using (SqlConnection conn = new SqlConnection(connString))
    {
        using (SqlCommand cmd = new SqlCommand("RALAM", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CurrentYear", currentYear);
            cmd.Parameters.AddWithValue("@spec", spec);

            conn.Open();

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                // Handle the first result set (M1, M2, ..., M12)
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        double m1 = double.Parse(reader["M1"].ToString());
                        double m2 = double.Parse(reader["M2"].ToString());
                        double m3 = double.Parse(reader["M3"].ToString());
                        double m4 = double.Parse(reader["M4"].ToString());
                        double m5 = double.Parse(reader["M5"].ToString());
                        double m6 = double.Parse(reader["M6"].ToString());
                        double m7 = double.Parse(reader["M7"].ToString());
                        double m8 = double.Parse(reader["M8"].ToString());
                        double m9 = double.Parse(reader["M9"].ToString());
                        double m10 = double.Parse(reader["M10"].ToString());
                        double m11 = double.Parse(reader["M11"].ToString());
                        double m12 = double.Parse(reader["M12"].ToString());

                        // You can now use the M1, M2, ..., M12 values as needed
                    }
                }

                // Move to the second result set (V1, V2, ..., V12)
                if (reader.NextResult() && reader.HasRows)
                {
                    while (reader.Read())
                    {
                        double v1 = double.Parse(reader["V1"].ToString());
                        double v2 = double.Parse(reader["V2"].ToString());
                        double v3 = double.Parse(reader["V3"].ToString());
                        double v4 = double.Parse(reader["V4"].ToString());
                        double v5 = double.Parse(reader["V5"].ToString());
                        double v6 = double.Parse(reader["V6"].ToString());
                        double v7 = double.Parse(reader["V7"].ToString());
                        double v8 = double.Parse(reader["V8"].ToString());
                        double v9 = double.Parse(reader["V9"].ToString());
                        double v10 = double.Parse(reader["V10"].ToString());
                        double v11 = double.Parse(reader["V11"].ToString());
                        double v12 = double.Parse(reader["V12"].ToString());

                        // You can now use the V1, V2, ..., V12 values as needed
                    }
                }
            }
        }
    }
}

Marius Vasile1y ago
I verified it, the result set returned by SqlDataAdapter.Fill(resultTable) contains only first query result, without second query. How do I manage that?
Brahma Prakash Shukla1y ago
Hi, The error you're encountering ("Column 'V1' does not belong to table") suggests that the query executed by your stored procedure (RALAM) is not returning the expected columns (V1, V2, etc.) in the result set. This can happen for a few reasons:
- Stored Procedure Not Returning the Correct Columns
- Column Aliases Not Matching
- Result Set Schema: The result set returned by SqlDataAdapter.Fill(resultTable) may not contain columns V1, V2, etc., if the stored procedure doesn't return them. You can verify this by logging or debugging the structure of resultTable.Columns.

Marius Vasile1y ago

Full stored procedure here

CREATE PROCEDURE [dbo].[RALAM]

@CurrentYear as int,
@Spec as nvarchar(10)

AS 

	SELECT
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 1 Then 1 Else 0 End) M1,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 2 Then 1 Else 0 End) M2,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 3 Then 1 Else 0 End) M3,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 4 Then 1 Else 0 End) M4,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 5 Then 1 Else 0 End) M5,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 6 Then 1 Else 0 End) M6,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 7 Then 1 Else 0 End) M7,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 8 Then 1 Else 0 End) M8,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 9 Then 1 Else 0 End) M9,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 10 Then 1 Else 0 End) M10,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 11 Then 1 Else 0 End) M11,
		SUM(CASE When tc.Subcategorie = 'Accident de munca' AND DATEPART(YEAR, tr.DataInitierii)=@CurrentYear AND DATEPART(MM, tr.DataInitierii) = 12 Then 1 Else 0 End) M12 

		FROM tblRCA_Import tr FULL JOIN tblClasificare tc ON tr.RCA# = tc.RCA# WHERE tc.Specialitate = @Spec;

	SELECT 
		SUM(CASE When Month = 'January' Then Value Else 0 End) AS V1, 
		SUM(CASE When Month = 'February' Then Value Else 0 End) AS V2, 
		SUM(CASE When Month = 'March' Then Value Else 0 End) AS V3, 
		SUM(CASE When Month = 'April' Then Value Else 0 End) AS V4, 
		SUM(CASE When Month = 'May' Then Value Else 0 End) AS V5, 
		SUM(CASE When Month = 'June' Then Value Else 0 End) AS V6, 
		SUM(CASE When Month = 'July' Then Value Else 0 End) AS V7, 
		SUM(CASE When Month = 'August' Then Value Else 0 End) AS V8, 
		SUM(CASE When Month = 'September' Then Value Else 0 End) AS V9, 
		SUM(CASE When Month = 'October' Then Value Else 0 End) AS V10, 
		SUM(CASE When Month = 'November' Then Value Else 0 End) AS V11, 
		SUM(CASE When Month = 'December' Then Value Else 0 End) AS V12

		FROM tblOmOra WHERE Year=@CurrentYear;

EXECUTE RALAM;

Marius Vasile1y ago

Thank you Brahma, I got the same error message (Column 'V1' does not belong to table). I try to get data with

protected void GetDataChartRALAM()
{
    int currentYear = DateTime.Now.Year;
    string spec = Session["Specialitate"].ToString();
    SqlConnection conn = new SqlConnection(connString);
    {
        using (SqlCommand cmd = new SqlCommand("RALAM", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CurrentYear", currentYear);
            cmd.Parameters.AddWithValue("@spec", spec);
            conn.Open();
            using (SqlDataAdapter da = new SqlDataAdapter())
            {
                DataTable resultTable = new DataTable();
                da.SelectCommand = cmd;
                da.Fill(resultTable);

                if (resultTable.Rows.Count > 0)
                {
                    DataRow row = resultTable.Rows[0];

                    double val1 = double.Parse(row["V1"].ToString());
                    double val2 = double.Parse(row["V2"].ToString());
                    double val3 = double.Parse(row["V3"].ToString());
                    double val4 = double.Parse(row["V4"].ToString());
                    double val5 = double.Parse(row["V5"].ToString());
                    double val6 = double.Parse(row["V6"].ToString());
                    double val7 = double.Parse(row["V7"].ToString());
                    double val8 = double.Parse(row["V8"].ToString());
                    double val9 = double.Parse(row["V9"].ToString());
                    double val10 = double.Parse(row["V10"].ToString());
                    double val11 = double.Parse(row["V11"].ToString());
                    double val12 = double.Parse(row["V12"].ToString());

                }
                conn.Close();
            };
        };
    }
}

Brahma Prakash Shukla1y ago

The issue with your query is that it will return multiple rows, each containing a value for a specific month, but the columns V1 through V12 will always return 0 for all months except the current one in the row.

You are using a CASE statement to assign values to V1, V2, etc., based on the month. However, since SQL processes rows individually, each row will only match one month at a time. To pivot this data into a single row with columns for each month, you will need to aggregate the results.

Please try this one -

SELECT
    SUM(CASE WHEN [Month] = 'January' THEN Value ELSE 0 END) AS V1,
    SUM(CASE WHEN [Month] = 'February' THEN Value ELSE 0 END) AS V2,
    SUM(CASE WHEN [Month] = 'March' THEN Value ELSE 0 END) AS V3,
    SUM(CASE WHEN [Month] = 'April' THEN Value ELSE 0 END) AS V4,
    SUM(CASE WHEN [Month] = 'May' THEN Value ELSE 0 END) AS V5,
    SUM(CASE WHEN [Month] = 'June' THEN Value ELSE 0 END) AS V6,
    SUM(CASE WHEN [Month] = 'July' THEN Value ELSE 0 END) AS V7,
    SUM(CASE WHEN [Month] = 'August' THEN Value ELSE 0 END) AS V8,
    SUM(CASE WHEN [Month] = 'September' THEN Value ELSE 0 END) AS V9,
    SUM(CASE WHEN [Month] = 'October' THEN Value ELSE 0 END) AS V10,
    SUM(CASE WHEN [Month] = 'November' THEN Value ELSE 0 END) AS V11,
    SUM(CASE WHEN [Month] = 'December' THEN Value ELSE 0 END) AS V12
FROM tblOmOra 
WHERE [Year] = @CurrentYear;