?
Solved

Reference to a non-shared member requires an object reference

Posted on 2011-05-03
32
Medium Priority
?
1,764 Views
Last Modified: 2012-05-11
I am getting the above error now when I run one of my ASPX pages, written in VB.NET. So I tried following the solution at:
http://msdn.microsoft.com/en-us/library/zwwhc0d0(v=vs.80).aspx
The above link seemed promising, cause it seemed to describe my problem exactly. However, I got the following error from this solution:

Compiler Error Message: BC30456: 'GlobalF2' is not a member of 'GlobalFunctions' Line 88:
DSProductData = GlobalFunctions.GlobalF2.ComplaintTrendingDrillDown3p(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber)
And here is my modified source code causing this error, but based off of Mike Smith's solution:

Namespace GlobalFunctions     Public Class GlobalF         Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet             Dim DSPageData As New System.Data.DataSet             Dim param(5) 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             param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)             param(3).Value = productGroup             param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)             param(4).Value = category             param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)             param(5).Value = ListNumber              ''# 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("ComplaintTrendingDrillDown3p", conn), _             da As New SQLDataAdapter(cmd)                 cmd.CommandType = CommandType.StoredProcedure                 cmd.Parameters.AddRange(param)                  da.Fill(DSPageData)             End Using              Return DSPageData         End Function     End Class      Public Class CallingClass         Dim GlobalF2 As New GlobalF         Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet             Dim DSPageData As New System.Data.DataSet             Dim param(5) 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             param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)             param(3).Value = productGroup             param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)             param(4).Value = category             param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)             param(5).Value = ListNumber              ''# 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("ComplaintTrendingDrillDown3p", conn), _             da As New SQLDataAdapter(cmd)                 cmd.CommandType = CommandType.StoredProcedure                 cmd.Parameters.AddRange(param)                  da.Fill(DSPageData)             End Using              Return DSPageData         End Function     End Class  End Namespace

Open in new window


I think Mike Smith is correct about not using Shared, cause I think that caused this problem. However, I am a newbie at VB.NET and I'm not sure how else to declare an instance as an object variable and then reference this instance by variable name. Can you help?
 
0
Comment
Question by:ironryan77
  • 18
  • 12
  • 2
32 Comments
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35516643
Can you repost the code in a formatted manner? You posted it as one line.

In the meantime, what this error means is that you are trying to do something like this:

Public Class MyExampleClass
    Private myPrivateField As String

    Public Shared Sub MySharedMethod()
        myPrivateField = "Hello World"
    End Sub
End Class

Open in new window


Here, the Shared method is trying to access an instance field (myPrivateField), but this is illegal to do. You cannot assign to or read from instance data from within a shared method or property; however, you can access Shared members from within instance data. The following is legal to do:


Public Class MyExampleClass
    Public Sub MyInstanceMethod()
        MySharedMethod()
    End Sub

    Public Shared Sub MySharedMethod()
        Console.WriteLine("Hello World!"
    End Sub
End Class

Open in new window

0
 

Author Comment

by:ironryan77
ID: 35517077
Here is my code reformatted:
Namespace GlobalFunctions
	Public Class GlobalF
        Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class

    Public Class CallingClass
        Dim GlobalF2 As New GlobalF
        Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class
    
End Namespace

Open in new window

0
 

Author Comment

by:ironryan77
ID: 35517116
Are you sure about your solution?  I do not use the Shared keyword.  
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:ironryan77
ID: 35517251
OK, I tried implementing your solution, Kaufmed, but now I'm getting a different error.  Here is my new code:
              
Dim DSProductData As New System.Data.DataSet
	        DSProductData = GlobalFunctions.CallingClass.MyInstanceMethod(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber)
...
    Public Class CallingClass
        'Dim GlobalF2 As New GlobalF
        Public Function MyInstanceMethod(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String)
            ComplaintTrendingDrillDown3p(FirstMonth, LastMonth, rowLevel, productGroup, category, ListNumber)
        End Function

        Public Shared Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class

Open in new window


And the error:
BC30469: Reference to a non-shared member requires an object reference.

DSProductData = GlobalFunctions.CallingClass.MyInstanceMethod(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber)
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 35517309
That wasn't a "solution"; I was just demonstrating what code that generates that type of error could look like.

Are you sure this code is the code that generates the error? Which line is highlighted when you recieve the error?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35517347
Is the name suggests, MyInstanceMethod requires an instance of the class to be created before it can be called. The Shared function ComplaintTrendingDrillDown3p can be called without creating an instance.
0
 

Author Comment

by:ironryan77
ID: 35517442
Kaufmed, line 2 (above) is the code that generates this error (highlighted line).  Yes, I'm sure it's the code that generates the error.  
@CodeCruiser, can you describe in more detail how I can reformat my code to accomplish your solution?  However, from the URL I described, it sounds like the problem is that I can't use shared functions because it is overwriting the same datatable, corrupting subsequent reads.  
Here is my new code where I attempt to create a class instance.  Unfortunately though, I don't know how to use "cc_new":
Public Class CallingClass
        'Dim GlobalF2 As New GlobalF
        Public Function CallingClass(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String)
            Dim cc_new As New CallingClass()
            'ComplaintTrendingDrillDown3p(FirstMonth, LastMonth, rowLevel, productGroup, category, ListNumber)
        End Function

        Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35517460
Use the following code to call the method

Dim cc as New CallingClass
cc.ComplaintTrendingDrillDown3p(...)


Public Class CallingClass
        'Dim GlobalF2 As New GlobalF
        Public Function CallingClass(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String)
            Dim cc_new As New CallingClass()
            'ComplaintTrendingDrillDown3p(FirstMonth, LastMonth, rowLevel, productGroup, category, ListNumber)
        End Function

        Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class

Open in new window

0
 

Author Comment

by:ironryan77
ID: 35517578
Thank you very much; I think you are really close to the solution, CodeCruiser!  Maybe I did not implement your solutoin correctly though cause this also gives me an error. Could you please advise on what I did wrong?
	        Dim cc_new As New CallingClass
	        DSProductData = cc_new.ComplaintTrendingDrillDown3p(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber)
....
    Public Class CallingClass
        Public Function CallingClass(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String)
            Dim cc_new As New CallingClass()
        End Function

        Public Shared Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class

Open in new window

And my error:
 
BC30002: Type 'CallingClass' is not defined
which is line:
Dim cc_new As New CallingClass
0
 

Author Comment

by:ironryan77
ID: 35517588
Sorry, I didn't mean to add the "Sharing" keyword above.  Please ignore that.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35517617
Sorry I meant to modify the code that I posted above. Also, you may have to include the namespace



Dim cc_new As New GlobalFunctions.CallingClass
Public Class CallingClass
        Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class

Open in new window

0
 

Author Comment

by:ironryan77
ID: 35517691
Thank you!  This is what I tried, which I think is the same as ur code above.  But now I get the same error I had initially...Maybe it's overwriting data in the table?
	        Dim gf As New GlobalFunctions.CallingClass
	        DSProductData = gf.GlobalF2.ComplaintTrendingDrillDown3p(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber)
...
    Public Class CallingClass
        Public GlobalF2 As New GlobalF
        'Public Function CallingClass(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String)
        '    Dim cc_new As New CallingClass()
        'End Function

        Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class

Open in new window

And the error:
System.ArgumentException: Column 'QXP_SHORT_DESC' does not belong to table Table.
The offending line:
If pException("QXP_SHORT_DESC") = TheCategory Then
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35517723
>Maybe it's overwriting data in the table?
It is filling a dataset so not sure what overwriting it is doing.

>System.ArgumentException: Column 'QXP_SHORT_DESC' does not belong to table Table

That's because that column is not included in your Select statement.
0
 

Author Comment

by:ironryan77
ID: 35517754
That's what I thought initially, but it is and according to the URL in my description it is overwriting this dataset.  This is my stored proc:
ALTER PROCEDURE [dbo].[ComplaintTrendingDrillDown3p]
@FirstMonthDate DATETIME,
@LastMonthDate DATETIME,
@TheLevel INT,
@ProductGroup VARCHAR(255),
@TheCategory VARCHAR(255),
@ListNumber VARCHAR(50)

AS
	SET NOCOUNT ON;

DECLARE @SelectedLevels table (LevelId int not null primary key) 
declare @OneYearAgo datetime
set @OneYearAgo = dateadd(year, -1, @FirstMonthDate)

IF @TheLevel = 3 
BEGIN
INSERT INTO @SelectedLevels (LevelId) VALUES (1)
INSERT INTO @SelectedLevels (LevelId) VALUES (2)
END
ELSE if @TheLevel = 5 
BEGIN
INSERT INTO @SelectedLevels (LevelId) VALUES (0)
INSERT INTO @SelectedLevels (LevelId) VALUES (1)
INSERT INTO @SelectedLevels (LevelId) VALUES (2)
END
ELSE
BEGIN 
INSERT INTO @SelectedLevels (LevelId) VALUES (@TheLevel) 
END

SELECT 
	count(distinct a.QXP_EXCEPTION_NO) AS QXP_EXCEPTION_NO,  
	left(datename(month, a.QXP_REPORT_DATE), 3) + ' ''' + 
	right(datename(year, a.QXP_REPORT_DATE), 2) AS MonthYear   ,
	CASE WHEN a.QXP_SHORT_DESC = @TheCategory OR ISNULL(@TheCategory, '') = '' THEN 1 ELSE 0 END AS SELECTED_CATEGORY, 
	e.new_modes 'QXP_SHORT_DESC '

FROM ALL_COMPLAINTS a   
INNER JOIN @SelectedLevels F ON A.[LEVEL] = F.LevelId
INNER JOIN CT_ProductFailures e ON e.old_modes = a.qxp_short_desc
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 >= @OneYearAgo AND 
	a.QXP_REPORT_DATE <= @LastMonthDate AND a.QXP_SHORT_DESC <> 'Design Control'  
	AND (c.QXP_EXCEPTION_TYPE <> 'Non-Diagnostic' OR c.QXP_EXCEPTION_TYPE IS NULL)
	AND PRODUCT_GROUP= @ProductGroup   
	AND (PRODUCT_CODE_STD = @ListNumber OR ISNULL(@ListNumber, '') = '')
  GROUP BY left(datename(month, a.QXP_REPORT_DATE), 3) + ' ''' + right(datename(year, a.QXP_REPORT_DATE), 2) , e.new_modes, a.QXP_EXCEPTION_NO, a.qxp_short_desc

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35688255
>but it is and according to the URL in my description it is overwriting this dataset.
I have still no idea what you are talking about. Is it overwriting something in your database? How can it do that? Is it overwriting some variable in your program? Which one?
0
 

Author Comment

by:ironryan77
ID: 35689222
@CodeCruiser, Did u look at the URL?  This explains the overwriting.  It was because originally I used the "Shared" keyword and so it was inadvertently overwriting on subsequent uses of this function.  The URL explains it.  All I know is it is for certain overwriting the QXP_SHORT_DESC variable.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35689286
> All I know is it is for certain overwriting the QXP_SHORT_DESC variable.
Where is that variable?

A shared function can be called without creating an instance of the class.
An instance function can only be called by creating an instance of the class.

I still have no idea about overwriting though.
0
 

Author Comment

by:ironryan77
ID: 35689353
@CodeCruiser, please please read that URL!  
That variable is in:
                              Case "CT1"
                                  NumExp = NumExp + pException("QXP_EXCEPTION_NO")
                                  If pException("QXP_SHORT_DESC") = TheCategory Then
                                      NumCriteria = NumCriteria + pException("QXP_EXCEPTION_NO")
                                  End If
where pException is a DataSet.  This code is in the main ASPX file, which calls ComplaintTrendingDrillDown3p.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35689401
Which URL are we talking about again? The one in your question?

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35689437
What is special in that link? It is saying that you are trying to call an instance method of a class without creating an instance of the class.

Show us your full code and explain what you are trying to do please.
0
 

Author Comment

by:ironryan77
ID: 35689500
Thank you, @CodeCruiser.  Maybe my code will explain more.  Here is my entire 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" %>
<%@ Register TagPrefix="chart" Namespace="ChartDirector" Assembly="netchartdir" %>
<%@ Register TagPrefix="ew" Namespace="eWorld.UI" Assembly="eWorld.UI" %>

<HTML>
	<SCRIPT LANGUAGE="VB" RUNAT="Server">
		Sub Page_Load(Sender as Object, E as EventArgs)
			If Not IsPostback Then 
				
			End If
			
			Main
		End Sub
		
		Sub Main()			
			'------------------------- Query database and get arrays for the chart and bind query results to datagrid ----------------------------------------
			
			Dim LastDate as date = Request("LastMonthDate")
	        Dim FirstMonthDate As Date = CDate((LastDate.Month) & "/1/" & Year(LastDate)).AddYears(-1).ToString("MM/dd/yyyy")
	        Dim TheFirstMonthDate As Date = CDate((LastDate.Month) & "/1/" & Year(LastDate)).AddYears(-1).ToString("MM/dd/yyyy")
	        Dim LastMonthDate As Date = GlobalFunctions.GlobalF.MonthLastDate(CDate((LastDate.Month) & "/1/" & Year(LastDate)).ToString("MM/dd/yyyy"))
	        Dim SelectedLevel As String = Request("SelectedLevel")
	        Dim TheLevel As Integer
	        Dim TitleLevel As String
	        Dim dv As New DataView()
	        
			
			Select Case SelectedLevel
	            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 ProductGroup as String
			Dim ProductGroupQuery as String
	        Dim productSQL As String
	        Dim FilterSQL As String
	        Dim TheCategory As String = ""
			Dim CategoryQuery as String
			Dim pException As DataRow
			Dim y as Integer = 0
			Dim NumExp as Integer = 0
			Dim NumCriteria as Integer = 0
			Dim TotalCriteria as Integer = 0
			Dim TotalExp as Integer = 0
			Dim arrNumExp() as Double
			Dim arr_p() as Double
			Dim arrNumCriteria() as Double
			Dim arrXaxis() as String
			Dim monthyear As String
			Dim TheMonth As String
			Dim TheYear As String
	        Dim ListNumber As String = ""
			Dim ThefileName as String = Request("ThefileName")
					
	      Select ThefileName
	            Case "CT"
	                ProductGroup = Request("xLabel")
	                
	            Case "CT1"
	                ProductGroup = Request("ProductGroup")
	                TheCategory = Request("xLabel")
	                
	            Case "CT2"
	                ProductGroup = Request("ProductGroup")
	                TheCategory = Request("TheCategory")
	                ListNumber = Request("xLabel")
               
	        End Select
	                	
	        Dim DSProductData As New System.Data.DataSet
	        
	        Dim gf As New GlobalFunctions.CallingClass
	        DSProductData = gf.GlobalF2.ComplaintTrendingDrillDown3p(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber)
	        
	        Dim arrMonthYear As New ArrayList()
				
	        'Get the month range 
	        While FirstMonthDate <= LastMonthDate
	            arrMonthYear.Add(FirstMonthDate.ToString("MMM") & " '" & FirstMonthDate.ToString("yy"))
	            FirstMonthDate = FirstMonthDate.AddMonths(1)
	            If FirstMonthDate > Today Then
	                Exit While
	            End If
	        End While

	        For Each monthyear In arrMonthYear
	    	
	            For Each pException In DSProductData.Tables(0).Rows
	                
	                If pException("MonthYear") = monthyear Then
	                    Select Case ThefileName
	                        Case "CT"
	                            NumExp = NumExp + pException("QXP_EXCEPTION_NO")
	                            If pException("PRODUCT_GROUP") = ProductGroup Then
	                                NumCriteria = NumCriteria + pException("QXP_EXCEPTION_NO")
	                            End If
	                            
	                        Case "CT1"
	                            NumExp = NumExp + pException("QXP_EXCEPTION_NO")
	                            If pException("QXP_SHORT_DESC") = TheCategory Then
	                                NumCriteria = NumCriteria + pException("QXP_EXCEPTION_NO")
	                            End If
	                            
	                        Case "CT2"
	                            NumExp = NumExp + pException("QXP_EXCEPTION_NO")
	                            If pException("PRODUCT_CODE_STD") = ListNumber Then
	                                NumCriteria = NumCriteria + pException("QXP_EXCEPTION_NO")
	                            End If
	                    End Select
	                End If
	            Next
		    	
	            TotalCriteria = TotalCriteria + NumCriteria
	            TotalExp = TotalExp + NumExp
		    	
	            ReDim Preserve arr_p(y)
	            If NumExp = 0 Then
	                arr_p(y) = 0
	            Else
	                arr_p(y) = Round(NumCriteria / NumExp, 3)
	            End If
	           				
	            ReDim Preserve arrNumCriteria(y)
	            arrNumCriteria(y) = NumCriteria
	            NumCriteria = 0
		    	
	            ReDim Preserve arrNumExp(y)
	            arrNumExp(y) = NumExp
	            NumExp = 0
				
	            ReDim Preserve arrXaxis(y)
	            arrXaxis(y) = monthyear

	            y = y + 1
	        Next
	        '---------------------- Statistical Calculations --------------------------------------------
	        Dim Average_p As Double
	        Dim AverageExp As Double
	        Dim UCL As Double
	        Dim LCL As Double
	        Dim IndivUpYScale As Double
	        Dim IndivLoYScale As Double
	        Dim IndivYScaleDiff As Double
	        Dim arrUCL(UBound(arr_p)) As Double
	        Dim arrLCL(UBound(arr_p)) As Double
						
	        Average_p = Round(TotalCriteria / TotalExp, 3)
	        AverageExp = Round(TotalExp / y, 3)
			
	        UCL = Round(Average_p + 3 * (Sqrt((Average_p * (1 - Average_p)) / AverageExp)), 3)
	        LCL = Round(Average_p - 3 * (Sqrt((Average_p * (1 - Average_p)) / AverageExp)), 3)

	        For y = 0 To UBound(arr_p)
	            arrUCL(y) = Round(arr_p(y) + 3 * (Sqrt((arr_p(y) * (1 - arr_p(y))) / arrNumExp(y))), 3)
	            arrLCL(y) = Round(arr_p(y) - 3 * (Sqrt((arr_p(y) * (1 - arr_p(y))) / arrNumExp(y))), 3)
	        Next
			
	        If LCL < 0 Then
	            LCL = 0
	        End If
			
	        For y = 0 To UBound(arr_p)
	            If IndivUpYScale < arr_p(y) Then
	                IndivUpYScale = arr_p(y)
	            End If
	        Next
	        
	        If IndivUpYScale < UCL Then
	            IndivUpYScale = UCL
	        End If
			
	        If LCL < 0 Then
	            IndivLoYScale = LCL
	        End If

	        IndivUpYScale = Round(IndivUpYScale + (IndivUpYScale * 0.1), 3)
	        IndivLoYScale = Round(IndivLoYScale - (IndivLoYScale * 0.1), 3)
			
	        If IndivUpYScale > 20 Then
	            IndivYScaleDiff = 5
	        Else
	            If IndivUpYScale - IndivLoYScale > 0.5 Then
	                IndivYScaleDiff = 0.1
	            Else
	                IndivYScaleDiff = 0.02
	            End If
	        End If
			
	        '------------------- If outside limits -------------------	
	        
	        Dim IndivOutOfRange(UBound(arr_p)) As Double
					    		
	        For y = 0 To UBound(arr_p)
	            If arr_p(y) > UCL Or arr_p(y) < LCL Then
	                IndivOutOfRange(y) = arr_p(y)
	            Else
	                IndivOutOfRange(y) = Chart.NoValue
	            End If
	        Next
	        
	        '------------------------ 6 increasing in a row trend -------------------------
	        
	        Dim IncreaseTrend(UBound(arr_p)) As Double
	        Dim DecreaseTrend(UBound(arr_p)) As Double
	        Dim numTrend As Integer
	        Dim z As Integer
	        Dim theprev As Double
	        Dim TrendLimit As Double
	        Dim TREND_LIMIT As Double = 6

	        For y = 0 To UBound(arr_p) 'this is to highlight a trend of increasing 6
	            numTrend = 0
	            z = y
	            If y = 0 Then
	                theprev = -1
	            Else
	                theprev = arr_p(y - 1)
	            End If
				
	            Do Until arr_p(z) <= theprev
	                numTrend = numTrend + 1
	                theprev = arr_p(z)
	                z = z + 1
	                If z > UBound(arr_p) Then
	                    Exit Do
	                End If
	            Loop
				
	            If y <> 0 Then
	                TrendLimit = TREND_LIMIT - 1
	            Else
	                TrendLimit = TREND_LIMIT
	            End If
				
	            If numTrend >= TrendLimit Then
	                If y <> 0 Then
	                    IncreaseTrend(y - 1) = arr_p(y - 1)
	                End If
	                Do Until y = z
	                    IncreaseTrend(y) = arr_p(y)
	                    y = y + 1
	                Loop
	                y = y - 1
	            Else
	                IncreaseTrend(y) = Chart.NoValue
	            End If
	        Next
			
	        '-------------------- 6 decreasing in a row trend -------------
			
	        For y = 0 To UBound(arr_p) 'this is to highlight a trend of decreasing 6
	            numTrend = 0
	            z = y
	            If y = 0 Then
	                theprev = -1
	            Else
	                theprev = arr_p(y - 1)
	            End If
				
	            Do Until arr_p(z) >= theprev
	                numTrend = numTrend + 1
	                theprev = arr_p(z)
	                z = z + 1
	                If z > UBound(arr_p) Then
	                    Exit Do
	                End If
	            Loop
				
	            If y <> 0 Then
	                TrendLimit = TREND_LIMIT - 1
	            Else
	                TrendLimit = TREND_LIMIT
	            End If
				
	            If numTrend >= TrendLimit Then
	                If y <> 0 Then
	                    DecreaseTrend(y - 1) = arr_p(y - 1)
	                End If
	                Do Until y = z
	                    DecreaseTrend(y) = arr_p(y)
	                    y = y + 1
	                Loop
	                y = y - 1
	            Else
	                DecreaseTrend(y) = Chart.NoValue
	            End If
	        Next
			
	        Dim c As XYChart = New XYChart(1200, 500)
			
	        ' Set the plotarea at (30, 20) and of size 200 x 200 pixels
	        c.setPlotArea(100, 40, 600, 400, &HFFFFFF, &HF8F8F8, Chart.Transparent, _
	               c.dashLineColor(&HCCCCCC, Chart.DotLine), c.dashLineColor(&HCCCCCC, _
	               Chart.DotLine))
	        ' Add a bar chart layer using the given data
	        Call c.addScatterLayer(Nothing, IndivOutOfRange, "", Chart.SquareSymbol, 7, &HFF0000)
	        Call c.addScatterLayer(Nothing, IncreaseTrend, "", Chart.SquareSymbol, 7, &HFF0000)
	        'Call c.addScatterLayer(Nothing, DecreaseTrend, "", Chart.SquareSymbol, 7, &HFF0000)
	        'c.addStepLineLayer(arrUCL, &HFF0000, "UCL") 'this is for the monthly UCL
	        Dim layer As LineLayer = c.addLineLayer2(Chart.Side, 0)
	        layer.addDataSet(arr_p, &H8080FF, "p").setDataSymbol(Chart.SquareSymbol, 7)
	        c.yAxis().addMark(UCL, &HFF0000, "UCL = " & UCL).setLineWidth(2)
	        c.yAxis().addMark(Average_p, &HFF00, "Avg =" & Average_p).setLineWidth(2)
			
	        If LCL <> 0 Then
	            c.yAxis().addMark(LCL, &HFF0000, "LCL = " & LCL).setLineWidth(2)
	        End If

	        c.yAxis().setTitle("p-value")
	        c.xAxis().setTitle("Month")
			
	        Dim yValue As Integer = 100
			
	        If ProductGroup <> "" Then
	            c.addText(705, yValue, "Product Group: " & ProductGroup, "timesbi.ttf", 12, &H0, Chart.Left)
	            yValue = yValue + 30
	        End If
			
	        If TitleLevel <> "" Then
	            c.addText(705, yValue, "Level: " & TitleLevel, "timesbi.ttf", 12, &H0, Chart.Left)
	            yValue = yValue + 30
	        End If
			
	        If TheCategory <> "" Then
	            c.addText(705, yValue, "Failure Mode: " & TheCategory, "timesbi.ttf", 12, &H0, Chart.Left)
	            yValue = yValue + 30
	        End If
			    
	        If ListNumber <> "" Then
	            c.addText(705, yValue, "Part Number: " & ListNumber, "timesbi.ttf", 12, &H0, Chart.Left)
	            yValue = yValue + 30
	        End If
			
	        c.yAxis().setLinearScale(IndivLoYScale, IndivUpYScale, IndivYScaleDiff)
	        layer.setBorderColor(Chart.Transparent)
	        layer.setDataLabelStyle("", 8, &H0, 0)
					
	        ' Set the labels on the x axis.
	        c.xAxis().setLabels(arrXaxis)
	        c.xAxis().setLabelStyle("", 8, &H0, 45)
	        'c.addTitle(TheCategory & " " & TitleLevel & " for " & ProductGroup & " Part Number: " & ListNumber, "Times New Roman Bold Italic", 18)
			
	        WebChartViewer1.Image = c.makeWebImage(Chart.PNG)
	        WebChartViewer1.ImageMap = c.getHTMLImageMap("ComplaintTrendingClk4_Ryan.aspx?TheCategory=" & _
	  TheCategory & "&ListNumber=" & ListNumber & "&SelectedLevel=" & SelectedLevel & "&ThefileName=" & ThefileName & "&ProductGroup=" & ProductGroup, "", "title='{xLabel}: {value}'")
        	
	        '--------------------- Table ---------------------------------------------------------------------------
	        Dim dt As DataTable
	        Dim dr As DataRow
	        Dim x As Integer
            
	        Dim arrayTableTitle(4) As String
	        arrayTableTitle(0) = "p-value"
	        arrayTableTitle(1) = "Selected Complaints"
	        arrayTableTitle(2) = "Total Complaints"
	        'arrayTableTitle(3) = "UCL"
	        'arrayTableTitle(4) = "LCL"
            
	        dt = New DataTable
	        dt.Columns.Add(New DataColumn("   "))
            
	        For y = 0 To UBound(arrXaxis)
	            dt.Columns.Add(New DataColumn(arrXaxis(y)))
	        Next
                        
	        dr = dt.NewRow()
	        dr(0) = "p-value"
	        For x = 0 To UBound(arrXaxis)
	            dr(x + 1) = arr_p(x)
	        Next
	        dt.Rows.Add(dr)
            
	        dr = dt.NewRow()
	        dr(0) = "Selected Complaints"
	        For x = 0 To UBound(arrXaxis)
	            dr(x + 1) = arrNumCriteria(x)
	        Next
	        dt.Rows.Add(dr)
            
	        dr = dt.NewRow()
	        dr(0) = "Total Complaints"
	        For x = 0 To UBound(arrXaxis)
	            dr(x + 1) = arrNumExp(x)
	        Next
	        dt.Rows.Add(dr)
	        
	        dv = New DataView(dt)
	        DataTable.DataSource = dv
	        DataTable.DataBind()
       				
	        '-------------------- Create Table for Details ---------------------------------------------------
				                        
	        Dim DSDetails As New System.Data.DataSet
	        DSDetails = GlobalFunctions.GlobalF.ComplaintTrendingDrillDownDetails3p(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber)
	        dgTable.DataSource = DSDetails
	        dgTable.DataBind()
	    End Sub
		
		Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
	        GlobalFunctions.GlobalF.DataGridToExcel(dgTable, Response)
	    End Sub
		
		Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs)
	        If e.Item.ItemType = System.Web.UI.WebControls.ListItemType.Item Or _
	         e.Item.ItemType = System.Web.UI.WebControls.ListItemType.AlternatingItem Then
				          
	            Dim drv As DataRowView = CType(e.Item.DataItem, DataRowView)
	            Dim QXP_EXCEPTION_NO As TableCell = e.Item.Cells(0)
	            Dim DOC_DOCUMENT_NO As TableCell = e.Item.Cells(12)
	            Dim link As HyperLink = CType(QXP_EXCEPTION_NO.Controls(0), HyperLink)
	            Dim link1 As HyperLink = CType(DOC_DOCUMENT_NO.Controls(0), HyperLink)
	            
	            If Not drv("QXP_ID") Is System.DBNull.Value Then
	                link.NavigateUrl = "http://10.248.110.37/QMS/CXP_CUSTOMER_PXP_MAIN.aspx?ItmID=" + drv("QXP_ID").ToString
	            Else
	                link.NavigateUrl = "http://gsr.add.abbott.com/gsr/GSR_Includes/TicketDisplay_Load.php?country=" + drv("COUNTRY_CODE").ToString + "&center=" + drv("CENTER_NUM").ToString + "&ticket=" + drv("TICKET_NUM").ToString
	            End If
	            
	            If Not drv("IRF_QEI_ID") Is System.DBNull.Value Then
	                link1.NavigateUrl = "http://10.248.110.37/QMS/QEI_QEX_ISSUE_MAIN_FRAME.aspx?ItmID=" + drv("IRF_QEI_ID").ToString
	            End If
	            
	        End If
	    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">
            Complaint Trending
        </div>
        <hr style="color:#000080"/>
		<form runat="Server" method="post" id="Form1">
			<chart:WebChartViewer id="WebChartViewer1" runat="server" HorizontalAlign="Center" /><br />
			<asp:DataGrid id="DataTable" runat="server" BorderColor="black" BorderWidth="1"
                GridLines="Both" CellPadding="3" CellSpacing="0" HeaderStyle-BackColor="#aaaadd"/><br />
            <span onclick="busyBox.Enabled = false;"> 
			    <asp:Button id="btnExport" runat="server" Text="Export List to Excel" onclick="btnExport_Click" />
			</span>
			<ASP:DATAGRID id="dgTable" runat="server" autogeneratecolumns="False" ShowHeader="true" OnItemDataBound="DataGrid1_ItemDataBound" >
					<AlternatingItemStyle BackColor = "#eeeeee" />
					<HEADERSTYLE BackColor = "#336699" ForeColor = "#ffffff" Font-Bold = "true" />
					<COLUMNS>
						<ASP:HyperLinkColumn HEADERTEXT="Complaint No." DATATEXTFIELD="QXP_EXCEPTION_NO" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Level" DATAFIELD="LEVEL" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="pRE" DATAFIELD="pRE" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Entered Date" DATAFIELD="QXP_REPORT_DATE" READONLY="true" DataFormatString="{0:d}" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Complete Date" DATAFIELD="CLOSE_DATE" READONLY="true" DataFormatString="{0:d}" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Product Code" DATAFIELD="EPA_PRD_CODE" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Product Name" DATAFIELD="EPA_PRD_NAME" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Lot No." DATAFIELD="EPL_LOT_NUMBER" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Short Description" DATAFIELD="QXP_SHORT_DESC" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Description" DATAFIELD="QXP_DESCRIPTION" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Product Group" DATAFIELD="PRODUCT_GROUP" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="Country" DATAFIELD="Country" READONLY="true" />
						<ASP:HyperLinkColumn HEADERTEXT="Issue No." DATATEXTFIELD="DOC_DOCUMENT_NO" />
						<ASP:BOUNDCOLUMN HEADERTEXT="QXP_ID" Visible="false" DATAFIELD="QXP_ID" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="COUNTRY" Visible="false" DATAFIELD="COUNTRY_CODE" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="CENTER" Visible="false" DATAFIELD="CENTER_NUM" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="TICKET" Visible="false" DATAFIELD="TICKET_NUM" READONLY="true" />
						<ASP:BOUNDCOLUMN HEADERTEXT="IRF_QEI_ID" Visible="false" DATAFIELD="IRF_QEI_ID" READONLY="true" />
					</COLUMNS>
				</ASP:DATAGRID>
			<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>

Open in new window


And this is all of the relevant parts of my global.vb code:
Imports System.Data
Imports System.Data.OLEDB
Imports System.Web
Imports System.Math
Imports system.data.SqlClient

Namespace GlobalFunctions
	Public Class GlobalF
Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
...
    End Class

    Public Class CallingClass
        Public GlobalF2 As New GlobalF
        'Public Function CallingClass(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String)
        '    Dim cc_new As New CallingClass()
        'End Function

        Public Function ComplaintTrendingDrillDown3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDown3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function
    End Class

End Namespace

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35689594
Now is the problem in following section?

                    Case "CT1"
                                  NumExp = NumExp + pException("QXP_EXCEPTION_NO")
                                  If pException("QXP_SHORT_DESC") = TheCategory Then
                                      NumCriteria = NumCriteria + pException("QXP_EXCEPTION_NO")
                                  End If


Do you mean NumCriteria is being overwritten?
0
 

Author Comment

by:ironryan77
ID: 35689722
No, I think QXP_SHORT_DESC is overwritten.  Maybe NumCriteria.  I'm not sure.  But error is on QXP_SHORT_DESC (See above).  Yes, that is where the problem is.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35689776
You are referring to this error?
>Column 'QXP_SHORT_DESC' does not belong to table Table.

Modify this part of sp

CASE WHEN a.QXP_SHORT_DESC = @TheCategory OR ISNULL(@TheCategory, '') = '' THEN 1 ELSE 0 END AS SELECTED_CATEGORY,
      e.new_modes 'QXP_SHORT_DESC '


to

CASE WHEN a.QXP_SHORT_DESC = @TheCategory OR ISNULL(@TheCategory, '') = '' THEN 1 ELSE 0 END AS SELECTED_CATEGORY,
      e.new_modes, QXP_SHORT_DESC
0
 

Author Comment

by:ironryan77
ID: 35689933
Thank you, @CodeCruiser!  That deleted the error!  However, now my WebchartViewer chart is showing up empty for SELECTED RECORDS.  So I guess problem was that I wasn't selecting QXP_SHORT_DESC.  But now SELECTED RECORDS shows up as all zeros in my chart even though when I run this SP from QA, I get 1's and 0's, so there should be some non-zero values.  From my main ASPX code above, can you see where this piece of logic is missing?
0
 

Author Comment

by:ironryan77
ID: 35689959
Sorry, it should be SELECTED_CATEGORY, not SELECTED RECORDS.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 35690092
So the Selected_Category shows correct values from stored procedure but no on chart? Post that relevant code here as the code above is too long for me to read through.
0
 

Author Comment

by:ironryan77
ID: 35690735
Yes, Selected_Category shows correct values from stored procedure but no on chart.  OK, relevant code for this I believe is below.  But I don't have a super good understanding of how this chart works.
Now this chart shows all ones.  But the cause is my stored proc in SQL.  I am going to accept your solution meanwhile.  Thank you so much!
0
 

Author Closing Comment

by:ironryan77
ID: 35690741
He was super good to me!  And knowledgeable on principles of OOP!
0
 

Author Comment

by:ironryan77
ID: 35694466
I don't know if u are still online, @CodeCruiser, but although this SP returns data in query analyzer, it is still appearing empty on ASPX page.
SQL Stored Proc:
ALTER PROCEDURE [dbo].[ComplaintTrendingDrillDownDetails3p]
--DECLARE 
@FirstMonthDate DATETIME,
@LastMonthDate DATETIME,
@TheLevel INT,
@ProductGroup VARCHAR(255),
@TheCategory VARCHAR(255),
@ListNumber VARCHAR(50)

AS
	SET NOCOUNT ON;

--ComplaintTrendingDrillDownDetails3p '2/1/11', '2/28/11 23:59:59', 3 , 'Debit Memo', 'am shipping error', ''
--SET @FirstMonthDate = DATEADD(mm, DATEDIFF(mm,0,@LastMonthDate), 0)
--declare @OneYearAgo datetime
--set @OneYearAgo = dateadd(year, -1, @FirstMonthDate)

DECLARE @SelectedLevels table (LevelId int not null primary key) 

IF @TheLevel = 3 
BEGIN
INSERT INTO @SelectedLevels (LevelId) VALUES (1)
INSERT INTO @SelectedLevels (LevelId) VALUES (2)
END
ELSE if @TheLevel = 5 
BEGIN
INSERT INTO @SelectedLevels (LevelId) VALUES (0)
INSERT INTO @SelectedLevels (LevelId) VALUES (1)
INSERT INTO @SelectedLevels (LevelId) VALUES (2)
END
ELSE
BEGIN 
INSERT INTO @SelectedLevels (LevelId) VALUES (@TheLevel) 
END

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 
INNER JOIN @SelectedLevels F ON A.[LEVEL] = F.LevelId
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
	--a.QXP_REPORT_DATE >= @OneYearAgo AND a.QXP_REPORT_DATE <= @LastMonthDate 
	AND (PRODUCT_GROUP = @ProductGroup or @ProductGroup = '' or @ProductGroup is null) 
	and (a.QXP_SHORT_DESC = @TheCategory OR ISNULL(@TheCategory, '') = '') 
	AND (PRODUCT_CODE_STD = @ListNumber OR ISNULL(@ListNumber, '') = '')
	AND (a.QXP_SHORT_DESC <> 'Design Control')   
	AND (d.QXP_EXCEPTION_TYPE <> 'Non-Diagnostic' OR d.QXP_EXCEPTION_TYPE IS NULL)   
ORDER BY 4

Open in new window


And the relevant part of my ComplaintTrendingClk3p_om.aspx file:
	        Dim DSDetails As New System.Data.DataSet
	        DSDetails = GlobalFunctions.GlobalF.ComplaintTrendingDrillDownDetails3p(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber)

	        dgTable.DataSource = DSDetails
	        dgTable.DataBind()
	    End Sub
		
		Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
	        GlobalFunctions.GlobalF.DataGridToExcel(dgTable, Response)
	    End Sub
		
		Sub DataGrid1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs)
	        If e.Item.ItemType = System.Web.UI.WebControls.ListItemType.Item Or _
	         e.Item.ItemType = System.Web.UI.WebControls.ListItemType.AlternatingItem Then
				          
	            Dim drv As DataRowView = CType(e.Item.DataItem, DataRowView)
	            Dim QXP_EXCEPTION_NO As TableCell = e.Item.Cells(0)
	            Dim DOC_DOCUMENT_NO As TableCell = e.Item.Cells(12)
	            Dim link As HyperLink = CType(QXP_EXCEPTION_NO.Controls(0), HyperLink)
	            Dim link1 As HyperLink = CType(DOC_DOCUMENT_NO.Controls(0), HyperLink)
	            
	            If Not drv("QXP_ID") Is System.DBNull.Value Then
	                link.NavigateUrl = "http://10.248.110.37/QMS/CXP_CUSTOMER_PXP_MAIN.aspx?ItmID=" + drv("QXP_ID").ToString
	            Else
	                link.NavigateUrl = "http://gsr.add.abbott.com/gsr/GSR_Includes/TicketDisplay_Load.php?country=" + drv("COUNTRY_CODE").ToString + "&center=" + drv("CENTER_NUM").ToString + "&ticket=" + drv("TICKET_NUM").ToString
	            End If
	            
	            If Not drv("IRF_QEI_ID") Is System.DBNull.Value Then
	                link1.NavigateUrl = "http://10.248.110.37/QMS/QEI_QEX_ISSUE_MAIN_FRAME.aspx?ItmID=" + drv("IRF_QEI_ID").ToString
	            End If
	            
	        End If
	    End Sub

Open in new window


And this call is to the following function in my global.vb file:

        Public Shared Function ComplaintTrendingDrillDownDetails3p(ByVal FirstMonth As DateTime, ByVal LastMonth As DateTime, ByVal rowLevel As Integer, ByVal productGroup As String, ByVal category As String, ByVal ListNumber As String) As DataSet
            Dim DSPageData As New System.Data.DataSet
            Dim param(5) 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
            param(3) = New SqlParameter("@ProductGroup", SqlDbType.Varchar)
            param(3).Value = productGroup
            param(4) = New SqlParameter("@TheCategory", SqlDbType.Varchar)
            param(4).Value = category
            param(5) = New SqlParameter("@ListNumber", SqlDbType.Varchar)
            param(5).Value = ListNumber

            ''# 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("ComplaintTrendingDrillDownDetails3p", conn), _
            da As New SQLDataAdapter(cmd)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddRange(param)

                da.Fill(DSPageData)
            End Using

            Return DSPageData
        End Function

Open in new window


(I removed the CalledClass class.  So it is just the GlobalF class now.)
I do not get any errors when I view this ASPX page, but although I get data back in the chart part, which comes from DSProductData = GlobalFunctions.GlobalF.ComplaintTrendingDrillDown3p(FirstMonthDate, LastMonthDate, TheLevel, ProductGroup, TheCategory, ListNumber),
I do not get any data back for this function.  Although it should be using same variables and although using these variables it does generate data records in query analyzer.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 35696913
Step through the above function and see what's up with DSPageData dataset. See if it contains any rows.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question