Dataset binding issue after deployment-Collection of common programming errors
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.
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.
What we have is a hard-coded sql server query that is passed into a Report Object like so:
rep = new Report(@"
select [Asset_KEY], [Asset_ID], [Description], [Entity_Name], [Group_Tree], [Category_ID], [Type_ID], [Master_Asset_ID], [Model], [Manufacturer],
(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],
(select isnull(sum([Change]),0) from [LogDetails] where [Log_Type_KEY] = @LogType and [Asset_KEY]=[AssetList].[Asset_KEY]
and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate) [Fuel_Consumption],
(select isnull(sum([Change]),0) from [LogDetails] where [Log_Type_KEY] = @LogType1 and [Asset_KEY]=[AssetList].[Asset_KEY]
and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate) [Log_1_Change],
(select top 1 isnull([Value],0)-isnull([Change],0) from [LogDetails] where [Log_Type_KEY] = @LogType1 and [Asset_KEY]=[AssetList].[Asset_KEY]
and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date], [Log_Detail_ID]) [Log_1_Start],
(select top 1 isnull([Value],0) from [LogDetails] where [Log_Type_KEY] = @LogType1 and [Asset_KEY]=[AssetList].[Asset_KEY]
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],
(select isnull(sum([Change]),0) from [LogDetails] where [Log_Type_KEY] = @LogType2 and [Asset_KEY]=[AssetList].[Asset_KEY]
and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate) [Log_2_Change],
(select top 1 isnull([Value],0)-isnull([Change],0) from [LogDetails] where [Log_Type_KEY] = @LogType2 and [Asset_KEY]=[AssetList].[Asset_KEY]
and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date], [Log_Detail_ID]) [Log_2_Start],
(select top 1 isnull([Value],0) from [LogDetails] where [Log_Type_KEY] = @LogType2 and [Asset_KEY]=[AssetList].[Asset_KEY]
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],
(select isnull(sum([Change]),0) from [LogDetails] where [Log_Type_KEY] = @LogType3 and [Asset_KEY]=[AssetList].[Asset_KEY]
and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate) [Log_3_Change],
(select top 1 isnull([Value],0)-isnull([Change],0) from [LogDetails] where [Log_Type_KEY] = @LogType3 and [Asset_KEY]=[AssetList].[Asset_KEY]
and CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate order by [Post_Date], [Log_Detail_ID]) [Log_3_Start],
(select top 1 isnull([Value],0) from [LogDetails] where [Log_Type_KEY] = @LogType3 and [Asset_KEY]=[AssetList].[Asset_KEY]
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]
from [AssetList]
WHERE [Asset_KEY] in (select [Asset_KEY] from [LogDetails] where CAST(FLOOR(CAST([Post_Date] AS float))AS datetime) between @StartDate and @EndDate
and [Log_Type_KEY] in (@LogType, @LogType1, @LogType2, @LogType3))
and [AssetList].[Entity_KEY] in (select [Entity_KEY] from AccessReports where [User_KEY]= " + CurrentSecurityUserKey.ToString() + @" and [Security_ID]='SomeKey')
", "Report Name", cTables, "Asset_Key", "datasetname);
Ugly I know, but it works well enough.
Then to bind the dataset it calls this:
DataSet ds = rep.GetDataSet(rep.whereClause);
Which does this:
public DataSet GetDataSet(string whereClause)
{
SqlConnection conn = new SqlConnection(DataManager.ConnectionString);
DataSet dsGetRecords = new DataSet(_dataSetName);
string modifiedQuery = "";
if (whereClause != "")
modifiedQuery = _reportQuery.Replace("!CRITERIAAND!", " AND " + whereClause);
else
modifiedQuery = _reportQuery.Replace("!CRITERIAAND!", "");
if (whereClause != "")
modifiedQuery = modifiedQuery.Replace("!CRITERIAWHERE!", " WHERE " + whereClause);
else
modifiedQuery = modifiedQuery.Replace("!CRITERIAWHERE!", "");
conn.Open();
try
{
SqlCommand cmdGetRecords = new SqlCommand(modifiedQuery, conn);
if (_parameters.Count > 0)
{
foreach (KeyValuePair kvp in _parameters)
{
cmdGetRecords.Parameters.AddWithValue(kvp.Key, kvp.Value);
}
}
SqlDataAdapter daGetRecords = new SqlDataAdapter();
daGetRecords.SelectCommand = cmdGetRecords;
daGetRecords.Fill(dsGetRecords);
if (calcFields.Count > 0)
{
foreach(CalculatedReportField crf in calcFields)
{
dsGetRecords.Tables[0].Columns.Add(crf.ColumnName, crf.Type, crf.Expression);
}
}
}
catch(Exception ex)
{
string s = ex.Message;
throw new Exception("Can't bind dataset");
}
finally
{
conn.Close();
}
return dsGetRecords;
}
The Exception on the server is getting thrown here, not sure exactly where though since it is working perfectly locally.
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.
EDIT Here is the all the error message I am getting when I look at eventviewer on the server:
Event Type: Warning
Event Source: ASP.NET 4.0.30319.0
Event Category: Web Event
Event ID: 1309
Date: 6/8/2012
Time: 8:56:47 AM
User: N/A
Computer: QAMCONQAMVW1
Description:
Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 6/8/2012 8:56:47 AM
Event time (UTC): 6/8/2012 2:56:47 PM
Event ID: 512217af060a43d1a249f0d19964aedf
Event sequence: 102
Event occurrence: 7
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/87257621/Root-1-129836365271042512
Trust level: Full
Application Virtual Path: /
Application Path: C:\Websites\www.mymanagerplus.com\
Machine name: QAMCONQAMVW1
Process information:
Process ID: 12420
Process name: w3wp.exe
Account name: NT AUTHORITY\NETWORK SERVICE
Exception information:
Exception type: Exception
Exception message: Can't bind dataset
at WebApplication.Reports.Report.GetDataSet(String whereClause) in c:\WebBuild\web\Reports\Report.cs:line 128
at WebApplication.Reports.Logs.ViewFuelComparison.RunReport() in c:\WebBuild\web\Reports\Logs\ViewFuelComparison.aspx.cs:line 42
at WebApplication.Reports.Logs.ViewFuelComparison.Page_Load(Object sender, EventArgs e) in c:\WebBuild\web\Reports\Logs\ViewFuelComparison.aspx.cs:line 32
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
Request information:
Request URL:
Request path:
User host address:
User:
Is authenticated: False
Authentication Type:
Thread account name: NT AUTHORITY\NETWORK SERVICE
Thread information:
Thread ID: 10
Thread account name: NT AUTHORITY\NETWORK SERVICE
Is impersonating: False
Stack trace: at WebApplication.Reports.Report.GetDataSet(String whereClause) in c:\WebBuild\web\Reports\Report.cs:line 128
at WebApplication.Reports.Logs.ViewFuelComparison.RunReport() in c:\WebBuild\web\Reports\Logs\ViewFuelComparison.aspx.cs:line 42
at WebApplication.Reports.Logs.ViewFuelComparison.Page_Load(Object sender, EventArgs e) in c:\WebBuild\web\Reports\Logs\ViewFuelComparison.aspx.cs:line 32
at System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)
at System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)