locked
System.NullReferenceException: 'The object reference is not defined to an instance of an object.' ر vb.net RRS feed

  • Question

  • hi members

    i face this error System.NullReferenceException: 'The object reference is not defined to an instance of an object.'

    i make sql select from database to a datatable

    when i click for the first time it work in second time it return this error

      Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by numseq", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                            tabNum.Rows.Add(da1(8))
                    End While
    help please
    Tuesday, June 23, 2020 2:33 PM

Answers

All replies

  • Post a concise and complete example.

    This includes using using for IDisposable classes as well as proper exception handling.

    And for your error: Where is dt5 defined? Is still in scope?

    Tuesday, June 23, 2020 2:37 PM
  • thank you for answering this is the complete code

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click tabNum.Clear() dt5.Clear() tabNum.Columns.Clear() dt5.Columns.Clear() DataGridView1.Columns.Clear() ' Dim headercell As DataGridViewRowHeaderCell tabNum.Columns.Add("Numseq") If cn.State = ConnectionState.Open Then cn.Close() End If cn.Open() Dim result As DialogResult = Form4.ShowDialog If result = DialogResult.Yes Then dt.Clear() dt5.Columns.Add("Numero") dt5.Columns.Add("Codearticle") dt5.Columns.Add("Designation") dt5.Columns.Add("Qte") dt5.Columns.Add("PrixUnitaire") dt5.Columns.Add("Remise") dt5.Columns.Add("ReferenceFournisseur") dt5.Columns.Add("NumeroEnsemble") dt5.Columns.Add("Marge") ' dt1.Columns.Add("numseq") Button7.Enabled = True If cn1.State = ConnectionState.Open Then cn1.Close() End If cn1.Open() If cn.State = ConnectionState.Open Then cn.Close() End If cn.Open() Dim cmd3 As New SqlCommand("select Codefournisseur,Reference,MontantTotal,Etat,DateSortieFrs,DateEmbarquement,DateDouane,Coursdevise from EnteteReceptionFrs where Numero='" & str1 & "'", cn) Dim rr As SqlDataReader = cmd3.ExecuteReader While rr.Read TextBox8.Text = rr("Coursdevise") TextBox4.Text = rr("DateSortieFrs") TextBox5.Text = rr("DateEmbarquement") TextBox6.Text = rr("DateDouane") TextBox7.Text = rr("MontantTotal") TextBox3.Text = rr("Reference") TextBox2.Text = rr("Codefournisseur") If rr("Etat") = "V" Or rr("Etat") = "v" Then Label11.Text = "Validée" ElseIf rr("Etat") = "E" Or rr("Etat") = "e" Then Label11.Text = "Enregistrée" End If End While Dim cmd5 As New SqlCommand("select * from F_COMPTET where CT_Num='" & TextBox2.Text & "'", cn1) Dim da5 As SqlDataReader = cmd5.ExecuteReader da5.Read() ComboBox1.Text = da5("CT_Intitule") TextBox1.Text = str1 rr.Close() If Label11.Text = "Validée" Then If cn.State = ConnectionState.Open Then cn.Close() End If cn.Open() Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by numseq", cn) Dim da1 As SqlDataReader = (cmd2.ExecuteReader) While da1.Read 'this is where the error' dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7)) tabNum.Rows.Add(da1(8)) End While If cn1.State = ConnectionState.Open Then cn1.Close() End If cn1.Open() Dim cmd245354 As New SqlCommand("select do_piece from F_DOCENTETE where do_ref='" & str1 & "'", cn1) Dim dabaz = cmd245354.ExecuteScalar For i = 0 To dt5.Rows.Count - 1 If cn1.State = ConnectionState.Open Then cn1.Close() End If cn1.Open() Dim cml As New SqlCommand("select DL_PieceBC from F_DOCLIGNE where do_piece='" & dabaz & "' and ar_ref='" & dt5.Rows(i).Item("Codearticle") & "' and DL_Qte='" & dt5.Rows(i).Item("Qte") & "' order by dl_ligne", cn1) Dim dabaz2 As SqlDataReader = cml.ExecuteReader dabaz2.Read() If dabaz2.HasRows Then dt5.Rows(i).Item("Numero") = dabaz2(0) Else dt5.Rows(i).Item("Numero") = TextBox1.Text End If Next End If If cn.State = ConnectionState.Open Then cn.Close() End If cn.Open() If Label11.Text = "Enregistrée" Then If cn.State = ConnectionState.Open Then cn.Close() End If cn.Open() Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by numseq", cn) Dim da1 As SqlDataReader = (cmd2.ExecuteReader) While da1.Read 'this is where the error'

    dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7)) tabNum.Rows.Add(da1(8)) End While ' da1.Fill(dt1) If cn.State = ConnectionState.Open Then cn.Close() End If cn.Open() Dim cmd2453 As New SqlCommand("select do_piece from dl_ligne_table where Numero='" & str1 & "' order by numseq", cn) Dim dab As SqlDataReader = cmd2453.ExecuteReader Dim i As Integer While dab.Read And i <= dt5.Rows.Count - 1 dt5.Rows(i).Item("Numero") = dab("do_piece") i += 1 End While While i <= dt5.Rows.Count - 1 ' Try dt5.Rows(i).Item("Numero") = str1 i += 1 ' Catch ex As Exception ' MessageBox.Show(e.ToString) ' End Try End While End If DataGridView1.DataSource = dt5 Dim var As DataGridViewColumn = DataGridView1.Columns(2) var.Width = 180 Dim var1 As DataGridViewColumn = DataGridView1.Columns(1) var1.Width = 120 Dim var2 As DataGridViewColumn = DataGridView1.Columns(5) var2.Width = 80 Dim var3 As DataGridViewColumn = DataGridView1.Columns(7) var3.Width = 100 Dim var4 As DataGridViewColumn = DataGridView1.Columns(8) var4.Width = 80 DataGridView1.RowHeadersWidth = 53 End If For i = 0 To dt5.Rows.Count - 1 DataGridView1.Rows(i).HeaderCell.Value = tabNum.Rows(i).Item(0) Next Dim total As Double For i = 0 To DataGridView1.Rows.Count - 1 Dim qte As Integer Dim prix As Double If IsDBNull(DataGridView1.Rows(i).Cells(4).Value) Then prix = 0 Else prix = CDbl(DataGridView1.Rows(i).Cells(4).Value) End If If IsDBNull(DataGridView1.Rows(i).Cells(3).Value) Then qte = 0 Else qte = CInt(DataGridView1.Rows(i).Cells(3).Value) End If total += qte * prix Next TextBox9.Text = FormatNumber(total, 3)


    Tuesday, June 23, 2020 2:43 PM
  • Set a breakpoint on the "While da1.Read", hover over dt5, is it valid? Same goes for ad1(X). I can only guess dt5 is null/Nothing.

    Bottom line is learn how to use the debugger as simply looking/examining code w/o the debugger will not solve anything.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, June 23, 2020 2:43 PM
  • Please refactor it.

    It contains redundant and unnecessary code as opening and closing the connection over and over again.

    Use using for IDisposable classes.

    The rr.Read loop makes not really sense, cause you only display the values of the last row.

    Your SQL concatenation code allows SQL injection, use parameterized queries instead.

    The code seems to have two main paths. this looks like some kind of strategy. At least extract them to separate methods.

    Tuesday, June 23, 2020 2:52 PM
  • I still not resolve it this problem

    its a select query from db to datatable to datagrid

    its always work fine untill i click on the column header to sort it

    i use this methode DataGridView1_ColumnHeaderMouseClick

    when i click on it i get this error null reference and its the same query so it should work fine

     Private Sub DataGridView1_ColumnHeaderMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles DataGridView1.ColumnHeaderMouseClick
            dt5.Clear()
            tabNum.Clear()
            If Label11.Text = "Validée" Then
                If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                cn.Open()
                If (e.ColumnIndex) = 0 Then
                    Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by numero", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                        tabNum.Rows.Add(da1(8))
                    End While
                ElseIf (e.ColumnIndex) = 1 Then
                    Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by Codearticle", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                        tabNum.Rows.Add(da1(8))
                    End While
                ElseIf (e.ColumnIndex) = 2 Then
                    Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by Designation", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                        tabNum.Rows.Add(da1(8))
                    End While
                Else
                    Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by numseq", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                        tabNum.Rows.Add(da1(8))
                    End While
                End If
                If cn1.State = ConnectionState.Open Then
                    cn1.Close()
                End If
                cn1.Open()
                Dim cmd245354 As New SqlCommand("select do_piece from F_DOCENTETE where do_ref='" & str1 & "'", cn1)
                Dim dabaz = cmd245354.ExecuteScalar
                For i = 0 To dt5.Rows.Count - 1
                    If cn1.State = ConnectionState.Open Then
                        cn1.Close()
                    End If
                    cn1.Open()
                    Dim cml As New SqlCommand("select DL_PieceBC from F_DOCLIGNE where do_piece='" & dabaz & "' and ar_ref='" & dt5.Rows(i).Item("Codearticle") & "' and DL_Qte='" & dt5.Rows(i).Item("Qte") & "' order by dl_ligne", cn1)
                    Dim dabaz2 As SqlDataReader = cml.ExecuteReader
                    dabaz2.Read()
                    If dabaz2.HasRows Then
                        dt5.Rows(i).Item("Numero") = dabaz2(0)
                    Else
                        dt5.Rows(i).Item("Numero") = TextBox1.Text
                    End If
                Next
            End If
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            cn.Open()
            If Label11.Text = "Enregistrée" Then
                If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                cn.Open()
                If (e.ColumnIndex) = 0 Then
                    Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by numero", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                        tabNum.Rows.Add(da1(8))
                    End While
                ElseIf (e.ColumnIndex) = 1 Then
                    Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by Codearticle", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                        tabNum.Rows.Add(da1(8))
                    End While
                ElseIf (e.ColumnIndex) = 2 Then
                    Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by Designation", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                        tabNum.Rows.Add(da1(8))
                    End While
                Else
                    Dim cmd2 As New SqlCommand("select Codearticle,Designation,Qte,PrixUnitaire,Remise,ReferenceFournisseur,NumeroEnsemble,Marge,numseq from DetailReceptionFrs where Numero='" & str1 & "' order by numseq", cn)
                    Dim da1 As SqlDataReader = (cmd2.ExecuteReader)
                    While da1.Read
                        dt5.Rows.Add("", da1(0), da1(1), da1(2), da1(3), da1(4), da1(5), da1(6), da1(7))
                        tabNum.Rows.Add(da1(8))
                    End While
                End If
               If cn.State = ConnectionState.Open Then
                    cn.Close()
                End If
                cn.Open()
                Dim cmd2453 As New SqlCommand("select do_piece from dl_ligne_table where Numero='" & str1 & "' order by numseq", cn)
                Dim dab As SqlDataReader = cmd2453.ExecuteReader
                Dim i As Integer
                While dab.Read And i <= dt5.Rows.Count - 1
                    dt5.Rows(i).Item("Numero") = dab("do_piece")
                    i += 1
                End While
    
                While i <= dt5.Rows.Count - 1
                    dt5.Rows(i).Item("Numero") = str1
                    i += 1
                End While
            End If
            DataGridView1.DataSource = dt5
            'For i = 0 To tabNum.Rows.Count - 1
            '    DataGridView1.Rows(i).HeaderCell.Value = tabNum.Rows(i).Item(0)
            'Next
            Dim var As DataGridViewColumn = DataGridView1.Columns(2)
            var.Width = 180
            Dim var1 As DataGridViewColumn = DataGridView1.Columns(1)
            var1.Width = 120
            Dim var2 As DataGridViewColumn = DataGridView1.Columns(5)
            var2.Width = 80
            Dim var3 As DataGridViewColumn = DataGridView1.Columns(7)
            var3.Width = 100
            Dim var4 As DataGridViewColumn = DataGridView1.Columns(8)
            var4.Width = 80
            DataGridView1.RowHeadersWidth = 53
            ' DataGridView2.DataSource = tabNum
            For i = 0 To dt5.Rows.Count - 1
                DataGridView1.Rows(i).HeaderCell.Value = tabNum.Rows(i).Item(0)
            Next
    
    my goal is to sort the datagridview with the clicked header column from database

    Wednesday, June 24, 2020 2:09 PM
  • for first entry the query it work  finث and always work untill i click on DataGridView1_ColumnHeaderMouseClickif i click it work fine it sorted like i want but when i lake the query from db it generate this error

    i don't know the source or how i find where it come


    • Edited by Houssem12 Wednesday, June 24, 2020 2:16 PM
    Wednesday, June 24, 2020 2:11 PM
  • i found it
    • Marked as answer by Houssem12 Wednesday, June 24, 2020 4:05 PM
    Wednesday, June 24, 2020 4:05 PM
  • i found it
    You found it? You found what?
    Wednesday, June 24, 2020 4:25 PM