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! Creating Custom Data Flow Task

I'm trying to create a custom data flow destination, and it has a custom property that needs to get value from variable(similar to the FileNameVariable property of Raw File Destination), how can I do that?

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

|||

DarrenSQLIS wrote:

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

So provide a custom IDTSCustomProperty90 in ProvideComponentProperties() and let users type in the variable name is enough(of course, I should also follow what you said)?

I wanted to make it behave the same way as the FileNameVariable property of Raw File Destination, that is: users don't type in the variable name, instead, they select from all available variable names.
|||

DarrenSQLIS wrote:

Create a propery of type string. Store the name of a variable in that property. Use the property value to locate the variable in the variables collection at run-time, lock and read the variable's value.

What are you asking? How to create a property? How to store a string in a property, the string being a variable name? How to read a string value from a property and get the matching varible and it's value at runtime?

You should also validate the value of the property during Validate of course, and check tht it is a valid variable name.

How can I get the matching variable and it's value at runtime? And generally in which method should I do that? Thanks.
|||

Here's a sample from a component I developed to raise custom events from a package's control flow; the same basic technique should work for you in data flow as well.

First, create a property that stores the variable name. This will be set by the package developer at design time.

Code Snippet

private string variableName;

///

/// This property gets or sets the name of the variable in which the

/// event text is stored.

///

[Browsable(true)]

[Category("Custom")]

[Description("Gets or sets the name of the variable in which the event text is stored.")]

public string VariableName

{

get

{

return variableName;

}

set

{

if (value.IndexOf("::") == -1)

{

variableName = "User::" + value;

}

else

{

variableName = value;

}

}

}

(The set accessor for the property is simply explicitly adding the User namespace to the specified variable name unless a namespace is already specified. I've yet to add a drop-down so the package developer can pick from a list of available variables.)

Then, you can use the component's VariableDispenser object (which is passed in a parameter to many of the methods you overload when developing your component) to lock and work with the package variable identified by the property. Here's a sample from the Validate method in my component:

Code Snippet

Variables vars = null;

DTSExecResult result;

variableDispenser.LockOneForRead(variableName, ref vars);

if (vars[variableName].DataType != TypeCode.String)

{

// The variable is accessable, but is not the correct data type

componentEvents.FireError(0, SubComponent(variableDispenser),

string.Format("Variable '{0}' is not a String variable", variableName), "", 0);

result = DTSExecResult.Failure;

}

else

{

result = DTSExecResult.Success;

}

vars.Unlock();

return result;

The key thing here is that you're using the variableDispenser to lock the specific variable identified by the component's property, working with it, and then unlocking it when you're done.

(The SubComponent method which is referenced in the code sample above is just a little helper routine that returns a string in a standard format to identify where an error or event is coming from. I need this type of string in many places, so I have a function that can be reused, but it's not really relevant to your question...)

Hope this helps!

Help! Can't put double lines in the Table Region.

Table region will not activate the "Double Lines" property. Does anyone know
how to fix this problem?I've noticed this as well. Must be a bug...
--
Adrian M.
MCP
"OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
message news:B06E97EB-9DEC-4485-BDAE-E2876A794809@.microsoft.com...
> Table region will not activate the "Double Lines" property. Does anyone
> know
> how to fix this problem?|||Take your report output and export to a pdf. When you magnify the output you
will probably see that it is indeed a double line. This is a problem and I
hope that it is fixed in RS 2005. This wasn't any help to you, other than to
know other people are having the same problem.
"Adrian M." wrote:
> I've noticed this as well. Must be a bug...
> --
> Adrian M.
> MCP
> "OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
> message news:B06E97EB-9DEC-4485-BDAE-E2876A794809@.microsoft.com...
> > Table region will not activate the "Double Lines" property. Does anyone
> > know
> > how to fix this problem?
>
>

Help! Cannot Insert Null

Hello Everyone,
This is my first time here.
I have been trying to turn off the "null" property for my id column.
And then I put yes for identity.
when i go to save it i get the error message,
"Cannot insert the value NULL into column 'ID'"
Can someone please help me out with this, its probably something simple, but i just cant figure it out.

Thanks,
PJIdentity values are create automatically by the server. They cannot be NULL.|||for some reason, the property in the ID column showed as "null"
but it should NOT be
when i uncheck the box for null and set the identity for yes, i get that error.
i have a username/password thing on the website, and when someone registers, it DOES insert all the information from the form, but under the ID column, instead, where it USED to give it a number, it says <null>.
i can go in manually and enter a number, and then it works, but i need the ID to be automatically added.|||A check in the "Allow Nulls" column indicates that the field will accept Null values, not that it will exclude them.

You cannot set the Identity property on a field that allows Nulls. If you try, it will automatically set the field to disallow null values.|||I happened to figure this out
What happened:
There were already null "id's" created/inserted into the table
you have to delete those that were created by null, and THEN you can uncheck the "allow nulls" and set the identity to yes
My whole problem was trying to NOT allow nulls, but once nulls are created in the table, you cannot uncheck the box

Thank you so much for your help though.

-PJ