Adjust values in db base in the value modified on the form?

Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!Hi everyone,

Here is the problem I am facing with. I have a form which has multiple fields including Price (read only), Discount(read/write), TotalSellPrice(read/write), Quantity(read/write) ... What I need to do is I need to adjust TotalSellPrice value if there was a new Discount value entered and vise versa. If both values have been changed I should use Discount value entered and calculate the TotalSellPrice. I am having hard time figuring the query out. Any thoughts or ideas in what direction should I go.

Thanks for your help!

Paste your query or the tables involved and explain what you want in new one?|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Ex. #1 - Discount value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (hasn't been changed) -> I need to calculate it then. it becomes $180

Ex. #2 - Discount and TotalSellPrice values are changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (changed to) 10%
TotalSellPrice $0 (changed to) $150
Then I need to calculate TotalSellPrice since both Discount and TotalSellPrice values have been changed. TotalSellPrice becomes $180

Ex. #3 - TotalSellPrcie value is changed
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to calculate it. Discount is 25%
TotalSellPrice $0 (changed to) $150

Ex. #4 - TotalSellPrice value is changed to number bigger then it's original one then I have to keep Discount at 0% don't go negative
Price is $100
Quantity was 0 (changed to) 2
Discount was 0% (hasn't been changed) -> I need to keep Discount at 0%
TotalSellPrice $0 (changed to) $250|||I don't have any query yet, I need to come up with the query that will check which value has been changed and do update approprietly.

Whatever I make out from the above problem is, you want to adjust the data according to the changes in the other fields.I think you can easily do it at the front end and send the update query to the database according to its invoice no.
An user can see the store data and update it anytime by changing the different fields.You can do the neccessary calculation at the front end.And pass a simple update query to the database.That will do the trick..|||I think you can easily do it at the front end and send the update query to the database according to its invoice no.
The thing is it would not be efficient from a programming stand point, because I have to grab all the information from the database and store it somewhere in the session, then compare it to what user enters, then create a query dynamically instead of using stored procedure. I need a back end query that will handle all these stuff for me.

Any idea?|||Any idea?

And you need not have to create a stored procedure for such a small calculation.I think so...;)|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.|||Maybe I explained it incorrectly, I am sorry.
What I do I initially populate those fields with the default data from the database

Ex. - Initial page load
Price -> "$100"
Quantity -> "0"
Discount -> "0%"
TotalSellPrice -> "$0"

After user updates any of writable fields I do update and populate that form again but base on the rules I described before, I hope that explains everything.
So...?? You can write the adjustment code in asp page,and simply update the record.Mind it you are not doing anything more than that..so I don't think on the point of efficiency ,use of stored procedure will make any difference...|||After user updates any of writable fields I do update and populate that form again but base on the rules I described before...You think it is efficient or good programming practice to make a call to the database every time a user changes a value on a form?

It's not, and you certainly wouldn't design scalable enterprise applications this way.

Your form should get complete recordsets from the database (even defaults), and should submit complete recordsets to the database.

And frankly, you don't have to store all the detail data to keep track of the new total. NewTotal = OldTotal - OldValue + NewValue. That is just three variables.

Adding values to a parameter that can take multiple values

If I have a Select statement like this in my C# code:

Select * From foods Where foodgroup In (@.foodgroup)

And I want @.foodgroup to have these values ... "meat", "dairy", fruit", what is the correct way to add the parameter?

I tried

meat, dairy, fruit

'meat', 'dairy', 'fruit'

but neither worked. Is this possible?

Please search these forums. This question has come up probably 10 times in the last few weeks.

|||

I tried a search and couldn't find anything. Plus the search function here isn't very fast.

But I found the solution after doing a Google search. Thanks...

If anyone stumbles on this post, you can go here for some answers:

http://www.msdner.com/forum/thread144871.html

Adding Values in a Text Box

I have multiple values in text boxes based on Summed values. I would like to
add the values that are in the text boxes. What is the ref for Boxes? I
know fields are Fields!.
Here is an example of what I am running it the text boxes. They are in the
group footer. I would like to add them in the report footer, which is in a
different scope.
=IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
Fields!ItemLoss.Value), 0)
--
Thank You, LeoI would like to sum the values not just add them.
Thanks
"TrussworksLeo" wrote:
> I have multiple values in text boxes based on Summed values. I would like to
> add the values that are in the text boxes. What is the ref for Boxes? I
> know fields are Fields!.
> Here is an example of what I am running it the text boxes. They are in the
> group footer. I would like to add them in the report footer, which is in a
> different scope.
> =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> Fields!ItemLoss.Value), 0)
> --
> Thank You, Leo|||Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
you to reference values in a textbox.
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TrussworksLeo" <Leo@.noemail.noemail> wrote in message
news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> I would like to sum the values not just add them.
> Thanks
> "TrussworksLeo" wrote:
> > I have multiple values in text boxes based on Summed values. I would
like to
> > add the values that are in the text boxes. What is the ref for Boxes?
I
> > know fields are Fields!.
> >
> > Here is an example of what I am running it the text boxes. They are in
the
> > group footer. I would like to add them in the report footer, which is
in a
> > different scope.
> >
> > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > Fields!ItemLoss.Value), 0)
> > --
> > Thank You, Leo|||Yes, But it will not allow me to us a aggregate like sum against it?
Leo
"Bruce Johnson [MSFT]" wrote:
> Take a look a the ReportItems!<TextboxName>.Value syntax. This syntax allows
> you to reference values in a textbox.
>
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "TrussworksLeo" <Leo@.noemail.noemail> wrote in message
> news:509843C2-29B9-4B0E-B22A-5C497B99F0AB@.microsoft.com...
> > I would like to sum the values not just add them.
> >
> > Thanks
> >
> > "TrussworksLeo" wrote:
> >
> > > I have multiple values in text boxes based on Summed values. I would
> like to
> > > add the values that are in the text boxes. What is the ref for Boxes?
> I
> > > know fields are Fields!.
> > >
> > > Here is an example of what I am running it the text boxes. They are in
> the
> > > group footer. I would like to add them in the report footer, which is
> in a
> > > different scope.
> > >
> > > =IIF( Fields!Part_Type_Name.Value = "WoodTruss" ,Sum(
> > > Fields!ItemLoss.Value), 0)
> > > --
> > > Thank You, Leo
>
>

adding precedence to multiple files

hi all,

i have a package here which updates a DB from a flat file source.now the problem is i may get multiple files.i have used a for each loop to handle this. it takes files based on the files name9(file names has a timestamp in it).but i want to give files in order of its Creation time.

Please help me on this.i have written a script task before the for each loop and i have got the minimum creation date from all the files,i am not able to going forward from here.

does any body has an idea!!

ASAIK, the files are show in creation order in the for each loop. However, I am not sure this is gospel.

You could use a script task to load a list of files and sort the list by the file attributes. using this sorted list, you could then loop over the list using the for each loop container.

If you wanted to get clever, modify the For Each Directory example in the MS SQL 2005 example. Create your own file enumerator, ensuring the files are in the order you want.|||Generally, it does sort by filename - but there are no guarantees of this. If you want to ensure the sort order is correct, follow Crispin's advice to modify the For Each Directory sample, or use the sample posted here (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1439218&SiteID=1) by jaegd. Or read your directory, save each filename to a temp table, and use SQL sorting.

Adding New Languages

I have a SQL Server 2000 application that must return raiserrors in multiple
languages. One of the languages is not in the list of supported languages
in master..syslanguages.
In SQL 6.5, there was a procedure sp_addlanguage that could be used to add
unsupported languages. This procedure is no longer available in SQL 2000.
The upgrade documentation suggests removing all references to sp_addlanguage
... as if to suggest that adding languages to SQL 2000 is not supported and
should not be done.
I could add the language directly to syslanguages (it works, I've tried it).
My question is: Is there actually a risk in adding a new language to
syslanguages? Are there any factors that I should be aware of?
Thanks,
BBThere may not be problem for the time being, but the structure of system
tables may change between versions of SQL Server, and selecting directly
from system tables is discouraged.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "bbofloma" <bbofloma@.online.nospam>
>Subject: Adding New Languages
>Date: Mon, 4 Apr 2005 15:55:03 -0400
>Lines: 20
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
>Message-ID: <e7$DLDVOFHA.3848@.TK2MSFTNGP14.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.server
>NNTP-Posting-Host: d150-29-198.home.cgocable.net 24.150.29.198
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:51233
>X-Tomcat-NG: microsoft.public.sqlserver.server
>I have a SQL Server 2000 application that must return raiserrors in
multiple
>languages. One of the languages is not in the list of supported languages
>in master..syslanguages.
>In SQL 6.5, there was a procedure sp_addlanguage that could be used to add
>unsupported languages. This procedure is no longer available in SQL 2000.
>The upgrade documentation suggests removing all references to
sp_addlanguage
>... as if to suggest that adding languages to SQL 2000 is not supported and
>should not be done.
>I could add the language directly to syslanguages (it works, I've tried
it).
>My question is: Is there actually a risk in adding a new language to
>syslanguages? Are there any factors that I should be aware of?
>Thanks,
>BB
>
>

Adding Multiple Values into a row/column help

Whats the fastest easiest way to take a select that returns say 4 values for the expression into a single column on defined row

basically I mean i want to do an update to say a persons i dunno ummm places they have traveled and I want it listed like france;usa;germany etc etc and the data would always be in the tables i pull from so I can overwrite the data each time i run it but has to take 3 or more values from a query and put them in separated by say a ; into the same persons coloumn that stores the info.

I did this once before with a cursor and adding a variable to itself with colasce or whatever the command was, but was just wondering if there is a fast way to do this by chance that im not thinking about :P.

Thanks!The following example will collect the values from a column in a select statement and create a delimited list from the values.

This will denormalize the values from the source table into a single column so the destination table will not meet the requirements of first normal form. This may be best used for reporting operations, that said:

Two tables are created, one to hold values for the list, and one where the results are inserted.

Test values are inserted into the test table and then a select statement collects the values. (Example supports only 4000 characters)

--Create Test Table
CREATE TABLE dbo.test (
dataField NVARCHAR(10) NOT NULL,
PRIMARY KEY (dataField)
)
GO

--Create Results Table
CREATE TABLE dbo.testResults (
resultId INT IDENTITY (1,1) NOT NULL,
result NVARCHAR(4000) NOT NULL,
PRIMARY KEY (resultId)
)
GO

--Insert Test Data
INSERT dbo.test (dataField) values ('here')
INSERT dbo.test (dataField) values ('there')
INSERT dbo.test (dataField) values ('everywhere')

--Verify Test Data
SELECT dataField FROM dbo.test

--Retrieve colon delimited list of dataField without a cursor
DECLARE @.collectValues NVARCHAR(4000)
SET @.collectValues = ('')

SELECT
@.collectValues = @.collectValues + dataField + ';'
FROM dbo.test

--Verify delimited list
SELECT @.collectValues

--Insert into result table
INSERT dbo.testResults
(result)
VALUES
(@.collectValues)

--Verify inserted data
SELECT resultId, result FROM dbo.testResults

The last select statement should return the result value:
everywhere;here;there;|||Im confused, this doesnt seem like I could get the results correctly from this, You could just use one single select to get all the data like that from that, but what if this works as above, then how would it diferentiate from members and there intrests. Let me give an exampe. Member1 has intrests of fishing,boating,camping, member2 has intrests of fising,hiking,running, how would i basically convert the below

table one

customer intrest

member1 fishing
member1 boating
member1 camping
member2 fishing
member2 hiking
member2 running

go from that data, to this data

table two

customer intrests
member1 fishing;boating;bamping
member2 fising;hiking;running

I dont think the above example can do this can it? Or Am I just missing something? Thanks! hehe|||You are absolutely correct. I misread your intention as wanting the value for a single person (as though you would add this update to a procedure for updating the base table, etc...).

Adding Multiple Users to Database

In the users section of a database, I can select multiple users and
delete them from being able to access the DB in one keystroke (or so).
Can I do the reverse? If I deleted everyone, is there an SP or a
location that I can select multiple users and grant them access to the
DB quickly?
Thus far, all I've found is to select each user one at a time and give
them "public" access to the database.
(It's a good thing we are not a big company! I tried to restrict
access to all but a couple users to a particular DB only to discover
that I had the wrong DB selected. I had to add each user back in
manually.)
Thanks!
-TimothyConsider using NT group membership to the database.
See sp_grantdbaccess in Books Online;
sp_grantdbaccess
Adds a security account in the current database for a Microsoft SQL
Server login or Microsoft Windows NT user or group, and enables it to be
granted permissions to perform activities in the database.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Adding multiple textboxes to PageHeader

I am creating matrix reports programmatically and have come up against a problem where I can only add a single textbox into the pageheader. Writing the report using the report designer software I can add multiple textboxes to the pageheader, however trying to add many of them using the ReportItem object I keep getting a syste.object[] cannot be used in this context. error.

for (int i = 0; i < Items.Count; i++)
{
reportItems.Items = new object[] { CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle) };
}

This is the line of code that I am hitting the problem. This works fine, however only inserts the last textbox in the Items Array.

If I change the code to

for (int i = 0; i < Items.Count; i++)

{

reportItems.Items = new object[] { CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle) };

}

It executes, but fails when trying to render it into XML.

Has anyone managed to get multiple textboxes programatically into the pageheader?In the first approach you are overwriting the Items array with each iteration of the for loop. And in the second approach you are setting each entry in the Items array to a new object array, so you end up with an array of arrays. Instead what you should do is just add the object to the array. Try using the following code.

// create a new array for the text boxes in the header
reportItems.Items = new object[Items.Count];

// iterate though all the Items creating a textbox, which is added to the report items object array
for (int i = 0; i < Items.Count; i++)
{
reportItems.Items = CreateTextBox(Items.ItemName, Items.ItemMessage, Items.ItemStyle);
}

Ian

Adding multiple role assignments all at once

Is there a way to add multiple role assignments to a report definition all at once?

Instead of going to the Report Manager, selecting each individual report definition, and then clicking on "New Role Assignment" multiple times to add users, is there a way to add multiple users all at once?

You can write a script and use rs.exe.

Here is a pointer to the tool. There should also be information for example scripts.

http://msdn2.microsoft.com/en-us/library/ms162839.aspx

Adding multiple fields together

Okay hopefully this is quick and easy.

I have 3 fields (Annual Salary, Medical, Pension) and I now need to working out Cost To Company which is just adding those three together.

I tried to do that in a formula but it doesn't return me anything... This is what I had.

=(Sum(Fields!AnnualSalary.Value, "CoreEmployeeData")+Sum(Fields!AnnualMedicalAid.Value, "CoreEmployeeData")+Sum(Fields!AnnualPension.Value, "CoreEmployeeData"))

So it should add the 3 records?

While you are here :), Can you tell me how I can put a currency symbol in the front of these numbers and add the thousand comma delimiter?

ok, What is the "CoreEmployeeData" section, I would think this is affecting your calcs.

Also i would braket each individual sum too as below, I have also added in the ￡ and thousand delimiter too.

="￡" & round(cdec((Sum(Fields!AnnualSalary.Value))+(Sum(Fields!AnnualMedicalAid.Value))+(Sum(Fields!AnnualPension.Value), 2)), 2)

I have not tested this but should provide what you are looking for.

Andy

|||CoreEmployeeData is the DataSet name.
If I leave that out I get an out of scope error.

If I leave it there I still get no response at all....?|||Okay well it's working now.
I had to paste the code in the "Format" and "Value" fields.

Not sure why it was required in both but it seems to work now. I think I need some more practice :)

Thanks,
Gavin|||

the easiest way to get aorund this issue is split out your sum to individual columns, create 3 new columns in your report, and put the Sum() in each, make sure that return the correct data in the new columns.

If so then just copy and paste each one into your total column and + them together making sure you bracket each one and wrapper the whole thing in brackets too.

((sum(A)) + (sum(B)) + (sum(B)))

Now add in my code above to format to dec and add ￡ and you should be ok

|||

Cool! - Its is one of those things, to be honest i dont use the format area at all but add my formatting only into the expression for each field, at least then you only have to change one area of needed.

SSRS certianly has its quirks but you get used to it.

Andy

Adding Multiple column items for a total.

Rookie question here -
I need to have one column show up in my RS 2000 report that I am creating.
The SQL table the information is coming out of is:
Column1 Column2 Column3 Column4 Column5 Column6
Name Tuition BookFee MaterialsFee OtherFee1 OtherFee2
In my report, I only want to show a column called Total Cost (For each Field
Name)which consists of Tuition+BookFee+MaterialsFee+OtherFee1+OtherFee2. In
other words, I want to add columns 2 - 6 together and only show that total in
my final report. Obviously I can do this in Excel, but I can't seem to do
this in SQL.
I can't alter my SQL tables, but I can alter the RS query if need be or sum
them up on my report layout. My only problem is I don't know how.
Any help would be greatly appreciated
Thanks.Solution I used
In the SELECT portion of my query after selecting approriate items I included
StudentProgram.BookFee+StudentProgram.MaterialFee+StudentProgram.OtherFee1+
StudentProgram.OtherFee2+StudentProgram.TuitionFee AS TotalCost
"TTU" wrote:
> Rookie question here -
> I need to have one column show up in my RS 2000 report that I am creating.
> The SQL table the information is coming out of is:
> Column1 Column2 Column3 Column4 Column5 Column6
> Name Tuition BookFee MaterialsFee OtherFee1 OtherFee2
> In my report, I only want to show a column called Total Cost (For each Field
> Name)which consists of Tuition+BookFee+MaterialsFee+OtherFee1+OtherFee2. In
> other words, I want to add columns 2 - 6 together and only show that total in
> my final report. Obviously I can do this in Excel, but I can't seem to do
> this in SQL.
> I can't alter my SQL tables, but I can alter the RS query if need be or sum
> them up on my report layout. My only problem is I don't know how.
> Any help would be greatly appreciated
> Thanks.
>

Adding Matrix affect the report print preview

I'm sure body+margins=page width, there is no way the report is splited to
multiple page when print preview. The report is a multi-page report. The
matrix is added in the 4th page. Before adding the matrix, everything is
fine. After added the matrix, the first page is splitted to 2 pages or even 3
pages when print previewing depending on the columns the matrix has. And the
matrix itself is within the same page(page width is good enough for it).
Any suggestions? I have run out of ideas. Thanks.Anyone can help?
"Jane" wrote:
> I'm sure body+margins=page width, there is no way the report is splited to
> multiple page when print preview. The report is a multi-page report. The
> matrix is added in the 4th page. Before adding the matrix, everything is
> fine. After added the matrix, the first page is splitted to 2 pages or even 3
> pages when print previewing depending on the columns the matrix has. And the
> matrix itself is within the same page(page width is good enough for it).
> Any suggestions? I have run out of ideas. Thanks.

Adding Hours, Minutes, Seconds (SQL 2000)

Hi There,
I would like to find the sum of a column with a date format of '01:10:10' which is the hours:minutes:seconds from multiple rows.
For instance, "01:50:10" + "01:20:5" = "3:10:15"
Any ideas?
Using SQL 2000try this tricky thing...

declare @.Dt as datetime
set @.Dt = '2007-02-20'
declare @.Dt1 as datetime
set @.Dt1 = '2007-02-20 01:50:10'
declare @.Dt2 as datetime
set @.Dt2 = '2007-02-20 01:20:05'

select convert(varchar,cast((cast(@.Dt1 as float) - cast(@.Dt as float)) + (cast(@.Dt2 as float) - cast(@.Dt as float)) as datetime),114)

now dont ask me what will happen if the sum is more than 24 hrs etc. etc... ;)|||select sum(datediff(s, '2000-01-01', '2000-01-01 ' + [TimeString]))
from [YourTable]
You'll need to verify that the above function syntax is correct, but you should get the general idea.|||declare @.tm1 datetime, @.tm2 datetime
select @.tm1='23:50:10', @.tm2='23:20:05'
select 'sum1'=
str((datediff(s,0,@.tm1)+datediff(s,0,@.tm2))/60/60,4,0)
+right(convert(char(8),dateadd(s,datediff(s,0,@.tm2 ),@.tm1),108),6)

sum1
----
47:10:15

Thanks upalsen, I didn't know it was that ease to convert between gregorian date and julian day number.
select 'JulianDayNo'=convert(float,getdate())+2415020.5

JulianDayNo
-------
2454154.9927028548|||I really don't think the formula needs to be that complicated...
set nocount on
declare @.TimeStrings table (TimeString varchar(8))

insert into @.TimeStrings (TimeString) values ('01:50:10')
insert into @.TimeStrings (TimeString) values ('01:20:5')

select sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)) as TotalSeconds,
convert(varchar(8), dateadd(s, sum(datediff(s, '2000-01-01', '2000-01-01 ' + TimeString)), 0), 8) as DateString
from @.TimeStrings|||UPalsen's way works - thanx