Cascading updates question

I have two tables, Stock and Positions. Stock contains a Symbol column and a
Price column, and Symbol is the key. Positions contain the columns as well a
s
several other columns whose data should change with the Price. Positions can
have the same Symbol multiple times (it is keyed by Symbol-Account).
My design is to have a foreign key between the tables, so that when the
Price in the Stock column is updated, the Price in Positions also updates. I
am making some columns in the Positions table computed columns, so that they
recalculate when the Price updates.
My questions/concerns are:
1. Is this design any good? One alternative I was thinkning of was to use no
computed columns, and run a stored procedure frequently to update the
Positions.
2. Will the computed columns updated automatically when Price changes?
3. Will there be the possibility that some rows in Positions with the same
symbols are not updated simultaneously, so that the Price could be different
for the same symbol?
4. Do the rows lock while they are updating? This has implications because I
am querying this table often for other purposes.
5. If one computed column depends on another computed column, is there a way
to specify the order in which they calculate, or is this just a big no-no?
I am grateful for any insight.
Thank you,
CP Developer"CP Developer" <steved@.newsgroup.nospam> wrote in message
news:A40102EA-6AF1-4377-9332-09EFCAA83204@.microsoft.com...
>I have two tables, Stock and Positions. Stock contains a Symbol column and
>a
> Price column, and Symbol is the key. Positions contain the columns as well
> as
> several other columns whose data should change with the Price. Positions
> can
> have the same Symbol multiple times (it is keyed by Symbol-Account).
> My design is to have a foreign key between the tables, so that when the
> Price in the Stock column is updated, the Price in Positions also updates.
> I
> am making some columns in the Positions table computed columns, so that
> they
> recalculate when the Price updates.
> My questions/concerns are:
> 1. Is this design any good? One alternative I was thinkning of was to use
> no
> computed columns, and run a stored procedure frequently to update the
> Positions.
There are a few reasonable ways I can think of to have a comupted column
based on a column in a related table.
Put a phoney foreign key on (StockID, Price) and use cascade updates (your
idea).
Put a trigger on Stock to update the related positions.
Use a view to join the two tables and define the calculations there.

> 2. Will the computed columns updated automatically when Price changes?

> 3. Will there be the possibility that some rows in Positions with the same
> symbols are not updated simultaneously, so that the Price could be
> different
> for the same symbol?
No.

> 4. Do the rows lock while they are updating? This has implications because
> I
> am querying this table often for other purposes.
Yes. Make sure you have an index supporting the foreign key relationship.

> 5. If one computed column depends on another computed column, is there a
> way
> to specify the order in which they calculate, or is this just a big no-no?
>
No you cannot base one computed column on another. However you are free to
cut and paste the calculation for one column into the other.
Here's an example:
drop table position
drop table stock
go
create table stock
(
id int primary key,
price decimal(9,2),
constraint uk_id_price
unique (id,price)
)
create table position
(
account int not null, -- references account
stock int not null references stock,
price decimal(9,2) not null,
other_price as cast(price*.9 as decimal(9,2)),
constraint pk_position
primary key(account,stock),
constraint fk_position_stock_price
foreign key (stock,price)
references stock(id,price)
on update cascade
)
create index ix_position_stock_price
on position(stock,price)
go
insert into stock(id,price) values (1,3.50)
insert into position(account,stock,price) values (23,1,3.50)
go
update stock set price = 5.25 where id = 1
select * from position
David|||The columns will update when the price changes.
Create Table TableA
(
Symbol varchar(10),
Price Money,
Qty Int,
TotalCost As (Price * Qty)
)
Insert Into TableA
Select 'GBP', 14.52, 2
select * From TableA
Update TableA
Set Qty = 151, price = 45.65
select * From TableA
Drop table TableA
HTH
Barry|||I would store the price in one table only, and select it from there. If
you need you selects to be as fast as possible, consider using an
indexed view.

Cascading Referential Integrity Constraints

In a master-detail one-to-many relationship, I have the foreign key set to '
allow null'. I would like, in this particular case, to automatically have th
e foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will al
ways delete the detail/many records when the master/one record is deleted. I
f the foreign key is nullable, would it not make sense to null it, and if it
is not nullable to delete
the detail/many records?
Is there any efficient way to set a table up so that foreign keys are automa
tically nulled when the primary key record is deleted?That functionality won't be available until the next release of SQL Server
(Yukon). Meanwhile, you will have to handle RI through triggers in that
case. This link may be useful:
http://msdn.microsoft.com/library/d...efintegrity.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"John Austin" <John.Austin@.ManagedNewsgroups.com> wrote in message
news:025F9D33-F01D-43AA-BF12-09C50AAD4670@.microsoft.com...
In a master-detail one-to-many relationship, I have the foreign key set to
'allow null'. I would like, in this particular case, to automatically have
the foreign key set to null when the master/one record is deleted.
My understanding from the 'books on line' is that cascading a delete will
always delete the detail/many records when the master/one record is deleted.
If the foreign key is nullable, would it not make sense to null it, and if
it is not nullable to delete the detail/many records?
Is there any efficient way to set a table up so that foreign keys are
automatically nulled when the primary key record is deleted?

Cascading deletes - Which is better - A Trigger or Foreign Key cascading delete?

I need to implement my cascading deletes on a SQL database. Is it better (performance/reliablility-wise) to use the Foreign Key Cascading Deletes or to just write my own triggers to do the deletes?
I was hoping someone had experimented and found which works best.DRI(declarative referential integrity) triggers are used when Cascade Deletes and Updates cannot be implemented in the RDBMS(relational database management system) as in SQL Server 7.0 and below. In SQL Server 2000 using Cascade Delete and Cascade Update is the way to go. The reason is reliability related because DRI(declarative referential integrity) with Cascade Delete and Cascade Update is guaranteed.
On a side note if you have a why question run a search for Peter Chen 1976 ERD paper on Google and see the 26 pages paper in Algebra that created DRI(declarative referential integrity) in ANSI SQL. Hope this helps.|||Thanks for the great info. All my servers run SQL 2000 so we'll be going with the built-in cascading deletes.

Cascading Deletes

When I setup a relationship in Access I can specify that Primary Key deletes cascade down to the Forgien Key. So when I delete an Order Header it cleans up all the items in the Order Details table for me automatically.

Can I get this same functionality in SQL Server 7 without having to write triggers or are triggers the only way?

thanks
dogNo, trigger is not the only way: you could create a procedure that does this for you or, when you create the table or add the constraint specify the on delete option to cascade (see BOL, create table).|||What!?!?!?

SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.

Triggers are NOT necessary for standard cascading.|||Cascade? Like a waterfall?

Has anyone scanned the landscape for a merry-go-round?

:D

Wholly disconnected ramblings bart man...

Seriously...be careful with cascading...should be no need...

I never liked messing with keys...

damn surrogates...

To me, if a key changes, then it's a new entity...or the key is defined improperly...

You lose all history...|||Well, you certainly aren't alone in your aversion to cascading relationships, but I've never had a problem with them.

Disconneted ramblings...

...many...non-sequiturs...

Wish I had a key for elipsis so I didn't have to hit the period key three times...

Must...complete...sentence.... damn!|||Seriously...be careful with cascading...should be no need...

pretty dogmatic Mr. Kaiser, what's your solution to my previous example, if in fact I don't care about history? Say I have OrderNumber as the Primary key in the OrderHeader table and OrderNumber as a Foreign key in the OrderDetails table, how is this a misconfigured key arrangement?|||Wow...dogmatic...

Cool...

You want to cascade...knock yourself out...|||SQL Server has cascading deletes! The easiest way to manage them is through the Relationships tab of the Properties dialog box in the Enterprise Manager table design form.
That seems to be the logical place for it, however the only options I have are:
Check existing data on creation
Enable relationship for INSERT and UPDATE
Enable relationship for replication

maybe a version difference :confused:|||You want to cascade...knock yourself out...
That's your solution?

WOW......
COOL DUDE.......
THANKS FOR THE MOST RIGHTOUS EXPLAINATION.....................
ATS AWESOME................|||Enable relationship for INSERT and UPDATE

Seems the SQL Server developers were too lazy to say "Enable relationship for INSERT, UPDATE and DELETE. My bad, I read the help screen and found that DELETE is included with this option, however, it doesn't give me the desired result. It actually disables Primary key deletion if Forgein key dependants exist.

Again, do I need to write triggers to accomplish my goal here :confused:

I want all Forgein keys associated with a Primary key to be deleted when I delete the PK record :D|||See attached screenshot.|||Yup, must be an update that I don't have in my v7 version, guess I'll find out what it means to be trigger happy. Thanks blindman.|||'bout time to upgrade, isn't it?|||cascade update\delete is "New" to sql 2000 v :eek:

and you dont have to create database devices anymore.. :D

cascade update / foreign key

Hi!
For the sake of simplicity, I have three tables, Employee, Department and
Work

Employee >-- Department
\ /
\ /
^ ^
Work

The Work table have two columns, empno and depno and consists that the
employee has worked on another department.

Here is my scripts:
create table employee (empno int not null primary key, depno int not null)
create table department (depno int not null primary key)
create table work (empno int not null, depno int not null)

alter table employee add constraint fk_employee_department foreign key
(depno)
references department (depno)
on update cascade

alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade

alter table work add constraint fk_work_department foreign key (depno)
references department (depno)
on update cascade

My problem is the last command. SQL Server responds:
Server: Msg 1785, Level 16, State 1, Line 1
Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

But I want the depno in the work table to be updated when a department.depno
changes a value.

Does anyone have a suggestion on how to overcome this problem?

Thanks in advance

Best regards,
Gunnar Vyenli
EDB-konsulent as
NORWAYHi,

SQL statement that you had provided is generating circular refrence in
updates
Workaround is to use triger instead of foreign key integrity.

Comment the one of foriegn key sql and use triger.

Note this trigger are not tested. but is only for your reference and
other work around for problem.

/*alter table work add constraint fk_work_employee foreign key (empno)
references employee (empno)
on update cascade
go
*/

create trigger trgUpdateEmpNoInDept
on employee
for update
as
set nocount on

declare @.NewEmpno int , @.OldEmpno int

select @.NewEmpno = inserted.empno from inserted
select @.OldEmpno = deleted.empno from deleted

update work set work.empno = @.NewEmpno
where work.empno = @.OldEmpno
if @.@.rowcount=0 or @.@.error<>0
begin
Rollback tran
end

set nocount off
go

create trigger trgCheckEmpExists
on Work
For Update
as

set nocount on

declare @.NewEmpno int
select @.NewEmpno = inserted.empno from inserted

If Not Exists (Select * from employee where empno = @.NewEmpno )
Begin
RAISERROR ('Empno do not exists', 16, 1)
Rollback Tran
End
set nocount off

go

Thanks & Regards, Amit

"Gunnar Vyenli" <gv@.edbkonsulent.no> wrote in message news:<3f6a24cb$1@.news.broadpark.no>...
> Hi!
> For the sake of simplicity, I have three tables, Employee, Department and
> Work
> Employee >-- Department
> \ /
> \ /
> ^ ^
> Work
> The Work table have two columns, empno and depno and consists that the
> employee has worked on another department.
> Here is my scripts:
> create table employee (empno int not null primary key, depno int not null)
> create table department (depno int not null primary key)
> create table work (empno int not null, depno int not null)
> alter table employee add constraint fk_employee_department foreign key
> (depno)
> references department (depno)
> on update cascade
> alter table work add constraint fk_work_employee foreign key (empno)
> references employee (empno)
> on update cascade
> alter table work add constraint fk_work_department foreign key (depno)
> references department (depno)
> on update cascade
> My problem is the last command. SQL Server responds:
> Server: Msg 1785, Level 16, State 1, Line 1
> Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work' may
> cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
> UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> But I want the depno in the work table to be updated when a department.depno
> changes a value.
> Does anyone have a suggestion on how to overcome this problem?
> Thanks in advance
> Best regards,
> Gunnar Vyenli
> EDB-konsulent as
> NORWAY|||Thank you very much!
-Gunnar

"AMIT" <amitb@.zenithinfotech.com> wrote in message
news:f3093a66.0309182236.2313bc10@.posting.google.c om...
> Hi,
>
> SQL statement that you had provided is generating circular refrence in
> updates
> Workaround is to use triger instead of foreign key integrity.
> Comment the one of foriegn key sql and use triger.
> Note this trigger are not tested. but is only for your reference and
> other work around for problem.
> /*alter table work add constraint fk_work_employee foreign key (empno)
> references employee (empno)
> on update cascade
> go
> */
>
> create trigger trgUpdateEmpNoInDept
> on employee
> for update
> as
> set nocount on
> declare @.NewEmpno int , @.OldEmpno int
> select @.NewEmpno = inserted.empno from inserted
> select @.OldEmpno = deleted.empno from deleted
> update work set work.empno = @.NewEmpno
> where work.empno = @.OldEmpno
> if @.@.rowcount=0 or @.@.error<>0
> begin
> Rollback tran
> end
> set nocount off
> go
> create trigger trgCheckEmpExists
> on Work
> For Update
> as
> set nocount on
> declare @.NewEmpno int
> select @.NewEmpno = inserted.empno from inserted
> If Not Exists (Select * from employee where empno = @.NewEmpno )
> Begin
> RAISERROR ('Empno do not exists', 16, 1)
> Rollback Tran
> End
> set nocount off
> go
>
> Thanks & Regards, Amit
> "Gunnar Vyenli" <gv@.edbkonsulent.no> wrote in message
news:<3f6a24cb$1@.news.broadpark.no>...
> > Hi!
> > For the sake of simplicity, I have three tables, Employee, Department
and
> > Work
> > Employee >-- Department
> > \ /
> > \ /
> > ^ ^
> > Work
> > The Work table have two columns, empno and depno and consists that the
> > employee has worked on another department.
> > Here is my scripts:
> > create table employee (empno int not null primary key, depno int not
null)
> > create table department (depno int not null primary key)
> > create table work (empno int not null, depno int not null)
> > alter table employee add constraint fk_employee_department foreign key
> > (depno)
> > references department (depno)
> > on update cascade
> > alter table work add constraint fk_work_employee foreign key (empno)
> > references employee (empno)
> > on update cascade
> > alter table work add constraint fk_work_department foreign key (depno)
> > references department (depno)
> > on update cascade
> > My problem is the last command. SQL Server responds:
> > Server: Msg 1785, Level 16, State 1, Line 1
> > Introducing FOREIGN KEY constraint 'fk_work_department' on table 'work'
may
> > cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
ON
> > UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
> > But I want the depno in the work table to be updated when a
department.depno
> > changes a value.
> > Does anyone have a suggestion on how to overcome this problem?
> > Thanks in advance
> > Best regards,
> > Gunnar Vyenli
> > EDB-konsulent as
> > NORWAY

Cascade Deletes

I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
**********************************************************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> **********************************************************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> I cannot understand why I receive the following error
>> mesage when trying to Create a cascading delete
>> constraint.
>> Introducing FOREIGN KEY
>> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> table 'DealAttribute' may cause cycles or multiple
>> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
>> NO ACTION, or modify other FOREIGN KEY constraints.
>> Deal Table
>> CREATE TABLE [Deal] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [Reference] [char] (20),
>> [CptyID] [int] NULL
>> ............
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> (
>> [DealID],
>> [Generation])
>>
**********************************************************
>> ******
>> Deal Attribute Table
>> CREATE TABLE [DealAttribute] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [AttributeID] [int] NOT NULL ,
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
CLUSTERED
>> (
>> [DealID],
>> [Generation],
>> [AttributeID])
>> I then want to add a constraint on the DealAttribute
>> table that references DealID and Generation in the Deal
>> table. I want the constraint to cascade Delete/Update
>> i.e. when I delete a record from the deal table for the
>> corresponding record to be removed from the
DealAttribute
>> table. But I get the above error. Below is the SQL I
>> use to create the Constraint.
>> if exists (select 1
>> from sysobjects
>> where id = object_id
>> ('FK_DEALATTR_RELATION__DEAL')
>> and type = 'FK')
>> alter table DealAttribute
>> drop constraint FK_DEALATTR_RELATION__DEAL
>> go
>> alter table DealAttribute
>> add constraint FK_DEALATTR_RELATION__DEAL foreign
key
>> (DealID, Generation)
>> references Deal (DealID, Generation)
>> on update cascade on delete cascade
>> go
>> I dont see how it is cyclic.
>> Please help.
>> Thanks
>> Jamie
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> >--Original Message--
> >Jamie
> >Read the error message which says what is exactly the
> problem
> >
> >You have tried to create a constraint that refernces to
> the table with two
> >columns(DealID, Generation)
> >The below script will work for you
> >
> >CREATE TABLE Parent
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > [NAME]CHAR(1) NOT NULL
> >)
> >INSERT INTO Parent VALUES (1,'A')
> >INSERT INTO Parent VALUES (2,'B')
> >INSERT INTO Parent VALUES (3,'C')
> >
> >CREATE TABLE Child
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
> DELETE CASCADE ON
> >UPDATE CASCADE,
> > [NAME]CHAR(2) NOT NULL
> >)
> >
> >INSERT INTO Child VALUES (1,1,'AA')
> >INSERT INTO Child VALUES (2,1,'AA')
> >INSERT INTO Child VALUES (3,2,'BB')
> >INSERT INTO Child VALUES (4,2,'BB')
> >INSERT INTO Child VALUES (5,2,'BB')
> >INSERT INTO Child VALUES (6,3,'CC')
> >
> >
> >
> >
> >
> >
> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> >> I cannot understand why I receive the following error
> >> mesage when trying to Create a cascading delete
> >> constraint.
> >>
> >> Introducing FOREIGN KEY
> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
> >> table 'DealAttribute' may cause cycles or multiple
> >> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> >> NO ACTION, or modify other FOREIGN KEY constraints.
> >>
> >> Deal Table
> >>
> >> CREATE TABLE [Deal] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [Reference] [char] (20),
> >> [CptyID] [int] NULL
> >> ............
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> >> (
> >> [DealID],
> >> [Generation])
> >>
> >>
> **********************************************************
> >> ******
> >>
> >> Deal Attribute Table
> >>
> >> CREATE TABLE [DealAttribute] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [AttributeID] [int] NOT NULL ,
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
> CLUSTERED
> >> (
> >> [DealID],
> >> [Generation],
> >> [AttributeID])
> >>
> >> I then want to add a constraint on the DealAttribute
> >> table that references DealID and Generation in the Deal
> >> table. I want the constraint to cascade Delete/Update
> >> i.e. when I delete a record from the deal table for the
> >> corresponding record to be removed from the
> DealAttribute
> >> table. But I get the above error. Below is the SQL I
> >> use to create the Constraint.
> >>
> >> if exists (select 1
> >> from sysobjects
> >> where id = object_id
> >> ('FK_DEALATTR_RELATION__DEAL')
> >> and type = 'FK')
> >> alter table DealAttribute
> >> drop constraint FK_DEALATTR_RELATION__DEAL
> >> go
> >>
> >> alter table DealAttribute
> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
> key
> >> (DealID, Generation)
> >> references Deal (DealID, Generation)
> >> on update cascade on delete cascade
> >> go
> >>
> >> I dont see how it is cyclic.
> >>
> >> Please help.
> >> Thanks
> >> Jamie
> >
> >
> >.
> >|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
>> Uri,
>> I reference the two columns because the combination of
>> the two make a unique key and are the PK for both
>> tables. Am I missing something really obvious here?
>> Thanks
>> Jamie
>> >--Original Message--
>> >Jamie
>> >Read the error message which says what is exactly the
>> problem
>> >
>> >You have tried to create a constraint that refernces
to
>> the table with two
>> >columns(DealID, Generation)
>> >The below script will work for you
>> >
>> >CREATE TABLE Parent
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > [NAME]CHAR(1) NOT NULL
>> >)
>> >INSERT INTO Parent VALUES (1,'A')
>> >INSERT INTO Parent VALUES (2,'B')
>> >INSERT INTO Parent VALUES (3,'C')
>> >
>> >CREATE TABLE Child
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])
ON
>> DELETE CASCADE ON
>> >UPDATE CASCADE,
>> > [NAME]CHAR(2) NOT NULL
>> >)
>> >
>> >INSERT INTO Child VALUES (1,1,'AA')
>> >INSERT INTO Child VALUES (2,1,'AA')
>> >INSERT INTO Child VALUES (3,2,'BB')
>> >INSERT INTO Child VALUES (4,2,'BB')
>> >INSERT INTO Child VALUES (5,2,'BB')
>> >INSERT INTO Child VALUES (6,3,'CC')
>> >
>> >
>> >
>> >
>> >
>> >
>> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> >> I cannot understand why I receive the following
error
>> >> mesage when trying to Create a cascading delete
>> >> constraint.
>> >>
>> >> Introducing FOREIGN KEY
>> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> >> table 'DealAttribute' may cause cycles or multiple
>> >> cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE
>> >> NO ACTION, or modify other FOREIGN KEY constraints.
>> >>
>> >> Deal Table
>> >>
>> >> CREATE TABLE [Deal] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [Reference] [char] (20),
>> >> [CptyID] [int] NULL
>> >> ............
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation])
>> >>
>> >>
**********************************************************
>> >> ******
>> >>
>> >> Deal Attribute Table
>> >>
>> >> CREATE TABLE [DealAttribute] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [AttributeID] [int] NOT NULL ,
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
>> CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation],
>> >> [AttributeID])
>> >>
>> >> I then want to add a constraint on the DealAttribute
>> >> table that references DealID and Generation in the
Deal
>> >> table. I want the constraint to cascade
Delete/Update
>> >> i.e. when I delete a record from the deal table for
the
>> >> corresponding record to be removed from the
>> DealAttribute
>> >> table. But I get the above error. Below is the
SQL I
>> >> use to create the Constraint.
>> >>
>> >> if exists (select 1
>> >> from sysobjects
>> >> where id = object_id
>> >> ('FK_DEALATTR_RELATION__DEAL')
>> >> and type = 'FK')
>> >> alter table DealAttribute
>> >> drop constraint FK_DEALATTR_RELATION__DEAL
>> >> go
>> >>
>> >> alter table DealAttribute
>> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
>> key
>> >> (DealID, Generation)
>> >> references Deal (DealID, Generation)
>> >> on update cascade on delete cascade
>> >> go
>> >>
>> >> I dont see how it is cyclic.
>> >>
>> >> Please help.
>> >> Thanks
>> >> Jamie
>> >
>> >
>> >.
>> >
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascade Delete Not Working Correctly?

I have a master table that is updated. The master has
child records that are referenced by a foreign key with
the CASCADE DELETE option.
In the transaction log, an update of the master appears
as a DELETE/INSERT. The primary key is not being updated.
For the child record, the same update appears to
DELETE/INSERT the child.
Occassionaly, when a master record is updated, the child
record is deleted but not inserted.
Can anyone explain why?
TIA,
HarryIn order to do this you will need to enable Cascade Update as well as
Cascade Delete...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
> I have a master table that is updated. The master has
> child records that are referenced by a foreign key with
> the CASCADE DELETE option.
> In the transaction log, an update of the master appears
> as a DELETE/INSERT. The primary key is not being updated.
> For the child record, the same update appears to
> DELETE/INSERT the child.
> Occassionaly, when a master record is updated, the child
> record is deleted but not inserted.
> Can anyone explain why?
> TIA,
> Harry|||My apologies, I've not made myself clear.
I'm not trying to do this.
Firstly, I'm trying to understand why an update of a
master record results in an delete/insert of the child.
Secondly, why the delete part sometimes fails.
TIA.
>--Original Message--
>In order to do this you will need to enable Cascade
Update as well as
>Cascade Delete...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
>news:ec2901c4127b$e58cbde0$a001280a@.phx.gbl...
updated.
>
>.
>|||What exactly are you auditing to see this?
I cannot replicate this on a sample db I have...
James Goodman
MCSE MCDBA
http://www.angelfire.com/sports/f1pictures/
"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
> Secondly, why the delete part sometimes fails.
> TIA.
> Update as well as
> message
> updated.|||I'm using the transaction log explorer
tool from Lumigent.
It shows that some updates of the master keep
the child and others do not.
>--Original Message--
>What exactly are you auditing to see this?
>I cannot replicate this on a sample db I have...
>--
>James Goodman
>MCSE MCDBA
>http://www.angelfire.com/sports/f1pictures/
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
has
with
appears
child
>
>.
>|||"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in message
news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
> My apologies, I've not made myself clear.
> I'm not trying to do this.
> Firstly, I'm trying to understand why an update of a
> master record results in an delete/insert of the child.
Has the table got triggers associated with it? An Update trigger results in
updates being converted to a delete followed by an insert (so the trigger
can reference the before and after values in the INSERTED and DELETED
tables)
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004|||The only triggers on the table are SQL Server merge
replication triggers.
Interesting point though. I was not aware of that
behaviour.
>--Original Message--
>"HarryArchibald" <HarryArchibald@.hotmail.com> wrote in
message
> news:13a5701c41284$663d1a40$a101280a@.phx
.gbl...
>Has the table got triggers associated with it? An Update
trigger results in
>updates being converted to a delete followed by an insert
(so the trigger
>can reference the before and after values in the INSERTED
and DELETED
>tables)
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.614 / Virus Database: 393 - Release Date:
05/03/2004
>
>.
>

CASCADE DELETE (No Action)

Hi,
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be do
ne through the GUI in Enterprise Manager. I CAN create a FK through the Ente
rprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box
"check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI a
nd not check existing data on creation?
If not how can I modify my script below to not check the data on creating th
e Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASCADE DELETE (No Action)

Hi,
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation?
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.
On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASCADE DELETE (No Action)

Hi
I have a table that I want to create a Foreign Key constraint on
This column has NULL values
I want to create the Foreign Key with a CASCADE DELETE NO ACTION
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on creation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working
Maybe I should have a Trigger instead
Any advice/info is much appreciated
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
[WKF_WORKFLOW_ID
) REFERENCES [dbo].[T_NWKF_WORKFLOW]
[WKF_WORKFLOW_ID
ON DELETE NO ACTION
G
Thanks
COn Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

carriage return problem..

While retrieving user input from an input control, eg: multi-line textbox, and inserting it into the database, the carriage return or the 'Enter' key is not getting inserting into the database.. instead it inserts a quad ( square ) in the database.. also the text typed after the 'Enter' key is not getting inserted into the database.. please help.The carriage returnis getting inserted. The square box confirms that. When rendering it to a web page, you need to replace the carriage returns with their html equivalent - "<br />"|||I'm having the same problem, how would this be accomplished using VB?|||

VB.Net
<%# Eval("MyValue").ToString().Replace(vbcrlf,"<br />") %>

C#
<%# Eval("MyValue").ToString().Replace("\r","<br />") %>

|||

Thanks!

It works great!

I was also able to get it going with the following a few minutes ago:

<%# Eval("MyValue").Replace(Environment.NewLine, "<br />") %>

Which method do you recommend or are they both good?

|||

With the .NET framework, there are approximately 63 ways to skin most particular cats. The difference between them is most often negligible, and you should use whatever you prefer so long as your page load doesn't appear to be adversely affected. Occasionally you will get a guru tell you to use one option rather than another, because it shaves nanoseconds off the operation, and they will have benchmark tests to prove it. Personally, I think life is too short. I usually use the option that requires less typing, unless I am informed of a convincing reason to use another.

Environment.NewLine has the benefit that it can be used regardless of page language, so I shall use it in future when I answer this question without knowing the language the poster is using. Quite simply, it meets my desire to do less typing.

Capture Primary Key Violation Error

Hello,,

I need to capture the primary key violation error:

If e.CommandName = "Insert" Then
Dim EmployeeIDTextBox As TextBox = CType(dvContact.FindControl("EmployeeIDTextBox"), TextBox)
Dim LastName As TextBox = CType(dvContact.FindControl("LastName"), TextBox)
Dim FirstName As TextBox = CType(dvContact.FindControl("FirstName"), TextBox)

Using cmdAdd As New System.Data.SqlClient.SqlCommand

'Establish connection to the database connection
Dim sqlcon As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("eConnString").ToString)

'Open connection
sqlcon.Open()

'Pass opened connection (see above) to the command object
cmdAdd.Connection = sqlcon

'Using "With/End With" pass content to columns from text objects and datatime variables (see above)
With cmdAdd
.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", EmployeeIDTextBox.Text))
.Parameters.Add(New SqlClient.SqlParameter("@.LastName", LastName.Text))
.Parameters.Add(New SqlClient.SqlParameter("@.FirstName", FirstName.Text))

'Establish the type of commandy object
.CommandType = CommandType.Text

'Pass the Update nonquery statement to the commandText object previously instantiated
.CommandText = "INSERT INTO ATTEmployee(EmployeeID, LastName, FirstName & _
"VALUES (@.EmployeeID, @.LastName, @.FirstName)"
End With

'Execute the nonquerry via the command object
cmdAdd.ExecuteNonQuery() '<==Need to capture primaryKey violation, give user message, cancel insert,return to detailView ReadOnly

'I haven't figured out the correct code to capture the primary key violation

EDITMsg.Text="You can not insert an duplicate record. Try Again."

'Close the sql connection
sqlcon.Close()
End Using
End If

Thank you for your help

Just wrap the ExecuteNonQuery call in a try block, then catch the SqlException. that object has a lot of information about all the errors that come from SQL Server.

Make sense?

Don

|||

No,

I tried to write Try,,Catch,,End Try, but it did not work. I got errors with the using/end using.

|||

Oh! You said you wanted to trap errors on the ExecuteNonQuery. So let's take this one thing at a time and look at using.

First, there really is no reason to use using with a SqlCommand object, since it is a managed resource, although it uses a connection. Better to use it with the SqlConnection object, which definitely needs closing when you're done with it.

That said, what errors do you get with the using? But it doesn't really matter at this point.

Don

|||

I do need to trap the error on the ExecuteNonquery.

Let me start again, I need to capture the Primary Key Violation Error, write a message, i.e. ErrorMsg.Text = "You have an error...".

|||

Okay, then something like this should do it:

Try
cmdAdd.ExecuteNonQuery()
Catch SqlException as sqlEx
' Do something here
Catch 'other exeptions if you need to
End Try

What you do with the exception depends on what you want to present to the UI. You'll normally want to use the exception object's Errors property to get a collection of SqlError objects with the details of what SQL Server sent about the error, and there may be more than one error. You can handle the primary key exception this way.

You also need to manage the connection to the database. It remains open if the error severity is 19 or less.

Does this make sense? Is this what you tried and said didn't work?

Don

|||

I tried the "Try-Catch-End Try" and it is not working nor has not worked for me..I shows are error when there is a duplicate record and when there I try to insert a new record.

My snipet:
<asp:DetailsViewID="dvContact"DataKeyNames="EmployeeID"DataSourceID="sqlEmployeeByID"Height="346px"Width="385px" OnItemInserted="Display_Insert_Msg"
<InsertItemTemplate>
<asp:ButtonID="Button8"CommandName="Insert"Text="Insert"runat="server"Font-Size="10pt"Width="50px"/>
<asp:ButtonID="Button9"CommandName="Cancel"Text="Cancel"runat="server"Font-Size="10pt"Width="50px"/>
</InsertItemTemplate>

Along with the above I have:

Protected Sub Display_Insert_Msg(ByVal sender As Object, ByVal e As DetailsViewInsertedEventArgs)

If e.Exception IsNot Nothing Then
'Error in the new inserted data value.
'Display error message.
' ErrorMessage.Visible = True
EditMSG.Text = "• Duplicate Employee ID Numbers not allowed." <<=== This line writes to the browser when there is a new record and a duplicate record.
e.ExceptionHandled = True
e.KeepInInsertMode = True
Else
'Update the FormView control display
'to reflect a product row insertion.
EditMSG.Text = String.Empty
dvContact.DataBind()
grdEmployees.DataBind()
End If

End Sub

Now, with the Try-Catch-End

Try
'Execute the nonquerry via the command object
cmdAddEmployee.ExecuteNonQuery()
Catch Sqlex As System.Exception
lblErrorMsg.Text = Sqlex.ToString '& ex.Number
End Try

Can't use TAB key in SQL Pane

Hello folks,
When working in Visual Studio 2005 Reporting Services, I have been
cursed with a small problem that I have run out of remedy ideas for.
On the data tab of a report I am no longer able to use the TAB key on
the keyboard to format my SQL. I say no longer because i was using
Visual Studio 2003 until recently and this problem did not exist. If
I hit TAB when writing code in the SQL pane the cursor just moves to
the next action object (button) as if on a form. The only band-aid
solution has been to write all my code in a SQL Management Studio
query window and than copy/paste it into the report SQL pane but this
is a nuisance, especially for short,easy report queries and when
returning to existing reports for upgrades or bug fixes. Another
workaround has been to copy a single TAB from Notepad or any other app
and paste the tab in the SQL pane when needed, but than i have to re-
copy it each time if i copy something else. Any ideas or
sympathizers, I would love to hear from you.
Thanks, and here is an example of what I mean.
/* this is what I am stuck with */
SELECT
foo.Column1,
foo.Column2
FROM
dbo.foo
/* this is what I want */
SELECT
foo.Column1,
foo.Column2
FROM
dbo.fooNo way t o use tab, you need to use spacebar with space, I understand if it
is a small query you can do it, but if it is a big query will be very
tedious.
what otherway you can do is, just click "generic query builder" and again
click what it does is, it indends automatically.
Amarnath
"Skilliam" wrote:
> Hello folks,
> When working in Visual Studio 2005 Reporting Services, I have been
> cursed with a small problem that I have run out of remedy ideas for.
> On the data tab of a report I am no longer able to use the TAB key on
> the keyboard to format my SQL. I say no longer because i was using
> Visual Studio 2003 until recently and this problem did not exist. If
> I hit TAB when writing code in the SQL pane the cursor just moves to
> the next action object (button) as if on a form. The only band-aid
> solution has been to write all my code in a SQL Management Studio
> query window and than copy/paste it into the report SQL pane but this
> is a nuisance, especially for short,easy report queries and when
> returning to existing reports for upgrades or bug fixes. Another
> workaround has been to copy a single TAB from Notepad or any other app
> and paste the tab in the SQL pane when needed, but than i have to re-
> copy it each time if i copy something else. Any ideas or
> sympathizers, I would love to hear from you.
> Thanks, and here is an example of what I mean.
> /* this is what I am stuck with */
> SELECT
> foo.Column1,
> foo.Column2
> FROM
> dbo.foo
> /* this is what I want */
> SELECT
> foo.Column1,
> foo.Column2
> FROM
> dbo.foo
>