Need help with Record Count

[carla]

Good Afternoon,

I have an HTML form in which users input a list of comma delimited numbers. Those numbers are then displayed in a table with other corresponding information from the database. The part I am having trouble with, is that I need to display a distinct count of the KA_ID field below the table. I've tried doing it in an SQL statement and I've tried doing it by using rs.RecordCount. Neither method has worked for me yet.

Does anyone have any ideas? Here is my code:

dim str1, str3, i
str1 = Replace(Request.Form("searchvalue&quot,"'","''&quot
str3 = split(str1,",&quot
for i = 0 to ubound(str3)

Set RS = Server.createobject("ADODB.Recordset&quot
Set MyConn = Server.CreateObject("ADODB.Connection&quot
MyConn.Open "UID=xxxxxxx;pwd=xxxxxx;DRIVER={Microsoft ODBC for Oracle};SERVER=xxxxxx"

SQL = "select KA_ID,BUG_ID, CREATED_BY from KA where BUG_ID like '%"&str3(i)&"%'"

RS.Open SQL, MyConn, 1, 1

If RS.EOF <> true Then
Do while RS.EOF <> true

Response.Write &quot;<tr><td class='left'>&quot; & str3(i) & &quot;</td><td class='center'><img src='blue.gif'></td><td>&quot; & RS(&quot;KA_ID&quot & &quot;</td><td>&quot; & RS(&quot;CREATED_BY&quot & &quot;</td>&quot;

rs.MoveNext

Loop

Else
Response.Write &quot;<tr><td class='left'>&quot; & str3(i) & &quot;</td><td class='center'>~</td><td>~</td><td>~</td><td>~</td>&quot;
End If

next

Response.Write &quot;</table></td></tr></table></body></html>&quot;

RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing

%>

Any help is appreciated!
Best Regards,
Carla

 

[gpzcrasher]

Hi

You could try ..

SQL = &quot;select count(distinct KA_ID) from KA where BUG_ID like '%&quot;&str3(i)&&quot;%'&quot;

Your recordset will contain the number.

However I believe it will not work with some databases such as access.

Hope this helps.



--------------------------------
If it ain't broke, don't fix it!

 

[Genimuse]

Hmm, only distinct KA_IDs, eh?

Well, I can think of a few ways to do it. The first is via a SQL statement you'd create as you go and execute after you've retrieved all of your earlier results. In semi-pseudocode it would look something like this:

Dim CountString

Your For Loop
  If CountString <> &quot;&quot; Then
    CountString = CountString & &quot; OR &quot;
  End If
  CountString = CountString & &quot; BUG_ID LIKE '%&quot; & str3 & &quot;%' &quot;
  Your other lines of code
Next

CountString = &quot;SELECT COUNT(DISTINCT KA_ID) FROM KA WHERE &quot; & CountString

YourCount = the results of that query

Another way to do it would be either with an array that you gradually fill or a string that you gradually grow. I'll show the latter because it's less effort on my part. Assuming KA_IDs are numeric:

Dim IDList

Your Do While Loop
  If InStr(IDList, "#" & RS("KA_ID") & "#") = 0 Then
    IDList = IDList & "#" & RS("KA_ID") & "#"
  End If
  Your other lines of code
Wend

'At the end of the code, outside the For loop
YourCount = UBound(Split(IDList, "##")) + 1

 

[Genimuse]

My post took a while to write. I wanted to note that the idea behind gpzcrasher's post is a good one, but it will only give you a count of the unique records for each recordset (which you could then sum). Unfortunately you have no way of knowing if the KA_IDs in each count overlap the KA_IDs in other recordsets, so the count could be high. Hence the more complex code I wrote.

 

[Genimuse]

(When I wrote &quot;Wend&quot; two posts up I meant &quot;Loop&quot;.)

 

[onpnt]

sure ya did

____________________________________________________
[sub]The most important part of your thread is the subject line.
Make it clear and about the topic so we can find it later for reference. Please!! faq333-3811[/sub]

http://www.onpntwebdesigns.com

 

[Genimuse]

You're right. When I wrote &quot;Your Do While Loop&quot; four posts up I meant &quot;While Loop&quot;.



(carla, I'm just kidding on this.)

 

[Tarwn]

I would think the recordcount would work for this particular situation. He is pulling the KA_ID field out of a table called KA, which means that all of the KA_IDs are going to be distinct already because they are likely the primary key for this table and the query is not crossing or joining any tables.
Using asOpenKeyset (1) or adOpenStatic(3) should allow the object to have a valid record count attribute.

I don't understand why this wouldn't have worked with the code you have above, was it returning -1 or an error?

[sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Do you know how hot your computer is running at home? I do

http://www.tiernok.com/hscripts/systemp.asp

 

[Genimuse]

RecordCount won't work because of the way the loop works. Here's the logic with a RecordCount and why it fails:
[ol][li]User enters &quot;345,67,45&quot; and submits
[li]The entry is broken into 345 67 45
[li]First time through the loop the query looks for BUG_IDs like %345% and finds 6 matches.
[li]Total records so far is 6
[li]Second time through the loop the query looks for BUG_IDs like %67% and finds 3 matches
[li]Total records found so far is 9
[li]Third time through the loop the query looks for BUG_IDs like %45% and returns 12 matches. Note that 6 of these are the very same ones found in step 3 but the application has no way of knowing that.
[li]Total records found so far is 21[/ol]The actual distinct IDs, though, is actually 15.

RecordCount won't work.

 

[Genimuse]

To clarify further, and to make things worse, step 5 could easily be matching duplicates as well, like a BUG_ID of &quot;12345678&quot; which would be counted 3 times using the RecordCount method.

A solution like one of mine (or something more clever) is required.

 

[Tarwn]

I misunderstood since they had already tried recordcount. I was attempting to clear that portion of it up. Using server-side scripting to count each distinct record is not going to be an efficient method to find the total number.
A more efficient overall method would be to simply send a single SQL statement to the server rather than loop through an unknown number of passed arguments, creating an SQL statement for each. This would only have one negative effect, you would lose the duplicates for your display later on. Basically you could turn the SQL statement into a set of OR statements using Replaces on the comma-delimited string. Then execute the one SQL statement and use the recordcount of that.

As far as counting the unique/distionct Id's, you would still be better off simply executing another SQL statement to the database. Looping through recordsets is slow and inefficient. You could build a single sql statement like so:

cntSQL = "SELECT COUNT(KA_ID) FROM KA WHERE BUG_ID Like '%" & Replace(str1,", ","%' OR BUG_ID Like %'") & "%'"

This sis similar to Genimuses first solution except we have removed the extra string concataenations and trimmed the whole thing down to a single line. In this case you would still do the seperate queries.

To do it all in one query (losing the duplicate entries) you would want something like this:

allSQL = "Select KA_ID, BUD_ID FROM KA WHERE BUG_ID Like '%" & Replace(str1,", ","%' OR BUG_ID Like %'") & "%'"

You could even order this by either KA_ID or BUG_ID. Unless a user is trying to compare in a single page the number of errors from one versus the other, they wouldn't need the duplicates and it is only slowing the process down to keep making multiple queries to the database. If the user was trying to compare results based on two differant keywords they would want seperate recordcounts for each as a comparison factor and would tgus have to do two diffreant searches in any case.

-Tarwn


[sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Do you know how hot your computer is running at home? I do

http://www.tiernok.com/hscripts/systemp.asp

 

[carla]

Thanks for all of your responses. Quite a bit to absorb, but I am trying! )

I tried adding in an additional SQL statment after the first one, to count the number of records, and it works somewhat, but not exactly. What I am having trouble with now is the placement of the additional SQL statement in the code.

If I place the statement before the line that says &quot;next&quot; I get the count for each row (0 or 1), when what I really want is the count for all rows. If I place the statement after the line that says &quot;next&quot;, I get a &quot;subscript out of range&quot; error.

Thanks for all of your help and patience. I am not really an experienced programmer and I have had this work project dumped in my lap. I am trying to muddle my way through it and appreciate the pointers!

Regards,
Carla

 

[Genimuse]

The first and most important question is this: Is it intentional that using the code you wrote that there may be duplicate KA_IDs shown in each table you write? That is, the way the code works now the results might look like this:

 45   12345  Bob Smith
 45   23456  Tom Jones
 45   34567  Sue Stone

 56   23456  Tom Jones
 56   34567  Sue Stone
 56   11561  Mary Blige

Notice how KA_IDs &quot;23456&quot; and &quot;34567&quot; show up under both tables. Is this desired? Or would it be fine if it looked like this:

Searched for "45,56"

12345  Bob Smith
23456  Tom Jones
34567  Sue Stone
11561  Mary Blige

The former method uses a lot more machine resources and will take longer to process, but if there's some value in the repeating of the information (or being able to specify which item was matched) then the code can be wrangled to make that happen, too (similar to the way you've been doing it).

Tarwn: I assumed that she needed the former for some reason, but agree that it's less than ideal if it can be avoided and also note that I dig your one-line replace.

 

[Tarwn]

I guess it's a lot easier to see where I am going with things when I post the whole thought all at once

[sub]01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101 [/sub]
[sup]29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19[/sup]
Do you know how hot your computer is running at home? I do

http://www.tiernok.com/hscripts/systemp.asp

 

[carla]

Hi Genimuse,

To answer your question, yes it is intentional that the KA ID may show up more than once in the table. The main thing the users want to see is the BUG_ID field. The KA (KA_ID) may contain more than bug id in the BUG_ID field. If the user inputs 2 BUG_IDs in the HTML form that appear in the same KA_ID, then both KA_IDs would appear in the results.

Hope this makes sense. )

 

[Genimuse]

Ok, then you'd put the SQL in right after your RS.Close but before your MyConn.Close (adding in another RS.Close) like this (the blue stuff is your existing code):

RS.Close

SQL = "SELECT COUNT(KA_ID) AS MyCount FROM KA WHERE BUG_ID Like '%" & Replace(Replace(str1," ",""),",","%' OR BUG_ID Like %'") & "%'"

RS.Open SQL, MyConn, 1, 1

Response.Write("Total Matches: " & RS("MyCount"))

RS.Close

MyConn.Close

Note that there's not much error checking here. If the user enters extra commas or double spaces then the code will fail, but this will work if the user enters something valid. The code would need to be rewritten a bit to handle such errors (but the original code has the same problems, unfortunately).

(Thanks to Tarwn for the clever replace -- I modified it a bit to handle cases where the user didn't put spaces between the comma-delimited items.)

 

[DreXor]

this is going to seem a little off the wall and possibly redundant, but seems most of the responses have been about how to re-write the sql statement or to handle the count of KA_ID in order to get proper counts so here goes :

reconfig the 2 loops and the SQL generation to something of this nature :

If IsArray(Str3) Then ' this is to ensure there was submission data

SQL = &quot;select DISTINCT KA_ID,BUG_ID, CREATED_BY from KA where &quot;
' initializing the string, but not inserting the values
For i=0 to ubound(str3)

SQL = SQL & &quot;BUG_ID like '%&quot;&str3(i)&&quot;%' OR &quot;

Next
SQL = Left(SQL,Len(SQL)-4) ' this is to remove the last &quot; OR &quot; added to the end
SQL = SQL & &quot; Order By Bug_ID&quot; 'this is to help with the output ordering, since this method will remove duplicates

Set RS = Conn.Execute(SQL) ' open the recordset
If Not RS.EOF Then
KA_Count = RS.RecordCount
for i=0 to Ubound(str3) ' the for next at this point is optional due to the changed format of record retrieval
Do while not RS.EOF
If instr(1,RS(&quot;BUG_ID&quot,str3(i),vbTextCompare) > 0 Then ' this is to remove some of the dupes from the for cycle, or the unmatching returns
'output code
End If
RS.MoveNext
Loop
Next ' optional see above, if this is used, note that records will be repeated frequently due to matching methods




another option is to leave out the conditional in the loop and take out the for next statements commented above, i have a highlighter function i supplied on this forum under the heading of &quot;Functions.inc&quot; that you could apply for each of the search criteria, and thus highlighting in the output the search strings, even if say in the example earlier of 45 and 345 it would highlight not only the 345 but the 45 within it, but it would just appear that 345 was highlighted.

hope the info helps,
DreX

sometimes logic isn't logical, especially when the logic is illogical