Datatable Function Problem

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


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() 'add call to initialize the datatable
                ConvertFiles(inFile, outFile)
                'Wave.Play("C:\resource\sounds\completed.wav")
                MsgBox("File Complete!")
            Else

This is my attempt at the two new functions

    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")

        da.Dispose()
    End Function


    Public Function IPLookup(ByVal DottedIP As String) As String

        Dim lngIP As Long
        Dim strCountry As String
        Dim cmdSelect As New OleDbCommand
        Dim ds As DataSet
        Dim dt As DataTable
        Dim da As New OleDbDataAdapter

        dt = ds.Tables("Country")

        da.SelectCommand = cmdSelect
        lngIP = Dot2LongIP(DottedIP)

        cmdSelect.CommandText = "SELECT countryname FROM Country WHERE " & lngIP.ToString & " >= ipl1 and " _
                   & lngIP.ToString & "  <= ipl2"

        If dt.Rows.Count = 0 Then
            strCountry = "Unknown"

        Else
            strCountry = dt.Rows(0).Item("countryname")
        End If
        Return strCountry

        da.Dispose()
    End Function


Thank you for your value 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.

cubixSoftwareCommented:
Hi

There are a number of errors I can see stright off...

-  CountryDataTable creates a dataset that is only in scope within that method - when the method ends the data is lost
-  IPLookUp doesn't seem to be called
-  If you want IPLookup function to use the dataset from CountryDataTable then you need to pass it in as a parameter and then use the Select method as

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

A link with examples on the Select method is

http://msdn2.microsoft.com/en-us/library/det4aw50(VS.80).aspx

HTH :)





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
gr8lifeAuthor Commented:
Thanks for the quick response.  I am new to programming and really need help on this.

CountryDataTable creates a dataset that is only in scope within that method - when the method ends the data is lost

I removed the dispose from the first function, which I believe was causing the data to be lost.  Not sure how to change the scope of the dataset to make it available. I thought the call to “country” was enabling that.

IPLookup doesn't seem to be called

 I am calling it in another function, and before trying to enhance the performance that part of the application worked fine.(just slow)


If you want IPLookUp function to use the dataset from CountryDataTable then you need to pass it in as a parameter and then use the Select method as

I replaced all the old code with the stuff you posted.

I’m getting an error, which I’m sure is related to my code.
“Problem OccurredObject reference not set to an instance of an object.”


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
0
gr8lifeAuthor Commented:
Thank you for your time,
Gr8life
0
cubixSoftwareCommented:
Did you manage to sort the problem ?
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.