locked
ASPX file output differs from SQL stored proc RRS feed

  • Question

  • User1450228099 posted

    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 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

    I would like to debug this, but this is the shell and so I don't seem to have the debug options. I can set breakpoints, but all of the Debug functionality is greyed out. This may be because I can only create SSAS/SSIS/SSRS projects

     

    Tuesday, April 19, 2011 9:48 AM

Answers

  • User1508394307 posted

    If output differs, it means that one or more parameters differ from when you call it from sql

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 21, 2011 1:48 PM

All replies

  • User1508394307 posted

    If output differs, it means that one or more parameters differ from when you call it from sql

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, April 21, 2011 1:48 PM
  • User42903263 posted

    Not actually to do with your question, but you might find the DateTime.DaysInMonth Method useful to replace your MonthLastDate function.

    --
    Andrew

    Friday, April 22, 2011 12:25 PM