Help with c# syntax

Hello,

I have at sp that return a value:
setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

ALTERPROCEDURE [dbo].[sp_getLastActivityDate]

(

@.useridnvarchar(256)

)

AS

DECLARE @.retnvarchar(256)

SET @.ret=(SELECTCAST(LastActivityDateASnvarchar)

FROM aspnet_Users

WHERE UserName= @.userid)

RETURN @.ret

The sp returns a nvarchar. How can i write the syntax in C# to grab the value in @.ret?

// Tomas

I tried to make a very simple try to retrieve the value from sp but I get exception

"String[0]: the Size property has an invalid size of 0." exception

Whats wrong with this code?

ALTERPROCEDURE [dbo].[sp_test1]
(
@.UNnvarchar='AHHHH'OUTPUT
)
as

and the c# code

using (SqlConnection connection =newSqlConnection(ConfigurationManager.ConnectionStrings["nyconn"].ConnectionString))

{

using (SqlCommand command =newSqlCommand("sp_test1", connection))

{

command.CommandType =CommandType.StoredProcedure;

SqlParameter para = command.Parameters.Add("@.UN",SqlDbType.NVarChar);

para.Direction =ParameterDirection.Output;

connection.Open();

command.ExecuteNonQuery();

string s = (String)command.Parameters["@.UN"].Value;

connnection.Close();|||

Hi,

From your second post, it seems that you are using SqlCommand to execute your stored procedure and declare a output parameter to retrieve the return from your stored procedure, right?

If so, the cause of getting errors like ""String[0]: the Size property has an invalid size of 0." exception" is that you didn't set the size of the output parameter. Since you are declaring an output parameter, it's necessary to declare the size of the parameter explicitly in your code. See the following code snippet:

SqlParameter para = command.Parameters.Add("@.UN", SqlDbType.NVarChar);
para.Direction = ParameterDirection.Output;
para.Size = 50; // 50 for example.

Thanks.

Help with ALTER COLUMN needed

Hi,
I have a database, in which I have made a mistake regarding the datatype =
of several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column =
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects =
access this column.
I can change the datatype without any problems from either Enterprise =
Manager or the new Sql Server Management Studio Express. I am working with =
a MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio =
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche
Ok, a bit of an update. I have discovered that the error is due to a =
DEFAULT constraint on the columns in question. Problem is, this constraint =
has system generated name, so I can't know the name for certain.
As I said I would like to automate the update, but how can I automatically =
detect the DEFAULT constraints, remove them, alter the column and add the =
constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do =
it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.TK2MSFTNGP02.phx.gbl...
Hi,
I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.
I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche
|||This is why you always should name your constraints. Here's an example on how to get the constraint
name using catalog views in 2005:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
SELECT df.name
FROM sys.default_constraints AS df
INNER JOIN sys.columns AS c
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE parent_object_id = object_id('t')
and c.name = 'c2'
Shouldn't be too hard to adapt above for 2000's system tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT constraint on the
columns in question. Problem is, this constraint has system generated name, so I can't know the name
for certain.
As I said I would like to automate the update, but how can I automatically detect the DEFAULT
constraints, remove them, alter the column and add the constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Tibor Karaszi wrote:

>This is why you always should name your constraints.
Ay, I completely agree. I just didn't realize that DEFAULT also added a =
constraint reference.
The example you posted does not work with 2000. I guess the system tables =
follow another scheme. Inspired by it, I got this though:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'c2'
It SEEMS to work. But does anyone have any comments on this, before I =
write a (probably) rather complex update script using this?
It only returns default constraints, but as that is what I needed, it =
suits my immediate needs.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
|||Thomas Due wrote:
Apparently I can manage with this:
create table t(c1 int, c2 int default 1, c3 int default 3)
go
select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =
though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche
sql

Help with ALTER COLUMN needed

Hi,
I have a database, in which I have made a mistake regarding the datatype =
of several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column =
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects =
access this column.
I can change the datatype without any problems from either Enterprise =
Manager or the new Sql Server Management Studio Express. I am working with =
a MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio =
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich NietzscheOk, a bit of an update. I have discovered that the error is due to a =
DEFAULT constraint on the columns in question. Problem is, this constraint =
has system generated name, so I can't know the name for certain.
As I said I would like to automate the update, but how can I automatically =
detect the DEFAULT constraints, remove them, alter the column and add the =
constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do =
it in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Hi
Is it possible that some users (queries) are accessing the table and this
column?
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message
news:OqpxzcI3GHA.4976@.TK2MSFTNGP02.phx.gbl...
Hi,
I have a database, in which I have made a mistake regarding the datatype of
several columns. So I found out that I could use
ALTER TABLE dbo.Plant ALTER COLUMN coreweight numeric(12,2)
CoreWeight is originally defined as an INT.
My problem though, is that I can't.
Whenever I try this T-SQL command, I get this error:
Msg 5074, Level 16, State 1, Line 1
The object 'DF__Plant__CoreWeigh__090A5324' is dependent on column
'coreweight'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN coreweight failed because one or more objects
access this column.
I can change the datatype without any problems from either Enterprise
Manager or the new Sql Server Management Studio Express. I am working with a
MS SQL 2000 database.
The compatibility level is 80.
What do I need to do, in order for the T-SQL command to be accepted?
I can't really use Enterprise Manager or Sql Server Management Studio
Express. I would much prefer to use an automatic update script.
TIA
Thomas Due
Posted with XanaNews version 1.18.1.3
"He who fights with monsters might take care lest he thereby become a
monster."
-- Friedrich Nietzsche|||This is why you always should name your constraints. Here's an example on ho
w to get the constraint
name using catalog views in 2005:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
SELECT df.name
FROM sys.default_constraints AS df
INNER JOIN sys.columns AS c
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE parent_object_id = object_id('t')
and c.name = 'c2'
Shouldn't be too hard to adapt above for 2000's system tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Thomas Due" <tdue@.mail_remove_.dk> wrote in message news:%23JdOmnI3GHA.988@.
TK2MSFTNGP02.phx.gbl...
Ok, a bit of an update. I have discovered that the error is due to a DEFAULT
constraint on the
columns in question. Problem is, this constraint has system generated name,
so I can't know the name
for certain.
As I said I would like to automate the update, but how can I automatically d
etect the DEFAULT
constraints, remove them, alter the column and add the constraints again?
Preferable in T-SQL...
If I need to, I can make the update via. C# but I would much prefer to do it
in a T-SQL script.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Tibor Karaszi wrote:

>This is why you always should name your constraints.
Ay, I completely agree. I just didn't realize that DEFAULT also added a =
constraint reference.
The example you posted does not work with 2000. I guess the system tables =
follow another scheme. Inspired by it, I got this though:
create table t(c1 int, c2 int default 1, c3 int default 3)
GO
select
o.name as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join sysdepends d on
o.parent_obj=3Dd.depid and
d.depnumber=3Do.info
join syscolumns c on
d.id=3Dc.id and
c.colid=3Do.info
where
o.parent_obj =3D object_id('t') and
c.name=3D'c2'
It SEEMS to work. But does anyone have any comments on this, before I =
write a (probably) rather complex update script using this?
It only returns default constraints, but as that is what I needed, it =
suits my immediate needs.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche|||Thomas Due wrote:
Apparently I can manage with this:
create table t(c1 int, c2 int default 1, c3 int default 3)
go
select
object_name(o.id) as constraint_name,
object_name(o.parent_obj) as table_name,
c.name as column_name
from sysobjects o
join syscolumns c on o.parent_obj=3Dc.id and o.info=3Dc.colid
where
o.parent_obj =3D object_id('t') and c.name=3D'c2'
It still does not return other constraints than default constraints =
though.
Thomas Due
Posted with XanaNews version 1.18.1.3
"There is always some madness in love. But there is also always some
reason in madness."
-- Friedrich Nietzsche

Help with a query please....

I have two tables, data_branchAddOns:
CREATE TABLE [dbo].[data_branchAddOns] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[branchId] [int] NOT NULL ,
[name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[cost] [decimal](5, 2) NOT NULL ,
[ipt] [decimal](5, 2) NOT NULL ,
[adminCharge] [decimal](5, 2) NOT NULL ,
[deleted] [bit] NULL
)
data_branchDetails:
CREATE TABLE [dbo].[data_branchDetails] (
[id] [int] IDENTITY (1000, 1) NOT NULL ,
[name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[b2b] [bit] NOT NULL ,
[discountOffPlan] [decimal](5, 2) NOT NULL ,
[discount1to4] [decimal](5, 2) NOT NULL ,
[discount5to19] [decimal](5, 2) NOT NULL ,
[discount20plus] [decimal](5, 2) NOT NULL ,
[loading] [decimal](5, 2) NOT NULL ,
[dateAdded] [datetime] NOT NULL ,
[deleted] [bit] NOT NULL
)
One branch in branchDetails can have many branchAddOns. The key is ID
in branchDetails, and the foreign key in branchAddOns is branchId.
I have a problem in that I need to return a dataset in the form:
BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
AddOnCost3 ... to 5
So basically I'm turning the table on its side and mapping the related
records to be fields. Any idea how I can write a query to do this?
There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
to map the field names in my procedure as I dont think it would be
possible to have a dynamic number of fields returned?
Thanks in advanceHi Chris,
you can write a correlated subquery and map different addon to different
column of your result set if there are a specific number of addons.
If there are differen number of addons for different branches, then the best
way is to fix the schema ouput with the max number of addon column and do th
e
same thing. You might wanna just load them with NULLS or something as per
your requirement.
hope this helps
Abhishek
"Chris Ashley" wrote:

> I have two tables, data_branchAddOns:
> CREATE TABLE [dbo].[data_branchAddOns] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [branchId] [int] NOT NULL ,
> [name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cost] [decimal](5, 2) NOT NULL ,
> [ipt] [decimal](5, 2) NOT NULL ,
> [adminCharge] [decimal](5, 2) NOT NULL ,
> [deleted] [bit] NULL
> )
> data_branchDetails:
> CREATE TABLE [dbo].[data_branchDetails] (
> [id] [int] IDENTITY (1000, 1) NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [b2b] [bit] NOT NULL ,
> [discountOffPlan] [decimal](5, 2) NOT NULL ,
> [discount1to4] [decimal](5, 2) NOT NULL ,
> [discount5to19] [decimal](5, 2) NOT NULL ,
> [discount20plus] [decimal](5, 2) NOT NULL ,
> [loading] [decimal](5, 2) NOT NULL ,
> [dateAdded] [datetime] NOT NULL ,
> [deleted] [bit] NOT NULL
> )
> One branch in branchDetails can have many branchAddOns. The key is ID
> in branchDetails, and the foreign key in branchAddOns is branchId.
>
> I have a problem in that I need to return a dataset in the form:
> BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
> AddOnCost3 ... to 5
> So basically I'm turning the table on its side and mapping the related
> records to be fields. Any idea how I can write a query to do this?
> There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
> to map the field names in my procedure as I dont think it would be
> possible to have a dynamic number of fields returned?
> Thanks in advance
>|||Hi Chris
You may want to try something like:
SELECT B.name AS BranchName,
( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 1 ) AS AddOnName1,
( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 1 ) AS AddOnCost1,
( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 2 ) AS AddOnName2,
( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 2 ) AS AddOnCost2,
...
FROM data_branchDetails B
but it may be slow! This may be better!
SELECT B.name AS BranchName,
MAX(CASE WHEN S.Cnt = 1 THEN S.name END ) AS AddOnName1,
MAX(CASE WHEN S.Cnt = 1 THEN S.cost END ) AS AddOnCost1,
MAX(CASE WHEN S.Cnt = 2 THEN S.name END ) AS AddOnName2,
MAX(CASE WHEN S.Cnt = 2 THEN S.cost END ) AS AddOnCost2,
..
FROM data_branchDetails B
LEFT JOIN
( SELECT A.Branchid, A.name, A.Cost,
(SELECT COUNT(*) FROM data_branchAddOns C WHERE A.Branchid = C.Branchid AND
A.Id >= C.Id ) AS cnt
FROM data_branchAddOns A ) S ON S.Branchid = B.id
John
"Chris Ashley" wrote:

> I have two tables, data_branchAddOns:
> CREATE TABLE [dbo].[data_branchAddOns] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [branchId] [int] NOT NULL ,
> [name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cost] [decimal](5, 2) NOT NULL ,
> [ipt] [decimal](5, 2) NOT NULL ,
> [adminCharge] [decimal](5, 2) NOT NULL ,
> [deleted] [bit] NULL
> )
> data_branchDetails:
> CREATE TABLE [dbo].[data_branchDetails] (
> [id] [int] IDENTITY (1000, 1) NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [b2b] [bit] NOT NULL ,
> [discountOffPlan] [decimal](5, 2) NOT NULL ,
> [discount1to4] [decimal](5, 2) NOT NULL ,
> [discount5to19] [decimal](5, 2) NOT NULL ,
> [discount20plus] [decimal](5, 2) NOT NULL ,
> [loading] [decimal](5, 2) NOT NULL ,
> [dateAdded] [datetime] NOT NULL ,
> [deleted] [bit] NOT NULL
> )
> One branch in branchDetails can have many branchAddOns. The key is ID
> in branchDetails, and the foreign key in branchAddOns is branchId.
>
> I have a problem in that I need to return a dataset in the form:
> BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
> AddOnCost3 ... to 5
> So basically I'm turning the table on its side and mapping the related
> records to be fields. Any idea how I can write a query to do this?
> There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
> to map the field names in my procedure as I dont think it would be
> possible to have a dynamic number of fields returned?
> Thanks in advance
>|||That's really good. Thanks for the help guys.
I was worried I wasn't making any sense, as I am infinitely bad at
explaining SQL problems. :)
Cheers,
Chris
John Bell wrote:
> Hi Chris
> You may want to try something like:
> SELECT B.name AS BranchName,
> ( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 1 ) AS AddOnName1,
> ( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 1 ) AS AddOnCost1,
> ( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 2 ) AS AddOnName2,
> ( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 2 ) AS AddOnCost2,
> ...
> FROM data_branchDetails B
> but it may be slow! This may be better!
> SELECT B.name AS BranchName,
> MAX(CASE WHEN S.Cnt = 1 THEN S.name END ) AS AddOnName1,
> MAX(CASE WHEN S.Cnt = 1 THEN S.cost END ) AS AddOnCost1,
> MAX(CASE WHEN S.Cnt = 2 THEN S.name END ) AS AddOnName2,
> MAX(CASE WHEN S.Cnt = 2 THEN S.cost END ) AS AddOnCost2,
> ..
> FROM data_branchDetails B
> LEFT JOIN
> ( SELECT A.Branchid, A.name, A.Cost,
> (SELECT COUNT(*) FROM data_branchAddOns C WHERE A.Branchid = C.Branchid A
ND
> A.Id >= C.Id ) AS cnt
> FROM data_branchAddOns A ) S ON S.Branchid = B.id
> John
> "Chris Ashley" wrote:
>|||Hi Chris
The following details the things that will help you get quick answers from
the news groups http://www.aspfaq.com/etiquette.asp particularly
http://www.aspfaq.com/etiquette.asp?id=5006. Having the example data and the
expected results from that data also helps to remove any ambiguity that may
arise from just describing the problem in words.
John
"Chris Ashley" wrote:

> That's really good. Thanks for the help guys.
> I was worried I wasn't making any sense, as I am infinitely bad at
> explaining SQL problems. :)
> Cheers,
> Chris
> John Bell wrote:
>|||Hi John,
Thanks for that.
I've tried to amend the query like so:
SELECT B.name AS BranchName,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "homecover") AS AddOn1,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "policyfee") AS AddOn2,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "legalprotection") AS AddOn3
FROM data_branchDetails B
but I get the error:
Invalid column name 'homecover'.
Any ideas?
TIA,
Chris|||Hi Chris
Try using single quotes instead of the double ones that you have as you have
quoted identifiers set.
John
"Chris Ashley" wrote:

> Hi John,
> Thanks for that.
> I've tried to amend the query like so:
> SELECT B.name AS BranchName,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "homecover") AS AddOn1,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "policyfee") AS AddOn2,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "legalprotection") AS AddOn3
> FROM data_branchDetails B
> but I get the error:
> Invalid column name 'homecover'.
> Any ideas?
> TIA,
> Chris
>

Help with a Query

Using MS SQL 2000
I will try and post all relevant information.
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblAsset]
GO
CREATE TABLE [dbo].[tblAsset] (
[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAssetOrgNode]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblAssetOrgNode]
GO
CREATE TABLE [dbo].[tblAssetOrgNode] (
[AssetID] [int] NOT NULL ,
[OrgSystemID] [int] NOT NULL ,
[OrgNodeID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblScan]
GO
CREATE TABLE [dbo].[tblScan] (
[ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AssetID] [int] NULL ,
[ScanDate] [smalldatetime] NULL ,
[AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NOT NULL
) ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOrgSystem]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOrgSystem]
GO
CREATE TABLE [dbo].[tblOrgSystem] (
[OrgSystemID] [int] NOT NULL ,
[OrgSystem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[OrgSystemDescr] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOrgSystemNode]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOrgSystemNode]
GO
CREATE TABLE [dbo].[tblOrgSystemNode] (
[OrgSystemID] [int] NOT NULL ,
[OrgNodeID] [int] NOT NULL ,
[OrgNode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
--
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOrgSystemNodeParent]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOrgSystemNodeParent]
GO
CREATE TABLE [dbo].[tblOrgSystemNodeParent] (
[OrgSystemID] [int] NOT NULL ,
[OrgNodeID] [int] NOT NULL ,
[OrgNodeParentID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
----
--
Here is a quick sample from each table...
tblAsset
AssetID AssetName MAC DateLastScanned
1 RyanPC 00:00:00:00:00:00 6/20/06
--
tblAssetOrgNode
AssetID OrgSystemID OrgNodeID
1 1 3
--
tblScan
ScanID AssetID ScanDate AssetName MAC
1 1 6/20/06 RyanPC x
--
tblOrgSystem
OrgSystemID OrgSystem
1 NorthAmerican
2 Canada
3 Europe
--
tblOrgSystemNode
OrgSystemID OrgNodeID OrgNode
1 3 Mano
1 4 Houston
1 7 Mano-sales
1 10 Houston-sales
2 5 Toronto
2 6 Ontario
2 13 Toronto-sales
2 16 Ontario-sales
3 31 Amsterdam
--
tblOrgSystemNodeParent
OrgSystemId OrgNodeID OrgNodeParentID
1 3 3
1 4 4
1 7 3
1 10 4
2 5 5
2 6 6
2 13 5
2 16 6
3 31 31
(nodes who's parents are themselves are that way for organization/query
purposes)
I am working with a treeview, and I go through the organization
strucute and have an "Asset" Node that is created as a child of each
Parent.
The way my project is RIGHT NOW the user clicks on the Asset Node to
display the Assets assigned to the Parent via this select statement.
select AssetName from tblAsset JOIN tblAssetOrgNode ON tblAsset.AssetID
= tblAssetOrgNode.AssetID where OrgNodeID = 'assetNodeID'
assetNodeID is determined in my app code.
These assets are displayed in a listview box. When one of those assets
are clicked it displays the relevant information in a gridview using
this select statement.
select AssetID, AssetName, MAC as 'MAC(if exists)', ScanID as
'ScanID(if exists)', ScanDate from tblScan
What I want to be able to do is illimate the middle man. I want the
user to be able to click on the "Asset" node that and display the
relevant information only for the Assets who's Parent is what the user
selected.
Any help on this one would be greatly appreciated.
If any more inforation is required, perhaps an explanation of the
structure (even though I posted more than enough info) I would be glad
to clear things up. Thanks in advance.This problem I have resolved myself, thanks to anyone who took the time
to take a look at it, I have another post in regards to Update then
Insert after Update statement that I could definately use some help on.
Thanks again.
rhaazy wrote:
> Using MS SQL 2000
> I will try and post all relevant information.
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblAsset]
> GO
> CREATE TABLE [dbo].[tblAsset] (
> [AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [AssetTypeID] [int] NULL ,
> [MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DatelastScanned] [smalldatetime] NULL ,
> [NextScanDate] [smalldatetime] NULL ,
> [DateCreated] [smalldatetime] NULL ,
> [LastModified] [smalldatetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblAssetOrgNode]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblAssetOrgNode]
> GO
> CREATE TABLE [dbo].[tblAssetOrgNode] (
> [AssetID] [int] NOT NULL ,
> [OrgSystemID] [int] NOT NULL ,
> [OrgNodeID] [int] NOT NULL ,
> [DateCreated] [datetime] NULL ,
> [LastModified] [datetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblScan]
> GO
> CREATE TABLE [dbo].[tblScan] (
> [ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [AssetID] [int] NULL ,
> [ScanDate] [smalldatetime] NULL ,
> [AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateCreated] [smalldatetime] NULL ,
> [LastModified] [smalldatetime] NULL ,
> [Deleted] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblOrgSystem]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblOrgSystem]
> GO
> CREATE TABLE [dbo].[tblOrgSystem] (
> [OrgSystemID] [int] NOT NULL ,
> [OrgSystem] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [OrgSystemDescr] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateCreated] [datetime] NULL ,
> [LastModified] [datetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblOrgSystemNode]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblOrgSystemNode]
> GO
> CREATE TABLE [dbo].[tblOrgSystemNode] (
> [OrgSystemID] [int] NOT NULL ,
> [OrgNodeID] [int] NOT NULL ,
> [OrgNode] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateCreated] [datetime] NULL ,
> [LastModified] [datetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> --
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblOrgSystemNodeParent]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblOrgSystemNodeParent]
> GO
> CREATE TABLE [dbo].[tblOrgSystemNodeParent] (
> [OrgSystemID] [int] NOT NULL ,
> [OrgNodeID] [int] NOT NULL ,
> [OrgNodeParentID] [int] NOT NULL ,
> [DateCreated] [datetime] NULL ,
> [LastModified] [datetime] NULL ,
> [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> ----
--
> Here is a quick sample from each table...
> tblAsset
> AssetID AssetName MAC DateLastScanned
> 1 RyanPC 00:00:00:00:00:00 6/20/06
> --
> tblAssetOrgNode
> AssetID OrgSystemID OrgNodeID
> 1 1 3
> --
> tblScan
> ScanID AssetID ScanDate AssetName MAC
> 1 1 6/20/06 RyanPC x
> --
> tblOrgSystem
> OrgSystemID OrgSystem
> 1 NorthAmerican
> 2 Canada
> 3 Europe
> --
> tblOrgSystemNode
> OrgSystemID OrgNodeID OrgNode
> 1 3 Mano
> 1 4 Houston
> 1 7 Mano-sales
> 1 10 Houston-sales
> 2 5 Toronto
> 2 6 Ontario
> 2 13 Toronto-sales
> 2 16 Ontario-sales
> 3 31 Amsterdam
> --
> tblOrgSystemNodeParent
> OrgSystemId OrgNodeID OrgNodeParentID
> 1 3 3
> 1 4 4
> 1 7 3
> 1 10 4
> 2 5 5
> 2 6 6
> 2 13 5
> 2 16 6
> 3 31 31
> (nodes who's parents are themselves are that way for organization/query
> purposes)
> I am working with a treeview, and I go through the organization
> strucute and have an "Asset" Node that is created as a child of each
> Parent.
> The way my project is RIGHT NOW the user clicks on the Asset Node to
> display the Assets assigned to the Parent via this select statement.
> select AssetName from tblAsset JOIN tblAssetOrgNode ON tblAsset.AssetID
> = tblAssetOrgNode.AssetID where OrgNodeID = 'assetNodeID'
> assetNodeID is determined in my app code.
> These assets are displayed in a listview box. When one of those assets
> are clicked it displays the relevant information in a gridview using
> this select statement.
> select AssetID, AssetName, MAC as 'MAC(if exists)', ScanID as
> 'ScanID(if exists)', ScanDate from tblScan
> What I want to be able to do is illimate the middle man. I want the
> user to be able to click on the "Asset" node that and display the
> relevant information only for the Assets who's Parent is what the user
> selected.
> Any help on this one would be greatly appreciated.
> If any more inforation is required, perhaps an explanation of the
> structure (even though I posted more than enough info) I would be glad
> to clear things up. Thanks in advance.

Help with a query

This summary is not available. Please click here to view the post.