User profile for JLueders

Hassan, That is correct. This way, if I need to generate data for a different database, I only have to change the default database for the project and not go into every column that uses a SQL Statement. Jeff / comments

Hassan,That is correct. This way, if I need to generate data for a different database, I only have to change the default database for the project and not go into every column that uses a SQL State...

0 votes

This did not work. The foreign key for FacilityId references the Facilities table while the ContactId references the Contacts table. I tried using Foreign Key (manual) for FacilityId and selecting the Contacts table, but could not get that to work. I was able to make it work using a SQL Statement. One other question: When using a SQL Statement for a column, is there a way to have it use the same connection as the project? / comments

This did not work. The foreign key for FacilityId references the Facilities table while the ContactId references the Contacts table. I tried using Foreign Key (manual) for FacilityId and selectin...

0 votes

Here is the DDL for the 3 tables: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Facilities]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](1000) NOT NULL, [StreetAddress] [nvarchar](1000) NOT NULL, [City] [nvarchar](1000) NOT NULL, [ZipCode] [nvarchar](20) NOT NULL, [PhoneNumber] [nvarchar](100) NOT NULL, [MailingAddress] [nvarchar](1000) NOT NULL, [MailingCity] [nvarchar](1000) NOT NULL, [MailingZipCode] [nvarchar](20) NOT NULL, [StateId] [int] NOT NULL, CONSTRAINT [PK_Facilities] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO CREATE TABLE [dbo].[Contacts]( [Id] [int] IDENTITY(1,1) NOT NULL, [FacilityId] [int] NULL, [FirstName] [nvarchar](200) NOT NULL, [LastName] [nvarchar](200) NOT NULL, [Email] [nvarchar](200) NOT NULL, [WorkPhone] [nvarchar](100) NOT NULL, [CellPhone] [nvarchar](100) NOT NULL, [Extension] [nvarchar](100) NOT NULL, CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Contacts] WITH CHECK ADD CONSTRAINT [FK_Contacts_Facilities_FacilityId] FOREIGN KEY([FacilityId]) REFERENCES [dbo].[Facilities] ([Id]) GO ALTER TABLE [dbo].[Contacts] CHECK CONSTRAINT [FK_Contacts_Facilities_FacilityId] GO CREATE TABLE [dbo].[Jobs]( [Id] [int] IDENTITY(1,1) NOT NULL, [FacilityId] [int] NULL, [DisciplineSpecialtyId] [int] NOT NULL, [JobTypeId] [int] NOT NULL, [ContactId] [int] NOT NULL, [Active] [bit] NOT NULL, CONSTRAINT [PK_Jobs] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Jobs] WITH CHECK ADD CONSTRAINT [FK_Jobs_Contacts_ContactId] FOREIGN KEY([ContactId]) REFERENCES [dbo].[Contacts] ([Id]) GO ALTER TABLE [dbo].[Jobs] CHECK CONSTRAINT [FK_Jobs_Contacts_ContactId] GO ALTER TABLE [dbo].[Jobs] WITH CHECK ADD CONSTRAINT [FK_Jobs_Facilities_FacilityId] FOREIGN KEY([FacilityId]) REFERENCES [dbo].[Facilities] ([Id]) GO ALTER TABLE [dbo].[Jobs] CHECK CONSTRAINT [FK_Jobs_Facilities_FacilityId] GO / comments

Here is the DDL for the 3 tables:SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Facilities]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](1000) NOT NULL, [StreetAdd...

0 votes

I am not trying to set the Id column in the Jobs table. What I am trying to do is set the ContactId based on the FacilityId in the Contacts table. Is it possible to use a generated value in a table in a SQL Query for another column in the same table. / comments

I am not trying to set the Id column in the Jobs table. What I am trying to do is set the ContactId based on the FacilityId in the Contacts table. Is it possible to use a generated value in a tab...

0 votes

I just realized there has been a change to the Jobs table since I got the error and it now causes a different problem. The FacilityId has been changed to allow NULL values. Now, I get only NULLs for the FacilityId when using the below query for FacilityId and the automatic Foreign Key generator for ContactId. SELECT Contacts.FacilityId FROM Contacts join Jobs on Contacts.Id = Jobs.ContactId Sorry I didn't realize this sooner. The DDL I sent is correct. / comments

I just realized there has been a change to the Jobs table since I got the error and it now causes a different problem. The FacilityId has been changed to allow NULL values. Now, I get only NULLs ...

0 votes

As I said before, the automatic Foreign Key generator will not work in this instance. What I need is to have the Facility ID based on the Contact ID. Using the SQL Statement generator makes sense, but I got an error when I tried the statement you provided. / comments

As I said before, the automatic Foreign Key generator will not work in this instance. What I need is to have the Facility ID based on the Contact ID. Using the SQL Statement generator makes sense...

0 votes

Any other ideas on how to accomplish this? / comments

Any other ideas on how to accomplish this?

0 votes

I tried your SQL Statement and got the error: An error occurred. The multi-part identifier "Jobs.ContactId" could not be bound. I also tried the following: SELECT Contacts.FacilityId FROM Contacts join Jobs on Contacts.Id = Jobs.ContactId This gives the message "Not enough rows for column 'FacilityId' in the preview. / comments

I tried your SQL Statement and got the error: An error occurred. The multi-part identifier "Jobs.ContactId" could not be bound.I also tried the following:SELECT Contacts.FacilityId FROM Contacts j...

0 votes

I did use the automatic Foreign Key generator for the Contact. The Facility ID uses the generator as it has a foreign key tying it to the Facilities table. When the Jobs table is populated, it has a valid Contact and Facility, but the Facility ID might not be the facility that is assigned to the contact in the Contacts table. / comments

I did use the automatic Foreign Key generator for the Contact. The Facility ID uses the generator as it has a foreign key tying it to the Facilities table. When the Jobs table is populated, it ha...

0 votes

How can we help you today?

JLueders

Comments