asked on

Alternative to Datatable.Fill

I've split a DB into two and have a handful of queries that do inner joins across both. While working on stored procedures, I'm also exploring ways to "fake" these joins using VB code functions. They are just "fetch" procedures with low volume and low use so I'm not that concerned about performance or failure.

In this one particular case I've built a "GetData" equivalent that fakes the join, that looks like it will work. The issue is that I need to do a Fill on a datatable that's already in a dataset...and I can't figure out a way to do that with a function that returns a datatable.

With this pseudocode:

MyWorkTable = Get Table(OtherID)
Dataset.MyTable = MyWorkTable

it says the Dataset.MyTable is readonly.

I was thinking of deleting the MyTable from the DataSet and then re-adding MyWorkTable to it in it's place...but I'm not sure if that would work and it seems like a real run-around.

Any help with this would be appreciated.

Thanks!

ASKER CERTIFIED SOLUTION

Sammy

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

codequest

ASKER

Thanks for input, I'll try it in am.

codequest

ASKER

Just to review:

Dataset.MyTable is already in the Dataset instance and filled with data.

I can say WorkTable1 = Dataset.MyTable

Then WorkTable1 is a reference to the MyTable in the Dataset.

If I do a

WorkTable2 = GetTable(somevar)

then

WorkTable1.Merge(WorkTable2) will merge the WorkTable2 data in with the WorkTable1 data, which will still be in the Dataset instance.

However, I need to completely replace the data in Dataset.MyTable with the data in WorkTable2.

Could that be done by first clearing the data in Dataset.MyTable?

And if so, how would that data be cleared out?

Would doing the "clear" operation on
Worktable1 ( which was set equal to DataSet.MyTable ) do the job (that is, because WorkTable1 is just a reference to DataSet.MyTable?)

SOLUTION

Fareed Ali Khan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

codequest

ASKER

Thanks for input. sammy's solution seems to be working...I'm going to go with that for now. Fareed points to a viable alternative.

Sammy

Codequest,
Sorry for the delay I was busy. are you still having any issues with the solution I provided or do you need any further clarifications?

codequest

ASKER

Thanks for the message. Your "merge" solution worked great. I built a sub

(call) FillTable(Dataset.MyTable)

Sub FillTable(varT as Dataset.MyTableDatatable)
dim wrkT as Dataset.MyTableDatatable
wrkT = Me.GetTable(varOtherID) ' ---- this executes the in-code "GetData"
varT.Clear()
varT.Merge(wrkT)
End Sub

I'm sure it could use some "safety" features...seems to work fine though, as a substitute for TableAdapter.Fill, and extends nicely off the Get, where the "pseudo join" logic takes place. Good thing, too, 'cause I'm having real issues with the cross-db stored procedures...

https://www.experts-exchange.com/questions/22627526/Error-on-Cross-DB-query-in-VS2005.html

Thanks!