DataTable already belongs to another DataSet

manivineet
manivineet used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You could clone the table before attaching it to a dataset. You can do so by using the code snippet I provide below. ( vbnet source is below the c# source )
// create a clone of the table.
            var cloneTable = new DataTable();
 
            cloneTable = dT.Clone();
 
            foreach (DataRow row in dT.Rows)
                cloneTable.ImportRow(row);
'vb.net
' create a clone of the table.
Dim cloneTable = New DataTable()
 
cloneTable = dT.Clone()
 
For Each row As DataRow In dT.Rows
    cloneTable.ImportRow(row)
Next

Open in new window

Obadiah ChristopherDeveloper User Interface

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

Most Valuable Expert 2012
Top Expert 2014

Commented:
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?
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Top Expert 2007

Commented:
Or just remove the "New" keyword:

    Dim resultTable As DataTable

Commented:
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

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.

Author

Commented:
@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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial