[RESOLVED] Adding Datatable.Select results to another Datatable

[GBeats]

Hi

My goal is to update a datatable which is linked to a combobox with the results of another datatables select command. my original code looped through the datatable first and added each row which took way to long to run so im messing around with this new idea but something is going wrong...

The current code im using is very simple...


DT = Table holding all the data im using

View = The fastest way to select the Distinct values using .ToTable method i found

DT_FeedYard = the DataTable linked in with the Combobox

        DT.Columns(14).ColumnName = "Text"
        DT.Columns(15).ColumnName = "Value"

        Dim View As DataView = New DataView(DT.Select("[Type] IN ('P', 'L', 'M') AND [Value] IS NOT NULL", "[Text] ASC").CopyToDataTable)
        DT_Feedyard = View.ToTable(True, "Value", "Text")

now the code runs fine except the results are not as expected

1. The number of rows is less than it should be
2. The row list in DT_Feedyard is empty but its row count

DT_Feedyard is initialized with a couple of default rows and the names on this table are not the same as the names on the View or DT table, should that even matter? (i know im loosing the items i initialise with ill handle that after)




just for reference here is the Query that fills the DT DataTable (transact SQL) this is what im using for testing only its not an actual SP although it does use one

DECLARE @Tbl1 TABLE(
	[ ] BIT,
	[Assign] VARCHAR(10) NULL,
	[UnAssign] VARCHAR(10) NULL,
	[Type] VARCHAR(5) NULL,
	[Description] VARCHAR(40) NULL,
	[Symbol] VARCHAR(20) NULL,
	[CloseDate] DATETIME NULL,
	[BuySell] VARCHAR(1) NULL,
	[CloseFill] INT NULL,
	[ClosePrice] FLOAT NULL,
	[Commission] FLOAT NULL,
	[IBTranFee] FLOAT NULL,
	[NFAFee] FLOAT NULL,
	[ExpireAssign] VARCHAR(1) NULL,
	[FeedYardName] VARCHAR(50) NULL,
	[FYID] INT NULL,
	[PurID] INT NULL)

	
INSERT INTO @tbl1 
EXEC dbo.uspTRXAssignments_AllFilters '1|0|0|!IGNORE!'

SELECT DISTINCT [FeedYardName] AS [Text], [FYID] AS [Value] FROM @Tbl1 WHERE [Type] IN('P', 'L', 'M') AND [FYID] IS NOT NULL ORDER BY [FeedYardName]

[kpmc]

Try this

Dim View As DataView = dt.Select("[Type] IN ('P', 'L', 'M') AND [Value] IS NOT NULL", "[Text] ASC").CopyToDataTable.DefaultView

[GBeats]

Hi thanks for the reply..

i changed it to

Dim View As New DataView
        View = DT.Select("[Type] IN ('P', 'L', 'M') AND [FYID] IS NOT NULL", "[FeedYardName] ASC").CopyToDataTable.DefaultView
        DT_Feedyard = View.ToTable(True, "FYID", "FeedYardName")

but it still giving the same results, i did look a little hard and found the the DataView is receiving data but the list is empty as in its nothing. but the row count reflects something in there....

ill try removing my select statement and see if it is that causing the problem..

i just had a thought, the DT table is huge with many columns but the DT_FeedYard only has 2 columns..can i tell the select only to select 2 columns? because i think its more like a filter than a select right?


let me know if you have any other ideas

[GBeats]

OK so i found out the DT, View and DT_FeedYard do have data, using the DataTable Viewer which i just found shows there is data and the right number of columns etc...so

DT_FeedYard is bound to a BindingSource which i use for filtering and then the BindingSource is bound to the combobox... i use the command 'FilterBinder_Feedyard.ResetBindings(True)' right after i make the change on DT_FeedYard.

What i also noticed is the default items i enter into the table before running this code are still there which tells me the new data is being rejected?!?! as the old data is still in there, i was expecting it to be erased..

[kpmc]

Maybe a rowfilter instead. Im not sure if it will support the IN operator.

        Dim view As DataView = dt.Copy.DefaultView
        view.RowFilter = "Type = 'P' OR Type = 'L' OR Type ='M' AND Value IS NOT NULL"
        DT_Feedyard = view.ToTable(True, "Value", "Text")

[GBeats]

im closing this thread because right now it looks as if all the data is there in the table so there must be a problem somewhere else, not sure where but its not with the datatable not getting the data..... ill make a new thread.

[kpmc]

Just a sidenote then, I think that this line is selecting dist values from both value and text cols

 DT_Feedyard = view.ToTable(True, "Value", "Text")