I am currently maintaining an ASP .NET application that connects to a SQL Server 2000 database. Everything has worked fine (as far as I know) until now.

When I open a view in SQL Server, all data is returned correctly. When I open this same view with a DataReader or DataAdapter (tried both to see if one or the other was a problem), I am able to view the correct results as well. The problem occurs when I bind the resulting datareader or datatable to a datagrid. I have a column that should have information in it (as it shows up in SQL Server or the Watch Window in Visual Studio), but the DataGrid displays the column as empty.

There are no errors that occur: The app uses On Error GoTo, so when I debug I have break points at every function and subroutine. I have also walked through the debug step-by-step. There are no problems at all, and like I said before, I can view the table and datareader contents in the watch window and it shows up correctly.

Here is the Databind code:

        Dim objGF As New GeneralFunctions
        Dim ReturnedResults As New DataTable
        ReturnedResults = objGF.GetDataSet(Global.gConn, strSQL) ' Returns a datatable from a dataadapter

        If ReturnedResults.Rows.Count < 1 Then
            'turn off datagrid
            OurDataGrid.Visible = False
            lblResults.Text = "No Entries found in the Database."
        Else
            'bind data
            lblResults.Text = ""
            OurDataGrid.Visible = True
            OurDataGrid.DataSource = ReturnedResults 'setup <asp:datagrid> tag
            OurDataGrid.DataBind() 'attach data from db to asp datagrid
        End If




Here's the function for filling the DataSet

Public Function GetDataSet(ByVal Conn As String, ByVal QueryStr As String) As DataTable
        On Error GoTo ErrHand

        'setup SQL db connection
        Dim OurConnection As New OleDbConnection 'connection
        Dim OurCommand As OleDbCommand 'sql command
        Dim OurDataAdapter As New OleDbDataAdapter 'results from sql statement
        Dim dsResults As New DataTable

        OurConnection = New OleDbConnection(Conn) 'set sql string to Conn
        OurCommand = New OleDbCommand(QueryStr, OurConnection) 'setup sql command
        OurDataAdapter.SelectCommand = OurCommand
        OurDataAdapter.Fill(dsResults)

        Return dsResults

ExitFunct:
        If Not OurCommand Is Nothing Then
            OurCommand = Nothing
        End If
        If Not OurDataAdapter Is Nothing Then
            OurDataAdapter = Nothing
        End If
        If Not OurConnection Is Nothing Then
            OurConnection = Nothing
        End If

        Exit Function

ErrHand:
        Resume ExitFunct

    End Function



Any help will be appreciated. I have searched Google and Microsoft for any possible known issues with the DataGrid displaying contents incorrectly, but with no results.

Thanks.

Do you see data in other columns and some are blank? Or is the whole grid blank?
Jim

The whole column is blank, but it is just the one column. There are 17 fields in each record, but just one is not showing up correctly. If certain criteria is not met, the view changes the returned data to 'Missing EDI information', which shows up fine in both SQL Server and the datatable within ASP.NET (using VB.NET), but is displayed incorrectly by the datagrid.

Can you post your SQL statment?

Here is view one, which made too many reference requirements for view two. It's called vwOldMissingInfo:

SELECT TOP 100 PERCENT (cast(DATEPART(MONTH, dbo.Orders.SOShipDate)as varchar) + '/' + cast(DATEPART(DAY, dbo.Orders.SOShipDate)as varchar) + '/' + cast(DATEPART(YEAR, dbo.Orders.SOShipDate) as varchar)) as [Ship Date],
            dbo.Orders.SONum AS [Sales Order], dbo.Orders.SOLine AS [Line Item],
                      dbo.Orders.TPKey AS [Trading Partner], dbo.Orders.STID AS [Ship To ID], dbo.Orders.Part AS [Customer Part #], dbo.Part.BosePN AS [Bose Part #],
                      dbo.Orders.Dock, CASE WHEN dbo.Orders.RAN = '' AND dbo.TradingPartner.UseRan = 1 THEN 'Missing Ran Information in Orders Table' ELSE '' END as [RAN #],
         CASE WHEN dbo.TradingPartner.TPType >=2 Then dbo.Orders.RAN ELSE '' END AS [Reference Number],
            dbo.Orders.ShipToNum AS [Ship To Number], dbo.Orders.MissingInfo AS [Missing Sales Order Information],
                      ISNULL(dbo.Address.MissingInfo, 'Missing Address Record') AS [Missing Address Information], ISNULL(dbo.Part.MissingInfo, 'Missing Part Record')
                      AS [Missing Part Information], ISNULL(dbo.EDI.MissingInfo, 'Missing EDI Record') AS [Missing EDI Information],
                      CASE WHEN dbo.TradingPartner.UseRan = 1 THEN ISNULL(dbo.RAN.MissingInfo, 'Missing RAN Record') ELSE 'N/A' END AS [Missing RAN Information],
                     CASE WHEN dbo.TradingPartner.TPType >=2 and dbo.Orders.RAN not in (Select dbo.VarmsRef.Ref from dbo.VarmsRef) and dbo.VarmsRef.MissingInfo != '' Then dbo.VarmsRef.MissingInfo + 'Incompatible Ref # in Orders Table'
        WHEN dbo.VarmsRef.MissingInfo != '' THEN dbo.VarmsRef.MissingInfo
        WHEN dbo.TradingPartner.TPType >=2 and dbo.Orders.RAN not in (Select dbo.VarmsRef.Ref from dbo.VarmsRef) THEN 'Incompatible Ref # in Orders Table'
         ELSE '' END AS [Missing VarmsRef Information]


        /*dbo.VarmsRef.Ref as [Reference Number]*/
FROM dbo.Part INNER JOIN
                      dbo.TradingPartner ON dbo.Part.TPKey = dbo.TradingPartner.TPKey RIGHT OUTER JOIN
                      dbo.EDI RIGHT OUTER JOIN
                      dbo.RAN RIGHT OUTER JOIN
                      dbo.Orders ON dbo.RAN.STID = dbo.Orders.STID AND dbo.RAN.Dock = dbo.Orders.Dock AND dbo.RAN.Part = dbo.Orders.Part AND
                      dbo.RAN.RAN = dbo.Orders.RAN ON dbo.EDI.STID = dbo.Orders.STID AND dbo.EDI.Dock = dbo.Orders.Dock AND
                      dbo.EDI.Part = dbo.Orders.Part LEFT OUTER JOIN
                      dbo.Address ON dbo.Orders.ShipToNum = dbo.Address.ShiptoNum ON dbo.Part.Part = dbo.Orders.Part LEFT OUTER JOIN
                      dbo.VarmsRef ON dbo.VarmsRef.TPKey = dbo.Orders.TPKey AND dbo.VarmsRef.Part = dbo.Orders.Part AND dbo.VarmsRef.Ref = dbo.Orders.RAN
WHERE (dbo.Address.Flag = 1) OR
                      (dbo.Part.Flag = 1) OR
                      (dbo.EDI.Flag = 1) OR
                      (dbo.RAN.Flag = 1) AND (dbo.TradingPartner.UseRan = 1) OR
                      (dbo.Orders.Flag = 1) OR
                      (dbo.VarmsRef.Flag = 1) AND (dbo.TradingPartner.TPType >=2) or
             (dbo.TradingPartner.TPType >=2 )and (dbo.Orders.RAN not in (Select dbo.VarmsRef.Ref from dbo.VarmsRef) ) OR
             (dbo.TradingPartner.TPType >=2 and dbo.Orders.RAN = '')

ORDER BY dbo.Orders.SOShipDate, dbo.Orders.SONum, dbo.Orders.SOLine






Here is the second view, titled vwSuffixPartErrors. This one is basically looking to see if a part with a suffix has just the part in the EDI table. If not, it should show up as an error.:
SELECT TOP 100 PERCENT (cast(DATEPART(MONTH, dbo.Orders.SOShipDate)as varchar) + '/' + cast(DATEPART(DAY, dbo.Orders.SOShipDate)as varchar) + '/' + cast(DATEPART(YEAR, dbo.Orders.SOShipDate) as varchar)) as [Ship Date],
                dbo.Orders.SONum AS [Sales Order], dbo.Orders.SOLine AS [Line Item],
                dbo.Orders.TPKey AS [Trading Partner], dbo.Orders.STID AS [Ship To ID],
                dbo.Orders.Part AS [Customer Part #], dbo.Part.BosePN AS [Bose Part #],
                dbo.Orders.Dock,
                CASE WHEN dbo.Orders.RAN = '' AND dbo.TradingPartner.UseRan = 1
                        THEN 'Missing Ran Information in Orders Table'
                        ELSE '' END as [RAN #],
                CASE WHEN dbo.TradingPartner.TPType >=2 Then dbo.Orders.RAN
                        ELSE '' END AS [Reference Number],
                dbo.Orders.ShipToNum AS [Ship To Number],
                dbo.Orders.MissingInfo AS [Missing Sales Order Information],
                ISNULL(dbo.Address.MissingInfo, 'Missing Address Record') AS [Missing Address Information],
                ISNULL(dbo.Part.MissingInfo, 'Missing Part Record') AS [Missing Part Information],
                ISNULL(dbo.EDI.MissingInfo, 'Missing EDI Record') AS [Missing EDI Information],
                CASE WHEN dbo.TradingPartner.UseRan = 1
                        THEN ISNULL(dbo.RAN.MissingInfo, 'Missing RAN Record')
                        ELSE 'N/A' END AS [Missing RAN Information],
                CASE WHEN dbo.TradingPartner.TPType >=2 and
                    dbo.Orders.RAN not in (Select dbo.VarmsRef.Ref from dbo.VarmsRef)
                    and dbo.VarmsRef.MissingInfo != ''
                        Then dbo.VarmsRef.MissingInfo + 'Incompatible Ref # in Orders Table'
                    WHEN dbo.VarmsRef.MissingInfo != ''
                        THEN dbo.VarmsRef.MissingInfo
                    WHEN dbo.TradingPartner.TPType >=2 and dbo.Orders.RAN not in (Select dbo.VarmsRef.Ref from dbo.VarmsRef)
                        THEN 'Incompatible Ref # in Orders Table'
                        ELSE '' END AS [Missing VarmsRef Information]





FROM dbo.CustPNSuffix right join
            dbo.Part on (dbo.Part.usesuffix = 2 and dbo.Part.part = dbo.CustPNSuffix.part + dbo.CustPNSuffix.suffix) LEFT OUTER JOIN
            dbo.Orders on dbo.Part.part = dbo.Orders.part LEFT OUTER JOIN
            dbo.TradingPartner on dbo.Orders.TPKey = dbo.TradingPartner.TPKey LEFT OUTER JOIN
            dbo.RAN on dbo.RAN.RAN = dbo.Orders.RAN and dbo.Orders.stid = dbo.RAN.STID and dbo.RAN.Dock = dbo.Orders.Dock LEFT OUTER JOIN
            dbo.EDI on dbo.EDI.STID = dbo.Orders.STID and dbo.EDI.Dock = dbo.Orders.Dock and
                (dbo.EDI.Part = dbo.CustPNSuffix.Part or dbo.EDI.Part = dbo.Part.Part) LEFT OUTER JOIN
            dbo.Address on dbo.Orders.ShipToNum = dbo.Address.ShipToNum LEFT OUTER JOIN
            dbo.VarmsRef on dbo.VarmsRef.TPKey = dbo.Orders.TPKey and dbo.VarmsRef.Part = dbo.Orders.Part and
                dbo.VarmsRef.Ref = dbo.Orders.RAN

WHERE
            (dbo.Part.UseSuffix = 2 and dbo.Part.Part = dbo.CustPNSuffix.Part + dbo.CustPNSuffix.Suffix and
                dbo.CustPNSuffix.Part not in (select distinct(dbo.EDI.Part) from dbo.EDI))





Here is the final view, which ties the two together in order to display them as one result set. It is called vwMissingInformation:
SELECT TOP 100 PERCENT * FROM dbo.vwOldMissingInfo
UNION
SELECT * FROM dbo.vwSuffixPartErrors




At first, I thought it was using the UNION that made the results go awry in ASP.NET, but when I used just the original vsOldMissingInfo, which worked originally, it no longer works like it used to either.

I believe sometimes the very first entry in that column shows up, but subsequent entries do not.