Link to home
Start Free TrialLog in
Avatar of Aleks
AleksFlag for United States of America

asked on

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  :$
Avatar of Shaun Kline
Shaun Kline
Flag of United States of America image

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.
Avatar of Aleks

ASKER

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.
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

Avatar of Aleks

ASKER

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
SOLUTION
Avatar of Big Monty
Big Monty
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

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

Avatar of Aleks

ASKER

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'  ...
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 ....
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
%>
also got rid of the NOT keyword on the BOF check
Avatar of Aleks

ASKER

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 )
----------------------------------------------------------------------------------------------------------------^
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.
Avatar of Aleks

ASKER

ok. no error but still shows region B   :(
Avatar of Aleks

ASKER

both solutions show region B still
for which code?
Avatar of Aleks

ASKER

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
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Aleks

ASKER

Yep, this last one worked.