Foreign Key issue in SPROC. 1

[willydude]

I am using SQL Server Express 2005.

I have verifed that a valid record with a JobNumber_PK_ID does exist in my JobNumber table and am able to properly enter info through the table, but I keep getting the classic message below when running the sproc.

Msg 547, Level 16, State 0, Procedure Jobs_Insert_NewJobSteps, Line 255
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tbl_Jobs_JobSteps_tbl_Jobs_JobNumber". The conflict occurred in database "ClientPayroll", table "dbo.tbl_Jobs_JobNumber", column 'JobNumber_PK_ID'.
The statement has been terminated.

I believe the problem is in the area of the code where I select the Customer_PK_ID and the JobNumber_PK_ID. There is some validation before getting to this spot. The
overall sproc process explanation is after the code.

Some info after running the sproc:
The Results grid/pane shows the correct Customer_PK_ID,
nothing in the JobNumber_PK_ID,
the @StepNumber_PK_ID is NULL,
the @JobNumber_PK_ID and the @Customer_PK_ID are 0 for each.
@Message is empty.
--
The Message pane shows the error message above.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Jobs_Insert_NewJobSteps] 
(@CustomerNumber varchar (3), 
@JobNumber varchar (4),
@StepNumber varchar (2), 
@LocationNumber varchar (2), 
@StepCode varchar (2), 
@StepDescription varchar (50),
@PrevailingWage numeric (5,2), 
@Standard Numeric (10,1),
@GuaranteedStepWage numeric (5,2), 
@StepNumber_PK_ID int output,
@JobNumber_PK_ID int output, 
@Customer_PK_ID int output,
@Message varchar (30) output)
as
--declare variables
declare @Continue bit,
  	@CustomerNumber_Required bit
		
set @Continue = 1
set @JobNumber_PK_ID = 0
set @Customer_PK_ID = 0
--set @JobNumber = 0  --have tried this with NULL also. 
--Neither works.
set @LocationNumber = 0
set @Message = ''
--SET @PrevailingWage = null
--set @Standard = null
--set @GuaranteedStepWage = null

Quite a bit of validation begins. I do not believe that the problem is in this area.

--validate that the customer number exists in the parameter 
--(not NULL or empty 
if @CustomerNumber is null or datalength(Ltrim(@CustomerNumber)) = 0
begin
set @Message = 'Customer Number Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@CustomerNumber as varchar (3))) < 1 or
	datalength(cast(@CustomerNumber as varchar (3))) > 3
begin
set @Message = 'Wrong Length of Customer Number'
set @Continue = 0
end

--validate the job number
if @JobNumber is null or datalength(Ltrim(@JobNumber)) = 0
begin
set @Message = 'Job Number Required'
set @Continue = 0
end
--then check its length. Must be 4, include leading 0, i.e. 0786
if datalength(cast(@JobNumber as varchar (4))) <> 4
begin
set @Message = 'Wrong Length of Job Number'
set @Continue = 0
end

--validate the Step number
if @StepNumber is null or datalength(Ltrim(@StepNumber)) = 0
begin
set @Message = 'Step Number Required'
set @Continue = 0
end
--then check its length. Must be 2, include leading 0, i.e. 07
if datalength(cast(@StepNumber as varchar (2))) <> 2
begin
set @Message = 'Wrong Length of Step Number'
set @Continue = 0
end

--validate Location
if @LocationNumber is null or datalength(Ltrim(@LocationNumber)) = 0
begin
set @Message = 'Location Number Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@LocationNumber as varchar (2))) < 1 or
	datalength(cast(@LocationNumber as varchar (2))) > 2
begin
set @Message = 'Wrong Length of Location Number'
set @Continue = 0
end

--validate Step Code
if @StepCode is null or datalength(Ltrim(@StepCode)) = 0
begin
set @Message = 'Step Code Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@StepCode as varchar (2))) < 1 or
	datalength(cast(@StepCode as varchar (2))) > 2
begin
set @Message = 'Wrong Length of Step Code'
set @Continue = 0
end

--validate description
if @StepDescription is null or datalength(@StepDescription) = 0
begin
set @Message = 'Description Required'
set @Continue = 0
end


--validate PrevailingWage
if @PrevailingWage is null or datalength(Ltrim(@PrevailingWage)) = 0
begin
set @Message = 'Prevailing Wage Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@PrevailingWage as numeric (5,2))) < 1 or
	datalength(cast(@PrevailingWage as numeric (5,2))) > 5
begin
set @Message = 'Check prevailing Wage Value'
set @Continue = 0
end
else
begin
	set @PrevailingWage = null
end
--end
--
--Enter and validate Standard
--Add If statement for PR?
if @StepCode = 'PR'
begin
	if @Standard is null or datalength(Ltrim(@Standard)) = 0
	begin
	set @Message = 'Standard Required'
	set @Continue = 0
	end
--then check its length
	if datalength(cast(@Standard as varchar (6))) < 1 or
		datalength(cast(@Standard as varchar (6))) > 6
	begin
	set @Message = 'Check Value of Standard'
	set @Continue = 0
	end
else
begin
	set @Standard = null
end
end

--validate GuaranteedStepWage (5,2)
if @GuaranteedStepWage is null or 
	datalength(Ltrim(@GuaranteedStepWage)) = 0
begin
set @Message = 'Guaranteed Step Wage Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@GuaranteedStepWage as numeric (5,2))) < 1 or
	datalength(cast(@GuaranteedStepWage as numeric (5,2))) > 5
begin
set @Message = 'Check Guaranteed Step Wage Value'
set @Continue = 0
end
else
begin
	set @GuaranteedStepWage = null
end
--end

All data is validated at this point. Begin SELECT and INSERT process.

if @Continue = 0
begin
return 1
end
else

if @StepNumber_PK_ID is null or
	@StepNumber_PK_ID = 0
begin

--PROBLEM STARTS HERE??

select Customer_PK_ID
from
tbl_Customers_CustomerBasicInfo
where
CustomerNumber = @CustomerNumber

select JobNumber_PK_ID
from
tbl_Jobs_JobNumber
where
Customer_PK_ID = Customer_PK_ID
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber
--order by

--PROBLEM ENDS HERE??

the following puts all the info into the JobsJobSteps table
The Customer_PK_ID and the LocationNumber do not need to be
in the JobSteps table b/c the JobNumber_PK_ID is already
composed of the Customer_PK_ID, the Job Number enterd by the user and the Location Number entered by the user.

insert into tbl_Jobs_JobSteps
(JobNumber_PK_ID, 
StepNumber,
StepDescription,
StepCode,
PrevailingWage,
Standard,
GuaranteedStepWage
--StartDate
)

values
(@JobNumber_PK_ID, 
@StepNumber, 
@StepDescription,
@StepCode,
@PrevailingWage,
@Standard,
@GuaranteedStepWage
)

--assigns new StepNumber_PK_ID to new record just added.
set @StepNumber_PK_ID = scope_identity ()
end

Explanation of overall sproc process.

tbl_JobsCustomerBasicInfo Table
The customer must already be set up in the table,
"tbl_JobsCustomerBasicInfo", before their job(s) and the
job steps can be set up in the
tables, "tbl_JobsJobNumber"
and tblJobsJobSteps.

Each customer will have a unique data entry
ID, "CustomerNumber", such as 12, 36,etc. that is
assigned by the data entry person.
This number is entered into
the "tbl_JobsCustomerBasicInfo".
SQL will assign a unique Auto-Incr Identity number in the --column
Customer_PK_ID.

tbl_JobsJobNumber table
The data entry person enters a unique "JobNumber" for the job, such as 0212, 0786, 2633, etc. SQL assigns a unique Auto-Incr Identity number in the column "JobNumber_PK_ID".
A particular Customer/Job combination can exist only once in any one location. Example: Customer 36, Job 0786, Location 1 cannot exist in Location 1 twice, but Customer 36, Job 0786, Location 2 is OK.

Continuing with the Jobs_JobNumber table, upon successful
validation of data entered by the data
entry person, SQL will pull the "Customer_PK_ID" Auto-Incr
Identtity number from the tbl_JobsCustomerBasicInfo table
and place it in the table "tbl_JobsJobNumber" in the column
Customer_PK_ID. THIS INSERT WORKS.

tbl_JobsJobSteps. THIS IS THE INSERT NOT WORKING. CODE ABOVE.
The data entry person enters the Customer, Job, Step
and Location Numbers. The sproc should pull the JobNumber_PK_ID
from the tbl_Jobs_JobNumber. This is determined by the
@CustomerNumber value that selects the Customer_PK_ID from
the CustomerBasicInfo table. This PK_ID is assigned to the variable
Customer_PK_ID in this sproc.

The code then SHOULD select the JobNumber_PK_ID from the JobNumber table based upon the Customer_PK_ID, the @JobNumber and the @LocationNumber. This JobNumber_PK_ID should then be placed into the JobSteps table. The Customer_PK_ID does not need to be in the JobSteps table b/c the JobNumber_PK_ID already reflects this value.
--
--There is currently no error handling.
--There is currently no Transaction, Rollback, Commit code
--
--Right now, trying to get the PK's, unique, and processes to work.

 

[SQLSister]

Print the values of your variables just before running. I believe you will see that you haven't set some of them.

"NOTHING is more important in a database than integrity." ESquared

 

[willydude]

SQL Sister,

I have tried numerous configurations of code with no success. I have recieved messages ranging from too many arguments back to the FK issue. At one time, I actually got the Customer_PK_ID to appear in the results pane, but still got NULLS in the other fields. Below is my current code. Any input would be greatly appreciated.

Thanks,

Bill

ALTER procedure [dbo].[Jobs_Insert_NewJobSteps] 
(@CustomerNumber varchar (3), 
@JobNumber varchar (4),
@StepNumber varchar (2), 
@LocationNumber varchar (2), 
@StepCode varchar (2), 
@StepDescription varchar (50),
@PrevailingWage numeric (5,2), 
@Standard Numeric (10,1),
@GuaranteedStepWage numeric (5,2), 
@StepNumber_PK_ID int output,
@JobNumber_PK_ID int output,
@Message varchar (30) output)
as
--declare variables
declare @Continue bit
--		@CustomerNumber_Required bit
--		@JobNumber_Required bit
--		@LocationNumber_Required bit,
--		@JobNumber_PK_ID int

set @Continue = 1
set @JobNumber_PK_ID = 0
--set @JobNumber = 0
set @LocationNumber = 0
set @Message = ''
--SET @PrevailingWage = null
--set @Standard = null
--set @GuaranteedStepWage = null

print @JobNumber --doesn't seem to do anything

 

[SQLDenis]

>>print @JobNumber --doesn't seem to do anything

so that means it is null

try this

print 'XXX' + COALESCE(@JobNumber,'') + 'XXX' --doesn't seem to do anything

if it is null XXXXXX will be printed (that is like double triple X rated empty string)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

BTW it could also be '' which is passed in

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Let's go back to the orginal code.

select Customer_PK_ID
from
tbl_Customers_CustomerBasicInfo
where
CustomerNumber = @CustomerNumber

select JobNumber_PK_ID
from
tbl_Jobs_JobNumber
where
Customer_PK_ID = Customer_PK_ID
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber
--order by

Here you do two selects but if you wanted them to be assigned to a variable used in another insert you haven't done that.

Instead try

select @JobNumber_PK_ID = JobNumber_PK_ID
from
tbl_Jobs_JobNumber
where
Customer_PK_ID = Customer_PK_ID
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber

then try the insert that it was failing on.
you can do the same for the first select if you need that value later on as well.


"NOTHING is more important in a database than integrity." ESquared

 

[willydude]

SQL Sister,
I tried the code. I still get the FK error. In the results pane, I get an @StepNumber_PK_ID = NULL, @JobNumber_PK_ID = NULL and @Message is empty. The ReturnValue is -6. It looks like my Declarations are not proper.

SQL Denis,
The code you suggested worked. It showed the value of the job number that I entered, such as XXX0212XXX. But when I changed the code to @CustomerNumber or @StepNumber, the job number value kept appearing. I changed the code, saved the sproc, ran it again and I keep getting the job number b/w the X's. The FK error also appeared.

Bill

 

[willydude]

Follow up. By using the code below:

select Customer_PK_ID
from
tbl_Customers_CustomerBasicInfo
where
CustomerNumber = @CustomerNumber

select @JobNumber_PK_ID 
from
tbl_Jobs_JobNumber
where
tbl_Jobs_JobNumber.Customer_PK_ID = Customer_PK_ID
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber

And hard coding the @JobNumber_PK_ID to an existing value, it works. Obviously I am not getting the JobNumber_PK_ID value into the @JobNumber_PK_ID parameter.

SQL Denis, I found the error with the Print function.

Thanks, Bill

 

[SQLSister]

What you are showing in your last post and what I suggested are two differnt things. You still are not setting the variable to the value of the id which is why you are getting the error.

If the code I gave you doesn't work it is because the select is not returning any records. Are you sure the select without the variable in it (the orginal select) returns a record?
try setting the variable this way since the other way didn't seem to work for you.

SET @JobNumber_PK_ID  =
(select JobNumber_PK_ID 
from
tbl_Jobs_JobNumber
where
tbl_Jobs_JobNumber.Customer_PK_ID = Customer_PK_ID
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber)
print @JobNumber_PK_ID

after using this does the print statement show a value for the variable now?

"NOTHING is more important in a database than integrity." ESquared

 

[willydude]

I copied and pasted your code as you posted, with the added (not replacement) line of "print 'XXX' + COALESCE(@JobNumber,'') + 'XXX' ".

When the code is run, in the message pane, the following is returned. (0212 being the entered job number.) Message:

XXX0212XXX
Msg 515, Level 16, State 2, Procedure Jobs_Insert_NewJobSteps, Line 255
Cannot insert the value NULL into column 'JobNumber_PK_ID', table 'ClientPayroll.dbo.tbl_Jobs_JobSteps'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The results pane shows the @StepNumber_PK_ID and @JobNumber_PK_ID as NULL, the @Message is empty and the return value is -6.

Thank you for your help.

 

[SQLSister]

You still aren't getting a value for the jobnumber_pk_id

Since this has gone through several changes please post exactly how you are setting the value of this variable right now.


"NOTHING is more important in a database than integrity." ESquared

 

[willydude]

SQL Sister, thank you for your help and patience. Below is everything that I have in this Sproc, including comments. The code is in a code box below.

A quick Q: Does it matter if this is an “ALTER Procedure”?

Thanks again, Bill

-----tbl_JobsCustomerBasicInfo Table
--The customer must already be set up in the table,
--"tbl_JobsCustomerBasicInfo", before their job(s) and the
--job steps can be set up in the tables, "tbl_JobsJobNumber"
--and tblJobsJobSteps.
--
--Each customer will have a unique data entry ID, "CustomerNumber",
--such as 12, 36,etc. that is assigned by the data entry person.
--This number is entered into the "tbl_JobsCustomerBasicInfo".
--SQL will assign a unique Auto-Incr Identity number in the column
--Customer_PK_ID.
--
-----tbl_JobsJobNumber TABLE
--The data entry person enters a unique "JobNumber" for the job,
--such as 0212, 0786, 2633, etc. SQL assigns a unique Auto-Incr
--Identity number in the column "JobNumber_PK_ID".
--
--A particular Customer/Job combination can exist only once in
--any one location. Example: Customer 36, Job 0786, Location 1
--cannot exist in Location 1 twice, but Customer 36, Job 0786,
--Location 2 is OK.
--
--Upon successful validation of data entered by the data
--entry person, SQL will pull the "Customer_PK_ID" Auto-Incr
--Identity number from the tbl_JobsCustomerBasicInfo table
--and place it in the table "tbl_JobsJobNumber" in the column
--Customer_PK_ID.
--
--The JobNumber_PK_ID is made up of the
--Customer_PK_ID, the JobNumber (not PK_ID) and the LocationNumber.
--
--tbl_JobsJobSteps
--The data entry person enters a Customer/Job/StepNumber string
--that is unique to the job and the location of the job.
--In other words, there can not
--be more than one Customer/Job/Step/Location record for each job.
--Example: Customer 36, Job 0786, Step 12, Loc 1 cannot be in
--Location 1 twice, but it can be in Location 2 and or 3. Such as:
--Customer 36, Job 0786, Step 12, Loc 2
--On the front end app, need to parse the above
--Customer/Job/StepNumber string for placement into the proper column.
--
--Must validate that the Customer_PK_ID selected from the
--Customers table upon entry of the customer number is the same
--Customer_PK_ID that exists with the desired job number in the
--JobNumber table.
--
--As noted above, the JobNumber_PK_ID is made up of the
--Customer_PK_ID, the JobNumber (not PK_ID) and the LocationNumber.
--Thus, it is not necessary to store the Customer_PK_ID and the
--LocationNumber in the JobSteps table.
--
--The JobStep_PK_ID is made up of the JobNumber_PK_ID and
--the StepNumber.

--There is currently no error handling.
--
--There is currently no Transaction, Rollback, Commit code
--
--Right now, trying to get the PK's, unique, and processes to work.
--

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[Jobs_Insert_NewJobSteps] 
(@CustomerNumber varchar (3), 
@JobNumber varchar (4),
@StepNumber varchar (2),
@LocationNumber varchar (2), 
@StepCode varchar (2),
@StepDescription varchar (50),
@PrevailingWage numeric (5,2), 
@Standard Numeric (10,1),
@GuaranteedStepWage numeric (5,2), 
@StepNumber_PK_ID int output,
@JobNumber_PK_ID int output,
@Message varchar (30) output)
as

--declare variables
declare @Continue bit
--@CustomerNumber_Required bit
--@JobNumber_Required bit
--@LocationNumber_Required bit,
--@JobNumber_PK_ID int

set @Continue = 1
set @JobNumber_PK_ID = 9 --works with hard coded PK_ID
--set @JobNumber = 0
set @LocationNumber = 0
set @Message = ''
--SET @PrevailingWage = null
--set @Standard = null
--set @GuaranteedStepWage = null

--Use the following to see variable value
--print 'XXX' + COALESCE(@LocationNumber,'') + 'XXX' 
--validation of entries begins
--validate that the customer number exists in the parameter (not 
	--NULL or empty 
if @CustomerNumber is null or datalength(Ltrim(@CustomerNumber)) = 0
begin
set @Message = 'Customer Number Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@CustomerNumber as varchar (3))) < 1 or
	datalength(cast(@CustomerNumber as varchar (3))) > 3
begin
set @Message = 'Wrong Length of Customer Number'
set @Continue = 0
end

--validate the job number
if @JobNumber is null or datalength(Ltrim(@JobNumber)) = 0
begin
set @Message = 'Job Number Required'
set @Continue = 0
end
--then check its length. Must be 4, include leading 0, i.e. 0786
if datalength(cast(@JobNumber as varchar (4))) <> 4
begin
set @Message = 'Wrong Length of Job Number'
set @Continue = 0
end

--validate the Step number
if @StepNumber is null or datalength(Ltrim(@StepNumber)) = 0
begin
set @Message = 'Step Number Required'
set @Continue = 0
end
--then check its length. Must be 2, include leading 0, i.e. 07
if datalength(cast(@StepNumber as varchar (2))) <> 2
begin
set @Message = 'Wrong Length of Step Number'
set @Continue = 0
end

--validate Location
if @LocationNumber is null or datalength(Ltrim(@LocationNumber)) = 0
begin
set @Message = 'Location Number Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@LocationNumber as varchar (2))) < 1 or
	datalength(cast(@LocationNumber as varchar (2))) > 2
begin
set @Message = 'Wrong Length of Location Number'
set @Continue = 0
end

--validate Step Code
if @StepCode is null or datalength(Ltrim(@StepCode)) = 0
begin
set @Message = 'Step Code Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@StepCode as varchar (2))) < 1 or
	datalength(cast(@StepCode as varchar (2))) > 2
begin
set @Message = 'Wrong Length of Step Code'
set @Continue = 0
end

--validate description
if @StepDescription is null or datalength(@StepDescription) = 0
begin
set @Message = 'Description Required'
set @Continue = 0
end

--validate PrevailingWage
if @PrevailingWage is null or datalength(Ltrim(@PrevailingWage)) = 0
begin
set @Message = 'Prevailing Wage Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@PrevailingWage as numeric (5,2))) < 1 or
	datalength(cast(@PrevailingWage as numeric (5,2))) > 5
begin
set @Message = 'Check prevailing Wage Value'
set @Continue = 0
end
else
begin
	set @PrevailingWage = null
end

--Enter and validate Standard
--Add If statement for PR?
if @StepCode = 'PR'
begin
	if @Standard is null or datalength(Ltrim(@Standard)) = 0
	begin
	set @Message = 'Standard Required'
	set @Continue = 0
	end
--then check its length
	if datalength(cast(@Standard as varchar (6))) < 1 or
		datalength(cast(@Standard as varchar (6))) > 6
	begin
	set @Message = 'Check Value of Standard'
	set @Continue = 0
	end
else
begin
	set @Standard = null
end
end

--validate GuaranteedStepWage (5,2)
if @GuaranteedStepWage is null or 
	datalength(Ltrim(@GuaranteedStepWage)) = 0
begin
set @Message = 'Guaranteed Step Wage Required'
set @Continue = 0
end
--then check its length
if datalength(cast(@GuaranteedStepWage as numeric (5,2))) < 1 or
	datalength(cast(@GuaranteedStepWage as numeric (5,2))) > 5
begin
set @Message = 'Check Guaranteed Step Wage Value'
set @Continue = 0
end
else
begin
	set @GuaranteedStepWage = null
end

--all data is validated at this point.
if @Continue = 0
begin
return 1
end
else

if @StepNumber_PK_ID is null or
	@StepNumber_PK_ID = 0
begin

SET @JobNumber_PK_ID  =
(select JobNumber_PK_ID 
from
tbl_Jobs_JobNumber
where
tbl_Jobs_JobNumber.Customer_PK_ID = Customer_PK_ID
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber)
print @JobNumber_PK_ID 
print 'XXX' + COALESCE(@JobNumber,'') + 'XXX' 

--the following puts all the info into the JobsJobSteps table
--The Customer_PK_ID and the LocationNumber do not need to be
--in the JobSteps table b/c the JobNumber_PK_ID is already 
--composed of the Customer_PK_ID, the Job Number enterd by the 
--user and the Location Number entered by the user.
insert into tbl_Jobs_JobSteps
(JobNumber_PK_ID, 
StepNumber,
StepDescription,
StepCode,
PrevailingWage,
Standard,
GuaranteedStepWage
--StartDate
)
values
(@JobNumber_PK_ID, 
@StepNumber, 
@StepDescription,
@StepCode,
@PrevailingWage,
@Standard,
@GuaranteedStepWage
)
--assigns new StepNumber_PK_ID to new record just added.
set @StepNumber_PK_ID = scope_identity ()
end

 

[willydude]

SQL Sister, the code I just put up has a "9" as the value assigned to JobNumber_PK_ID. This should be zero. I used the 9 b/c I know that I have a JobNumber_PK_ID with that value. I used it to test the code to no avail.

Thanks.

 

[SQLSister]

OK. Exactly how are you calling the proc? and exactly what do you see when these print statments are run:
print @JobNumber_PK_ID
print 'XXX' + COALESCE(@JobNumber,'') + 'XXX'



"NOTHING is more important in a database than integrity." ESquared

 

[willydude]

I am using Management Studio Express. When in Object Explorer, I highlight the sproc, right click and select Execute Procedure. The Execute Procedure box opens showing all of the required parameters. I enter the info that the data entry person would enter, such as: 12 for the CustomerNumber, 0212 for the JobNumber, 13 for the StepNumber and 3 for the LocationNumber. There are a few other items to enter, but they have nothing to do with selecting the various PK’s that I need.

With the code listed above, the following is my output to the message pane:

XXX0212XXX
Msg 515, Level 16, State 2, Procedure Jobs_Insert_NewJobSteps, Line 261
Cannot insert the value NULL into column 'JobNumber_PK_ID', table 'ClientPayroll.dbo.tbl_Jobs_JobSteps'; column does not allow nulls. INSERT fails.
The statement has been terminated.

(1 row(s) affected)

As you can see, there is only one Print statement appearing. The message also is about NULLS, not about the FK as it was before.

When I add other print statements for the Step, Location and Customer Number, I get:

XXX0212XXX
SSS11SSS
LLL0LLL
CCC12CCC
Msg 515, Level 16, State 2, Procedure Jobs_Insert_NewJobSteps, Line 263
Cannot insert the value NULL into column 'JobNumber_PK_ID', table 'ClientPayroll.dbo.tbl_Jobs_JobSteps'; column does not allow nulls. INSERT fails.
The statement has been terminated.

The LLL should be 3, not 0. The X, S and C values are correct. A LocationNumber of 3 is valid.

I will be away from my PC for a day or two due to the holiday.

Thank you for all of your help..

 

[SQLSister]

your problem is here

select JobNumber_PK_ID 
from
tbl_Jobs_JobNumber
where
tbl_Jobs_JobNumber.Customer_PK_ID = Customer_PK_ID
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber

this is not returning any records, therefore you are not able to populate the variable. Substitute the exact values you are using for the variables and see if anything is returned.

How is data getting inserted into this table? The insert to here is not part of your process.

"NOTHING is more important in a database than integrity." ESquared

 

[willydude]

SQL Sister,

One of the problems was with the lines:

set @JobNumber = 0  
set @LocationNumber = 0

With the combination of your code amd me REM’ing the above, it worked. Thank you for your help.