Click here to Skip to main content
12,508,422 members (52,554 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB .NET ADO.NET VB.NET
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
Posted 12-Jun-12 6:58am
Emzari358
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
Comments
Dave Kreskowiak 13-Jun-12 16:02pm
   
In that case, I have no idea. In the countless times I've used sorting in a DGV, I've NEVER encountered a problem anywhere close to what you're describing.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160927.1 | Last Updated 25 Jun 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100