Any true public variables for custom code section?

I have been searching the newsgroup for showing the group sum in the
footer section. This isn't the same as a normal group sum as the
amount shown in the textbox is using the value straight out of the
dataset - =Fields!Amt.Value, instead of =SUM(Fields!Amt.Value). I then
wanted to show the sum of the group amounts in the footer.
I thought I had found a solution by calling a function to store the
sum and then show the total by calling the same function with a
parameter to return the accumulated amount. Unfortunately I hit
another brick wall with this approach because even though I declared a
public variable outside the function (also tried a static within the
function), the accumulated amount only remains within the scope of
the group calling the function. When the footer section calls the same
function the accumulator is 0 instead of the accumulated amount for
the group.
My question, how do you declare and access a true public variable?
Here's an example of what is happening:
The group amount only needs to be displayed within the group, but
summed at the end of the report
Report
--
Group1 (grouped by MbGUID)
Textbox = AddGroupTotal("A", Fields!SizeTtl.Value)
Footer
Textbox = AddGroupTotal("T", 0) ' Note the "0" is just a
placeholder
Dataset columns
--
MbGUID,SizeTtl,Year,Month,Week,MaxSize
Data
--
1,120,2006,1,2,80
1,120,2006,1,3,90
1,120,2006,1,4,92
2,130,2006,1,3,87
2,130,2005,1,4,120
The output with the custom code
--
MbGuid Total
1 120
2 130
Report Total: 0
Obviously the Report Total should show 250 instead of 0.
Custom Code Section
--
Public ttl As Double = 0
Public Function AddGroupTotal(pMode As String, pNbr As Long) As Long
'Static Ttl AS Long
If pMode = "A" Then
If Ttl = Nothing Then
Ttl = 0
End If
Ttl = Ttl + pNbr
' Runtime error stating parameter is read only for the below
statement
'Report.Parameters!pSizeTtl = pNbr
AddGroupTotal = pNbr
ElseIf pMode = "T" Then
AddGroupTotal = Ttl
End If
End Function
MarkI'm also having the same problem.
I'm attempting to build a public array in the details section of a table and
then perform calculations against that, now filled array in the group footer.
The problem is that when I call my custom AddToArray code in the details
section and then return the ubound value of the array, it works fine in the
details section but when it gets to the footer the array is empty again.
Anyone have any suggestions?
"Mark" wrote:
> I have been searching the newsgroup for showing the group sum in the
> footer section. This isn't the same as a normal group sum as the
> amount shown in the textbox is using the value straight out of the
> dataset - =Fields!Amt.Value, instead of =SUM(Fields!Amt.Value). I then
> wanted to show the sum of the group amounts in the footer.
> I thought I had found a solution by calling a function to store the
> sum and then show the total by calling the same function with a
> parameter to return the accumulated amount. Unfortunately I hit
> another brick wall with this approach because even though I declared a
> public variable outside the function (also tried a static within the
> function), the accumulated amount only remains within the scope of
> the group calling the function. When the footer section calls the same
> function the accumulator is 0 instead of the accumulated amount for
> the group.
> My question, how do you declare and access a true public variable?
> Here's an example of what is happening:
> The group amount only needs to be displayed within the group, but
> summed at the end of the report
> Report
> --
> Group1 (grouped by MbGUID)
> Textbox = AddGroupTotal("A", Fields!SizeTtl.Value)
> Footer
> Textbox = AddGroupTotal("T", 0) ' Note the "0" is just a
> placeholder
> Dataset columns
> --
> MbGUID,SizeTtl,Year,Month,Week,MaxSize
> Data
> --
> 1,120,2006,1,2,80
> 1,120,2006,1,3,90
> 1,120,2006,1,4,92
> 2,130,2006,1,3,87
> 2,130,2005,1,4,120
> The output with the custom code
> --
> MbGuid Total
> 1 120
> 2 130
> Report Total: 0
> Obviously the Report Total should show 250 instead of 0.
>
> Custom Code Section
> --
> Public ttl As Double = 0
> Public Function AddGroupTotal(pMode As String, pNbr As Long) As Long
> 'Static Ttl AS Long
> If pMode = "A" Then
> If Ttl = Nothing Then
> Ttl = 0
> End If
> Ttl = Ttl + pNbr
> ' Runtime error stating parameter is read only for the below
> statement
> 'Report.Parameters!pSizeTtl = pNbr
> AddGroupTotal = pNbr
> ElseIf pMode = "T" Then
> AddGroupTotal = Ttl
> End If
> End Function
> Mark
>

Any SQL Gods out there

I have a SP that I am trying to finalize however; my inexperience is showing itself on this one.

History:
3 Tables: Tooldb - Employee - ToolUserdb

Scenario:
I have a webform in c# that gathers data concerning internal tools(applications) that are written in-house. One of the fields is a listbox of names pulled from the Employee table called Creator. When the form is submitted I need to have the list of selected employees published to the ToolUserdb.

My SP:
ALTER PROCEDURE dbo.InsertTool
(
@.ToolName nvarchar(250),
@.Platform nvarchar(250),
@.Vendor nvarchar(250),
@.Subplatform nvarchar(250),
@.Submitter nvarchar(250),
@.Finders nvarchar(250),
@.LTD nvarchar(50),
@.JobArea nvarchar(250),
@.Func nvarchar(250),
@.Owners nvarchar(250),
@.Active nvarchar(250),
@.Version numeric,
@.Build numeric,
@.CWSTD nvarchar(50),
@.Status nvarchar(250),
@.Cost numeric,
@.Notes nvarchar(250),
@.Keywords nvarchar(250),
@.Links nvarchar(250),
@.Paths nvarchar(250),
@.eid int,
@.tdbid int,
@.Creator nvarchar(250)
)
AS

INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
VALUES
(@.ToolName, @.Platform, @.Vendor, @.Subplatform, @.Submitter, @.Finders, @.LTD, @.JobArea, @.Func, @.Owners, @.Active, @.Version, @.Build, @.CWSTD, @.Status, @.Cost, @.Notes, @.Keywords, @.Links, @.Paths)
INSERT INTO ToolUsersdb (@.Creator) SELECT + @.eid + ',[ID] FROM Employee + @.tdbid + ',[ID] FROM ToolDB IN (@.Creator)

Error:
Incorrect syntax near keywork IN (referring to last insert statement).

Can someone tell me how I can get this multi-insert stmt. to work?

Thank you!

TimWhat's the DDL for ToolUsersDB table?

INSERT INTO ToolUsersdb (EmpID, ToolID, Creator) values (@.eid, @.tdbid, @.Creator) ?|||if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ToolUsersdb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ToolUsersdb]
GO

CREATE TABLE [dbo].[ToolUsersdb] (
[id] [int] NOT NULL ,
[eid] [int] NULL ,
[tdbid] [int] NULL
) ON [PRIMARY]
GO|||After examining my SP closer I have made the following changes. I think this is closer to the end result. The problem is that I am getting an error:

The select list for the insert statement contains fewer items than the insert list. Not sure why this is.

ALTER PROCEDURE dbo.InsertTool
(
@.ToolName nvarchar(250),
@.Platform nvarchar(250),
@.Vendor nvarchar(250),
@.Subplatform nvarchar(250),
@.Submitter nvarchar(250),
@.Finders nvarchar(250),
@.LTD nvarchar(50),
@.JobArea nvarchar(250),
@.Func nvarchar(250),
@.Owners nvarchar(250),
@.Active nvarchar(250),
@.Version numeric,
@.Build numeric,
@.CWSTD nvarchar(50),
@.Status nvarchar(250),
@.Cost numeric,
@.Notes nvarchar(250),
@.Keywords nvarchar(250),
@.Links nvarchar(250),
@.Paths nvarchar(250),
@.eid int,
@.tdbid int,
@.Creator nvarchar(250)
)
AS

INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
VALUES
(@.ToolName, @.Platform, @.Vendor, @.Subplatform, @.Submitter, @.Finders, @.LTD, @.JobArea, @.Func, @.Owners, @.Active, @.Version, @.Build, @.CWSTD, @.Status, @.Cost, @.Notes, @.Keywords, @.Links, @.Paths)
INSERT INTO [ToolUsersdb] (eid, tdbid) select e.id from employee e where e.id in (@.Creator) select t.id from Tooldb t|||Huh?

INSERT INTO [ToolUsersdb] (eid, tdbid)
select e.id from employee e where e.id in (@.Creator) select t.id from Tooldb t

What this?

You can't do that|||Brett,

Could you expand on your thoughts?

Do you have any ideas on how this can be done. I am sure that I am not the first person to be presented with a one to many realtionship between tables.

Even a link would be helpful if you know of one.

Thanks,

Tim|||It looks like, and correct me if I'm wrong, and INSERT statement with 2 SELECTs...

I guess you could do it this way...

INSERT INTO [ToolUsersdb] (eid, tdbid)
SELECT e.id, t.id
FROM employee e JOIN Tooldb t
ON e.id = t.id
WHERE e.id = @.Creator

The IN error message is erroneous...|||Brett,

Thanks for helping out. You thoughts are appreciated.

Here is the modification I have made (with your thoughts in mind):

INSERT INTO [ToolUsersdb] (eid, tdbid)
SELECT e.id FROM employee e WHERE e.id = (@.Creator)
UNION
SELECT t.id FROM Tooldb t

I put the UNION in place because my understanding is that without it, the second SLELECT statement would be ignored.

With the above script I am getting the following error:
The Select list for the insert statment contains fewer items than the Insert list.

There are only two columns in the ToolUsersdb outside of the ID column.

Any additional ideas?

Have I explained what I am trying to do clearly enough in the beginning? Let me know if you need any further clarification.

Thanks again.

Tim|||Concepts!

You're mixing rows with columns.

For example, your INSERT expects 2 columns per row.

Your result set is producing 1 column per row.

If you want e.id and t.id on the same row, you have to relate them somehow.

any *simple* external activation examples available?

BOL only seems to say that you can do it w/o really showing how, and the ExternalActivator sample at gotdotnet.com contains so much functionality I'm not sure what's required just for the external activation. Are there any docs or samples out there that focus on how to do it w/o obscuring the matter with a bunch of other functionality? (I prefer docs to project samples, b/c the samples tend to have hacks like hardcoded paths and connection strings so that they rarely work correctly right out of the box.)

TIA

The sample tries to do it correctly. That is, it must gaurantee that it will try to launch the process even under memory pressure and process failure. If it didn't, there is the danger of orphaning messages in the application queue. In order to provide this gaurantee, it must implement basic recovery logging. That adds to the complexity.