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! Trying to create a trigger in SQL 2000...

Hello All!

I am trying to create a trigger that upon insertion into my table an email will be sent to that that recipeinent with a image attached ( like a coupon)That comes from a different table, problem is, It will not allow me to send the email ( using xp_sendmail) with the coupon attached. I am using varbinary for the coupon and nvarchar for the rest to be sent, I get an error that Invaild operator for data type. operator equals add, type equals varchar.

Looks basically like this(This is my test tables):

CREATE TRIGGER EileenTest ON OrgCouponTestMain
FOR Insert
AS
declare @.emailaddress varchar(50)
declare @.body varchar(300)
declare @.fname varchar(50)
declare @.coupon varbinary(4000)

if update(emailaddress)
begin

Select
@.emailaddress=(select EmailAddress from OrgCouponTestMain as str),
@.fname=(select EmailAddress from OrgCouponTestMain as str)
@.Coupon=(select OrgCoupon1 from OrgCouponTest2 as image)

SET @.body= 'Thank you' +' '+ @.fname +' '+ ',Here is the coupon you requested' +' ' + @.coupon
exec master.dbo.xp_sendmail
@.recipients = @.emailaddress,
@.subject = 'Coupon',
@.message = @.body
END

Hello my friend,

Try the following: -

SET @.body = 'Thank you ' + @.fname + ', Here is the coupon you requested ' + CAST(@.coupon AS VARCHAR(8000))

Kind regards

Scotty

|||

Hi there,

I've recently developed a functionality at my work place that is very similar to the one you need.

My suggestion is that you make things other way (very much simpler i think):

Instead of coding in T-SQL, you can develop your application in any .NET language. I suggest you to develop a Web Service or an ASP.NET page that retrieves the "destinations" from the SQL Server, loads the coupon and sends it to them.

Then you just have to develop a very simple COM Class that the ONLY thing it does is calling the Web Service (our ASP.NET page) you developed. There is many documentation on the Web on how to call a COM Class from an SQL Trigger / Job.

It's a bit tricky at the beggining but when you figure it out, it's easy as a walk in the park. And remember, when you need to change your application, you just have to change the .NET Web Service that it's called by the COM Class (much more friendly than coding in T-SQL).

Even if this isn't a web server, you can always create a website on localhost to host the service/page.

Hope I've helped you out!

gonzzas

|||

Thank you both for your input, Ask scotty, it didn't work, got the email out but no image, and of course there is that other option, which unless i can figure this out i will be doing instead. Thank you!

|||

So anyone else have an idea? This is what comes up in the email message, notice the symbols instead of the image...

Thank you Eileen, Here is the coupon you requested /

|||

Anyone else have a suggestion? Here is what comes in the email , notice the symbols instead of the image:

Thank you Eileen, Here is the coupon you requested /

|||

Hi,

The problem is focused on how to display the image which saved as Image Data type in database. Because what you get from database directly is binary steam of the image, it can't be displayed in your email directly. What I suggest is pass the steam (and other information ) from Stored Procedure to .NET, and use SqlDataReader to read the image and send the mail to your customer.

HOW to Retrieve an image from sql server and display it in ASP.net using "imagemap or image" ?https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=531518&SiteID=1

Hope it can helps. Thanks.

sql