Problem OccurredObject reference not set to an instance of an object

I am trying to split the function IPlookup in to two separate functions. The splitting of the functions is an attempt to reduce the amount of times the application has to go to the database by populating a datatable and selecting information from it.  This approach was suggested as a performance enhancement in a post I made for suggestions on performance enhancements. Please see the URL below for questions:

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21784599.html

I attempted several different approaches, but am still struggling to make it work.

I’m getting an error “Problem OccurredObject reference not set to an instance of an object.”

Original code:
     
Public Function IPLookup(ByVal DottedIP As String) As String
        Dim lngIP As Long
        Dim strCountry As String
        Dim con As New OleDbConnection
        Dim dtm As DataTableMapping
        Dim da As New OleDbDataAdapter
        Dim cmdSelect As New OleDbCommand
        Dim ds As New DataSet
        Dim dt As DataTable

        lngIP = Dot2LongIP(DottedIP)
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb"
        dtm = da.TableMappings.Add("Table", "Country")
        cmdSelect.CommandText = "SELECT countryname FROM Country WHERE " & lngIP.ToString & " >= ipl1 and " _
                   & lngIP.ToString & "  <= ipl2"

        cmdSelect.Connection = con
        da.SelectCommand = cmdSelect
        da.Fill(ds)
        dt = ds.Tables("Country")
        If dt.Rows.Count = 0 Then
            strCountry = "Not is Database!"
        Else
            strCountry = dt.Rows(0).Item("countryname")
        End If

        Return strCountry
    End Function
End Class

Split code


Partial code (I included this partial code section because I wasn't sure if this was what was causing the error)

If (inFile.Length > 0) Then
            If (outFile.Length > 0) Then
                CountryDatatable() 'I added call to the datatable
                ConvertFiles(inFile, outFile)
                'Wave.Play("C:\resource\sounds\completed.wav")
                MsgBox("File Complete!")
            Else

Here is the modified code:

    Public Function CountryDatatable()
        Dim cmdSelect As New OleDbCommand
        Dim dtm As DataTableMapping
        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim con As New OleDbConnection

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb"
        cmdSelect.CommandText = "SELECT * from Country"
        dtm = da.TableMappings.Add("Table", "Country")
        cmdSelect.Connection = con
        da.SelectCommand = cmdSelect
        da.Fill(ds)
        dt = ds.Tables("Country")

    End Function

    Public Function IPLookup(ByVal DottedIP As String) As String
        Dim lngIP As Long
        Dim strCountry As String
        Dim ds As DataSet
        Dim dt As DataTable

        dt = ds.Tables("Country")
        lngIP = Dot2LongIP(DottedIP)
        Dim sql As String
        Dim foundrows() As DataRow
        sql = lngIP.ToString & ">=ipl1 and " & lngIP.ToString & " <= ipl2"
        foundrows = dt.Select(sql)

        If foundrows.Length = 0 Then
            strCountry = "unknown"

        Else
            strCountry = foundrows(0).Item("countryname")
        End If
        Return strCountry
    End Function


Thank you very much for your time and expertise,
Gr8life
gr8lifeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

appariCommented:

where are you getting the error?
I think on this line
 dt = ds.Tables("Country")
in IPLookup function?
thats because ds is not yet created.
where are you calling function CountryDatatable()?

i think you are trying to call CountryDatatable() once and prepare the datatable and use it in IPLookup function many times in a loop. if thats the case remove
        Dim ds As DataSet
        Dim dt As DataTable

      dt = ds.Tables("Country") from IPLookup function

and shift Dim dt As New DataTable from CountryDatatable() to module level
and try
0
appariCommented:
your modified code should look something like this

        Dim dt As DataTable

Public Function CountryDatatable()
        Dim cmdSelect As New OleDbCommand
        Dim dtm As DataTableMapping
        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet

        Dim con As New OleDbConnection

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb"
        cmdSelect.CommandText = "SELECT * from Country"
        dtm = da.TableMappings.Add("Table", "Country")
        cmdSelect.Connection = con
        da.SelectCommand = cmdSelect
        da.Fill(ds)
        dt = ds.Tables("Country")

    End Function

    Public Function IPLookup(ByVal DottedIP As String) As String
        Dim lngIP As Long
        Dim strCountry As String

        lngIP = Dot2LongIP(DottedIP)
        Dim sql As String
        Dim foundrows() As DataRow
        sql = lngIP.ToString & ">=ipl1 and " & lngIP.ToString & " <= ipl2"
        foundrows = dt.Select(sql)

        If foundrows.Length = 0 Then
            strCountry = "unknown"

        Else
            strCountry = foundrows(0).Item("countryname")
        End If
        Return strCountry
    End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian CroweDatabase AdministratorCommented:
within function IPLookup there is no table "Country" because that table is defined within the scope of the CountryDataTable function.  You will need to declare the dataset with class-level scope.

private ds as new dataset

public sub CountryDatatable() 'I'm surprised this compiles since a function must return a value
        Dim cmdSelect As New OleDbCommand
        Dim dtm As DataTableMapping
        Dim da As New OleDbDataAdapter
        Dim dt As New DataTable
        Dim con As New OleDbConnection

   with da
      .selectcommand = new oledbcommand
      with .selectcommand
         .connection = new oledbconnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\resource\master.mdb")
         .commandtext = "SELECT * from Country"
      end with

      .tablemapping.add("Table", "Country")
      .acceptchangesduringfill = true
   end with

   try
      da.Fill(ds)
   catch ex as exception
      messagebox.show("Unable to load Country data: " + ex.message)
   end try
End sub

Public Function IPLookup(ByVal DottedIP As String) As String
   Dim lngIP As Long
   Dim strCountry As String
   Dim foundroys() as datarow
   Dim sql as string

   lngIP = Dot2LongIP(DottedIP)

   sql = lngIP.ToString & ">=ipl1 and " & lngIP.ToString & " <= ipl2"
   foundrows = ds.tables("Country").Select(sql)

        If foundrows.Length = 0 Then
            strCountry = "unknown"
        Else
            strCountry = foundrows(0).Item("countryname")
        End If
        Return strCountry
    End Function
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

gr8lifeAuthor Commented:
I'm trying the code solution provided by BriCrowe and I'm getting an underlined error 'tablemapping" is not a member of the 'System.Data.OleDbDataAdapter'
In this row
    .tablemapping.add("Table", "Country")

Any suggestions,
Thank you very much!
Gr8life

0
gr8lifeAuthor Commented:
Also appari your are exactly right!
I am trying to call CountryDatatable() once and prepare the datatable and use it in IPLookup function many times in a loop.
Thank you for time,
Gr8life
0
gr8lifeAuthor Commented:
Here is the code where the CountryDatatable() is being called.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim inFile As String
        Dim outFile As String
        Dim openFileDialog1 As New OpenFileDialog
        openFileDialog1.InitialDirectory = "c:\resource"
        openFileDialog1.Filter = "txt files (*.txt|*.txt|All files(*.*)|*.*"
        openFileDialog1.FilterIndex = 2
        openFileDialog1.RestoreDirectory = True

        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            inFile = openFileDialog1.FileName
            outFile = Mid(inFile, 1, inFile.LastIndexOf("."))
            outFile += "-out.CSV"
        End If

        If (inFile.Length > 0) Then
            If (outFile.Length > 0) Then

                CountryDatatable() 'Here is where I am calling function

                ConvertFiles(inFile, outFile)
                'Wave.Play("C:\resource\sounds\completed.wav")
                MsgBox("File Complete!")
            Else
                MsgBox("No output file specified!")
            End If
        Else
            MsgBox("No input file specified!")
        End If
    End Sub

Thank you very much,
Gr8life
0
appariCommented:

did you try the code i posted in my second post?
0
Brian CroweDatabase AdministratorCommented:
i was doing it off the cuff...it should be

.tablemappings.add("Table", "Country")
0
gr8lifeAuthor Commented:
I tried both solutions and they both work great.  However I am receiving an error “Problem Occurred Index was outside the bounds of the array” I believe it is because of the way I am calling the CountryDatatable function.  Any suggestions?

Thanks for your time and expertise,
Gr8life
0
Brian CroweDatabase AdministratorCommented:
can you run through it in debug mode and nail down which line of code is throwing the error?
0
gr8lifeAuthor Commented:
here is the output of the debug

'DefaultDomain': Loaded 'c:\windows\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols loaded.
'performance testing': Loaded 'C:\Documents and Settings\home\My Documents\Visual Studio Projects\performance testing\bin\performance testing.exe', Symbols loaded.
'performance testing.exe': Loaded 'c:\windows\assembly\gac\system.windows.forms\1.0.5000.0__b77a5c561934e089\system.windows.forms.dll', No symbols loaded.
'performance testing.exe': Loaded 'c:\windows\assembly\gac\system\1.0.5000.0__b77a5c561934e089\system.dll', No symbols loaded.
'performance testing.exe': Loaded 'c:\windows\assembly\gac\system.data\1.0.5000.0__b77a5c561934e089\system.data.dll', No symbols loaded.
'performance testing.exe': Loaded 'c:\windows\assembly\gac\system.xml\1.0.5000.0__b77a5c561934e089\system.xml.dll', No symbols loaded.
'performance testing.exe': Loaded 'c:\windows\assembly\gac\system.drawing\1.0.5000.0__b03f5f7f11d50a3a\system.drawing.dll', No symbols loaded.
'performance testing.exe': Loaded 'c:\windows\assembly\gac\microsoft.visualbasic\7.0.5000.0__b03f5f7f11d50a3a\microsoft.visualbasic.dll', No symbols loaded.
The program '[3776] performance testing.exe' has exited with code 0 (0x0).
0
Brian CroweDatabase AdministratorCommented:
unfortunately that doesn't help much.  It would be much more useful if you could just step through the code and determine which line is throwing the error.
0
appariCommented:
>>I tried both solutions and they both work great.  
>>However I am receiving an error “Problem Occurred Index was outside the bounds of the array” I believe it is because of the way I am calling the CountryDatatable function.
what does this mean, first statement says it works and next you are saying you are getting error.
just add try catch blocks to catch the exceptions and display proper error as follows

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
try
        Dim inFile As String
        Dim outFile As String
        Dim openFileDialog1 As New OpenFileDialog
        openFileDialog1.InitialDirectory = "c:\resource"
        openFileDialog1.Filter = "txt files (*.txt|*.txt|All files(*.*)|*.*"
        openFileDialog1.FilterIndex = 2
        openFileDialog1.RestoreDirectory = True

        If openFileDialog1.ShowDialog() = DialogResult.OK Then
            inFile = openFileDialog1.FileName
            outFile = Mid(inFile, 1, inFile.LastIndexOf("."))
            outFile += "-out.CSV"
        End If

        If (inFile.Length > 0) Then
            If (outFile.Length > 0) Then

                CountryDatatable() 'Here is where I am calling function

                ConvertFiles(inFile, outFile)
                'Wave.Play("C:\resource\sounds\completed.wav")
                MsgBox("File Complete!")
            Else
                MsgBox("No output file specified!")
            End If
        Else
            MsgBox("No input file specified!")
        End If
catch ex as exception
                MsgBox("Error in Button1_Click :" & ex.description)
end try
    End Sub
0
appariCommented:
sorry chaneg
 MsgBox("Error in Button1_Click :" & ex.description)

to

 MsgBox("Error in Button1_Click :" & ex.message)
0
gr8lifeAuthor Commented:
Same problem occurred after I added I the changes provided by appari.  I really appreciate all the help the two have you have given me.  I’m going to close this question and open another one with this problem.  So if you want to continue to help I would greatly appreciate it.  I wish I could have provided more points but the limit is 500.  Thank you very much for all your hard work and expertise.

Thank you for your valuable time,
Gr8life
0
gr8lifeAuthor Commented:
The thing that doesn't make the most sense is that the application finishes processing all the IP lookups and then it seams like it wants to continue through the looping.  Then it is almost like it errors because it can't stop the looping any other way.
Gr8life
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.