ADO Recordset Filter Query
Hello,
I have a recordset that will have data looking something like the following
C1 C2 C3 C4 C5 C6
AAA BBB CCC DDD EEE FFF
AAA BBB AAA DDD EEE FFF
AAA BBB DDD DDD EEE FFF
I want to consolidate the ADO Recordset on C1, C4, C5, C6 and discard C2/C3 . If this was pure SQL I would write a query something like:
Select Distinct C1, C4, C5, C6 ... Is there a way to do this to the ADO Recordset maybe using the Filter() method? The recordset I am working with is quite large (10,000+ records) so I wanted some optimized way of doing this.
Thanks.
Dwight
Hi Dwight,
I am assuming in this answer that C1, C2, etc are columns in the database.
I would suggest using ADO.Net as opposed to ADO. ADO.Net has been optimized to run with SQL and contains additional features/commands that can make life easier. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/adonetanchor.asp for more information on the benefits you would recieve using ADO.Net.
The recommended method to do this type of desired funtion, and this will also provide the optimized results, is to use a SQL Stored Procedure. The reasons this is a best practice is that the CPU load will be placed on the Server, the network traffic is reduced by (at minimum) 1/2 because you are only waiting for a reply from the server, and you can use direct SQL statements if you are more comfortable working with SQL directly. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_7yw5.asp for information on SQL Stored Procedures; this will provide a great detail of information on the benefits and also good examples. I would ultimately recommend this method over everything else where possible.
There are two fairly easy ways to SELECT specific fields in VB.Net., there are other methods also; these are the two the come straight to mind for me...
First, this is the easiest way, use a TableAdapter and DataSet controls. Once they have been associated to the appropriate database and table you can double-click on the DataSet you created in your Solution Explorer. The DataSetDesigner window will appear; in here you can configure your TableAdapter. You would do this by first selecting the appropriate TableAdapter; then right click on it and select 'Add Query...'; then follow the on-screen wizard to create the filter as appropriate. You will find that if you use the 'Select an SQL statement' and choose the 'Query Builder' you can easily select and de-select the fields (columns) you want to be returned in your results.
The second way, using VB.Net coding, is to create a DataView object and fill it with the information. You can then step through the DataView and fill controls, variables, etc...with the returned results as you desire. The added benefit is to be able to later filter the DataView even more specific. The example below will show you a method to fill a DataView object with only certain rows; this is not a best practice method! See the above URL for best practices with ADO.Net
Public Sub GetSelectedRecords()Dim dvFilteredRecords As Data.DataView
Dim strSELECT As String = "SELECT C1, C4, C5, C6 FROM TableName"
dvFilteredRecords = New DataView(DataSet1.TableName, strSELECT)
Dim intCurrencyCount As Integer
For intCurrencyCount = 0 To dvCurrency.Count - 1
'Do code here
'Example below shows how to step through and fill a combobox
combobox1.Items.Add(dvCurrency(intCurrencyCount)("CurrencyCode"))
NextEnd SubI hope the above information helps you out; I am sure one of these three options will suit your needs.
Good Luck!
Thank you,
James
