Dataadapter.Fill方法出现问题 [英] Problem with Dataadapter.Fill method

问题描述

大家好.我正在做一个大型项目，遇到了一个问题.我在窗体上有一个DataGridView控件.该控件填充有SQLExpress数据库中的数据.我的项目完全可以正常工作，但是当用户对DataGridView中的数据进行排序时(通过单击DataGridView控件中的列标题执行排序)会出现问题.在用户对数据进行排序之前，我可以使用不同的查询一次又一次地填充DataGridView，但是在对DataGridView进行排序后，我收到对象引用未设置为对象的实例"错误.我检查了所有需要的对象以获取没有问题的数据，看来错误源在DataAdapter类的Fill方法内部.我已经花了很多时间，但无法找出错误的根源，请帮助我.

因为这是一个很大的项目，所以我无法在此处发布所有代码，而只将部分代码放入其中.

这是一个DataGridViews填充子例程:

 私有  Sub  FillData()
         Dim  sSQL  As   String  = GetQueryString() ' 这是获取查询字符串的调用
        如果 sSQL = "  然后 退出 子
        我.Cursor=游标.WaitCursor
        如果 DSet.Tables.Contains("  span>)然后
            tbl = DSet.Tables(" )
            tbl.Clear()
            tbl.Columns.Clear()
        其他
            tbl = DSet.Tables.Add(" )
        结束 如果
        如果 SqlDAL.GetTabular(sSQL，tbl)然后 '  SqlDAL是实际上从SQL数据库获取数据的类
            dgDetail.DataSource = 没什么
            dgDetail.Rows.Clear()
            dgDetail.Columns.Clear()
            dgDetail.DataSource = tbl
        其他
            MsgBox(字符串 .Format(" ，vbLf，_
                                 SqlDAL.GetLastException.Message)，MsgBoxStyle.Critical)
        结束 如果
        我.光标=光标.默认
    结束 子 

GetTabular函数.它在SqlDAL类内部定义.在此功能中，我会遇到错误.

Public Shared Function GetTabular(ByVal strSQL As String, ByRef DTable As DataTable, Optional ByVal trans As SqlTransaction = Nothing) As Boolean
        Using cn As New SqlConnection
            cn.ConnectionString = SqlDAL.ConnectionString
            Try
                cn.Open()
            Catch ex As Exception
                _Ex = ex
                Return False
            End Try
            DTable.Clear()
            Dim cmd As SqlCommand = cn.CreateCommand
            cmd.CommandText = strSQL
            cmd.CommandType = CommandType.Text
            Using DAdapter As New SqlDataAdapter(cmd)
                Try
                    DAdapter.Fill(DTable)    ''In this line of code I''m getting "Object reference not set to an instance of an object" error which occurs only if data have been sorted previously. 
                Catch ex As Exception
                    cn.Close()
                    _Ex = ex
                    Return False
                End Try
            End Using
            cn.Close()
        End Using
        Return True
    End Function 

这是StackTrace

在System.Data.Index.CompareRecords(Int32 record1，Int32 record2)
在System.Data.Index.IndexTree.CompareNode(Int32 record1，Int32 record2)
在System.Data.RBTree`1.RBInsert处(Int32 root_id，Int32 x_id，Int32 mainTreeNodeID，Int32位置，布尔值附加)
在System.Data.RBTree`1.InsertAt(Int32位置，K个项目，布尔值附加)处
在System.Data.Index.InsertRecord(Int32记录，布尔型FireEvent)中
在System.Data.Index.ApplyChangeAction(Int32记录，Int32动作，Int32 changeRecord)
在System.Data.DataTable.RecordStateChanged(Int32 record1，DataViewRowState oldState1，DataViewRowState newState1，Int32 record2，DataViewRowState oldState2，DataViewRowState newState2处)
在System.Data.DataTable.SetNewRecordWorker(DataRow行，Int32建议的记录，DataRowAction操作，布尔值isInMerge，Int32位置，布尔值fireEvent，异常和deferredException)处
在System.Data.DataTable.InsertRow(DataRow行，Int32提议的ID，Int32 pos，布尔型fireEvent)
在System.Data.DataTable.LoadDataRow(Object []值，布尔值fAcceptChanges)
在System.Data.ProviderBase.SchemaMapping.LoadDataRow()
在System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping映射)
在System.Data.Common.DataAdapter.FillFromReader(DataSet数据集，DataTable数据表，字符串srcTable，DataReaderContainer数据阅读器，Int32 startRecord，Int32 maxRecords，DataColumn parentChapterColumn，Object parentChapterValue)中
在System.Data.Common.DataAdapter.Fill(DataTable [] dataTables，IDataReader dataReader，Int32 startRecord，Int32 maxRecords)
在System.Data.Common.DbDataAdapter.FillInternal处(DataSet数据集，DataTable []数据表，Int32 startRecord，Int32 maxRecords，字符串srcTable，IDbCommand命令，CommandBehavior行为)
在System.Data.Common.DbDataAdapter.Fill处(DataTable [] dataTables，Int32 startRecord，Int32 maxRecords，IDbCommand命令，CommandBehavior行为)
在System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
在E:\ Proeqtebi \ ESoft_Pawnshop11 \ ESoft_Pawnshop \ DataAccess \ SqlDAL.vb:line 159

解决方案

<问题不在您发布的代码段之内.这就是到处都是类级变量的问题.跟踪其中的值发生了什么事情要困难得多.

无论您附加了Sort或Header Click事件的任何代码，在运行此代码之前，都将DTable设置为Nothing.

由于此GetTablar之外的其他方法都可以访问DTable变量，因此最好在尝试将其传递给Fill方法之前检查一下它是否为Nothing.

谢谢为您的答案.我也检查了DTable变量和所有其他变量，它们都更新了.我没有自己的分类代码.排序由DataGridView本身执行

请尝试这个

Private Sub FillData()
        Dim sSQL As String = GetQueryString()  ''This is a call to get a query string
        tbl = New DataTable() 
        If sSQL = "" Then Exit Sub
        Me.Cursor = Cursors.WaitCursor
        If DSet.Tables.Contains("Temp") Then
            tbl = DSet.Tables("Temp")
            tbl.Clear()
            tbl.Columns.Clear()
        Else
            tbl = DSet.Tables.Add("Temp")
        End If
        If SqlDAL.GetTabular(sSQL, tbl) Then   ''SqlDAL is a class which actually gets data from a SQL database
            dgDetail.DataSource = Nothing
            dgDetail.Rows.Clear()
            dgDetail.Columns.Clear()
            dgDetail.DataSource = tbl
        Else
            MsgBox(String.Format("Can''t fill a grid!{0}{1}", vbLf, _
                                 SqlDAL.GetLastException.Message), MsgBoxStyle.Critical)
        End If
        Me.Cursor = Cursors.Default
    End Sub

Hello to all. I''m working on a large project and have faced a problem. I have a DataGridView control on a form. This control is filled with data from a SQLExpress database. At all my project is working correctly but problem occurs when a user sorts data in a DataGridView (sorting is performed by clicking on the column headers in a DataGridView control). Before user sorts data I can fill DataGridView again and again using different queries, but I''m getting "Object reference not set to an instance of an object" error when a DataGridView have been sorted. I check all needed objects to get data they have no problem, it seems the error source is inside Fill method of the DataAdapter class. I have spend many hours but cant catch what is the error source, please help me.

Because it is the large project I can''t post all of code here and I put only part of my code.

This is a DataGridViews fill subroutine:

Private Sub FillData()
        Dim sSQL As String = GetQueryString()  'This is a call to get a query string
        If sSQL = "" Then Exit Sub
        Me.Cursor = Cursors.WaitCursor
        If DSet.Tables.Contains("Temp") Then
            tbl = DSet.Tables("Temp")
            tbl.Clear()
            tbl.Columns.Clear()
        Else
            tbl = DSet.Tables.Add("Temp")
        End If
        If SqlDAL.GetTabular(sSQL, tbl) Then   'SqlDAL is a class which actually gets data from a SQL database
            dgDetail.DataSource = Nothing
            dgDetail.Rows.Clear()
            dgDetail.Columns.Clear()
            dgDetail.DataSource = tbl
        Else
            MsgBox(String.Format("Can't fill a grid!{0}{1}", vbLf, _
                                 SqlDAL.GetLastException.Message), MsgBoxStyle.Critical)
        End If
        Me.Cursor = Cursors.Default
    End Sub

GetTabular function. It is defined inside of SqlDAL class. In this function I''m getting an errror.

Public Shared Function GetTabular(ByVal strSQL As String, ByRef DTable As DataTable, Optional ByVal trans As SqlTransaction = Nothing) As Boolean
        Using cn As New SqlConnection
            cn.ConnectionString = SqlDAL.ConnectionString
            Try
                cn.Open()
            Catch ex As Exception
                _Ex = ex
                Return False
            End Try
            DTable.Clear()
            Dim cmd As SqlCommand = cn.CreateCommand
            cmd.CommandText = strSQL
            cmd.CommandType = CommandType.Text
            Using DAdapter As New SqlDataAdapter(cmd)
                Try
                    DAdapter.Fill(DTable)    ''In this line of code I''m getting "Object reference not set to an instance of an object" error which occurs only if data have been sorted previously. 
                Catch ex As Exception
                    cn.Close()
                    _Ex = ex
                    Return False
                End Try
            End Using
            cn.Close()
        End Using
        Return True
    End Function 

This is a StackTrace

at System.Data.Index.CompareRecords(Int32 record1, Int32 record2)
at System.Data.Index.IndexTree.CompareNode(Int32 record1, Int32 record2)
at System.Data.RBTree`1.RBInsert(Int32 root_id, Int32 x_id, Int32 mainTreeNodeID, Int32 position, Boolean append)
at System.Data.RBTree`1.InsertAt(Int32 position, K item, Boolean append)
at System.Data.Index.InsertRecord(Int32 record, Boolean fireEvent)
at System.Data.Index.ApplyChangeAction(Int32 record, Int32 action, Int32 changeRecord)
at System.Data.DataTable.RecordStateChanged(Int32 record1, DataViewRowState oldState1, DataViewRowState newState1, Int32 record2, DataViewRowState oldState2, DataViewRowState newState2)
at System.Data.DataTable.SetNewRecordWorker(DataRow row, Int32 proposedRecord, DataRowAction action, Boolean isInMerge, Int32 position, Boolean fireEvent, Exception& deferredException)
at System.Data.DataTable.InsertRow(DataRow row, Int32 proposedID, Int32 pos, Boolean fireEvent)
at System.Data.DataTable.LoadDataRow(Object[] values, Boolean fAcceptChanges)
at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at ESoft_Pawnshop.SqlDAL.GetTabular(String strSQL, DataTable& DTable, SqlTransaction trans) in E:\Proeqtebi\ESoft_Pawnshop11\ESoft_Pawnshop\DataAccess\SqlDAL.vb:line 159

解决方案

The problem is outside of the code snippets you posted. This is the problem with having class-level variables all over the place. It''s much harder to track what happened to the values in them.

Whatever code you have attached the Sort or Header Click events is setting DTable to Nothing before this code runs.

Since other methods outside of this GetTablar have access to the DTable variable, it would be best if you checked to to see if it was Nothing before trying to pass it to the Fill method.

Thanks for your answer. I''ve checked DTable variable and all other variables too and they newer go to nothing. I don''t have my own sort code. Sorting is performed by DataGridView itself

Hi,
Please try this

Private Sub FillData()
        Dim sSQL As String = GetQueryString()  ''This is a call to get a query string
        tbl = New DataTable() 
        If sSQL = "" Then Exit Sub
        Me.Cursor = Cursors.WaitCursor
        If DSet.Tables.Contains("Temp") Then
            tbl = DSet.Tables("Temp")
            tbl.Clear()
            tbl.Columns.Clear()
        Else
            tbl = DSet.Tables.Add("Temp")
        End If
        If SqlDAL.GetTabular(sSQL, tbl) Then   ''SqlDAL is a class which actually gets data from a SQL database
            dgDetail.DataSource = Nothing
            dgDetail.Rows.Clear()
            dgDetail.Columns.Clear()
            dgDetail.DataSource = tbl
        Else
            MsgBox(String.Format("Can''t fill a grid!{0}{1}", vbLf, _
                                 SqlDAL.GetLastException.Message), MsgBoxStyle.Critical)
        End If
        Me.Cursor = Cursors.Default
    End Sub

这篇关于Dataadapter.Fill方法出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！