I will do my best to show what is going on without pasting the pages of code that is involved here. Primarily this question is about debugging an asp.net site. I can’t replicate the issue on my local machine, and can’t find any issues with the sql query that could be messing up the dataset.<\/p>\n
I didn’t write the code that does all the back end work, but I am relatively familiar with it, but don’t be surprised if I say “i dunno” if you as a terribly complicated question regarding it.<\/p>\n
What we have is a hard-coded sql server query that is passed into a Report Object like so:<\/p>\n
rep = new Report(@\"\n select [Asset_KEY], [Asset_ID], [Description], [Entity_Name], [Group_Tree], [Category_ID], [Type_ID], [Master_Asset_ID], [Model], [Manufacturer], \n (select [Budget_ID] from [Budgets] where [Budget_KEY]=isnull((select [Budget_KEY] from [LogEntry] where [Asset_KEY]=[AssetList].[Asset_KEY] and [Log_Type_KEY]=@LogType), [AssetList].[Budget_KEY])) [Budget_ID],\n (select isnull(sum([Change]),0) from [LogDetails] where [Log_Type_KEY] = @LogType and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate) [Fuel_Consumption], \n (select isnull(sum([Change]),0) from [LogDetails] where [Log_Type_KEY] = @LogType1 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate) [Log_1_Change], \n (select top 1 isnull([Value],0)-isnull([Change],0) from [LogDetails] where [Log_Type_KEY] = @LogType1 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date], [Log_Detail_ID]) [Log_1_Start], \n (select top 1 isnull([Value],0) from [LogDetails] where [Log_Type_KEY] = @LogType1 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date] desc, [Log_Detail_ID] desc) [Log_1_End], \n (select isnull(sum([Change]),0) from [LogDetails] where [Log_Type_KEY] = @LogType2 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate) [Log_2_Change],\n (select top 1 isnull([Value],0)-isnull([Change],0) from [LogDetails] where [Log_Type_KEY] = @LogType2 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date], [Log_Detail_ID]) [Log_2_Start], \n (select top 1 isnull([Value],0) from [LogDetails] where [Log_Type_KEY] = @LogType2 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date] desc, [Log_Detail_ID] desc) [Log_2_End], \n (select isnull(sum([Change]),0) from [LogDetails] where [Log_Type_KEY] = @LogType3 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate) [Log_3_Change],\n (select top 1 isnull([Value],0)-isnull([Change],0) from [LogDetails] where [Log_Type_KEY] = @LogType3 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date], [Log_Detail_ID]) [Log_3_Start], \n (select top 1 isnull([Value],0) from [LogDetails] where [Log_Type_KEY] = @LogType3 and [Asset_KEY]=[AssetList].[Asset_KEY] \n and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date] desc, [Log_Detail_ID] desc) [Log_3_End] \n from [AssetList]\n WHERE [Asset_KEY] in (select [Asset_KEY] from [LogDetails] where CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate\n and [Log_Type_KEY] in (@LogType, @LogType1, @LogType2, @LogType3)) \n and [AssetList].[Entity_KEY] in (select [Entity_KEY] from AccessReports where [User_KEY]= \" + CurrentSecurityUserKey.ToString() + @\" and [Security_ID]='SomeKey')\n \", \"Report Name\", cTables, \"Asset_Key\", \"datasetname);\n<\/code><\/pre>\nUgly I know, but it works well enough.<\/p>\n
Then to bind the dataset it calls this:<\/p>\n
DataSet ds = rep.GetDataSet(rep.whereClause);\n<\/code><\/pre>\nWhich does this:<\/p>\n
public DataSet GetDataSet(string whereClause)\n {\n SqlConnection conn = new SqlConnection(DataManager.ConnectionString);\n DataSet dsGetRecords = new DataSet(_dataSetName);\n string modifiedQuery = \"\";\n if (whereClause != \"\")\n modifiedQuery = _reportQuery.Replace(\"!CRITERIAAND!\", \" AND \" + whereClause);\n else\n modifiedQuery = _reportQuery.Replace(\"!CRITERIAAND!\", \"\");\n if (whereClause != \"\")\n modifiedQuery = modifiedQuery.Replace(\"!CRITERIAWHERE!\", \" WHERE \" + whereClause);\n else\n modifiedQuery = modifiedQuery.Replace(\"!CRITERIAWHERE!\", \"\");\n\n conn.Open();\n try\n {\n SqlCommand cmdGetRecords = new SqlCommand(modifiedQuery, conn);\n if (_parameters.Count > 0)\n {\n foreach (KeyValuePair kvp in _parameters)\n {\n cmdGetRecords.Parameters.AddWithValue(kvp.Key, kvp.Value);\n }\n }\n SqlDataAdapter daGetRecords = new SqlDataAdapter();\n daGetRecords.SelectCommand = cmdGetRecords;\n daGetRecords.Fill(dsGetRecords);\n if (calcFields.Count > 0)\n {\n foreach(CalculatedReportField crf in calcFields)\n {\n dsGetRecords.Tables[0].Columns.Add(crf.ColumnName, crf.Type, crf.Expression);\n }\n }\n }\n catch(Exception ex)\n {\n string s = ex.Message;\n throw new Exception(\"Can't bind dataset\");\n }\n finally\n {\n conn.Close();\n }\n return dsGetRecords;\n }\n<\/code><\/pre>\nThe Exception on the server is getting thrown here, not sure exactly where though since it is working perfectly locally.<\/p>\n
Any help or tips would be appreciated in helping me figure this out. Also, I have already ran the sql query on the database that our QA server is pointed to and they are returning results just fine.<\/p>\n
EDIT<\/strong> Here is the all the error message I am getting when I look at eventviewer on the server:<\/p>\nEvent Type: Warning\nEvent Source: ASP.NET 4.0.30319.0\nEvent Category: Web Event \nEvent ID: 1309\nDate: 6\/8\/2012\nTime: 8:56:47 AM\nUser: N\/A\nComputer: QAMCONQAMVW1\nDescription:\nEvent code: 3005 \nEvent message: An unhandled exception has occurred. \nEvent time: 6\/8\/2012 8:56:47 AM \nEvent time (UTC): 6\/8\/2012 2:56:47 PM \nEvent ID: 512217af060a43d1a249f0d19964aedf \nEvent sequence: 102 \nEvent occurrence: 7 \nEvent detail code: 0 \n\nApplication information: \nApplication domain: \/LM\/W3SVC\/87257621\/Root-1-129836365271042512 \nTrust level: Full \nApplication Virtual Path: \/ \nApplication Path: C:\\Websites\\www.mymanagerplus.com\\ \nMachine name: QAMCONQAMVW1 \n\nProcess information: \nProcess ID: 12420 \nProcess name: w3wp.exe \nAccount name: NT AUTHORITY\\NETWORK SERVICE \n\nException information: \nException type: Exception \nException message: Can't bind dataset\nat WebApplication.Reports.Report.GetDataSet(String whereClause) in c:\\WebBuild\\web\\Reports\\Report.cs:line 128\nat WebApplication.Reports.Logs.ViewFuelComparison.RunReport() in c:\\WebBuild\\web\\Reports\\Logs\\ViewFuelComparison.aspx.cs:line 42\nat WebApplication.Reports.Logs.ViewFuelComparison.Page_Load(Object sender, EventArgs e) in c:\\WebBuild\\web\\Reports\\Logs\\ViewFuelComparison.aspx.cs:line 32\nat System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)\nat System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)\nat System.Web.UI.Control.OnLoad(EventArgs e)\nat System.Web.UI.Control.LoadRecursive()\nat System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)\n\n\n\nRequest information: \nRequest URL: \nRequest path: \nUser host address: \nUser: \nIs authenticated: False \nAuthentication Type: \nThread account name: NT AUTHORITY\\NETWORK SERVICE \n\nThread information: \nThread ID: 10 \nThread account name: NT AUTHORITY\\NETWORK SERVICE \nIs impersonating: False \nStack trace: at WebApplication.Reports.Report.GetDataSet(String whereClause) in c:\\WebBuild\\web\\Reports\\Report.cs:line 128\nat WebApplication.Reports.Logs.ViewFuelComparison.RunReport() in c:\\WebBuild\\web\\Reports\\Logs\\ViewFuelComparison.aspx.cs:line 42\nat WebApplication.Reports.Logs.ViewFuelComparison.Page_Load(Object sender, EventArgs e) in c:\\WebBuild\\web\\Reports\\Logs\\ViewFuelComparison.aspx.cs:line 32\nat System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)\nat System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)\nat System.Web.UI.Control.OnLoad(EventArgs e)\nat System.Web.UI.Control.LoadRecursive()\nat System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"I will do my best to show what is going on without pasting the pages of code that is involved here. Primarily this question is about debugging an asp.net site. I can’t replicate the issue on my local machine, and can’t find any issues with the sql query that could be messing up the dataset. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-7356","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/7356","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=7356"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/7356\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=7356"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=7356"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=7356"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}