asked on

DataTable already belongs to another DataSet

I get this error
DataTable already belongs to another DataSet

when I try to add a datatable to my data set

those datatable get their data from other functions which return "datatable"

I have listen one such function.

Public Function SearchAny(ByVal UserInput As String) As DataSet
        Dim resultDs As New DataSet
 
        Dim dtNMI As New DataTable
        Dim dtAccount As New DataTable
        Dim dtInvoice As New DataTable
        Dim dtProducts As New DataTable
 
        dtNMI.TableName = "NMI"
        dtAccount.TableName = "Account"
        dtInvoice.TableName = "Invoice"
        dtProducts.TableName = "Products"
 
        dtNMI = SearchNMIAny(UserInput)
        dtAccount = SearchAccountAny(UserInput)
        dtInvoice = SearchInvoiceAny(UserInput)
        dtProducts = SearchProductAny(UserInput)
 
        resultDs.Tables.Add(dtNMI)
        resultDs.Tables.Add(dtAccount)
        resultDs.Tables.Add(dtInvoice)
        resultDs.Tables.Add(dtProducts)
 
        Return resultDs
    End Function
 
one of the function
 
Public Function SearchNMIAny(ByVal UserInput As String) As DataTable
        Dim resultTable As New DataTable
        Dim result As New DataSet
        Dim sqlQuery As String
        Dim sb As New StringBuilder
 
        sb.AppendFormat("SELECT TOP 50 NMI, NmiID FROM tbnmi WHERE nmi LIKE '{0}%' ", UserInput)
        sqlQuery = sb.ToString
 
        result = DB.GetDataSetFromSQL(sqlQuery)
        resultTable = result.Tables(0)
        Return resultTable
 
    End Function

Open in new window

ASKER CERTIFIED SOLUTION

Joep_Killaars

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Obadiah Christopher

Possibly Dt.Copy() might work also...

Nasir Razzaq

But keeping two copies of the same data table would harm the performance side. The problem is in these two lines

result = DB.GetDataSetFromSQL(sqlQuery)
resultTable = result.Tables(0)

Why not modify the GetDataSetFromSQL function to return a datatable instead of a dataset?

VBRocks

Or just remove the "New" keyword:

Dim resultTable As DataTable

jinal

This will solve your problem.

 resultDs.Tables.Add(dtNMI.Copy())
        resultDs.Tables.Add(dtAccount.Copy())
        resultDs.Tables.Add(dtInvoice.Copy())
        resultDs.Tables.Add(dtProducts.Copy())

Open in new window

Joep_Killaars

CodeCruiser is right, creating a copy of the datatable would not affect the performance positively, however, there is no way to add a datatable to multiple datasets. so basically you have 2 options :

- only use one instance of your datatable ( if you only use your table for one specific task on one specific object, try to change the methods so you do not have to toss tables around

- Copy the table if you are planning on using different tables on different objects.

manivineet

ASKER

@Joep_Killaars: thanks.
but the thing that worked for me was to create a temp table which is a clone of the table from the table I need from the dataset and then passing that temp table back.
you "clone" suggestion came in handy.