Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need to use filter for 2D array 1

Status
Not open for further replies.

AlaskanDad

Programmer
Mar 1, 2002
188
US
I've got a recordset that I'm bringing into a two dimensional array. It will look something like this:

1234 324568
2345 456789
1222 308923
5555 984023

I have a large grid of table cells named from 1000 to 7777. I want to display the six digit number in the appropriate cell and leave the others open. Since I only expect my array to have under 100 rows, I don't want to do 7777 filtered database queries. Instead, I'd like to just do a quick filter on the array that I've pulled.

On each cell, I'd like to do something like this:

IF ThisCellsName is found in the first column of the array THEN
response.write TheSecondColumnNumber
Else
response.write "Open"
End If

Any suggestions on how to perform a filtered search on a two dimensional array?

Thanks,
Rob
 
Would it be a problem to leave it as a recordset and use the recordset.filter object to filter out the data you want froma particular row? If you do that then you can use a do until recordset.eof to populate the data in a grid and only populate the records you filtered out.

If you need an example let me know.

Thanks

Cassidy
 
I would appreciate an example!

I've gotten out of the habit of using recordsets... I send everything to an array through getrows.

Again, I'll have to make a query into the recordset for each of the 1000 to 7777 cells as I have to create each cell.

Thanks!
 
Why not just create a comm delimited list of all the values you want to match against, then "SELECT * FROM Wherever WHERE myField IN (" & yourListHere & ")"

That way you only get back the values your interested in , cutting down on communications time, increasing efficiency of Recordset and subsequent call to GetRows, and increasing memory management efficincy by declaring as small an array as possible (ie, only 100 elements instead of 7000).

-T

01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website:
 
Ok here is an example of how it works.
<!-- #include file="adovbs.inc" -->
<%
dim rs
dim sFilter
dim i

set rs = Server.CreateObject ("ADODB.Recordset")

rs.Fields.Append "Data1",adDouble
rs.Fields.Append "Data2",adDouble

rs.Open

rs.AddNew
rs("Data1") = "123456"
rs("Data2") = "654321"
rs.Update

rs.AddNew
rs("Data1") = "789101"
rs("Data2") = "101987"
rs.Update

rs.AddNew
rs("Data1") = "121314"
rs("Data2") = "413121"
rs.Update

rs.AddNew
rs("Data1") = "151617"
rs("Data2") = "716151"
rs.Update

rs.MoveFirst

sFilter = "Data1 = 123456"

rs.Filter = sFilter
rs.MoveFirst

do until rs.EOF = True
Response.Write rs("Data1") & "&nbsp" & rs("Data2") & "<BR>"
rs.MoveNext
loop

%>

Please keep in mind that you need to also include the "adovbs.inc" file in order for the field appends to work. if you need a copy of that I will be happy to send it to you.

Let me know if this helps.

Thanks

Cassidy
 
I think the recordset filter may be the way to go.

To better explain my goal, imagine that I created a table that has names like this:

1001 1011 1021 1031 1041 1051 1061 1071
1002 1012 1022 1032 1042 1052 1062 1072
1003 1013 1023 1033 1043 1053 1063 1073
1004 1014 1024 1034 1044 1054 1064 1074
1005 1015 1025 1035 1045 1055 1065 1075
1006 1016 1026 1036 1046 1056 1066 1076
1007 1017 1027 1037 1047 1057 1067 1077

My recordset looks like this:

1014 ABCDEF
1033 BDFGAE
1055 JKLMLL
1012 WUIOOP
1063 JNQQDF
1007 LPASIV

What I'd like to see displayed is this (kinda wavy but you get the idea):

Open Open Open Open Open Open Open Open
Open WUIOOP Open Open Open Open Open Open
Open Open Open BDFGAE Open Open JNQQDF Open
Open ABCDEF Open Open Open Open Open Open
Open Open Open Open Open JKLMLL Open Open
Open Open Open Open Open Open Open Open
LPASIV Open Open Open Open Open Open Open

You can see how the ID in the first column tells the grid where to place the name from the second column of the recordset.

Would it be most efficient to run through the recordset each time with a filter? In this example, I have 49 cells. In my real problem, I have almost 2,500 cells.

Thanks for your continued input.
 
you can getrows the recordsets,handle them as arrays, nest loop your qualifiers inside your raw data, repeat cyle through your qualifiers for each raw data, if a match is hit dump the qualifier loop and output what you want, say open etc..

example without using getrows :
( although getrows and array handling will be much more efficient, but i dont know of the static nature of this )

raw = tableRecordset
qual = qualifierRecordset

do while not raw.eof [green]' move vertically in the grid[/green]
for each field in raw.fields [green]' this is to move horizontally in the grid[/green]
do while not qual.eof
found = false [green] ' set and reset default to make "open" appear[/green]
if qual(ID) = raw(ID) then [green]' checking for like numbers in both sets[/green]
found = True [green]' flips the default[/green]
exit do [green]' found, dont need to keep searching in this recordset[/green]
end if
qual.movenext
loop
if found then
response.write qual(Letters&Numbers)
else
response.write "open"
end if
Next
raw.movenext
loop



OR

in the exact example you specifically gave, perhaps a replace environment would be better?
 
Wonderful!

This is why I keep coming back here. :)

I really appreciate the reminder to leave the loop once I've found a match. That will really speed things up.

This is what I've adapted from what I've seen. PeopleArr is my two columned array after a getrows.
For each cell:

Found = False
For a = 0 to Ubound(PeopleArr,2)
If PeopleArr(0,a) = CurrentCellName Then
Found=True
PersonName=PeopleArr(1,a)
Exit For
End If
Next ‘a
If Found Then
Response.write PersonName
Else
Response.write “Open”
End If

For my benefit, what would a replace example look like?

Thanks again,
Rob
 
well replace example would be more along the lines if that was a text blob instead of a presplit array, say it was a csv file you loaded via FSO, if you did a 2 way split on it you'd end up with your getrows, if you left it as a text blob you could use a single replace per qualifier on the specific instances throughout the blob, although, it wouldn't do anything for the extra values, and of course later on you'd have to split it anyhow to put into a pretty table :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top