How to assign values to variables in a procedure in with select s

Hi,
I have code like this
:
CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
OUTPUT,@.sex VARCHAR(1) OUTPUT
AS
SELECT NAME,SEX from table1 where .....
Now I want to assign the value of Name and sex from the select statement to
variables @.name and @.sex.
How do I do that. I just do not want to execute the select statement twice
as the selection criteria is big.
Thanks,
JSSELECT @.name = name, @.sex = sex
FROM table1
WHERE (something that guarantees exactly one row)
"JS" <JS@.discussions.microsoft.com> wrote in message
news:3796EA53-CC28-46F5-9653-68A052634C94@.microsoft.com...
> Hi,
> I have code like this
> :
> CREATE PROCEDURE GET_OFFENDER_DATA @.value1 VARCHAR(8),@.name VARCHAR(30)
> OUTPUT,@.sex VARCHAR(1) OUTPUT
> AS
> SELECT NAME,SEX from table1 where .....
> Now I want to assign the value of Name and sex from the select statement
> to
> variables @.name and @.sex.
> How do I do that. I just do not want to execute the select statement twice
> as the selection criteria is big.
> Thanks,
> JS
>sql

How to assign roles to a user on reporting server through Web Service MEthod

I am using the Web service of SSRS to assign the roles for a user that exists on that reporting server machine.

I am using the followng code to do this.

Policy[] p = new Policy[1];
p[0] = new Policy();
Role[] R = new Role[1];
R[0]= new Role();
R[0].Name = "Content Manager";
p[0].GroupUserName = "systemtest\user1";
rs.SetPolicies(@."/",p);

The same I have tried with SetSystemPolicy method also.

Policy[] p = new Policy[1];
p[0] = new Policy();
Role[] R = new Role[1];
R[0]= new Role();
R[0].Name = "Content Manager";
p[0].GroupUserName = "systemtest\user1";
rs.SetSystemPolicies(p);

I have used the above codes in achieving the mapping between the SSRS role and the windows local user. Getting error on this.

Vikas

The code looks good to me. What kind of error did you get? There might be a couple of reasons for failing:

1. you do not have permission to perform this action

2. Role can be managed by user, "Content Manager" is a pre-defined role out of box. But it may have been deleted. You have to make sure the role exists. ListRoles soap APIs can get you the available roles.

3. There is already a policy associated with the same user.

...

|||

There is only one issue i see...

p[0].Roles = R;

Before calling SetPolicies.

How to assign roles to a user on reporting server through Web Service MEthod

I am using the Web service of SSRS to assign the roles for a user that exists on that reporting server machine.

I am using the followng code to do this.

Policy[] p = new Policy[1];
p[0] = new Policy();
Role[] R = new Role[1];
R[0]= new Role();
R[0].Name = "Content Manager";
p[0].GroupUserName = "systemtest\user1";
rs.SetPolicies(@."/",p);

The same I have tried with SetSystemPolicy method also.

Policy[] p = new Policy[1];
p[0] = new Policy();
Role[] R = new Role[1];
R[0]= new Role();
R[0].Name = "Content Manager";
p[0].GroupUserName = "systemtest\user1";
rs.SetSystemPolicies(p);

I have used the above codes in achieving the mapping between the SSRS role and the windows local user. Getting error on this.

Vikas

The code looks good to me. What kind of error did you get? There might be a couple of reasons for failing:

1. you do not have permission to perform this action

2. Role can be managed by user, "Content Manager" is a pre-defined role out of box. But it may have been deleted. You have to make sure the role exists. ListRoles soap APIs can get you the available roles.

3. There is already a policy associated with the same user.

...

|||

There is only one issue i see...

p[0].Roles = R;

Before calling SetPolicies.

How To Assign OPENQUERY Results from Linked Servers to local variables..

Hi All,

I have a problem about assigning the results of an OPENQUERY. Please check the code below:

DECLARE @.sqlString nvarchar(4000)
DECLARE @.sqlString1 nvarchar(4000)
DECLARE @.custName nvarchar(100)

SET @.custID = 2
SET @.sqlString1 = 'SELECT * FROM myTable WHERE CustID = ' + CAST( @.custID as varchar(6))
SELECT @.sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXECUTE(@.sqlString)

--
The above code works fine but i need something like
SELECT @.sqlString = 'SELECT @.custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXECUTE(@.sqlString)

By doing this i want to assign the CustName to the local variable @.custName.
I tried to use a temp table but in that case i was not able to specify a where clause in @.sqlString1. (i'd rather use linked_server.databasename.owner.tablename , but i read that this does not provide good performance )

Any suggestions about the problem are welcomed!
Thanks in advance,

Bahtiyar KARANLIKdeclare @.sqlString nvarchar(1000)
SELECT @.sqlString = 'SELECT @.custname=CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
EXEC sp_executesql @.sqlString, N'@.custname varchar(20) out', @.custname out

or

SELECT @.sqlString = 'SELECT CustName FROM OPENQUERY(DEEPACCESS,''' + @.sqlString1 + ''')'
create table #a (s varchar(20))
insert #a
exec (@.sqlstring)

select @.custname = s from #a
drop table #a

Using the 4 part name in the query should be just s good. If you are joining with strings then make sure the servers are collation compatible so that the filter is performed on the remote server and check the query plan.
This sort of thing can give bad performance if used as a join to a local table but if you are just selecting using constants for a filter it should be OK.

how to alternate the colours on a column with parent-child dimension

Hi

I have a matrix and one of the column has a parent-child dimension, how can I code to have alternate colours for the column?

Thanks a lot for your help

G

Since this is a matrix, you would need to follow an approach similar to http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx

-- Robert

|||thanks a lot for your answer!sql

How to alter column in Micorsoft SQL

Hi,
I have the following sample code to alter two table columns. But it
did not work. Please help me out. Thanks!
drop table one ;
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a (NULL) ,
alter column b (NULL)
;
Thanks,
Mike
On Feb 1, 2:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following sample code to alter two table columns. But it
> did not work. Please help me out. Thanks!
> drop table one ;
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a (NULL) ,
> alter column b (NULL)
> ;
> Thanks,
> Mike
Hi Mike,
Perhaps you are not using Books Online properly.
The following code does what u wann.
ALTER TABLE ONE
ALTER COLUMN A FLOAT NULL
ALTER TABLE ONE
ALTER COLUMN B VARCHAR NULL
Thanks
Dutt...

How to alter column in Micorsoft SQL

Hi,
I have the following sample code to alter two table columns. But it
did not work. Please help me out. Thanks!
drop table one ;
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a (NULL) ,
alter column b (NULL)
;
Thanks,
MikeOn Feb 1, 2:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following sample code to alter two table columns. But it
> did not work. Please help me out. Thanks!
> drop table one ;
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a (NULL) ,
> alter column b (NULL)
> ;
> Thanks,
> Mike
Hi Mike,
Perhaps you are not using Books Online properly.
The following code does what u wann.
ALTER TABLE ONE
ALTER COLUMN A FLOAT NULL
ALTER TABLE ONE
ALTER COLUMN B VARCHAR NULL
Thanks
Dutt...

How to alter column in Micorsoft SQL

Hi,
I have the following sample code to alter two table columns. But it
did not work. Please help me out. Thanks!
drop table one ;
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a (NULL) ,
alter column b (NULL)
;
Thanks,
MikeOn Feb 1, 2:09 am, "Michael" <michae...@.gmail.com> wrote:
> Hi,
> I have the following sample code to alter two table columns. But it
> did not work. Please help me out. Thanks!
> drop table one ;
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a (NULL) ,
> alter column b (NULL)
> ;
> Thanks,
> Mike
Hi Mike,
Perhaps you are not using Books Online properly.
The following code does what u wann.
ALTER TABLE ONE
ALTER COLUMN A FLOAT NULL
ALTER TABLE ONE
ALTER COLUMN B VARCHAR NULL
Thanks
Dutt...

How to Alter a column?

Hi,
I am new to SQL Server 2005. Please help
what is the problem with this code..?

ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT getdate();
Go

The error is like

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.As with the CHECK constraint, the expression for a DEFAULT constraint must be included within brackets.

Your example will then be:

ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT (getdate());
Go|||Try this:
ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL CONSTRAINT [DF_AM_Master] DEFAULT (getdate());
Go|||Indeed, as T'zozo suggested above, you may wish to name your constraints to make your physical schema easier to manage.

Regards,|||Thanks for your help...
but when i used the sql above following comments were returned;

1. ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT (getdate());

Error Message
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

2.ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL CONSTRAINT [DF_AM_Master] DEFAULT (getdate());

Error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'CONSTRAINT'.

... Could you please tell why this happens...?

2.|||I was mistaken. It happens occasionally. It appears that you cannot actually modify a DEFAULT definition of a table column using the alter command statement.

From the documentation:

ALTER COLUMN

Specifies that the named column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or lower. For more information, see sp_dbcmptlevel (Transact-SQL).

The modified column cannot be any one of the following:

...

Associated with a default definition. However, the length, precision, or scale of a column can be changed if the data type is not changed.

Perhaps you can do this using Enterprise Manager.

Regards,|||Robert, Robert, Robert...
EM / SSMS are evil ;)

[removed, because it was rubbish]|||I edited my post after you made that reply, which is the reason why your post doesn't make much sense to people who never read my original message, in which I stated something about using Enterprise Manager to make DDL changes rather than searching for that elusive DDL definition.

That view came from a year or two ago when, using SQL Server, I realised that I could perform a task, relating to object manipulation, in Enterprise Manager but not using direct SQL.|||CREATE TABLE tableName (
columnName int
CONSTRAINT constraintName DEFAULT 1
)
GO

ALTER TABLE tableName
DROP CONSTRAINT constraintName
GO

ALTER TABLE tableName
ADD CONSTRAINT constraintName DEFAULT 2
FOR columnName
GO

DROP TABLE tableName
GO

How to alter a column

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikeSyntax usage is incorrect. Please use the below code to modify NOT NULL to NULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:
> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

How to alter a column

Hi,
What's wrong with the following code? Please help. I am using MS SQL
2000.
create table one (
a float NOT NULL,
b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
alter table one
alter column a {drop} NOT NULL
alter column b {drop} NOT NULL
;
Thanks,
MikeSyntax usage is incorrect. Please use the below code to modify NOT NULL to N
ULL
alter table one alter column a Float NULL
alter table one alter column b Varchar(50) NULL
Thanks
Hari
"Michael" wrote:

> Hi,
> What's wrong with the following code? Please help. I am using MS SQL
> 2000.
> create table one (
> a float NOT NULL,
> b varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> )
> alter table one
> alter column a {drop} NOT NULL
> alter column b {drop} NOT NULL
> ;
> Thanks,
> Mike
>

How to align the text in justify format with SQL Server Reporting Services?

How to align the text in justify format In SQL server Reporting Services? Is there any code to do so?

Hi,

I suppose that with justify you mean "fit to entire line" format. This is not available in reporting services. There is only Left, Center and Right.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

Thank you for your reply. Yes, of course, i knew there was no option to justify the text in SQL Server Reporting Services. But i am in need to apply justify format for my text in one of my reports. Hence i also ask "Is there any code to do so?". If u came to know about this, kindly do reply me.

Kalaivani.

|||

Hi,

I'm afraid that if you want to have this kind of functionality, you will need to write a custom control. Here is an example: http://msdn2.microsoft.com/en-us/library/ms345265.aspx. The issue with custom controls is that it needs to be known by all the reportservers that will render your report.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

Thanks for your suggestion. I will try that way.

Kalaivani.

|||

Hello Everybody,

is there any other way to justify text on both sides , i am having the same problem as Kalaivani.

I appreciate ur response.

Ramzan

How to align the text in justify format with SQL Server Reporting Services?

How to align the text in justify format In SQL server Reporting Services? Is there any code to do so?

Hi,

I suppose that with justify you mean "fit to entire line" format. This is not available in reporting services. There is only Left, Center and Right.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

Thank you for your reply. Yes, of course, i knew there was no option to justify the text in SQL Server Reporting Services. But i am in need to apply justify format for my text in one of my reports. Hence i also ask "Is there any code to do so?". If u came to know about this, kindly do reply me.

Kalaivani.

|||

Hi,

I'm afraid that if you want to have this kind of functionality, you will need to write a custom control. Here is an example: http://msdn2.microsoft.com/en-us/library/ms345265.aspx. The issue with custom controls is that it needs to be known by all the reportservers that will render your report.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Hi,

Thanks for your suggestion. I will try that way.

Kalaivani.

|||

Hello Everybody,

is there any other way to justify text on both sides , i am having the same problem as Kalaivani.

I appreciate ur response.

Ramzan

how to add table and all its index for replication

If you're talking about code as in stored procedure
calls, the stored procedures you need are:
exec sp_addarticle
exec sp_addsubscription or exec sp_refreshsubscriptions
I can script mine out for you and post them up, but it
would be more useful for you to create a dummy
publication and add the new articles using enterprise
manager. After they're created, right click the
publication and get EM to script out the publications.
Selecting the relevant sp_addarticle sp_and
addsubscription bits will give you what you need.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I guess i can try it out.. Are indexes added thru sp_addarticle ? or is
there a parameter in there that states to includes indexes,
constraints,etc..?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:0faf01c4f6f9$292fbae0$a501280a@.phx.gbl...
> If you're talking about code as in stored procedure
> calls, the stored procedures you need are:
> exec sp_addarticle
> exec sp_addsubscription or exec sp_refreshsubscriptions
> I can script mine out for you and post them up, but it
> would be more useful for you to create a dummy
> publication and add the new articles using enterprise
> manager. After they're created, right click the
> publication and get EM to script out the publications.
> Selecting the relevant sp_addarticle sp_and
> addsubscription bits will give you what you need.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Have a look at @.schema_option in sp_addarticle. If you
select teh elipsis button in EM and on the article
properties, snapshot tab, you'll see the corresponding
checkboxes. The easiest thing to do is to select what you
want graphically, and then have EM do the bitmask logic
to generate the correct parameter value.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

How to add parameters and filters?

Hi,

I would like to ask a few questions about Reporting Services.

1. Can we add new parameters (by code on VS2005) at runtime when the report it's on a server? Since the job of the Report Viewer only allow to get/set parameters and not add new parameter, i wonder if there is a way to add parameter without adding manually on the report design mode.

2. Can i add some filter to change the data show on the report? (To be more specific, I didnt mean to add filter at the design mode on VS2005, I want to be able to add filter by code)

3. Can i change the query (dataset) of a report at runtime (once again i mean by code) which it's on the reportserver? (Since we can change the query on a rdlc report , i wonder if we can do the same on a rdl.)

Thanks in advance

1. No, you can only change the # of parameters by republishing the RDL.

2. No, you can only add filters in the RDL. You can affect the filter by runtime parameters, though.

3. You can change the connection string at runtime via a parameter, but not the query. That would require republishing an updated RDL.

BTW, you can call SetReportDefinition programatically, so you can achieve all your objectives roundabout by crafting an updated RDL and republishing in code or with script.

How to add if ~else to a exist Stored Procedure

here is my original Sql Code

I add a parameters @.CustId in line 13

when CustID not Null, I want to add this to the Where clause line 146 and 156

if CustId not null then where clause will add a reference like CustId = @.CustID

if CustId is Null then not reference @.custID

how to Add a if ~else clause to my code? i tried all day.. but it doesn't work..

1SET QUOTED_IDENTIFIEROFF2GO3SET ANSI_NULLSOFF4GO5678ALTER PROCEDURE [dbo].[usp_OutDataDownQuery]9@.DTBEGDATETIME,10@.DTENDDATETIME,11@.RemarkINT,12@.BankIdVARCHAR (128)13@.CustIDCHAR141516as17181920SELECT21*22FROM23(24SELECT25ZT_Master.PriKey,26ZT_Master.BankId,27ZT_Master.TDateTime,28ZT_Master.PNo,29ZT_Master.Remark,30ZT_Master.CustId,31ZT_Master.ProcStatus,32ZT_Customer.[Name],33--ZT_Customer.AccountBAK AS Account,34--ZT_Customer.SCAccountBAK AS SCAccount35(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SD')AS Account,36(SELECT TOP 1SUBSTRING(PCLNO, 3, 12)FROM ZT_DetailWHERE ZT_Master.PriKey = ZT_Detail.MasterKeyAND ZT_Detail.TXTYPE ='SC')AS SCAccount37FROM38ZT_MasterLEFTJOIN ZT_CustomerON ZT_Master.CustId=ZT_Customer.Id39) a40,---------------------------------------------------41(SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),42CtcbBanSDTotal=ISNULL(本行代收筆數,0),43CtcbBanSDTotalAMT=ISNULL(本行代收金額,0),44OtherBanSDTotal=ISNULL(他行代收筆數,0),45OtherBanSDTotalAMT=ISNULL(他行代收金額,0),46CtcbBanSCTotal=ISNULL(本行代付筆數,0),47CtcbBanSCTotalAMT=ISNULL(本行代付金額,0),48OtherBanSCTotal=ISNULL(他行代付筆數,0),49OtherBanSCTotalAMT=ISNULL(他行代付金額,0),50GoodSDTotal=ISNULL(代收成功筆數,0),51GoodSDTotalAMT=ISNULL(代收成功金額,0),52GoodSCTotal=ISNULL(代付成功筆數,0),53GoodSCTotalAMT=ISNULL(代付成功金額,0),54BadSDTotal=ISNULL(代收失敗筆數,0),55BadSDTotalAMT=ISNULL(代收失敗金額,0),56BadSCTotal=ISNULL(代付失敗筆數,0),57BadSCTotalAMT=ISNULL(代付失敗金額,0)58FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),59本行代收筆數,60本行代收金額,61他行代收筆數,62他行代收金額,63本行代付筆數,64本行代付金額,65他行代付筆數,66他行代付金額,67代收成功筆數,68代收成功金額,69代付成功筆數,70代付成功金額71FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),72本行代收筆數,73本行代收金額,74他行代收筆數,75他行代收金額,76本行代付筆數,77本行代付金額,78他行代付筆數,79他行代付金額80--------------------代收成功筆數與代收成功金額-------------------81FROM (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),82本行代收筆數,83本行代收金額,84他行代收筆數,85他行代收金額86FROM (SELECT MasterKey,87COUNT(PriKey)AS 本行代收筆數,88SUM(AMT)AS 本行代收金額89FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a90FULLJOIN (SELECT MasterKey,91COUNT(PriKey)AS 他行代收筆數,92SUM(AMT)AS 他行代收金額93FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b94ON a.MasterKey=b.MasterKey) a95-------------------代付成功筆數與代付成功金額-------------------96FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),97本行代付筆數,98本行代付金額,99他行代付筆數,100他行代付金額101FROM (SELECT MasterKey,102COUNT(PriKey)AS 本行代付筆數,103SUM(AMT)AS 本行代付金額104FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) ='822'GROUP BY MasterKey) a105FULLJOIN (SELECT MasterKey,106COUNT(PriKey)AS 他行代付筆數,107SUM(AMT)AS 他行代付金額108FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)ANDSUBSTRING(RBANK, 1, 3) <>'822'GROUP BY MasterKey) b109ON a.MasterKey=b.MasterKey) b110ON a.MasterKey=b.MasterKey) a111--------------------成功筆數與成功金額-----------------------112FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),113代收成功筆數,114代收成功金額,115代付成功筆數,116代付成功金額117FROM (SELECT MasterKey,118COUNT(PriKey)AS 代收成功筆數,119SUM(AMT)AS 代收成功金額120FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) a121FULLJOIN (SELECT MasterKey,122COUNT(PriKey)AS 代付成功筆數,123SUM(AMT)AS 代付成功金額124FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE='00'OR RCODE='')GROUP BY MasterKey) b125ON a.MasterKey=b.MasterKey) b126ON a.MasterKey=b.MasterKey) a127---------------------失敗筆數與失敗金額------------------------128FULLJOIN (SELECT MasterKey=ISNULL(a.MasterKey,b.MasterKey),129代收失敗筆數,130代收失敗金額,131代付失敗筆數,132代付失敗金額133FROM (SELECT MasterKey,134COUNT(PriKey)AS 代收失敗筆數,135SUM(AMT)AS 代收失敗金額136FROM ZT_DetailWHERE TXTYPE='SD'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) a137FULLJOIN (SELECT MasterKey,138COUNT(PriKey)AS 代付失敗筆數,139SUM(AMT)AS 代付失敗金額140FROM ZT_DetailWHERE TXTYPE='SC'AND MasterKeyIN (SELECT PriKeyFROM ZT_MasterWHERE TDATETIMEBETWEEN @.DTBEGAND @.DTEND)AND (RCODE<>'00'AND RCODE<>'')GROUP BY MasterKey) b141ON a.MasterKey=b.MasterKey) b142ON a.MasterKey=b.MasterKey) b143144145146WHERE a.PriKey=b.MasterKeyAND147--TDateTime BETWEEN @.DTBEG AND @.DTEND AND148TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND149150(a.Remark=@.RemarkOR @.Remark=2)AND151(a.BankId=@.BankIdOR @.BankId='')152ORDER BY TDateTime,CustId,PNo153154155156WHERE a.PriKey=b.MasterKeyAND157--TDateTime BETWEEN @.DTBEG AND @.DTEND AND158TDateTimeBETWEEN'2007/9/5'AND'2007/9/5'AND159160(a.Remark=@.RemarkOR @.Remark=2)AND161(a.BankId=@.BankIdOR @.BankId='')162ORDER BY TDateTime,CustId,PNo163164165GO166SET QUOTED_IDENTIFIEROFF167GO168SET ANSI_NULLSON169GO170171

CustID=COALESCE(@.CustID,CustId)

COALESCE function returns the first non-null expression in its expression list.

Refer the below link for more information

http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

How to add a variable in Source view ?

There are 3 views in VS IDE, Designer, Code and Source.

In source view, there are html codes, how do I add a variable into it ?

For example:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:JJConnectionString %>"
SelectCommand="SELECT * FROM [Tbl]"

...

How to change the SelectCommand to like:

SelectCommand="SELECT * FROM [Tbl] WHERE [CREATEDBY] = '" & User.Identity.Name & "'"

The User.Identity.Name is not valid in source view, but i need it to work, are there any way ?

Hello my friend,

To use code within aspx/ascx files, you enclose it within <% %> tags. The page directive at the top of your aspx pages is an example of this.

<%@. %> is for directives

<%= %> is for simple printing of code/variables. Try this on your web page: -

<%

=User.Identity.Name %>

<%# %> is for pre-processing but usually used in binding controls to print/use fields of the data source: -

<asp:Repeater ID="rptCountries" runat="server" Visible="true">
<ItemTemplate>
<td width="200"><%# Container.DataItem("CountryName")%><br/>
</ItemTemplate>
</asp:Repeater>

Kind regards

Scotty

How to add 3 days to the existing date

Bottom is my table structure. please some one can give me the sql code to add 3 days to the existing date.

SemesterID int
SemesterLongID varchar
SemesterIdentifier char
SemesterIdentifierName varchar
SemesterName varchar
SemesterStart datetime
SemesterEnd datetime
SemesterNameAlt varchar
EnrollmentDeadline datetime
RegistrationStart datetime
RegistrationEnd datetime
LengthInWeeks varchar
BulletinStartDate datetime
BulletinEndDate datetime
ClassroomStartDate datetime
ClassroomEndDate datetime
FacClassStartDate datetime
FacClassEndDate datetime
PlanningStart datetime
PlanningEnd datetime
GoToTeach datetime
TOButAppears datetime
TOButDisappears datetime
Signature datetime
TOButDue datetime
BookListDue datetime
ProfAccess datetime
ExamDue datetime
SCGDue datetime
GradesDue datetime
ExtGradesDue datetime
TOExt datetime
SessionPlanning datetime
TODue datetime
SCGStart datetime
TOExpire datetime
SRPTOCDue datetime
SRPCopyDue datetime
SRPCCCFormDue datetime
TOID int
SemesterIdentifierID int
Code varchar
TitleIVDescription varchar
CreatedBy varchar
ModifiedBy varchar
DateCreated datetime
DateModified datetime

Check out books online for "Datediff" and "Dateadd" functions.

How to access to database with c# using DSN

Hello, im new in c# language.

Can someone show me, step by step(with simples code) how can i connect to a DSN connection that aconfigure in my odbc connection in my machine, to connect to mydatabase ?

I need only a simple class that use a DSN connection to retreive information from the database.

Best regards

You don't want to use a System DSN. A System DSN uses ODBC which is a wrapper for OLE DB, meaning that you're talking about a lot of unnecessary overhead. The .Net System.Data.SqlClient classes talk directly to the SQL Server, without having to use OLE DB OR ODBC. Instead, you need to figure out how to build your Connection correctly.

TryConnectionStrings.com.

|||

Im not use SQL server, my database is db2 in AS/400.

Do u know how to connect directly to an as/400 database ?

|||

Hi,

From you description, it seems that you want to connect to your DB2 database by using DSN, right?

I suggest that you can try DB2OdbcFileConnectionString class which describes a DB2 ODBC file connection string.

To create a DSN file and associated connection string

Call the constructor for DB2OdbcFileConnectionString, DB2OdbcSysConnectionString, or DB2OdbcUserConnectionString.
Calling the constructor creates a connection string with default settings. These default settings can be set only through the Data Access Tool user interface.

If you use a file path for a file that currently exists, the system loads the connection string information in that file instead.

Fill in the relevant connection string properties with a call to the properties of the object created.
You can also fill in the connection string with GetString, which returns the connection string as a text string. Once you finish modifying the relevant values, you can return the connection string to the object with a call to SetString.

Save the connection string information back into storage with a call to Save.

For more information, see:
http://msdn2.microsoft.com/en-us/library/ms945575.aspx

Thanks.

How to Access SqlDataSource Fields from Code?

I have <asp:SqlDataSource> in aspx page that is being used by an updateable GridView. However, there are a couple fields in the dataset that contain filenames that I want to access directly in code and place them into some image tags on the page.

This line should give me access to the dataset but how to I access the fields?

Dim myDataSourceAs DataView =DirectCast(SqlDataSourceGridView.[Select](DataSourceSelectArguments.Empty), DataView)

When the SqlDataSource is a DataReader I would access it with the code below but since this SqlDataSource is a DataSet I can't access it with this code.

If myDataSource.ReadThen
If Convert.IsDBNull(myDataSource("CusAgentPhoto"))Then
ImageAgent.ImageUrl ="/photos/nophoto.gif"
Else
ImageAgent.ImageUrl = AgentImagePath & myDataSource("CusAgentPhoto").ToString
EndIf
IfConvert.IsDBNull(myDataSource("CusCompanyLogo"))Then
ImageCompany.ImageUrl="/photos/nophoto.gif"
Else
ImageCompany.ImageUrl=OfficeImagePath & myDataSource("CusCompanyLogo").ToString
EndIf
End If

What would be the correct way to get at the DataSet fields that contain the filenames?

Programmatically accessing values from datasource controls:http://www.mikesdotnetting.com/Article.aspx?ArticleID=45

Also, How to conditionally show an image:http://www.mikesdotnetting.com/Article.aspx?ArticleID=18

|||

That was the info I needed. Excellent reference!