ASPX file output differs from SQL stored proc
I am developing an ASPX file that returns results from a SQL stored proc into a data adapter, which is then displayed in a GridView. But the problem is that the numbers do not match between the SQL stored proc and Gridview. They are close, but are off just slightly: 3 thousandths. So the SQL Stored proc gives me 0.199, but in Gridview this same value = .203. And it doesn't appear to be a rounding issue either. Also, I looked at the Gridview properties, but I didn't see anything that would be causing this. All of the values for this one column are off by the same margin.
What troubleshooting steps can u recommend? I am using VB in a VS 2008 Shell for SSRS, SSIS. Here is my code from the main ASPX file:
<%@ Page Language="VB" Debug="true" Src="../Global.vb"%>
<%@ Import Namespace="ChartDirector" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<%@ Import Namespace="System.Math" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Register TagPrefix="chart" Namespace="ChartDirector" Assembly="netchartdir" %>
<HTML>
<SCRIPT LANGUAGE="VB" RUNAT="Server">
Sub Page_Load(Sender as Object, E as EventArgs)
If Not IsPostback Then
Dim YearDate as date = "1/ 1/05"
Dim arrYear as New ArrayList()
While YearDate <= Today
arrYear.Add(YearDate.ToString("yyyy"))
YearDate = YearDate.AddYears(1)
End While
dYear.DataSource = arrYear
dYear.DataBind()
dYear.SelectedValue = Today.AddMonths(-1).ToString("yyyy")
Dim arrLevel as New ArrayList()
arrLevel.Add("All")
arrLevel.Add("Inquiries")
arrLevel.Add("All Complaints")
arrLevel.Add("Elevated Complaints")
arrLevel.Add("Non-Elevated Complaints")
dLevel.DataSource = arrLevel
dLevel.DataBind()
dLevel.SelectedValue = "All Complaints"
'********* Set Month dropdown ************
Dim EndMonth as Date
dim StartMonth as Date = "1/1/" & dYear.SelectedValue
Dim arrMonth as New ArrayList()
EndMonth = "12/1/" & dYear.SelectedValue
While StartMonth <= EndMonth
arrMonth.Add(MonthName(month(StartMonth)))
StartMonth = StartMonth.AddMonths(1)
End While
dMonth.DataSource = arrMonth
dMonth.DataBind()
If dYear.SelectedValue = Today.ToString("yyyy") then
dMonth.SelectedValue = MonthName(month(Today.AddMonths(-1)))
End If
Label1.Text = "Complaint Trending List"
btnExport.Visible = "false"
Else
Main()
btnExport.Visible = "true"
End If
End Sub
Sub Main()
Dim TheLevel As Integer
Dim TitleLevel As String
Dim FirstMonthDate As Date = dMonth.SelectedValue & "/1/" & dYear.SelectedValue
Dim LastMonthDate as date = GlobalFunctions.GlobalF.MonthLastDate(FirstMonthDate)
Dim arrMonthYear As New ArrayList()
Select Case dLevel.SelectedValue
Case "All"
TheLevel = 5
TitleLevel = "Inquiries and Complaints"
Case "Inquiries"
TheLevel = 0
TitleLevel = "Inquiries"
Case "All Complaints"
TheLevel = 3
TitleLevel = "All Complaints"
Case "Elevated Complaints"
TheLevel = 2
TitleLevel = "Elevated Complaints"
Case "Non-Elevated Complaints"
TheLevel = 1
TitleLevel = "Non-Elevated Complaints"
End Select
Dim dataPG As New System.Data.DataSet
Dim dataSD As New System.Data.DataSet
Dim dataPCHART As New System.Data.DataSet
PrintMessageGrid.DataSource = GlobalFunctions.GlobalF.GetComplaintTrendingList6(FirstMonthDate, LastMonthDate, TheLevel)
PrintMessageGrid.DataBind()
Dim ListDataTable As DataTable
Dim ListDataRow As DataRow
ListDataTable = New DataTable
ListDataTable.Columns.Add("Product Group")
ListDataTable.Columns.Add("Short Description")
ListDataTable.Columns.Add("p-value")
ListDataTable.Columns.Add("LCL")
ListDataTable.Columns.Add("UCL")
ListDataTable.Columns.Add("Six In A Row")
ListDataTable.Columns.Add("Exceeds Limits")
'ListDataTable.Columns.Add("Selected")
'ListDataTable.Columns.Add("Total")
Label1.Text = dMonth.SelectedValue & " " & dYear.SelectedValue & " Complaint Trending List"
End Sub
Sub PrintMessageGrid_RowDataBound(ByVal sender As Object, _
ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
Dim exceeds_limits As Integer = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Exceeds_Limit"))
Dim six_in_a_row As Integer = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Six_In_A_Row"))
If exceeds_limits = 1 Or six_in_a_row = 1 Then
e.Row.BackColor = Drawing.Color.Red
End If
End If
End Sub
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e 开发者_JAVA百科As System.EventArgs)
GlobalFunctions.GlobalF.GridViewToExcel(PrintMessageGrid, Response)
End Sub
</SCRIPT>
<head>
<title>AMD QA Metrics</title>
</head>
<body onbeforeunload="LoadBusy();">
<script language="javascript" src="../includes/CastleBusyBox.js"></script>
<div style="font-size:18pt; font-family:verdana; font-weight:bold">
<asp:Label ID="Label1" runat="server"></asp:Label>
</div>
<hr style="color:#000080"/>
<form runat="Server" method="post" id="Form1">
<table>
<tr><th>Month</th><th>Year</th><th>Level</th></tr>
<tr>
<td><ASP:DROPDOWNLIST id="dMonth" runat="Server" autopostback="false" /></td>
<td><ASP:DROPDOWNLIST id="dYear" runat="Server" autopostback="false" /></td>
<td><ASP:DROPDOWNLIST id="dLevel" runat="Server" autopostback="false" /></td>
</tr>
</table>
<asp:Button id="btnSubmit" runat="server" Text="submit" />
<br />
<br />
<span onclick="busyBox.Enabled = false;">
<asp:Button id="btnExport" runat="server" Text="Export List to Excel" onclick="btnExport_Click" autopostback="false" />
</span>
<ASP:GridView id="PrintMessageGrid" runat="server" AUTOGENERATECOLUMNS="true" ShowHeader="true" OnRowDataBound="PrintMessageGrid_RowDataBound">
<HEADERSTYLE BackColor = "#336699" ForeColor = "#ffffff" Font-Bold = "true" />
</ASP:GridView>
<iframe id="BusyBoxIFrame" name="BusyBoxIFrame" frameBorder="0" scrolling="no" ondrop="return false;">
</iframe>
<SCRIPT>
// Instantiate our BusyBox object
var busyBox = new BusyBox("BusyBoxIFrame", "busyBox", 4, "../Images/gears_ani_", ".gif", 125, 147, 207);
</SCRIPT>
</form>
</body>
</HTML>
And portions of the global.vb file:
Namespace GlobalFunctions
Public Class GlobalF
'Added by Ryan on 4/14/11
Public Shared Function GetComplaintTrendingList6(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer) As DataSet
Dim DSPageData As New System.Data.DataSet
Dim param(2) As SqlClient.SqlParameter
param(0) = New SqlParameter("@FirstMonthDate", SqlDbType.DateTime)
param(0).Value = FirstMonth
param(1) = New SqlParameter("@LastMonthDate", SqlDbType.DateTime)
param(1).Value = LastMonth
param(2) = New SqlParameter("@TheLevel", SqlDbType.Int)
param(2).Value = rowLevel
''# A Using block will ensure the .Dispose() method is called for these variables, even if an exception is thrown
''# This is IMPORTANT - not disposing your connections properly can result in an unrespsonsive database
Using conn As New SQLConnection(ConfigurationSettings.AppSettings("AMDMetricsDevConnectionString")), _
cmd As New SQLCommand("ComplaintTrendingList6", conn), _
da As New SQLDataAdapter(cmd)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddRange(param)
da.Fill(DSPageData)
End Using
Return DSPageData
End Function
...
Public Shared Function MonthLastDate(ByVal TheDate As Date)
Select Case TheDate.Month
Case 1, 3, 5, 7, 8, 10, 12
MonthLastDate = TheDate.Year & "-" & TheDate.Month & "-31"
Case 4, 6, 9, 11
MonthLastDate = TheDate.Year & "-" & TheDate.Month & "-30"
Case 2
If (CInt(TheDate.Year) Mod 4) = 0 Then
MonthLastDate = TheDate.Year & "-" & TheDate.Month & "-29"
Else
MonthLastDate = TheDate.Year & "-" & TheDate.Month & "-28"
End If
End Select
End Function
You're going to have to debug into this to find what the values in question are at what steps.
If you look at the result of GlobalFunctions.GlobalF.GetComplaintTrendingList6(FirstMonthDate, LastMonthDate, TheLevel) directly when debugging the page, what do the numeric values in question look like?
If you step into that function and look at the contents of DSPageData after it's filled, what do the numeric values look like?
If you take the command from cmd with the given parameters and execute it directly on the database, what do the numeric values look like?
If you run a trace on the database and catch the actual execution command being sent for the stored procedure, does it match what you think it should match? Run it manually exactly as it's being sent to the server and see what the results look like.
加载中,请稍侯......
精彩评论