about accessing subReport's item

Hi all,
Is there anyone know how to access subReport's item? For example, can i use:
Reports!subReport.controls.textbox1.value in main report to access
subreport's textbox value? I have tried that, but failed. :-(
Thanks,
LisaHello Lisa,
Sadly no, I dont think you can do that.
The only property that I recall when looking at the IL that is exposed on a
ReportItem appears to be the Value property and that's I think is typed as a
String.
So on a report itself you can have a TextBox say called "TextBox1" and that
might be bound to a value from the database, and then you could bind another
TextBox to the first one with this syntax
=ReportItems!TextBox1.Value.
This kind of trick we discuss on p378 and 379 of our book where we explain
that it is useful for when you want to get image content from a DataSet into
a Report Header or Footer.
Peter Blackburn
Author: Hitchhiker's Guide to SQL Server 2000 Reporting Services
www.sqlreportingservices.net
"Lisa" <Lisa@.discussions.microsoft.com> wrote in message
news:8BDED34A-EC44-41B0-AC89-4763086BD414@.microsoft.com...
> Hi all,
> Is there anyone know how to access subReport's item? For example, can i
> use:
> Reports!subReport.controls.textbox1.value in main report to access
> subreport's textbox value? I have tried that, but failed. :-(
> Thanks,
> Lisa

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') => 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
>>From sys.sysdatabases
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.
NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegr oups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>
|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End
|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTable'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegr oups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>

about a SQL script

Dear All,
i recently would like to drop a table, then create a new one and then
insert the value to that new table
i have write a script as below:
use test
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [dbo].[Titles]
GO
SELECT * INTO [dbo].[Titles]
FROM [other_table].[dbo].[Titles]
GO
Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
GO
it work fine if it use one database only but my server have 20
databases, and all the database would like to have that modification.
So is there any method to automatically do the modification using a
script?
i really cant figure it out, i hope someone have give me a help
thanks you very much.NEMA,
You could use sp_MSForEachdb (an undocumented stored procedure) as described
at:
http://www.mssqlcity.com/Articles/U...2000UndocSP.htm
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185463821.216208.166530@.z24g2000prh.googlegroups.com...
> Dear All,
> i recently would like to drop a table, then create a new one and then
> insert the value to that new table
> i have write a script as below:
> use test
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Titles]') and OBJECTPROPERTY(id, N'IsUserTable'
) =
> 1)
> drop table [dbo].[Titles]
> GO
> SELECT * INTO [dbo].[Titles]
> FROM [other_table].[dbo].[Titles]
> GO
> Insert TABLE [dbo].[Titles] (name, id) Values ( 'good book',1)
> GO
> it work fine if it use one database only but my server have 20
> databases, and all the database would like to have that modification.
> So is there any method to automatically do the modification using a
> script?
> i really cant figure it out, i hope someone have give me a help
> thanks you very much.
>|||thanks you Russell
i dont know how to write as the example is all in one statment only.
but i have write a new one using variable but the error is that ' use
@.db_name' is not correct syntax
is anyone how to fix it ?
Declare @.db_count int
Declare @.db_name varchar(100)
Declare @.start int
/* start at 7 which are user databases*/
Set @.start = 7
Set @.db_count = 0
Select @.db_count = count(*)
>From sys.sysdatabases
Where dbid >= @.start
While @.db_count > 0
Begin
Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
/* avoid delete the table in database test2 as it need use as
template for copy */
If @.db_name <> 'test2'
Begin
use @.db_name
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTable'
)
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
End
Set @.db_count = @.db_count - 1
Set @.start = @.start + 1
End|||NEMA,
The ? substitutes the database name. So, you could do the following I
believe. (I tested a similar script, but I don't actually want to create
these tables on my server.)
exec sp_MSforeachdb
'USE ?
if DB_ID() > = 7
BEGIN
if exists (select * from dbo.sysobjects where id =
object_id(N''[dbo].[customer]'') and OBJECTPROPERTY(id, N''IsUserTab
le'')
= 1)
drop table [dbo].[customer]
SELECT * INTO [dbo].[customer]
FROM [test2].[dbo].[customer]
END'
Or you could use your code, but turn the block of SQL above into Dynamic SQL
(which is what sp_MSForEachDB does) and EXECUTE the prepared strings of SQL.
A good reference is:
http://www.sommarskog.se/dynamic_sql.html
RLF
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185469482.519284.216740@.x40g2000prg.googlegroups.com...
> thanks you Russell
> i dont know how to write as the example is all in one statment only.
> but i have write a new one using variable but the error is that ' use
> @.db_name' is not correct syntax
> is anyone how to fix it ?
> Declare @.db_count int
> Declare @.db_name varchar(100)
> Declare @.start int
> /* start at 7 which are user databases*/
> Set @.start = 7
> Set @.db_count = 0
> Select @.db_count = count(*)
> Where dbid >= @.start
> While @.db_count > 0
> Begin
> Select @.db_name = [name] From sys.sysdatabases Where dbid = @.start
> /* avoid delete the table in database test2 as it need use as
> template for copy */
> If @.db_name <> 'test2'
> Begin
> use @.db_name
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[customer]') and OBJECTPROPERTY(id, N'IsUserTabl
e')
> = 1)
> drop table [dbo].[customer]
> SELECT * INTO [dbo].[customer]
> FROM [test2].[dbo].[customer]
> End
> Set @.db_count = @.db_count - 1
> Set @.start = @.start + 1
> End
>sql

A way to reference the value in the current cell, not by name?

Is there any way to reference the value in the current table cell (Textbox)
from within formulas in that cell? I am curious because there are many places,
like 'Visiblity' where I would like to set it based on the current value,
but need to duplicate the formula I use for "Value". I would rather not
reference the name of the cell (like textbox4, etc), because that ALSO does
not change if I rename the cell, leaving yet another dangling reference.
I have many forumlas in the 'Visiblity->Expression' field that look like
this :
=Fields!with_xxxx_value.Value < 1
When I would like to have this (or something to that effect)
= CurrentObj.Value < 1, where CurrentObj would = the textbox, and Value
would = the "Value" expression for it.
Thanks,
// Andrew
P.S. Why does "Visibility->Expression:" seem to have the opposite effect?
If I want to HIDE a field when the value is < 0, then I have to set the
formula to "= Fields!the_value.Value < 1", which of course evaluates to TRUE
when I want to set Visible to False. Verrrrry funny.Hey Andrew,
I haven't actually tried this, but you the "ME" keyword might be what
your looking for i.e. Me.value
Michael
"Andrew Backer" wrote:
> Is there any way to reference the value in the current table cell (Textbox)
> from within formulas in that cell? I am curious because there are many places,
> like 'Visiblity' where I would like to set it based on the current value,
> but need to duplicate the formula I use for "Value". I would rather not
> reference the name of the cell (like textbox4, etc), because that ALSO does
> not change if I rename the cell, leaving yet another dangling reference.
> I have many forumlas in the 'Visiblity->Expression' field that look like
> this :
> =Fields!with_xxxx_value.Value < 1
> When I would like to have this (or something to that effect)
> = CurrentObj.Value < 1, where CurrentObj would = the textbox, and Value
> would = the "Value" expression for it.
> Thanks,
> // Andrew
> P.S. Why does "Visibility->Expression:" seem to have the opposite effect?
> If I want to HIDE a field when the value is < 0, then I have to set the
> formula to "= Fields!the_value.Value < 1", which of course evaluates to TRUE
> when I want to set Visible to False. Verrrrry funny.
>
>

A URL expression problem with a Jump to command

I have a report that uses the table control, the last column has id's for
dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
any of the other Columns in a row I would like to be able to grab the id from
the last column in the row and insert it into the expression for the Jump to
url feature.
this url works fin
="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={535d58cc-a1b3-da11-9864-001320020c86}','_blank'))"
this one gives me an Error on pag
="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={'&Fields!res_dealCompanyid.Value&'}','_blank'))"
How can I find out what is wrong
MikeI got this to work, my problem was that the dealCompanyid value was not a
string so I had to do a Fields!res_dealcompanyid.Value.tostring() then it
worked fine.
Parameters!CRMServer.Value just holds a Server info
="javascript:void(window.open('http://" & Parameters!CRMServer.Value &
"/EE2/DealCompany.aspx?id=" & Fields!res_dealcompanyid.Value.tostring() & "',
'_blank'))"
"Hotwheels" wrote:
> I have a report that uses the table control, the last column has id's for
> dealcompanies in it(Fields!res_dealCompanyid.Value). When the user clicks on
> any of the other Columns in a row I would like to be able to grab the id from
> the last column in the row and insert it into the expression for the Jump to
> url feature.
> this url works fine
> ="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={535d58cc-a1b3-da11-9864-001320020c86}','_blank'))"
> this one gives me an Error on page
> ="javascript:void(window.open('http://sandbox:82/EE2/DealCompany.aspx?id={'&Fields!res_dealCompanyid.Value&'}','_blank'))"
> How can I find out what is wrong
> Mike
>

A trigger can it transform a column into counter ?

Hi with you all,
I need to automatically increase the value of a column by one. Except of cou
rse
for the first value of my column, in that case the value must be 1.
It's like a counter.
In the table this counter must be initialized by 1 for each new value of
"InheritedKey". The following line must have the value increase by one for e
ach
"Key" value increased.
Sample :
Key InheritedKey AutomaticValue
12345 40 1
12346 40 2
12347 41 1
12348 40 3
12347 41 2
Is it possible by a trigger ?
Thanks by advance for any help.
Thanks to have read until there.You could use an INSTEAD OF trigger...
Note, you need to use tablockx in the transaction below in order to make
sure you get the max(...)+1 as a unique value - if two sessions run the code
at exactly the same time they would get the same value and force a duplicate
key on insert.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"YDN" <fgargamel@.hotmail.com> wrote in message
news:%23TgEqCI1FHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi with you all,
> I need to automatically increase the value of a column by one. Except of
> course
> for the first value of my column, in that case the value must be 1.
> It's like a counter.
> In the table this counter must be initialized by 1 for each new value of
> "InheritedKey". The following line must have the value increase by one for
> each
> "Key" value increased.
> Sample :
> Key InheritedKey AutomaticValue
> 12345 40 1
> 12346 40 2
> 12347 41 1
> 12348 40 3
> 12347 41 2
> Is it possible by a trigger ?
> Thanks by advance for any help.
> Thanks to have read until there.
>|||As (Key, InheritedKey) isn't unique in your sample data it looks like
the "AutomaticValue" is redundant unless there are some other key
columns that you haven't specified. It does help if you post DDL,
including keys and constraints. Otherwise we can only guess at your
requirements.
If you don't have a key at all then I don't think this will be very
straightforward in a trigger - nor should it be necessary. More
important to fix the table design first.
David Portas
SQL Server MVP
--|||> Note, you need to use tablockx in the transaction below in order to make
> sure you get the max(...)+1 as a unique value - if two sessions run the
> code
Hmm, Tony ,should not be enough to use (updlock ,holdlock) to get a max
value
Can you elaborate a little bit?
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:u9VmUMI1FHA.2312@.TK2MSFTNGP14.phx.gbl...
> You could use an INSTEAD OF trigger...
> Note, you need to use tablockx in the transaction below in order to make
> sure you get the max(...)+1 as a unique value - if two sessions run the
> code at exactly the same time they would get the same value and force a
> duplicate key on insert.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key
> violation,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "YDN" <fgargamel@.hotmail.com> wrote in message
> news:%23TgEqCI1FHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||Hi Uri,
Yer, you are probably right on that one :)
But i'd use updatelockx rather than the holdlock, holdlock is a big cause of
deadlocking.
Anything that will correctly serialise the MAX, i even though of using the
serialisable transaction isolation level but i dont have a range predicate
so doubt it would work - need to test.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2323vdtJ1FHA.3300@.TK2MSFTNGP15.phx.gbl...
> Hmm, Tony ,should not be enough to use (updlock ,holdlock) to get a max
> value
> Can you elaborate a little bit?
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:u9VmUMI1FHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Hi with you all, Hi and thank you David,
I'm Sorry I make a mistake in my sample
Sample :
Key InheritedKey AutomaticValue
12345 40 1
12346 40 2
12347 41 1
12348 40 3
12349 41 2
The table is the line of (something like) an invoice database. The key is th
e number of the line (unique excuse me) and the
InheritedKey is the Invoice number.
I want to automate the number of line of an invoice.
Thanks by advance for any help.
Thanks to have read until there.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> a crit dans le mess
age de news:
1129709255.420542.258460@.g43g2000cwa.googlegroups.com...
> As (Key, InheritedKey) isn't unique in your sample data it looks like
> the "AutomaticValue" is redundant unless there are some other key
> columns that you haven't specified. It does help if you post DDL,
> including keys and constraints. Otherwise we can only guess at your
> requirements.
> If you don't have a key at all then I don't think this will be very
> straightforward in a trigger - nor should it be necessary. More
> important to fix the table design first.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi with you all, Hi and thank you Tony and Uri,
I'm sorry my sample got a mistake :
Sample :
Key InheritedKey AutomaticValue
12345 40 1
12346 40 2
12347 41 1
12348 40 3
12349 41 2
I'm not sure to understand the Tony solution and the uri contribution ?
Well I'm not so far to think that an "INSTEAD OF trigger" is an humour tech
nics rather than an sql technics...
I'm sorry I'm a Newbie.
Is there an automatic solution to make a counter in my invoice line table.
Thanks by advance for any help.
Thanks to have read until there.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> a crit dans le message de news: OoP0myJ1FH
A.3600@.TK2MSFTNGP10.phx.gbl...
> Hi Uri,
> Yer, you are probably right on that one :)
> But i'd use updatelockx rather than the holdlock, holdlock is a big cause
of deadlocking.
> Anything that will correctly serialise the MAX, i even though of using the
serialisable transaction isolation level but i dont
> have a range predicate so doubt it would work - need to test.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%2323vdtJ1FHA.3300@.T
K2MSFTNGP15.phx.gbl...
>|||You will need to test this, but it would be something like...
create table testtrg (
mycol int not null ,
lineitem tinyint not null
)
go
insert testtrg ( mycol, lineitem ) values ( 1, 1 )
insert testtrg ( mycol, lineitem ) values ( 2, 1 )
insert testtrg ( mycol, lineitem ) values ( 3, 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
insert testtrg ( mycol, lineitem )
select mycol, isnull( ( select max( lineitem )
from testtrg t
where t.mycol = i.mycol ), 0 ) + 1
from inserted i
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg order by mycol
insert testtrg ( mycol, lineitem ) values( 1, 0 )
select * from testtrg order by mycol
insert testtrg ( mycol, lineitem ) values( 1, 0 )
select * from testtrg order by mycol
insert testtrg ( mycol, lineitem ) values( 2, 0 )
select * from testtrg order by mycol
go
drop table testtrg
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"YDN" <fgargamel@.hotmail.com> wrote in message
news:eq%23P9rL1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> Hi with you all, Hi and thank you Tony and Uri,
> I'm sorry my sample got a mistake :
> Sample :
> Key InheritedKey AutomaticValue
> 12345 40 1
> 12346 40 2
> 12347 41 1
> 12348 40 3
> 12349 41 2
> I'm not sure to understand the Tony solution and the uri contribution ?
> Well I'm not so far to think that an "INSTEAD OF trigger" is an humour
> technics rather than an sql technics...
> I'm sorry I'm a Newbie.
> Is there an automatic solution to make a counter in my invoice line table.
> Thanks by advance for any help.
> Thanks to have read until there.
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> a crit dans le message de
> news: OoP0myJ1FHA.3600@.TK2MSFTNGP10.phx.gbl...
>|||Hi with you all, Hi and thank you Tony,
Well I'll test as soon as I have access to a Sql server.
I suppose I add as line like :
insert testtrg ( mycol, lineitem ) values ( 1, 1 )
insert testtrg ( mycol, lineitem ) values ( 2, 1 )
insert testtrg ( mycol, lineitem ) values ( 3, 1 )
as I consider it will have case ?
Thanks to have read until there.
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> a crit dans le message de news: %23wfAkwL1
FHA.2964@.TK2MSFTNGP09.phx.gbl...
> You will need to test this, but it would be something like...
> create table testtrg (
> mycol int not null ,
> lineitem tinyint not null
> )
> go
> insert testtrg ( mycol, lineitem ) values ( 1, 1 )
> insert testtrg ( mycol, lineitem ) values ( 2, 1 )
> insert testtrg ( mycol, lineitem ) values ( 3, 1 )
> go
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
> insert testtrg ( mycol, lineitem )
> select mycol, isnull( ( select max( lineitem )
> from testtrg t
> where t.mycol = i.mycol ), 0 ) + 1
> from inserted i
> end
> go
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg order by mycol
> insert testtrg ( mycol, lineitem ) values( 1, 0 )
> select * from testtrg order by mycol
> insert testtrg ( mycol, lineitem ) values( 1, 0 )
> select * from testtrg order by mycol
> insert testtrg ( mycol, lineitem ) values( 2, 0 )
> select * from testtrg order by mycol
> go
> drop table testtrg
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "YDN" <fgargamel@.hotmail.com> wrote in message news:eq%23P9rL1FHA.3892@.TK2
MSFTNGP12.phx.gbl...
>

a tricky query

Hello

I have a table: myTable(#Product_ID, #Month, Value), where Product_ID and Month are the PK columns. I would like to retrieve all the rows from Month 10 to Month 12, if-and-only-if all the Values are the same (and not NULL).

Example:

(Cod01, 10, 456), (Cod01, 11, 456), (Cod01, 12, 456) <-- Would pass
(Cod02, 10, 1234), (Cod02, 11, 1234), (Cod02, 12, 1234) <-- Would pass

(Cod03, 10, 345), (Cod03, 11, 1677), (Cod03, 12, 981) <-- Would not pass

How can I accomplish that?

Thanks a lot.select myTable.Product_ID
, myTable.Month
, myTable.Value
from myTable
inner
join (
select Product_ID
from myTable
where Month between 10 and 12
group
by Product_ID
having count(distinct Value)
= count(*)
) as these
on these.Product_ID = myTable.Product_ID
where myTable.Month between 10 and 12|||Declare @.monthStart int
Declare @.monthEnd int

Set @.monthStart = 10
Set @.monthEnd = 12

Select myTable.* from myTable
INNER JOIN
(
Select Product_ID from myTable
where [month] between @.monthStart and @.monthEnd
Group by Product_ID, [Value]
having count(Product_ID) = ((@.monthEnd-@.monthStart)+1)
) this ON this.Product_ID= myTable.Product_ID

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

a stored procedures question or two.

My main problem is retrieving an output value. My stored procedure is:

.......................

USE [CyclingClub]
GO
/****** Object: StoredProcedure [dbo].[ValidateMemberUsrPwd] Script Date: 05/20/2007 14:46:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Poldie
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[ValidateMemberUsrPwd]
-- Add the parameters for the stored procedure here
@.username nvarchar(16) = NULL,
@.password nvarchar(16) = NULL,
@.memberid int output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT @.memberid = member_id from members
where @.username = member_username
and @.password= member_password

END
.......................

and when I run it from the management studio app I get the results I expect. When I run it from within Visual Studio 2005 Pro Server Explorer, if I assign a valid value for @.username and @.password but leave @.memberid as <DEFAULT> in the Run Stored Procedure box I get the following output:

.......................

Running [dbo].[ValidateMemberUsrPwd] ( @.username = poldie, @.password = plop, @.memberid = <DEFAULT> ).

Procedure or function 'ValidateMemberUsrPwd' expects parameter '@.memberid', which was not supplied.
No rows affected.
(0 row(s) returned)
@.memberid =
@.RETURN_VALUE =
Finished running [dbo].[ValidateMemberUsrPwd].

.......................

Which is a little odd, as I wouldn't have thought the value of an output parameter would have mattered very much. But I can live with that, and if I try again and give a dummy value of 666 I get the following:

.......................

Running [dbo].[ValidateMemberUsrPwd] ( @.username = poldie, @.password = plop, @.memberid = 666 ).

No rows affected.
(0 row(s) returned)
@.memberid = 1
@.RETURN_VALUE = 0
Finished running [dbo].[ValidateMemberUsrPwd].

.......................

Which is better, as 1 is the correct value. But when I try and retrieve the output value in code I only get what I've assigned as the dummy value. My code is:

.......................
Dim cn As New SqlConnection("server=(local);Trusted_Connection=yes;initial catalog=CyclingClub")
Dim cmd As New SqlCommand("ValidateMemberUsrPwd", cn)
cmd.CommandType = Data.CommandType.StoredProcedure

cmd.Parameters.Add(New SqlParameter("@.username", Data.SqlDbType.NVarChar, 16, Data.ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.password", Data.SqlDbType.NVarChar, 16, Data.ParameterDirection.Input))
cmd.Parameters.Add(New SqlParameter("@.memberid", Data.SqlDbType.Int, 0, Data.ParameterDirection.Output))

cmd.Parameters("@.memberid").Value = 666
cmd.Parameters("@.username").Value = sUsername
cmd.Parameters("@.password").Value = sPassword

cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
.......................

In the Immediate window:

?cmd.Parameters("@.memberid").Value
666 {Integer}
Integer: 666 {Integer}

Any ideas what I'm doing wrong? Is it the whole way in which I'm trying to retrieve data? I know there are all sorts of datagrids and sets and readers etc but I'd like to do it this way initially. I tried using the return value initially and couldn't get that working - could that be for the same reason this isn't working?

Thanks in advance for even reading this far!

Hello my friend,

Working with output parameters is tedious. It would be better if you do not use the output parameter. Do not pass in @.MemberID. Declare it in the procedure, set it and then return it from the procedure like so: -

DECLARE @.MemberID AS INT

SET @.MemberID = (SELECT ...)

SELECT @.MemberID

Then instead of using ExecuteNonQuery(), use Object myID = ExecuteScalar() to return one value; which will be the @.MemberID. Then cast it to an integer if it is not null if you need to.

Kind regards

Scotty

|||

Hi poldie,

I think your code is perfectly fine just check these things

1. When you are retrieving the output value? It should be done after cmd.ExecuteNonQuery

Like

cn.open()

cmd.ExecuteNonQuery()

cn.close()

Dim str as string

str=cmd.parameters("@.memberid").value

This should work..

Satya

|||

Thanks. That works, although I chose output type parameters because I'll later need to return a number of fields! I guess this is as good a time as any to learn a little more about this sort of thing!

|||

Thats good... mark the reply as answered if this helped ...

Satya

|||

satya_tanwar:

Thats good... mark the reply as answered if this helped ...

Does the bestest answerer get sweeties?

|||

No,

But its always good time to help anyone and save some time...

Satya

|||

satya_tanwar:

1. When you are retrieving the output value? It should be done after cmd.ExecuteNonQuery

Like

cn.open()

cmd.ExecuteNonQuery()

cn.close()

Dim str as string

str=cmd.parameters("@.memberid").value

I was doing it after ExecuteNonQuery but before I closed the connection.

|||

First, all resultsets must be fully returned and closed before output parameters are available. So, there are specifics that must happen.

Using cn As SqlConnection = New SqlConnection("server=(local);Trusted_Connection=yes;initial catalog=CyclingClub")
Using cmd As SqlCommand = New SqlCommand("dbo.ValidateMemberUsrPwd", cn)
cmd.CommandType = CommandType.StoredProcedure

Dim pUsername As New SqlParameter("@.username", SqlDbType.NVarChar, 16)
Dim pPassword As New SqlParameter("@.password", SqlDbType.NVarChar, 16)
Dim pMemberId As New SqlParameter("@.memberid", SqlDbType.Int)

pUsername.Value = sUsername
pPassword.Value = sPassword
pMemberid.Value = 666
pMemberId.ParameterDirection = ParameterDirection.Output

cmd.Parameters.Add( pUsername )
cmd.Parameters.Add( pPassword )
cmd.Parameters.Add( pMemberId )

cn.Open()
cmd.ExecuteNonQuery()

Response.Write( "MemberId: " + pMemberId.Value.ToString() )

'''
''' Output Parameters available now
'''
End Using
End Using

|||

davidpenton:

First, all resultsets must be fully returned and closed before output parameters are available. So, there are specifics that must happen.

Yes, it's not that though. I just tried your code - that works too. It's as if your parameters (pMemberId) are getting updated by the stored procedure, whereas I'm see the original, unchanged parameters that went into the stored procedure. Is it anything like strings, where if you change a string the old memory gets removed after being copied to the memory used by what will become the new string (which is why you should use Append and not just + to build strings)? Perhaps I'm looking at memory which has been marked for removal by the garbage collector later but which hasn't occurred yet?

Anyway, thanks - that's fixed it!

a small problem

How to retreive the value of last identity has been updated in a database (SQL Server)?From your Stored procedure return SCOPE_IDENTITY

a short question ?

how can i convert the varchar value to a column of data type int?

i am trying to do this

declare @.w varchar(50)
select @.w = col1 from myTable
select colname from mtable
where mtableID in (@.w)

but the mtableID is a int

and i always got

Syntax error converting the varchar value to a column of data type int.

thanks for everyone for helpingtry this

where mtableID in CAST(@.w AS int)

not sure if you want to swap your "in" for an "=" or not...

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

a row value between two dates

hi,

i have a resultset as ( sample )

id startdate enddate value

1 01.01.2007 10.05.2007 20

2 04.01.2007 12.04.2007 40

3 07.01.2007 09.06.2007 30

.

.

i need an olap resulset of the query : value between startdate (xx.xx.xx) and enddata (xx.xx.xx)

how am i supposed the get it on SSAS

2 different dimensions? mdx ?

plz help i could not find a resolution..

thx in advance

Keep in mind I have worked with this for less than a year, so take it for what it's worth...

It looks like you are still in a relational world - the MDX world is a bit different. You would have to have a fact table that holds the value and the foreign keys for your dimension table(s), which contain your dates. So, your schema would look like:

Code Block

fact_values

-

id [business key]

start_calendar_key

end_calendar_key

value

dim_calendar

-

calendar_key

date

and the contents of the tables:

Code Block

fact_values

-

1 20070101 20071005 20

2 20070401 20071204 40

3 20070701 20070906 30

You would create a single dimension (Calendar), include it twice in your cube (once for start date, once for end date - see "Role Playing Dimensions" - http://technet.microsoft.com/en-us/library/ms174487.aspx) and a single measure group based on your fact table. Do a sum on the value.

And your MDX would do something like:

Code Block

Select [Measures].[Fact Value] on 0

From [Cube Name]

Where ( { [Calendar Start].[Date].&[YYYYMMDD] : [Calendar Start].[Date].&[YYYYMMDD] }

, { [Calendar End].[Date].&[YYYYMMDD] : [Calendar End].[Date].&[YYYYMMDD] }

)

I think this is what you were after... I'm sure there's a hundred ways to model this out -

Hope this helps,

John

A problem getting value out of Stored Procedure

Server management studio does not give error from following query, but output parameter (kokonaissumma) is always NULL. I tested it other ways, by making it return value then it worked. But that required changes to the query, so I really don't know. The problem query is the last.

create PROCEDURE [dbo].[kori2]
(
@.Tuotekoodi varchar(20),
@.kokonaissumma money output
)
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_osto WHERE Tuotekoodi=@.Tuotekoodi)
BEGIN
INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)
SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv
FROM dbo.t_Tuote
WHERE Tuotekoodi= @.Tuotekoodi
END
ELSE
BEGIN
UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
END
return (SELECT count(*) FROM dbo.t_osto)
select @.kokonaissumma =sum(Yhteensa)FROM dbo.t_osto

sum(Yhteensa)FROM dbo.t_osto

your missing a space:

sum(Yhteensa) FROM dbo.t_osto

|||

Move your SELECTs before the RETURN.

create PROCEDURE [dbo].[kori2]( @.Tuotekoodivarchar(20),@.kokonaissummamoney output)ASBEGIN SET NOCOUNT ON;IFNOT EXISTS(SELECT *FROM dbo.t_ostoWHERE Tuotekoodi=@.Tuotekoodi)BEGIN INSERT dbo.t_osto (Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,Alv)SELECT Tuotekoodi, Nimi,Malli,Toimittajanimi,Ryhma,Myyntihinta,AlvFROM dbo.t_TuoteWHERE Tuotekoodi= @.TuotekoodiENDELSE BEGIN UPDATE dbo.t_ostoSET Maara=Maara+1WHERE Tuotekoodi=@.TuotekoodiENDselect @.kokonaissumma =sum(Yhteensa)FROM dbo.t_ostoEND

|||

It works. Thank you both. It looks like this now.

...

...

UPDATE dbo.t_osto
SET Maara=Maara+1
WHERE Tuotekoodi=@.Tuotekoodi
END
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)
END

Regards

Leif

|||

You dont need the return statement. The count is being returned through the OUTPUT parameter.

|||

I see. I'll fix that too. Below is part of the query now, it has now all features what I planned.

--

--

select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto --total money
select @.tuotemaara =sum(Maara) FROM dbo.t_osto -- how many items

return (SELECT count(*) FROM dbo.t_osto) --how many lines (and no return)

END

I should learn some sql. It is my weakest point in ASP.NET. I looked at "Books on line", but even first page used so unfamiliar terms, I was not able to go much further than that. Well, search is there and today I found some tutorials in there.

Thanks

Leif

|||

Hi again

I tried my stored procedure without return. Like this. This is my latest a refresh only version.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
SELECT count(*) FROM dbo.t_osto

With this the return value was 0. That was not the correct value. I wonder what else is wrong in my code.

This gave correct value as before. Below.

create PROCEDURE [dbo].[kori3paivitys]
(
@.kokonaissumma money output,
@.tuotemaara numeric(18, 0) output
)
AS
select @.kokonaissumma =sum(Yhteensa) FROM dbo.t_osto
select @.tuotemaara =sum(Maara) FROM dbo.t_osto
return (SELECT count(*) FROM dbo.t_osto)

Regards

Leif

A good way to increment field value

Hi

I have a field containing numbers. I want to do some simple arithmetics with it, say value=value+1 or value=value-1 or or even value+2. What is to be done, is fixed at design time. I think this could be done by loading the row or record to my program and doing the calculations there. And then storing the record back. But this seems too complicated.

Is there a single query doing that in data table.

You can create all your calculation in an sql server user defined function.

And call this function while inserting the data into a table.

|||

Thanks. That seems interesting. And while searching for user defined functions I found help to other problem as well.

I found this:

create function getfulldate (@.date varchar(10))
returns datetime
as
begin
declare @.getfulldate datetime
set @.getfulldate = dateadd (mi,55,@.date)
return @.getfulldate
end

and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03') from emp

If I undestand this right, this goes into code. Do you know how to put udf in sql server. Or rather, where to start learning it.

Regards

Leif

|||

I found a tutorial about T-sql.

a few questions

Hi,
I am new to RSS and I have a few questions that I hope you can help me with.
1. I have a sp with parameter that gets a default value of null
@.misgeretid smallint=null and the where statement is as follows:
WHERE (misgeretcode = @.misgeretid or misgeretcode is null)
In rss I want to add a parameter but allow the user not to select a value
and if he doesn't select a value it will return null to the sp.
2.Also, I noticed that in the employee sales sample the employee dropdown
list gets a value <select a value>. I want to replace that label with all and
set the value to 0. Is it possible?
3. Are there any books on how to user rss with visual studion .net
preferablly vb.net?
ThanksAnswers inline.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"collie" <collie@.discussions.microsoft.com> wrote in message
news:ABC14A10-D68A-41AC-871A-B45B1E57E3B1@.microsoft.com...
> Hi,
> I am new to RSS and I have a few questions that I hope you can help me
> with.
> 1. I have a sp with parameter that gets a default value of null
> @.misgeretid smallint=null and the where statement is as follows:
> WHERE (misgeretcode = @.misgeretid or misgeretcode is null)
> In rss I want to add a parameter but allow the user not to select a value
> and if he doesn't select a value it will return null to the sp.
You need to enable the parameter to "allow nulls". This is done in the
parameter dialog box.
> 2.Also, I noticed that in the employee sales sample the employee dropdown
> list gets a value <select a value>. I want to replace that label with all
> and
> set the value to 0. Is it possible?
Yes, you need to make 0 the default value and add an "all" to the parameter
set.
> 3. Are there any books on how to user rss with visual studion .net
> preferablly vb.net?
Yes, there are several RS books available. See
http://www.microsoft.com/sql/reporting/techinfo/books.asp.
> Thanks
>