Solved

Creating an asp script or function to count duplicate string variables

Posted on 2009-07-10
5
228 Views
Last Modified: 2012-05-07
Hello, I need help on creating an asp script or function to count duplicate string variables and match them to a table variable for that record. I believe I should use the SQL COUNT function but not sure how to implement it! Example below&

I have a string: (1,2,1,5,5,3,3,3,7,4,6,6,3,3,3) these numbers representing record IDs in my SQL DB. I need to pull the records and count how many times its placed in the string and match that number to a table variable to do something with that record. For instants 3 is shown 6 time, so I need to show that record and check that string count agents a table of that record.

Help on this would be greatly appreciated..
0
Comment
Question by:inrworx
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:rg20
ID: 24825055
something like this?

select count(*) from table group by recordID
0
 

Author Comment

by:inrworx
ID: 24827376
Hello rg20, yes i can use this in the SQL statement, but how do i count up the number in the string first to match it to the records table value?

thanks..
0
 

Author Comment

by:inrworx
ID: 24827459
To add to my last comment, I only have a single record in the DB say record id 3. so Im not really grouping in the SQL statement, I think. Im only trying to pull that single record and match the string count to a value in a table from that record. Hope that helps a bit&
0
 
LVL 9

Accepted Solution

by:
rg20 earned 500 total points
ID: 24827768
For instants 3 is shown 6 time, so I need to show that record and check that string count agents a table of that record

what you really are asking is
You have a counter for a record that counts how many times a record appears.  you then have a string (as above) in a datafield that you need to query that record.  I think

for example
record ID      counter  
2                     2        
3                     3

table 2
string
1,2,3,1,2,3,3

That would lead me to something like this
select string from table 2
you put this in ASP so I will use some of that here
dim arry()
Dim d
Set d=Server.CreateObject("Scripting.Dictionary")

arry = split(rst("string"),",")
'cycle through the array
for x = 0 to ubound(arry)
http://webcheatsheet.com/asp/dictionary_object.php
use a dictionary here to enter the string elements as the key and increment the counter if the key exists
something like
if key exists
    d.item(arry(x)) = d.item(array(x)) + 1
else
    d.add(arry(x),1)
end if
next
once done, you can get the sums in a string
myItem = allItems(i) 'This is the item value
myitem2 = join(myitem,",")
myitem 2 will be part of the where clause

select recordID from table1 where counter in (myitem2)
0
 

Author Comment

by:inrworx
ID: 24828078
humm, can i do something like this, tried to do it within the record set:

while not rs.eof
      arry = split(session("track_batchVals"),",")
      boxcount=0
      tracknumid=rs("f_tracknum")'from the record set
      for x = 0 to ubound(arry)
      if tracknumid=x then boxcount=boxcount+1
      next
rs.movenext
wend
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question