HOME > .NET Framework > Forum > ช่วยผมด้วย An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
ช่วยผมด้วย An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Server Error in '/' Application.
--------------------------------------------------------------------------------
Incorrect syntax near ')'.
Incorrect syntax near ')'.
WITH TempTaskJob AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY a.[Update_Date] DESC ,a.[RECEIVE_DATE] DESC ) AS RowNumber
,a.[JOB_ID] ,a.[MLST_ID] ,a.[DOC_NO]
,a.[SUBJECT] ,a.[DESCRIPTION] ,a.[INST_ID]
,d.[INST_NAME] ,a.[RECEIVE_DATE] ,a.[ALLOW_TO_EDIT]
,a.[ALLOW_TO_VISIBLE] ,a.[STEP_SEQ] ,a.[ACTLST_SEQ]
,a.[STATUS] ,a.[REF] ,a.[LOCK]
,a.[LOCK_BY] ,a.[Job_Status] ,'' AS LOCK_NAME
,a.[USERGROUP_ID] ,a.[DEP_ID] ,a.[nYear]
,a.[Entry_Date] ,a.[Entry_User] ,a.[Update_Date]
,a.[Update_user] ,b.FullName AS Entry_Name
,c.FullName AS Update_Name ,t1.JobStatus_name
,a.[OWNER_ID] ,t2.FullName AS Owner_name
,a.[Has_Attach] ,a.[Job_Day]
,a.[Finish_date] ,a.[Content_count]
, Min_End_date = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] )
, Max_Alert = (SELECT MAX(aa.[Alertbef_due]) FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id]
AND aa.[End_date] = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] ))
FROM [dbo].[TrnJob] AS a WITH(NOLOCK)LEFT OUTER JOIN
dbo.View_UserInfo AS b ON b.[User_id] = a.[Entry_User] LEFT OUTER JOIN
dbo.View_UserInfo AS c ON c.[User_id] = a.[Update_user] INNER JOIN
dbo.MasInstitute AS d ON d.[INST_ID] = a.[INST_ID] INNER JOIN
dbo.MasJobStatus AS t1 ON t1.JobStatus_id = a.Status LEFT OUTER JOIN
dbo.View_UserInfo AS t2 ON t2.[User_id] = a.[OWNER_ID] WHERE a.[Job_Status] = 1 And a.[DEP_ID] = 1 And (a.[UserGroup_id] = 0 Or a.[UserGroup_id] = ) And (a.[Entry_User]= 109)
)
SELECT *
FROM TempTaskJob
WHERE RowNumber > 0 AND RowNumber < 6
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 ')'.
Incorrect syntax near ')'.
WITH TempTaskJob AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY a.[Update_Date] DESC ,a.[RECEIVE_DATE] DESC ) AS RowNumber
,a.[JOB_ID] ,a.[MLST_ID] ,a.[DOC_NO]
,a.[SUBJECT] ,a.[DESCRIPTION] ,a.[INST_ID]
,d.[INST_NAME] ,a.[RECEIVE_DATE] ,a.[ALLOW_TO_EDIT]
,a.[ALLOW_TO_VISIBLE] ,a.[STEP_SEQ] ,a.[ACTLST_SEQ]
,a.[STATUS] ,a.[REF] ,a.[LOCK]
,a.[LOCK_BY] ,a.[Job_Status] ,'' AS LOCK_NAME
,a.[USERGROUP_ID] ,a.[DEP_ID] ,a.[nYear]
,a.[Entry_Date] ,a.[Entry_User] ,a.[Update_Date]
,a.[Update_user] ,b.FullName AS Entry_Name
,c.FullName AS Update_Name ,t1.JobStatus_name
,a.[OWNER_ID] ,t2.FullName AS Owner_name
,a.[Has_Attach] ,a.[Job_Day]
,a.[Finish_date] ,a.[Content_count]
, Min_End_date = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] )
, Max_Alert = (SELECT MAX(aa.[Alertbef_due]) FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id]
AND aa.[End_date] = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] ))
FROM [dbo].[TrnJob] AS a WITH(NOLOCK)LEFT OUTER JOIN
dbo.View_UserInfo AS b ON b.[User_id] = a.[Entry_User] LEFT OUTER JOIN
dbo.View_UserInfo AS c ON c.[User_id] = a.[Update_user] INNER JOIN
dbo.MasInstitute AS d ON d.[INST_ID] = a.[INST_ID] INNER JOIN
dbo.MasJobStatus AS t1 ON t1.JobStatus_id = a.Status LEFT OUTER JOIN
dbo.View_UserInfo AS t2 ON t2.[User_id] = a.[OWNER_ID] WHERE a.[Job_Status] = 1 And a.[DEP_ID] = 1 And (a.[UserGroup_id] = 0 Or a.[UserGroup_id] = ) And (a.[Entry_User]= 109)
)
SELECT *
FROM TempTaskJob
WHERE RowNumber > 0 AND RowNumber < 6
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near ')'.
Incorrect syntax near ')'.
WITH TempTaskJob AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY a.[Update_Date] DESC ,a.[RECEIVE_DATE] DESC ) AS RowNumber
,a.[JOB_ID] ,a.[MLST_ID] ,a.[DOC_NO]
,a.[SUBJECT] ,a.[DESCRIPTION] ,a.[INST_ID]
,d.[INST_NAME] ,a.[RECEIVE_DATE] ,a.[ALLOW_TO_EDIT]
,a.[ALLOW_TO_VISIBLE] ,a.[STEP_SEQ] ,a.[ACTLST_SEQ]
,a.[STATUS] ,a.[REF] ,a.[LOCK]
,a.[LOCK_BY] ,a.[Job_Status] ,'' AS LOCK_NAME
,a.[USERGROUP_ID] ,a.[DEP_ID] ,a.[nYear]
,a.[Entry_Date] ,a.[Entry_User] ,a.[Update_Date]
,a.[Update_user] ,b.FullName AS Entry_Name
,c.FullName AS Update_Name ,t1.JobStatus_name
,a.[OWNER_ID] ,t2.FullName AS Owner_name
,a.[Has_Attach] ,a.[Job_Day]
,a.[Finish_date] ,a.[Content_count]
, Min_End_date = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] )
, Max_Alert = (SELECT MAX(aa.[Alertbef_due]) FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id]
AND aa.[End_date] = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] ))
FROM [dbo].[TrnJob] AS a WITH(NOLOCK)LEFT OUTER JOIN
dbo.View_UserInfo AS b ON b.[User_id] = a.[Entry_User] LEFT OUTER JOIN
dbo.View_UserInfo AS c ON c.[User_id] = a.[Update_user] INNER JOIN
dbo.MasInstitute AS d ON d.[INST_ID] = a.[INST_ID] INNER JOIN
dbo.MasJobStatus AS t1 ON t1.JobStatus_id = a.Status LEFT OUTER JOIN
dbo.View_UserInfo AS t2 ON t2.[User_id] = a.[OWNER_ID] WHERE a.[Job_Status] = 1 And a.[DEP_ID] = 1 And (a.[UserGroup_id] = 0 Or a.[UserGroup_id] = ) And (a.[Entry_User]= 109)
)
SELECT *
FROM TempTaskJob
WHERE RowNumber > 0 AND RowNumber < 6]
SPMLib.DAL.SqlClient.TrnJobSqlClient.ListDashboard_Admin(Int32 PageSize, Int32 CurrentPage, String user_query) in D:\SPM_SRC\SPM\SPM Project\SPMLib\DAL\SqlClient\.Trn\TrnJobSqlClient.cs:399
SpmWeb.UserControl.Dashboard.Document_admin.BindingData() in D:\SPM_SRC\SPM\SPM Project\SPM Web\UserControl\Dashboard\Document_admin.ascx.cs:51
SpmWeb._Default.VerifyDashboard() in D:\SPM_SRC\SPM\SPM Project\SPM Web\Default.aspx.cs:46
SpmWeb._Default.Page_Load(Object sender, EventArgs e) in D:\SPM_SRC\SPM\SPM Project\SPM Web\Default.aspx.cs:24
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Tag : .NET, Ms SQL Server 2005, Web (ASP.NET), C#, VS 2008 (.NET 3.x)
1. ลองทดสอบ SQL บน SQL Server Management Studio (SSMS) หรือยัง???
2. เกี่ยวกับการใช้ Common Table Expression (CTE) ลักษณะนี้ Code
WITH TempTaskJob AS
(
SELECT ROW_NUMBER() OVER ( ORDER BY a.[Update_Date] DESC ,a.[RECEIVE_DATE] DESC ) AS RowNumber
,a.[JOB_ID] ,a.[MLST_ID] ,a.[DOC_NO]
,a.[SUBJECT] ,a.[DESCRIPTION] ,a.[INST_ID]
,d.[INST_NAME] ,a.[RECEIVE_DATE] ,a.[ALLOW_TO_EDIT]
,a.[ALLOW_TO_VISIBLE] ,a.[STEP_SEQ] ,a.[ACTLST_SEQ]
,a.[STATUS] ,a.[REF] ,a.[LOCK]
,a.[LOCK_BY] ,a.[Job_Status] ,'' AS LOCK_NAME
,a.[USERGROUP_ID] ,a.[DEP_ID] ,a.[nYear]
,a.[Entry_Date] ,a.[Entry_User] ,a.[Update_Date]
,a.[Update_user] ,b.FullName AS Entry_Name
,c.FullName AS Update_Name ,t1.JobStatus_name
,a.[OWNER_ID] ,t2.FullName AS Owner_name
,a.[Has_Attach] ,a.[Job_Day]
,a.[Finish_date] ,a.[Content_count]
, Min_End_date = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] )
, Max_Alert = (SELECT MAX(aa.[Alertbef_due]) FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id]
AND aa.[End_date] = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] ))
FROM [dbo].[TrnJob] AS a WITH(NOLOCK)LEFT OUTER JOIN
dbo.View_UserInfo AS b ON b.[User_id] = a.[Entry_User] LEFT OUTER JOIN
dbo.View_UserInfo AS c ON c.[User_id] = a.[Update_user] INNER JOIN
dbo.MasInstitute AS d ON d.[INST_ID] = a.[INST_ID] INNER JOIN
dbo.MasJobStatus AS t1 ON t1.JobStatus_id = a.Status LEFT OUTER JOIN
dbo.View_UserInfo AS t2 ON t2.[User_id] = a.[OWNER_ID] WHERE a.[Job_Status] = 1 And a.[DEP_ID] = 1 And (a.[UserGroup_id] = 0 Or a.[UserGroup_id] = ) And (a.[Entry_User]= 109)
)
SELECT *
FROM TempTaskJob
WHERE RowNumber > 0 AND RowNumber < 6
ผมว่าใช้ TOP ดีกว่านะครับ Code
SELECT TOP 5 a.[JOB_ID] ,a.[MLST_ID] ,a.[DOC_NO]
,a.[SUBJECT] ,a.[DESCRIPTION] ,a.[INST_ID]
,d.[INST_NAME] ,a.[RECEIVE_DATE] ,a.[ALLOW_TO_EDIT]
,a.[ALLOW_TO_VISIBLE] ,a.[STEP_SEQ] ,a.[ACTLST_SEQ]
,a.[STATUS] ,a.[REF] ,a.[LOCK]
,a.[LOCK_BY] ,a.[Job_Status] ,'' AS LOCK_NAME
,a.[USERGROUP_ID] ,a.[DEP_ID] ,a.[nYear]
,a.[Entry_Date] ,a.[Entry_User] ,a.[Update_Date]
,a.[Update_user] ,b.FullName AS Entry_Name
,c.FullName AS Update_Name ,t1.JobStatus_name
,a.[OWNER_ID] ,t2.FullName AS Owner_name
,a.[Has_Attach] ,a.[Job_Day]
,a.[Finish_date] ,a.[Content_count]
, Min_End_date = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] )
, Max_Alert = (SELECT MAX(aa.[Alertbef_due]) FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id]
AND aa.[End_date] = (SELECT MIN(aa.[End_date])FROM [TrnTracking] AS aa WHERE aa.[FINISH_DATE] IS NULL AND aa.[Job_id] = a.[Job_id] ))
FROM [dbo].[TrnJob] AS a WITH(NOLOCK)LEFT OUTER JOIN
dbo.View_UserInfo AS b ON b.[User_id] = a.[Entry_User] LEFT OUTER JOIN
dbo.View_UserInfo AS c ON c.[User_id] = a.[Update_user] INNER JOIN
dbo.MasInstitute AS d ON d.[INST_ID] = a.[INST_ID] INNER JOIN
dbo.MasJobStatus AS t1 ON t1.JobStatus_id = a.Status LEFT OUTER JOIN
dbo.View_UserInfo AS t2 ON t2.[User_id] = a.[OWNER_ID] WHERE a.[Job_Status] = 1 And a.[DEP_ID] = 1 And (a.[UserGroup_id] = 0 Or a.[UserGroup_id] = ) And (a.[Entry_User]= 109)
ORDER BY a.[Update_Date] DESC ,a.[RECEIVE_DATE] DESC
Load balance : Server 05
