[Solved] ASP.Net SqlDataReader Error: Incorrect syntax near ,

lingers

SOLVED CLOSED ACTIVE ANSWERED
User: lingers
Posted: on Dec 06, 2021 09:54 PM
Forum: ASP.Net Web Forms
Answer: 2
Views: 3485

I am trying to get report for 3 company namely NAND, LOGGER AND SECURITY.

The code below works well for getting report for a single work report for the company called NAND

USE [qqq]
GO
/****** Object:  Table [dbo].[MealLog]    Script Date: 12/7/2021 1:46:30 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MealLog](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](250) NOT NULL,
	[Emp_No] [nvarchar](250) NOT NULL,
	[Meal_Cat] [nvarchar](250) NOT NULL,
	[Department] [nvarchar](250) NOT NULL,
	[Company] [nvarchar](250) NOT NULL,
	[Reciept_No] [int] NOT NULL,
	[Value] [int] NOT NULL,
	[Time] [datetime] NOT NULL,
	[Time1] [date] NOT NULL,
	[Exact] [int] NULL,
 CONSTRAINT [PK_MealLog] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[MealLog] ON 
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2012, N'SECURITY', N'SECURITYBULK', N'X', N'PRODUCTION', N'SECURITY', 10000353, 600, CAST(N'2020-12-07T14:47:00.000' AS DateTime), CAST(N'2021-11-17' AS Date), 10)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (2013, N'SECURITY', N'SECURITYBULK', N'X', N'PRODUCTION', N'SECURITY', 10000354, 600, CAST(N'2020-12-07T14:50:00.000' AS DateTime), CAST(N'2021-11-16' AS Date), 20)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (3005, N'LOGGER', N'LOGGERBULK', N'X', N'LOGGERBULK', N'LOGGER', 10000356, 600, CAST(N'2021-10-29T00:00:00.000' AS DateTime), CAST(N'2021-11-16' AS Date), 30)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (3006, N'LOGGER', N'LOGGERBULK', N'2X', N'LOGGERBULK', N'LOGGER', 10000357, 600, CAST(N'2021-10-30T00:00:00.000' AS DateTime), CAST(N'2021-11-17' AS Date), 40)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (3007, N'NAND', N'NANDBULK', N'x', N'NANDBULK', N'NAND', 10000358, 600, CAST(N'2021-11-17T00:00:00.000' AS DateTime), CAST(N'2021-11-17' AS Date), 200)
GO
INSERT [dbo].[MealLog] ([id], [Name], [Emp_No], [Meal_Cat], [Department], [Company], [Reciept_No], [Value], [Time], [Time1], [Exact]) VALUES (3008, N'NAND', N'NANDBULK', N'x', N'NANDBULK', N'NAND', 10000359, 600, CAST(N'2021-11-16T00:00:00.000' AS DateTime), CAST(N'2021-11-16' AS Date), 100)
GO
SET IDENTITY_INSERT [dbo].[MealLog] OFF
GO

 <%
    SqlCommand scz = new SqlCommand("SELECT  Datename(dw, Time1)+', '+CONVERT(VARCHAR(12), Time1, 107) as Query_Date, sum(Exact) AS NAND,Exact = SUM(Exact),N_MCOST = SUM(Exact) * MAX(Value)FROM MealLog WHERE Company = 'NAND'  , sum(Exact) AS LOGGER,Exact1 = SUM(Exact),L_MCOST = SUM(Exact) * MAX(Value)FROM MealLog WHERE Company = 'LOGGER'  ,sum(Exact) AS LOGGER,Exact1 = SUM(Exact),S_MCOST = SUM(Exact) * MAX(Value)FROM MealLog WHERE Company = 'SECURITY'    and Time1  >= '" + (Session["D"].ToString()) + "' and Time1 <= '" + (Session["D1"].ToString()) + "'  GROUP BY Time1 ORDER BY Time1 ASC ", dbConn); 
    scz.CommandType = CommandType.Text;
    dbConn.Open();
    SqlDataReader sdz = scz.ExecuteReader();
    int ct = 1;
    while (sdz.Read())
    {
        Response.Write("<tr style><td class='style2'>" + ct + "</td><td class='style2'>" + sdz["Query_Date"].ToString() + "</td><td class='style5'>" + sdz["Exact"].ToString() + "</td><td class='style5'>" + "₦" + sdz["N_MCOST"].ToString() + "</td> <td class='style5'>" + "₦" + sdz["Exact1"].ToString() + "</td><td class='style5'>" + "₦" + sdz["L_MCOST"].ToString() + "</td><td class='style5'>" + "₦" + sdz["Exact2"].ToString() + "</td><td class='style5'>" + "₦" + sdz["S_MCOST"].ToString() + "</td></tr>");
        ct++;
    }
    dbConn.Close();
%>

I GOT THIS ERROR

Server Error in '/WebsiteP' Application.

Incorrect syntax near ','.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','. Source Error:

Line 169:                               
Line 170:
Line 171:                            SqlDataReader sdz = scz.ExecuteReader();
Line 172:                            int ct = 1;
Line 173:                            while (sdz.Read())

Source File: c:\Users\hp\Documents\Visual Studio 2010\WebSites\WebsiteP\BioPluginWebApp\LogA2.aspx Line: 171 Stack Trace:

PLEASE HELP

dharmend...

0 9

Reply
ANSWER
User: dharmendr
Replied: on Dec 08, 2021 03:00 AM
Report

Hi lingers,

Refer below query.

SQL

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
SET @DynamicPivotQuery =
N';WITH cte
AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ''S/N'',
    Datename(dw, Time1)+'', '' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,
	NAND, NAND * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''NAND'') NANDCOST,
	LOGGER, LOGGER * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''LOGGER'') LOGGERCOST,
	SECURITY, SECURITY * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''SECURITY'') SECURITYCOST
FROM (SELECT Time1,Exact,Company from MealLog) t
pivot
(
	MAX(Exact) FOR Company IN ([LOGGER],[NAND],[SECURITY])
) piv
)
SELECT CAST([S/N] AS VARCHAR(10)) [S/N],QUERYDATE,NAND,NANDCOST COST,LOGGER,LOGGERCOST COST,SECURITY,SECURITYCOST COST FROM cte
UNION
SELECT ''TOTAL'','''',SUM(CAST(NAND AS BIGINT)),SUM(CAST(NANDCOST AS BIGINT)),SUM(CAST(LOGGER AS BIGINT)),SUM(CAST(LOGGERCOST AS BIGINT)),SUM(CAST(SECURITY AS BIGINT)),SUM(CAST(SECURITYCOST AS BIGINT)) FROM cte'
EXEC (@DynamicPivotQuery)

Output

S/N	QUERYDATE	NAND	COST	LOGGER	COST	SECURITY	COST
1	Tuesday, Nov 16, 2021	100	60000	30	18000	20	12000
2	Wednesday, Nov 17, 2021	200	120000	40	24000	10	6000
TOTAL		300	180000	70	42000	30	18000

arjunv

0 11

Reply
ANSWER
User: arjunv
Replied: on Dec 09, 2021 05:57 AM
Report

lingers says:

SqlCommand scz = new SqlCommand("DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) SET @DynamicPivotQuery =N';WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ''S/N'', Datename(dw, Time1)+'', '' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,   NAND, NAND * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''NAND'') NANDCOST,LOGGER, LOGGER * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''LOGGER'') LOGGERCOST,SECURITY, SECURITY * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''SECURITY'') SECURITYCOST FROM (SELECT Time1,Exact,Company from MealLog) t pivot ( MAX(Exact) FOR Company IN ([LOGGER],[NAND],[SECURITY])) piv) SELECT CAST([S/N] AS VARCHAR(10)) [S/N],QUERYDATE,NAND,NANDCOST COST,LOGGER,LOGGERCOST COST,SECURITY,SECURITYCOST COST FROM cte UNION SELECT ''TOTAL'','''',SUM(CAST(NAND AS BIGINT)),SUM(CAST(NANDCOST AS BIGINT)),SUM(CAST(LOGGER AS BIGINT)),SUM(CAST(LOGGERCOST AS BIGINT)),SUM(CAST(SECURITY AS BIGINT)),SUM(CAST(SECURITYCOST AS BIGINT)) FROM cte' EXEC (@DynamicPivotQuery) ", dbConn);

Replace with below.

SqlCommand scz = new SqlCommand("DECLARE @DynamicPivotQuery AS NVARCHAR(MAX) SET @DynamicPivotQuery =N';WITH cte AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) ''S/N'', Datename(dw, Time1)+'', '' + CONVERT(VARCHAR(12), Time1, 107) QUERYDATE,   NAND, NAND * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''NAND'') NANDCOST,LOGGER, LOGGER * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''LOGGER'') LOGGERCOST,SECURITY, SECURITY * (SELECT TOP 1 Value FROM [MealLog] WHERE Company = ''SECURITY'') SECURITYCOST FROM (SELECT Time1,Exact,Company from MealLog) t pivot ( MAX(Exact) FOR Company IN ([LOGGER],[NAND],[SECURITY])) piv) SELECT CAST([S/N] AS VARCHAR(10)) [S/N],QUERYDATE,NAND,NANDCOST,LOGGER,LOGGERCOST,SECURITY,SECURITYCOST FROM cte UNION SELECT ''TOTAL'','''',SUM(CAST(NAND AS BIGINT)),SUM(CAST(NANDCOST AS BIGINT)),SUM(CAST(LOGGER AS BIGINT)),SUM(CAST(LOGGERCOST AS BIGINT)),SUM(CAST(SECURITY AS BIGINT)),SUM(CAST(SECURITYCOST AS BIGINT)) FROM cte' EXEC (@DynamicPivotQuery) ", dbConn);

[Solved] ASP.Net SqlDataReader Error: Incorrect syntax near ,

Server Error in '/WebsiteP' Application.

Incorrect syntax near ','.

Company

Explore

Follow us

Contact Us