Show region if three recordsets are emtpy and show if any of three have data.

I have two regions. I want to show region A if ALL three recordsets are empty.  
And I want to show region B if any of the recordsets contain data.

I have the following code to show region A when one of the recordsets is empty:

 <% If rs_linkedcorpusers.EOF And rs_linkedcorpusers.BOF Then %>
REGION A
<% End If ' end rs_linkedcorpusers.EOF And rs_linkedcorpusers.BOF %>

Open in new window


I have the following code to display region B when there is data on the first recordset:

<% If Not rs_linkedcorpusers.EOF Or Not rs_linkedcorpusers.BOF Then %>  
REGION B
<% End If ' end Not rs_linkedcorpusers.EOF Or NOT rs_linkedcorpusers.BOF %>

I know that the first one I need code to display when first_recordset AND second_recordset AND third_recordset are empty
And second recordset to display region B when first_recordset OR second_recordset OR third_recordset have data

I just don't know the syntax for it and want to make sure I am doing it right. Below are the names of the recordsets:

First recordset: linkedcorpusers
Second recordset: linkedcontacts
Third  recordset: linkedcases

I tried the code below for the RegionA:

 <% If (rs_linkedcorpusers.EOF And rs_linkedcorpusers.BOF) AND (rs_linkedcontacts.EOF And rs_linkedcontacts.BOF) AND (rs_linkedcases.EOF And rs_linkedcases.BOF) Then %>

Open in new window


I tried the code below for region:

<% If Not (rs_linkedcorpusers.EOF Or Not rs_linkedcorpusers.BOF) OR (rs_linkedcontacts.EOF Or Not rs_linkedcontacts.BOF) OR (rs_linkedcases.EOF Or Not rs_linkedcases.BOF) Then %>  

Open in new window


Unfortunately it shows region B in both cases  :$
LVL 1
AleksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
You can use the If Else End of control flow. So use the first code block you posted. Add an <% else if %> after region A and then your region B.
AleksAuthor Commented:
Yes, still there is something wrong with the first part of the code. It is now showing REGION A when it should since all the recordsets are empty.
Shaun KlineLead Software EngineerCommented:
If your datasource returns the record count, instead of using BOF / EOF, use RecordCount.

So your IF statement would look like:
<% IF recordset1.Recordcount = 0 and recordset2.RecordCount = 0 and recordset3.Recordcount = 0 %>
Region A
<% Else If %>
Region B
<% End If %>

Open in new window

Change recordset1/2/3 to match your recordset names.

If your datasource does not return the record count, you can still use the EOF flag:
<% IF recordset1.EOF and recordset2.EOF and recordset3.EOF %>

Open in new window

Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

AleksAuthor Commented:
I am using a count. There were typos in the code above. This is what I have now, but still even though all 3 recordsets return '0' it shows REGION B  :(

 <% IF rs_linkedcorpusers.Recordcount = 0 and rs_linkedcontacts.RecordCount = 0 and rs_linkedcases.Recordcount = 0 Then %>

REGION A

 <% Else If %>  -- this gives the error below, so I entered  <% Else  %>

REGION B

<% End If %>

Open in new window


Error when using <% End If %> :

Microsoft VBScript compilation  error '800a03ea'

Syntax error

/BlueDot/Intranet/employers/delete.asp, line 148
Else If
-------^


I am showing the count on the screen and it returns this:

Corporate users linked: 0
 Contacts linked: 0
 Cases linked: 0
Big MontyWeb Ninja at largeCommented:
you can only use RecordCount if you use a certain cursor when you open your recordset, otherwise it'll always be zero. you were on the right track originally, simply use:

<% 
If (rs_linkedcorpusers.EOF and Not rs_linkedcorpusers.BOF) AND
   (rs_linkedcontacts.EOF and Not rs_linkedcontacts.BOF) AND 
   (rs_linkedcases.EOF and Not rs_linkedcases.BOF) Then 
%>  

Open in new window


the following code will return true only if all three recordsets are empty
AleksAuthor Commented:
This is an example of one of my recordsets which returns '0' as the count:

<%
Dim rs_linkedcases__MMColParam
rs_linkedcases__MMColParam = "0"
If (Request.querystring("employerid") <> "") Then 
  rs_linkedcases__MMColParam = Request.querystring("employerid")
End If
%>
<%
Dim rs_linkedcases
Dim rs_linkedcases_cmd
Dim rs_linkedcases_numRows

Set rs_linkedcases_cmd = Server.CreateObject ("ADODB.Command")
rs_linkedcases_cmd.ActiveConnection = MM_bluedot_STRING
rs_linkedcases_cmd.CommandText = "SELECT COUNT (id) AS casestotal FROM dbo.Cases WHERE EmpId = ?" 
rs_linkedcases_cmd.Prepared = true
rs_linkedcases_cmd.Parameters.Append rs_linkedcases_cmd.CreateParameter("param1", 5, 1, -1, rs_linkedcases__MMColParam) ' adDouble

Set rs_linkedcases = rs_linkedcases_cmd.Execute
rs_linkedcases_numRows = 0
%>

Open in new window

AleksAuthor Commented:
I think that is what I had to begin with. Turns out doesn't display region A  :#

  <% 
If (rs_linkedcorpusers.EOF and Not rs_linkedcorpusers.BOF) AND (rs_linkedcontacts.EOF and Not rs_linkedcontacts.BOF) AND  (rs_linkedcases.EOF and Not rs_linkedcases.BOF) Then 
%>  
 

Open in new window




Those three records return '0'  ...
Shaun KlineLead Software EngineerCommented:
Ok. Your select statement will always return a row because the count in the select statement will return 0 if no rows.

In that case, you will need to inspect the value of casestotal in the returned row. Try:

If rs_linkedcases("casestotal ") = 0 and ....
Big MontyWeb Ninja at largeCommented:
has to be all on one line, I just did it that way to make it easier to read:

<%
If (rs_linkedcorpusers.EOF and rs_linkedcorpusers.BOF) AND   (rs_linkedcontacts.EOF and rs_linkedcontacts.BOF) AND    (rs_linkedcases.EOF and rs_linkedcases.BOF) Then
%>
Big MontyWeb Ninja at largeCommented:
also got rid of the NOT keyword on the BOF check
AleksAuthor Commented:
I try:

  
  <% 
If (rs_linkedcases("casestotal") = 0 AND rs_linkedcorpusers("corptotal") AND rs_linkedcontacts("conttotal") = 0 Then )

%>

Open in new window


Error:

Microsoft VBScript compilation  error '800a03ee'

Expected ')'

/BlueDot/Intranet/employers/delete.asp, line 133
If (rs_linkedcases("casestotal") = 0 AND rs_linkedcorpusers("corptotal") AND rs_linkedcontacts("conttotal") = 0 Then )
----------------------------------------------------------------------------------------------------------------^
Shaun KlineLead Software EngineerCommented:
Your end ) should be before Then.

If (rs_linkedcases("casestotal") = 0 AND rs_linkedcorpusers("corptotal") AND rs_linkedcontacts("conttotal") = 0) Then

Open in new window


You might also need to perform a MoveFirst on each record set. You shouldn't need to as the cursor should be positioned at the start of your data set.
AleksAuthor Commented:
ok. no error but still shows region B   :(
AleksAuthor Commented:
both solutions show region B still
Big MontyWeb Ninja at largeCommented:
for which code?
AleksAuthor Commented:
I tried:

<% If (rs_linkedcases("casestotal") = 0 AND rs_linkedcorpusers("corptotal") AND rs_linkedcontacts("conttotal") = 0) Then %>

AND

<%
 If (rs_linkedcorpusers.EOF and rs_linkedcorpusers.BOF) AND   (rs_linkedcontacts.EOF and rs_linkedcontacts.BOF) AND    (rs_linkedcases.EOF and rs_linkedcases.BOF) Then
 %>

Both show REGIONB
Big MontyWeb Ninja at largeCommented:
please post the full code you're using to display the different regions...my guess is that you're getting results back from the recordset
Shaun KlineLead Software EngineerCommented:
In this line of code:
<% If (rs_linkedcases("casestotal") = 0 AND rs_linkedcorpusers("corptotal") AND rs_linkedcontacts("conttotal") = 0) Then %>

Open in new window

It should be:
<% If (rs_linkedcases("casestotal") = 0 AND rs_linkedcorpusers("corptotal") = 0 AND rs_linkedcontacts("conttotal") = 0) Then %>

Open in new window

You were missing the equals zero for the second recordset. Don't know if that will solve the issue. As Big Monty suggested, you'll need to post the full code.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Yep, this last one worked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.