HELP!!! Creating Nested Data in Repeated Region Problem

Hi All, Would appreciate some assistance with the attached, I'm using some fairly complicated recordsets (ASP VBScript) for a property search website, I have a page where a user selects search by County this in turn takes them to a page which lists all the towns in the county and number of live properties in each, I'm using 4 recordsets for this - 3 of which list the towns (townsA2I, townsJ2R & townsS2Z) the 4th is a Property Count, examples of the recordsets are below (using townsAtoI and LiveProperties -

TownsA2I

<%
Dim TownA2I
Dim TownA2I_numRows

Set TownA2I = Server.CreateObject("ADODB.Recordset")
TownA2I.ActiveConnection = MM_recruta2_STRING
TownA2I.Source = "SELECT towncountyID, Town, County FROM dbo.easytolettowncounty WHERE Town LIKE 'A%' AND County = '" + Replace(TownA2I__MMColParam, "'", "''") + "' OR Town LIKE 'B%' AND County = '" + Replace(TownA2I__MMColParam1, "'", "''") + "' OR Town LIKE 'C%' AND County = '" + Replace(TownA2I__MMColParam2, "'", "''") + "' OR Town LIKE 'D%' AND County = '" + Replace(TownA2I__MMColParam3, "'", "''") + "' OR Town LIKE 'E%' AND County = '" + Replace(TownA2I__MMColParam4, "'", "''") + "' OR Town LIKE 'F%' AND County = '" + Replace(TownA2I__MMColParam5, "'", "''") + "' OR Town LIKE 'G%' AND County = '" + Replace(TownA2I__MMColParam6, "'", "''") + "' OR Town LIKE 'H%' AND County = '" + Replace(TownA2I__MMColParam7, "'", "''") + "' OR Town LIKE 'I%' AND County = '" + Replace(TownA2I__MMColParam8, "'", "''") + "' ORDER BY Town ASC"
TownA2I.CursorType = 0
TownA2I.CursorLocation = 2
TownA2I.LockType = 1
TownA2I.Open()

TownA2I_numRows = 0
%>

LiveProperties

<%
Dim LiveProperties
Dim LiveProperties_numRows

Sub sLivePropertyCount(vLocation)
vCount=0

Set LiveProperties = Server.CreateObject("ADODB.Recordset")
LiveProperties.ActiveConnection = MM_recruta2_STRING
LiveProperties.Source = "SELECT COUNT(PropertyID) As NumberofProperties, propertylive, propertylocation FROM dbo.easytoletproperty WHERE propertylive = 'y' AND propertylocation = " & vLocation & " GROUP BY propertylocation, propertylive"
LiveProperties.CursorType = 0
LiveProperties.CursorLocation = 2
LiveProperties.LockType = 1
LiveProperties.Open()

vCount=(LiveProperties("NumberofProperties"))
Response.write(vCount)

LiveProperties.Close()
Set LiveProperties = Nothing
end sub

LiveProperties_numRows = 0
%>

I've tried following the tutorial here http://www.webthang.co.uk/tuts/tuts_dmx/rob9/rob9.asp but when i review the page i get the following error -

"Microsoft OLE DB Provider for SQL Server error '80040e14'

Invalid column name 'Barnet'.

/PropertiesbyTown1.asp, line 338 "

Where line 338"

Where line 338 is the "LiveProperties.Open()" of the above recordset, when i try to view bindings on this recordset Dreamweaver gives me the following errors -

"Column 'dbo.easytoletproperty.propertylive' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Column 'dbo.easytoletproperty.propertylocation' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

I would appreciate any assistance -

Try adding propertylocation to the GROUP BY clause as indicated by the error message.

HELP! Stored Procedure Problem

I need to pull distinct records in my SP, but if there are different values in some of the columns it grabs those records too. I tried a nested query but I get an error that it is returning more than one value and not allowed.

I can grab the 1st record for each cust I need like this in a view:

SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad
FROM dbo.PropReportData
GROUP BY vcCustId, vcAdsource
ORDER BY vcCustId

and then Inner Join it in my SP like this:

CREATE PROCEDURE Reports_GetReportData
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT *
FROM dbo.vPrePull INNER JOIN
 dbo.PropReportData ON dbo.vPrePull.MinRecNo = dbo.PropReportData.siEntry
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GO

Now, the problem is this, the parameters are in my stored procedure which is parsed second. So I am not getting the unique data to pull from.

Ultimately what I need to use is below with something inside it that will do what the view above did:

CREATE PROCEDURE Reports_GetReportData2
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT TOP 100 PERCENT vcCustId, MIN(siEntry) AS MinRecNo, vcAdsource AS Ad,sientry,vcProspectName,
vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,vcComments
FROM dbo.PropReportData
WHERE
cApartmentSite = @.cApartmentSite and vcAdSource = @.vcAdSource
GROUP BY vcCustId, vcAdsource,sientry,vcProspectName,vcPhone,vcEmail,vcDesiredHome,vcMoveInDate,vcStatus,vcVisitDate,
vcComments
ORDER BY vcCustId
GO

Got to have this done by C.O.B. Monday or I may not have a job.
Thanks.What you want seems doable, but some more info (structure of the tables, some sample data) would help.

Not that I feel pressured or anything here...|||I'm using it to create reports for my CRM application and I used the Reports Starter Kit as my base. I am displaying Each Ad Source the Prospect called in on and the Prospects Record data in a tabular report. In the footer of each Ad Source it gives a count of the total leads that came in on that Ad Source. The problem is this, when a change is made it creates another record for that prospect with a unique record number; this has to be for history purposes, because I have another report that display all record activity, (that one works). So when I display the Prospect records it has a duplicate, which I can cull out by using vcCustID, MIN(siEntry) this gives me the first record entry for that Prospect, BUT, if one of the fields that I am trying to display was changed (like Visit Date in the example below), another instance of the Prospect is displayed. So for example I have a count of 2 unique leads by Ad Source and they call back and change their Visit Date, it will display a count of 2 leads (which is correct) and display 3 records (wrong), showing that Prospect twice.

Example:
ApartmentGuide.com
Prospect Name.Telephone.Email.DesiredHome.Move-In Date.Status.Visit Date
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/23/2003
Bear, Smokey 911-911-9119 smokey@.nofire.com 1 x 1 10/31/2003 Visit Set10/26/2003
Walker, Johnny 555-645-7895 drunk@.booze.com 1 x 1 10/23/2003 Visit Set 10/23/2003

Total Leads this Ad Source: 2

I am pulling the data from a single table called PropReportData that has just the info I need for reporting. This was necessary because the information necessary to create a report is in 9 different tables and the amount of executes necessary to do the inner joins caused major performance issues and after running an execution plan it just didn't seem feasible to continue in that direction.

The table has vcCustID(Unique), cApartmentSite(used to associate Client to Cust to), siEntry(Unique Record Number), Ad Source, etc.

The last Stored Procedure in my first post is what I need to work, it has the parameters in it I need to display client specific data, which uses cApartmentSite. The value is picked up from the UserLogin and put in session to be used with my parameters and a few other things.

Each Prospect record created by my Marketing Associates for that client has this value inserted in a field in the PropReportData table creating the Client to Prospect relationship. So when the client logs it only pulls their information based on the cApartmentSite value in the tables.

Let me know if you need more info.

Thanks.|||This seems to work:

CREATE PROCEDURE Reports_PainInTheButt
(
@.cApartmentSite varchar(25),
@.vcAdSource varchar(50)
)
AS
SELECT DISTINCT
 TOP 100 PERCENT dbo.PropReportData.vcCustId, MIN(DISTINCT dbo.PropReportData.siEntry) AS siEntry,
 MIN(DISTINCT dbo.PropReportData.vcAdsource) AS vcAdsource,
 MIN(DISTINCT dbo.PropReportData.vcProspectName) AS vcProspectName, MIN(DISTINCT dbo.PropReportData.vcPhone) AS vcPhone,
 MIN(DISTINCT dbo.PropReportData.vcEmail) AS vcEmail, MIN(DISTINCT dbo.PropReportData.vcDesiredHome) AS vcDesiredHome,
 MIN(DISTINCT dbo.PropReportData.vcMoveInDate) AS vcMoveInDate, MIN(DISTINCT dbo.PropReportData.vcStatus) AS vcStatus,
 MIN(DISTINCT dbo.PropReportData.vcVisitDate) AS vcVisitDate, MIN(DISTINCT dbo.PropReportData.vcComments) AS vcComments
FROM dbo.PropReportData
WHERE dbo.PropReportData.cApartmentSite = @.cApartmentSite AND dbo.PropReportData.vcAdsource = @.vcAdSource
GROUP BY dbo.PropReportData.vcCustId
ORDER BY dbo.PropReportData.vcCustId
GO

If you see any potential problems with this let me know, it's all I can come up with.

Thanks.