adding up column values (asp & access 2000)

Hi

I've got a quandry - I have a detailed database that handles advert
orders between a design agency and printers / magazines etc.

I want to add up the total spent by the client and put the results to a

field.

I've actually done that using a query table in access - it should be
quite simple as i can bind the 'total amount' to my table - the only
thing it does not currently do is filter the total based on the month
selected.

For example if you look at
http://www.daneverton.com/dg2data/months/2006-12.asp
The data here is filtered by the issue equaling Dec-2006

The actual order total is 13,622 but the column is showing the total
for all entries to date (a years worth = 422,048)

I'm sure that there is only a basic tweak required but i'm banging my
head over what to do

The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"

Any help gladly received.Hi Dan,

What is the SQL behind: q_monodraught_total ?
Quote:
The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"
I'm thinking you might benefit from a SELECT SUM... query
SELECT SUM(fieldname) FROM table WHERE condition ORDER BY fieldname; SELECT SUM(Age) FROM Persons WHERE Age>20good luck ;o)

Cheers,
Douglas

------------------------

"Dan" <mail@.daneverton.comwrote in message news:1164792444.626863.242620@.h54g2000cwb.googlegr oups.com...
Hi

I've got a quandry - I have a detailed database that handles advert
orders between a design agency and printers / magazines etc.

I want to add up the total spent by the client and put the results to a

field.

I've actually done that using a query table in access - it should be
quite simple as i can bind the 'total amount' to my table - the only
thing it does not currently do is filter the total based on the month
selected.

For example if you look at
http://www.daneverton.com/dg2data/months/2006-12.asp
The data here is filtered by the issue equaling Dec-2006

The actual order total is 13,622 but the column is showing the total
for all entries to date (a years worth = 422,048)

I'm sure that there is only a basic tweak required but i'm banging my
head over what to do

The sql is "SELECT * FROM monnodraught, q_monodraught_total WHERE
[Issue / Edition] LIKE ? ORDER BY Publication ASC"

Any help gladly received.

Adding number of decimal places during table design

Hello,

Nice easy one (hopefully) from a newbie on SQL 2000.

I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;

TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No

I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.

I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,

So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.

What simple thing I am not doing ?

Cheers
NealPut values in Scale properties,you will find it just below precision if you are creating tables in EM design Table option...|||Many thanks.
Neal

Adding custom property to custom component

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Well, luckily it works after all. Closing down VS and restarting it did the trick. Probably kept reference to an old version of my custom component...|||You will need to restart VS everytime you recompile a component or task. You can set your project as the startup command for debugging the component, but it seems to take ages to load.

When testing the component execution side, set DTExec and a ready buildt package as the debug, as this is much faster.|||We've added a few tidbits that you might find interesting to the BOL topic on the subject of Custom Properties since the last CTP.

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

You can indicate that your custom property supports property expressions by setting the value of its ExpressionType property to CPET_NOTIFY from the DTSCustomPropertyExpressionType enumeration, as shown in the following example:

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

Visual Basic Copy Code Dim myCustomProperty As IDTSCustomProperty90 ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

You can specify a custom editor dialog box for the value of your custom property by using the UITypeEditor property, as shown in the following example. First, you must create a custom type editor that inherits from System.Drawing.Design.UITypeEditor, if you are unable to locate an existing UI type editor class that fits your needs.

C# Copy Code public class MyCustomTypeEditor : UITypeEditor { ... }

Visual Basic Copy Code Public Class MyCustomTypeEditor Inherits UITypeEditor ... End Class

Then specify this class as the value of the UITypeEditor property of your custom property.

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the editor with the custom property. customProperty.UITypeEditor = typeof(MyCustomTypeEditor).AssemblyQualifiedName;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the editor with the custom property. customProperty.UITypeEditor = GetType(MyCustomTypeEditor).AssemblyQualifiedName

For more information, see "Implementing a UI Type Editor" in the MSDN Library.

|||Great, thanks!|||

I have a question though...I followed those directions and I can get the enumerated list to show up in my transformation when added through SSIS.

My question now is, how do I access the property within the "ProcessInput" function?

|||

I would read the property value in PreExecute and store in a class level variable, but the code is the same regardless of location-

IDTSCustomProperty90 property = componentMetaData.CustomPropertyCollection[propertyName];
object o = property.Value;

|||Ahh, makes sense. Thank you.

Adding custom property to custom component

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

I thought this would involve adding a IDTSCustomProperty to the ComponentMetaData.CustomPropertyCollection and the right place to do this to mee seemed be in the ProvideComponentProperties() method:

IDTSCustomProperty90 property = ComponentMetaData.CustomPropertyCollection.New();
property.Name = "MyLittleProperty";

However, after compiling and redeploying the custom component and adding it to a package, the property does not show up in the editor.

Would anyone have suggestions on how what I want to do should be done right?

Thnx in advance,
Henk

Well, luckily it works after all. Closing down VS and restarting it did the trick. Probably kept reference to an old version of my custom component...|||You will need to restart VS everytime you recompile a component or task. You can set your project as the startup command for debugging the component, but it seems to take ages to load.

When testing the component execution side, set DTExec and a ready buildt package as the debug, as this is much faster.|||We've added a few tidbits that you might find interesting to the BOL topic on the subject of Custom Properties since the last CTP.

Creating Custom Properties

The call to the ProvideComponentProperties method is where component developers should add custom properties (IDTSCustomProperty90) to the component.

You can indicate that your custom property supports property expressions by setting the value of its ExpressionType property to CPET_NOTIFY from the DTSCustomPropertyExpressionType enumeration, as shown in the following example:

C# Copy Code IDTSCustomProperty90 myCustomProperty; ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

Visual Basic Copy Code Dim myCustomProperty As IDTSCustomProperty90 ... myCustomProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY

You can limit users to selecting a custom property value from an enumeration by using the TypeConverter property, as shown in the following example, which assumes that you have defined a public enumeration named MyValidValues:

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the type with the custom property. customProperty.TypeConverter = typeof(MyValidValues).AssemblyQualifiedName; // Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the type with the custom property. customProperty.TypeConverter = GetType(MyValidValues).AssemblyQualifiedName ' Now you can use the enumeration values directly. customProperty.Value = MyValidValues.ValueOne

For more information, see "Generalized Type Conversion" and "Implementing a Type Converter" in the MSDN Library.

You can specify a custom editor dialog box for the value of your custom property by using the UITypeEditor property, as shown in the following example. First, you must create a custom type editor that inherits from System.Drawing.Design.UITypeEditor, if you are unable to locate an existing UI type editor class that fits your needs.

C# Copy Code public class MyCustomTypeEditor : UITypeEditor { ... }

Visual Basic Copy Code Public Class MyCustomTypeEditor Inherits UITypeEditor ... End Class

Then specify this class as the value of the UITypeEditor property of your custom property.

C# Copy Code IDTSCustomProperty90 customProperty = outputColumn.CustomPropertyCollection.New(); customProperty.Name = "My Custom Property"; // This line associates the editor with the custom property. customProperty.UITypeEditor = typeof(MyCustomTypeEditor).AssemblyQualifiedName;

Visual Basic Copy Code Dim customProperty As IDTSCustomProperty90 = outputColumn.CustomPropertyCollection.New customProperty.Name = "My Custom Property" ' This line associates the editor with the custom property. customProperty.UITypeEditor = GetType(MyCustomTypeEditor).AssemblyQualifiedName

For more information, see "Implementing a UI Type Editor" in the MSDN Library.

|||Great, thanks!|||

I have a question though...I followed those directions and I can get the enumerated list to show up in my transformation when added through SSIS.

My question now is, how do I access the property within the "ProcessInput" function?

|||

I would read the property value in PreExecute and store in a class level variable, but the code is the same regardless of location-

IDTSCustomProperty90 property = componentMetaData.CustomPropertyCollection[propertyName];
object o = property.Value;

|||Ahh, makes sense. Thank you.