Database

Bad Robot wrote:

If I give the PollutionData table a FactoryId this would give me the opportunity to create more than one row of PollutionData referencing the same factory.

Bad Robot wrote:

I have the factory referencing the PollutionId in the PollutionData table.

CREATE TABLE [Factory] (
    [FactoryID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Factory] PRIMARY KEY  CLUSTERED 
    (
        [FactoryID]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO

CREATE TABLE [Pollution] (
    [PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryID] [int] NULL ,
    [PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Pollution] PRIMARY KEY  CLUSTERED 
    (
        [PollutionID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY 
    (
        [FactoryID]
    ) REFERENCES [Factory] (
        [FactoryID]
    ) ON DELETE CASCADE 
) ON [PRIMARY]
GO


insert into factory (FactoryName) values ('Foo1')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY(),'Garbage')

insert into factory (FactoryName) values ('Foo2')
declare @nF1ID int
select @nF1ID = SCOPE_IDENTITY( )
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Toxic Waste')
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Sewage')

insert into factory (FactoryName) values ('Foo3')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY( ),'Tar')

delete from factory where FactoryID = @nF1ID
Go

drop table pollution
drop table factory

CREATE TABLE [Factory] (
    [FactoryID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Factory] PRIMARY KEY  CLUSTERED 
    (
        [FactoryID]
    )  ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE TABLE [Pollution] (
    [PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryID] [int] <font size="5">NOT </font>NULL ,
    [PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Pollution] PRIMARY KEY  CLUSTERED 
    (
        [PollutionID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY 
    (
        [FactoryID]
    ) REFERENCES [Factory] (
        [FactoryID]
    ) ON DELETE CASCADE 
) ON [PRIMARY]
GO

insert into factory (FactoryName) values ('Foo1')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY(),'Garbage')

insert into factory (FactoryName) values ('Foo2')
declare @nF1ID int
select @nF1ID = SCOPE_IDENTITY( )
insert into Pollution (FactoryID,PollutionDesc) values (@nF1ID,'Toxic Waste')

insert into factory (FactoryName) values ('Foo3')
insert into Pollution (FactoryID,PollutionDesc) values (SCOPE_IDENTITY( ),'Tar')

delete from factory where FactoryID = @nF1ID
Go

Bad Robot wrote:

Primary Key in one table, but also primary key in another table

Bad Robot wrote:

UNIQUE constraint could be a way to go

Colin Angus Mackay wrote:

That won't fix that part of the problem. Making FactoryId the primary key or putting a unique constraint on it will.

CREATE TABLE [Pollution] (
    [PollutionID] [int] IDENTITY (1, 1) NOT NULL ,
    [FactoryID] [int] NOT NULL ,
    [PollutionDesc] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Pollution] PRIMARY KEY  CLUSTERED 
    (
        [PollutionID]
    )  ON [PRIMARY] ,
    CONSTRAINT [IX_FactoryID] UNIQUE  NONCLUSTERED 
    (
        [FactoryID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Pollution_Factory] FOREIGN KEY 
    (
        [FactoryID]
    ) REFERENCES [Factory] (
        [FactoryID]
    ) ON DELETE CASCADE 
) ON [PRIMARY]
GO

myDataSet[0].Tables[0].Row[i]

protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)<br />
    {<br />
        Label1.Text = GridView1.SelectedRow.Cells[2].Text;<br />
        int index = GridView1.SelectedIndex;<br />
    }

Regards,

Jay

sbao004 wrote:

when you select the row, it will change the row index to "-1"

Regards,

Jay