title: Why no data is appearing?
body: <p>I am developing an ASPX web page to return results from two SQL stored procs via VB.  When I execute these two stored procs, they both return valid data.  But for some reason, when I run this report it doesn't return any errors, however, it doesn't return any records/data either!  </p>
  
  <p>Also, I can't debug it for some reason, although I can set breakpoints!  This is using VS 2008, but I think reason I can't debug is I believe this is a limited version that just works for SSRS and SSIS.  </p>
  
  <p>Here is an excerpt of my code:</p>
  
  <pre><code>    &lt;HTML&gt;
      &lt;SCRIPT LANGUAGE="VB" RUNAT="Server"&gt;
          Sub Page_Load(Sender as Object, E as EventArgs)
              If Not IsPostback Then 
                  Dim TheMonthDate As Date = DateAdd(DateInterval.Month, -1, Today)
                  calStartDate.SelectedDate = CDate((TheMonthDate.Month) &amp; "/1/" &amp; Year(TheMonthDate)).ToString("MM/dd/yyyy")
                  calEndDate.SelectedDate = GlobalFunctions.GlobalF.MonthLastDate(CDate((TheMonthDate.Month) &amp; "/1/" &amp; Year(TheMonthDate)).ToString("MM/dd/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"
  
  
  
      End If
                  Main
              End Sub
  
              Sub Main()          
                  '------------------------- Query database and get arrays for the chart and bind query results to datagrid ----------------------------------------
  
                  Dim FirstMonthDate as date = calStartDate.SelectedDate
                  Dim LastMonthDate as date = calEndDate.SelectedDate
  
                  Dim TheLevel As Integer
                  Dim TitleLevel as String
  
                  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 DSPageData as new System.Data.DataSet
                  DSPageData = GlobalFunctions.GlobalF.GetComplaintTrending2(FirstMonthDate, LastMonthDate, TheLevel)
      ...
                  Dim DSDetails As New System.Data.DataSet
                  DSDetails = GlobalFunctions.GlobalF.GetComplaintTrendingDetails2(FirstMonthDate, LastMonthDate, TheLevel)
                  dgTable.DataSource = DSDetails
                  dgTable.DataBind()
  </code></pre>
  
  <p>Where in my Global.vb file I have:</p>
  
  <pre class="lang-vb prettyprint-override"><code>            'Added by Ryan on 4/17/11
          Public Shared Function GetComplaintTrending2(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("ComplaintTrending2", conn), _
              da As New SQLDataAdapter(cmd)
                  cmd.CommandType = CommandType.StoredProcedure
                  cmd.Parameters.AddRange(param)
  
                  da.Fill(DSPageData)
              End Using
  
              Return DSPageData
          End Function
          'Added by Ryan on 4/17/11
          Public Shared Function GetComplaintTrendingDetails2(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("ComplaintTrendingDetails2", conn), _
              da As New SQLDataAdapter(cmd)
                  cmd.CommandType = CommandType.StoredProcedure
                  cmd.Parameters.AddRange(param)
  
                  da.Fill(DSPageData)
              End Using
  
              Return DSPageData
          End Function
  </code></pre>
  
  <p>And these stored procs are defined as:</p>
  
  <pre><code>    CREATE PROCEDURE [dbo].[ComplaintTrendingDetails2]
  --DECLARE 
  @FirstMonthDate DATETIME,
  @LastMonthDate DATETIME,
  @TheLevel INT
  
  AS
      SET NOCOUNT ON;
  
  --ComplaintTrendingDetails2 '2/1/11', '2/28/11 23:59:59', 2 
  --SET @FirstMonthDate = '2/1/11'
  --SET @LastMonthDate = '2/28/11 23:59:59'
  --SET @TheLevel = '2'
  
  SELECT DISTINCT    
      A.QXP_EXCEPTION_NO, A.[LEVEL], A.pRE,     
      A.QXP_REPORT_DATE, A.CLOSE_DATE, A.EPA_PRD_NAME,     
      A.EPA_PRD_CODE, A.EPL_LOT_NUMBER,     
      A.QXP_SHORT_DESC, A.QXP_DESCRIPTION,     
      A.QXP_RESOLUTION_DESC, A.CXP_CLIENT_NAME, A.Country,     
      C.PRODUCT, C.PRODUCT_GROUP, C.PRODUCT_ORG_UNIT,     
      B.DOC_DOCUMENT_NO, A.TICKET_NUM, A.CENTER_NUM,     
      A.COUNTRY_CODE, A.QXP_ID, B.IRF_QEI_ID 
  
      FROM ALL_COMPLAINTS A LEFT OUTER JOIN     
      SMARTSOLVE.V_QXP_ISSUE_REF B ON A.QXP_ID = B.IRF_QXP_ID LEFT OUTER JOIN     
      MANUAL.PRODUCTS C ON A.EPA_PRD_CODE = C.LIST_NUMBER    
      LEFT OUTER JOIN SMARTSOLVE.V_CXP_CUSTOMER_PXP D ON A.QXP_ID = D.QXP_ID    
      WHERE A.QXP_REPORT_DATE &gt;= @FirstMonthDate AND A.QXP_REPORT_DATE &lt;= @LastMonthDate 
      AND (A.QXP_SHORT_DESC &lt;&gt; 'Design Control') AND LEVEL = @TheLevel      
      AND (D.QXP_EXCEPTION_TYPE &lt;&gt; 'Non-Diagnostic' OR D.QXP_EXCEPTION_TYPE IS NULL)    
   ORDER BY 4 
  
  and
  
  --ALTER PROCEDURE [dbo].[ComplaintTrending2]
  DECLARE 
  @FirstMonthDate DATETIME,
  @LastMonthDate DATETIME,
  @TheLevel INT
  
  --AS
  --  SET NOCOUNT ON;
  
  --ComplaintTrending2 '2/1/11', '2/28/11 23:59:59', 2 
  SET @FirstMonthDate = '2/1/11'
  SET @LastMonthDate = '2/28/11 23:59:59'
  SET @TheLevel = '2'
  
  SELECT 
  CASE ISNULL(PRODUCT_GROUP, '') WHEN '' THEN 'Unspecified' ELSE PRODUCT_GROUP END AS PRODUCT_GROUP, 
  COUNT(DISTINCT A.QXP_EXCEPTION_NO) AS CountOfTickets   
    FROM ALL_COMPLAINTS a
    LEFT OUTER JOIN MANUAL.PRODUCTS b ON a.EPA_PRD_CODE = b.LIST_NUMBER   
    LEFT OUTER JOIN SMARTSOLVE.V_CXP_CUSTOMER_PXP c ON a.QXP_ID = c.QXP_ID   
    WHERE a.QXP_REPORT_DATE &gt;= @FirstMonthDate AND a.QXP_REPORT_DATE &lt;= @LastMonthDate 
    AND (a.QXP_SHORT_DESC &lt;&gt; 'Design Control') AND LEVEL = @TheLevel
    AND (c.QXP_EXCEPTION_TYPE &lt;&gt; 'Non-Diagnostic' OR c.QXP_EXCEPTION_TYPE IS NULL)   
  GROUP BY PRODUCT_GROUP   
  ORDER BY COUNT(DISTINCT a.QXP_EXCEPTION_NO) DESC 
  </code></pre>
  
  <p>Could the problem be due to the fact that this web page uses the same SQL connection for both stored procs?  The first proc should be returned initially, and the second proc after this screen.  So I don't need both stored procs information simultaneously, so I would think I could reuse the same SQL connection.</p>
  
  <p>OK, I found where the problem is located.  If I just comment out the Level statement in the Where clause, it does return data.  So only thing I changed was that one line in my SQL code and it works.  This means missing data must be due to datatype incompatibility, no?  What is the problem?</p>