If Then Loop - help with my poor scripting

Hi

I am a relative beginner with VBscript AND VERY INEXPERIENCED WITH if,
Then Loops. I have an ASP page which successfullly prints out a list of
all GroupID's held in a variable named GroupsAll and another list of
GroupID's held in a variable named GroupsJ (ie the groups a member has
joined).

My Problem
Under the 2 lists above, I then tried (using a loop within a loop) to
print out a list of the GroupID's which were in GroupsAll but not in
GroupsJ (it should have produced 1 record).

The script runs OK but no additional records are printed.

The script is shown below:

Any help would be appreciated.

Colink

===========

'Loop through the GroupsAllrecordset THIS LOOP PRINTS 5 RECORDS
Do While not rsGroupsAll.EOF
'Write the HTML to display the contents of the recordset

%>

Re: If Then Loop - help with my poor scripting

iam247@gmail.com wrote:
> Hi
>
> I am a relative beginner with VBscript AND VERY INEXPERIENCED WITH if,
> Then Loops. I have an ASP page which successfullly prints out a list
> of all GroupID's held in a variable named GroupsAll and another list
> of GroupID's held in a variable named GroupsJ (ie the groups a member
> has joined).
>
> My Problem
> Under the 2 lists above, I then tried (using a loop within a loop) to
> print out a list of the GroupID's which were in GroupsAll but not in
> GroupsJ (it should have produced 1 record).
>

I suspect that you're on the wrong track. Unless your database is poorly
designed, you can probably use a JOIN in your SQL statement to return a
single recordset containing all the data you need. No need to create the
overhead of two separate recordsets. Give me an idea about what your
database looks like (table names, relevant field names and datatypes,
database type and version), and I am sure I can provide a more efficient
method for you to accomplish this goal.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: If Then Loop - help with my poor scripting

Hi Bob

Thanks for the offer to build a suitable SQL statement for me.

Here are the details

Database MS Access 2002


tblContacts
ContactID Lng Integer - example data = 1,2,3,67,88,99
(Other fields not relevant)

tblGroups
GroupID Lng Integer - data = 1,2,3,4,5
GroupName Text - matching data = Football, Rugby, Cricket, Hockey,
Netball

tblGroupContact
ContactID Lng Integer - example data = 1,1,1,2,67
GroupID Lng Integer - data = 1,2,3,2,5

ie from the above, ContactID 1 is a member of groups 1,2 & 3 but not
groups 4 & 5.
**************
I want my sql to find (for a particular ContactID)the group names of
the groups they are not a member of. ie Hockey and Netball
**************
I already have an SQL that finds the ContactID from tblContacts and
assigns it to a variable named ContactID. I also have other SQL's which
finds ALL groups from tblGroups which may or may not be useful.

I think this needs to be done in 2 stages. I look forward to seeing
your solution.

Thanks Colin

Please do not hesitate to request further information.



> I suspect that you're on the wrong track. Unless your database is poorly
> designed, you can probably use a JOIN in your SQL statement to return a
> single recordset containing all the data you need. No need to create the
> overhead of two separate recordsets. Give me an idea about what your
> database looks like (table names, relevant field names and datatypes,
> database type and version), and I am sure I can provide a more efficient
> method for you to accomplish this goal.
>
> Bob Barrows
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"

Re: If Then Loop - help with my poor scripting

You are sort of correct that the solution will require two stages. However,
that does not translate into two trips to the database. There is a special
type of JOIN that is relevant for this task: the outer join. Since you are
using Access, you should look it up in online help since this example will
only utilize one aspect of this type of join.

This example will also utilize a "derived" or "virtual" table, which is
defined as a table, a set of rows, derived as the result of a select
statement. The derived table can be used in the FROM clause of another
query. There are two ways this can be accomplished. The first that I will
demonstrate utilizes a saved query created using the Access Query Builder.
Open your database in Access and switch to the Queries tab. Then
double-click the item in the window that says "Create New Query in Design
View". Close the Choose Tables dialog without selecting a table, switch to
SQL View and paste this sql statement into the window:

SELECT GroupID FROM
tblGroupsContacts
WHERE ContactID = [pContactID]

Run the query, entering 1 when you are prompted for a value for
[pContactID]. You should see a list of GroupIDs to which ContactID 1
belongs. Click the Save toolbar button (or do File|Save from the menu) and
save this query as "qGroupsForSpecifiedID".

Now create another query in Design View, again switching to SQL View without
selecting any tables and paste this sql statement in:

SELECT g.GroupID, GroupName
FROM tblGroups As g LEFT OUTER JOIN
qGroupsForSpecifiedIDAs q
ON g.GroupID = q.GroupID
WHERE q.GroupID Is Null

Run this query, again supplying 1 when prompted. You should see your desired
result.

The other way to do this is to use a subquery, like this (assumes Access
2000 or later):

SELECT g.GroupID, GroupName
FROM tblGroups As g LEFT OUTER JOIN
(SELECT GroupID FROM
tblGroupsContacts
WHERE ContactID = [pContactID]) As q
ON g.GroupID = q.GroupID
WHERE q.GroupID Is Null

Which version should you use? Well, it depends on whether or not you intend
to re-use the sql used in the subquery. If this is the only place it will be
needed, then use the second (subquery) version. If you see other
applications for the sql in the subquery, then you should put it in the
saved query so it can be reused whenever you need it for other queries,
without having to rewrite it.

In either case, you should save the final version as
"qGroupsNotJoinedBySpecifiedContact" so you can call it from ASP like this:

contactid = clng(1)
Set rs=createobject("adodb.recordset")
conn.qGroupsNotJoinedBySpecifiedContact contactid , rs
If not rs.EOF then
'process the array - I recommend using GetRows*
else
'no records were returned
end if

Here is more information about using this technique to run saved parameter
queries from asp:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/b3d322b882a604bd
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

and this illustrates why I recommend using saved parameter queries instead
of dynamic sql:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

OK, I've shown you how to achieve your specific requirement: "I want my sql
to find (for a particular ContactID)the group names of
the groups they are not a member of. ie Hockey and Netball"
Hopefully you'll be able to build on this to retrieve other data you need
for your page without making multiple trips to your database.

Bob Barrows
*Should I use recordset iteration, or GetRows(), or GetString()?
http://www.aspfaq.com/show.asp?id=2467


iam247@gmail.com wrote:
> Hi Bob
>
> Thanks for the offer to build a suitable SQL statement for me.
>
> Here are the details
>
> Database MS Access 2002
>
>
> tblContacts
> ContactID Lng Integer - example data = 1,2,3,67,88,99
> (Other fields not relevant)
>
> tblGroups
> GroupID Lng Integer - data = 1,2,3,4,5
> GroupName Text - matching data = Football, Rugby, Cricket, Hockey,
> Netball
>
> tblGroupContact
> ContactID Lng Integer - example data = 1,1,1,2,67
> GroupID Lng Integer - data = 1,2,3,2,5
>
> ie from the above, ContactID 1 is a member of groups 1,2 & 3 but not
> groups 4 & 5.
> **************
> I want my sql to find (for a particular ContactID)the group names of
> the groups they are not a member of. ie Hockey and Netball
> **************
> I already have an SQL that finds the ContactID from tblContacts and
> assigns it to a variable named ContactID. I also have other SQL's
> which finds ALL groups from tblGroups which may or may not be useful.
>
> I think this needs to be done in 2 stages. I look forward to seeing
> your solution.
>


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: If Then Loop - help with my poor scripting

Hi Bob

Thanks for your detailed answer.

I will have a go at implementing it tonight or Wednesday.

Thanks ColinK

Re: If Then Loop - help with my poor scripting

Hi Bob

Thanks for your help. I got the Optiion 1 Access queries working fine.

I did some research into Get Rows, but cannot implement on my page.

I tried some Do, While, loops but always get 1 wrong record for any
given ID, rather than up to 5.

I changed Conn.P..... to AdoCon.P... to match my existing ado
connection.

I addded Dim rs

I would really appreciate your help with processing the array.

I want <%Response.Write (rs("GroupName"))%> to print out the relevant
rows.

contactid =3D clng(1)
Set rs=3Dcreateobject("adodb.records=ADet")
adoCon.qGroupsNotJoinedBySpecifi=ADedContact contactid , rs
If not rs.EOF then
'process the array - I recommend using GetRows*
else=20
'no records were returned=20
end if

Re: If Then Loop - help with my poor scripting

I can't do this blind. Please show me the result of:

<%
'open the recordset, then:
if not rs.eof then
response.write "

Re: If Then Loop - help with my poor scripting

Hi Bob

Your suggestion worked nearly perfectly, the loop need modified
slightly to get the 2 related fields to print beside each other.

However, my original attempt at processing the recordset was OK, the
problem was my naming of ContactID and rsContactID.

With regard to arrays and recordsets (as per your other contribution
today), you are correct it is an a recordset not an array; if I
understand the difference correctly!

By now you are very aware that I am on a steep learning curve.


Is this correct?

An array can contain exactly the same data as a recordset. Both are
evffectively tables; however an array is indexed so that any individual
element (field) can be individually referenced, where a record set must
be procesed as a whole. Even if I am correct I am sure it could be
worded better.

Thanks ColinK

Re: If Then Loop - help with my poor scripting

iam247@gmail.com wrote:
>
> An array can contain exactly the same data as a recordset.
> Both are
> evffectively tables; however an array is indexed so that any
> individual element (field) can be individually referenced, where a
> record set must be procesed as a whole. Even if I am correct I am
> sure it could be worded better.
>
> Thanks ColinK


For starters, if you do not have the vbscript documentation, you should get
it now from:
http://tinyurl.com/7rk6
This download will also contain the documentation for jscript and for the
scripting host components: Regular Expressions and FileSystemObject


An array is a data structure that is intrinsic to vbscript (builtin). It
knows nothing about databases or tables or anything except how many
dimensions and how many pieces of data are contained in it. So that's the
first of its differences from a recordset. Another difference is that a
recordset (unless it's a rarely used hierarchical recordset) is strictly
two-dimensional: columns and rows (fields and records). An array can be
created with ahy number of dimensions, including a single dimension:

ar = array(1,2,3)

The only means of accessing any of the data elements in the array is by use
of the array's index. There is no metadata (data about data) stored in an
array.

On the other hand, a recordset, which is a COM object supplied by the ADODB
COM library, is a cursor engine used to work with data from databases*.
Since it is a cursor engine, many things are easily done with a recordset
that are very difficult to do with arrays : filtering, sorting, etc.** As a
COM object, a recordset is a class which consists of various properties,
collections and methods all of which enable the developer to accomplish
various tasks. The recordset is a large bulky object compared to an array
due to its need to contain metadata: field names, types, sizes, underlying
values, etc. as well as all the things a cursor engine needs to make
navigation through the recordset possible.

Bob Barrows


*Ad hoc recordsets, i.e., recordsets created from scratch and populated
without ever being connected to a database, are possible, but rarely used.
For the most part, when you see a recordset in somebody's code, it exists as
the result of retrieving some data from a database.

**I am talking only about VB/VBA/vbscript arrays here. I am aware that
arrays created by other languages, such as jscript, have builtin methods for
sorting, etc.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"