开发者

Why no data is appearing?

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!

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.

Here is an excerpt of my code:

    <HTML>
    <SCRIPT LANGUAGE="VB" RUNAT="Server">
        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) & "/1/" & Year(TheMonthDate)).ToString("MM/dd/yyyy")
                calEndDate.SelectedDate = GlobalFunctions.GlobalF.MonthLastDate(CDate((TheMonthDate.Month) & "/1/" & 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"
                   开发者_StackOverflow     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()

Where in my Global.vb file I have:

            '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

And these stored procs are defined as:

    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 >= @FirstMonthDate AND A.QXP_REPORT_DATE <= @LastMonthDate 
    AND (A.QXP_SHORT_DESC <> 'Design Control') AND LEVEL = @TheLevel      
    AND (D.QXP_EXCEPTION_TYPE <> '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 >= @FirstMonthDate AND a.QXP_REPORT_DATE <= @LastMonthDate 
  AND (a.QXP_SHORT_DESC <> 'Design Control') AND LEVEL = @TheLevel
  AND (c.QXP_EXCEPTION_TYPE <> 'Non-Diagnostic' OR c.QXP_EXCEPTION_TYPE IS NULL)   
GROUP BY PRODUCT_GROUP   
ORDER BY COUNT(DISTINCT a.QXP_EXCEPTION_NO) DESC 

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.

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?


I think the problem is your code does not even compile. Looking at the posted page right at the start we see this:

End If
            Main
        End Sub

This is not valid VB. You are running an old version of your code. This is also why you can't debug it. You don't have the current one to set break points on.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜