-
Apr 25th, 2017, 07:00 AM
#1
Thread Starter
Junior Member
Stored Procedure/TableAdapter and Failed to enable contraints
I have been struggling for several days now trying to figure out why I am getting the failed to enable constraints, one or more rows contain values violating non-null, unique, or foreign-key constraints when running my code. I have a stored procedure in sql server, part of it below:
Code:
BEGIN
CREATE TABLE #t
(
empl_id VARCHAR(20),
empl_cctr_id INTEGER,
yr DECIMAL(4,0),
rhrs DECIMAL(8,2),
othrs DECIMAL(8,2),
x10210 DECIMAL(8,2),
x10220 DECIMAL(8,2),
x103xx DECIMAL(8,2),
x10303 DECIMAL(8,2),
x104xx DECIMAL(8,2),
x105xx DECIMAL(8,2),
x106xx DECIMAL(8,2),
x109xx DECIMAL(8,2),
x10901 DECIMAL(8,2),
other DECIMAL(8,2),
nhrs DECIMAL(8,2),
tothrs DECIMAL(8,2)
)
INSERT INTO #t (empl_id, empl_cctr_id, yr, rhrs, othrs, x10210, x10220, x103xx, x10303, x104xx, x105xx, x106xx, x109xx, x10901, other, nhrs, tothrs)
EXEC getSMETDetailsAll @yr, 0, 55, @mgr
SELECT
e.empl_last_nm + ', ' + e.empl_first_nm + ' ' + e.empl_middle_init AS Employee,
e.empl_id AS [Empl ID],
e.empl_myid AS MyID,
ISNULL(s.Scheduled,0) AS Scheduled,
ISNULL(n.nph_vac_used,0) AS [PTO-Used],
ISNULL(w.wsum_nph10220,0) AS Holiday,
ISNULL(w.wsum_nph109xx,0) AS [109xx],
ISNULL(w.wsum_other,0) AS Misc,
ISNULL(s.Scheduled - (n.nph_vac_used + w.wsum_nph10220 + w.wsum_other +
w.wsum_nph109xx + n.nph_fmla + n.nph_mil + n.nph_disability),0) AS Net,
ISNULL(n.nph_fmla,0) AS FMLA,
ISNULL(n.nph_mil,0) AS Military,
ISNULL(n.nph_disability,0) AS Disability,
ISNULL(convert(decimal(8,2),((t.rhrs + t.othrs) / (s.pp_cnt * 40)) * 100),0) AS [Productivity Index]
FROM
TEMSEMPL e
LEFT JOIN TEMSNPH n ON e.empl_id = n.nph_empl_id
LEFT JOIN TEMSWSUM w on e.empl_id = w.wsum_empl_id
LEFT JOIN
(SELECT hist_empl_id, hist_yr, max(convert(integer,hist_pp)) - min(convert(integer,hist_pp)) + 1 as pp_cnt,
(max(convert(integer,hist_pp)) - min(convert(integer,hist_pp)) + 1) * 40 AS Scheduled
FROM TEMSHIST
WHERE hist_yr = @yr
GROUP BY hist_empl_id, hist_yr) s
ON s.hist_empl_id = e.empl_id
LEFT JOIN
(SELECT * from #t) t
ON t.empl_id = e.empl_id
WHERE
(e.empl_stat_id IN (1, 2, 3, 4, 6, 8)) AND
n.nph_payyr = @yr and e.empl_mgr_id = @mgr and e.empl_id is not null
DROP TABLE #t
END
In my application I perform get data on the stored procedure:
Code:
Dim absdt As New EMS_DS.getABS4BARDataTable
Dim absta As New EMS_DSTableAdapters.getABS4BARTableAdapter
absdt = absta.GetData(BaseYr, managerLUE.EditValue)
On the getdata line i get the failed to enable constraints. There shouldnt be any constraints. I removed the primary key that the data designer created when I added the table adapter (something i saw when I searched for a solution prior to posting). I tried turning off enforce constraints, but that didnt work. Ive tried changing the statement to left join, inner join, etc.
If i execute the stored procedure in sql server management studio, it works just fine. No data is missing.
Please help me figure this out.
-
Apr 25th, 2017, 08:39 AM
#2
Re: Stored Procedure/TableAdapter and Failed to enable contraints
Firstly, GetData returns a new DataTable so there's no point creating a new EMS_DS.getABS4BARDataTable yourself first and then discarding it when you call GetData. Only create an object if you intend to use it.
As for the issue, one way to debug might be to create your own DataTable and populate it using the same sproc. You can then examine the data it contains and see where there's a value that would violate a constraint that exists in your typed DataTable. It can't be a foreign key for a standalone DataTable and it seems more likely to be a null rather than a non-unique value.
-
Apr 25th, 2017, 09:14 AM
#3
Thread Starter
Junior Member
Re: Stored Procedure/TableAdapter and Failed to enable contraints
On your first point would I just do dim absdt as new datatable?
I think i figured out the problem, in the middle of testing it. I think i was missing the where wsum_yr = @yr so it was returning multiple rows from that table. Checking it now.
Originally Posted by jmcilhinney
Firstly, GetData returns a new DataTable so there's no point creating a new EMS_DS.getABS4BARDataTable yourself first and then discarding it when you call GetData. Only create an object if you intend to use it.
As for the issue, one way to debug might be to create your own DataTable and populate it using the same sproc. You can then examine the data it contains and see where there's a value that would violate a constraint that exists in your typed DataTable. It can't be a foreign key for a standalone DataTable and it seems more likely to be a null rather than a non-unique value.
-
Apr 25th, 2017, 10:09 AM
#4
Thread Starter
Junior Member
Re: Stored Procedure/TableAdapter and Failed to enable contraints
Well that didnt solve it. No idea what is going on.
-
Apr 25th, 2017, 10:15 AM
#5
Thread Starter
Junior Member
Re: Stored Procedure/TableAdapter and Failed to enable contraints
So how can i fill the datatable to debug like you suggested when i get that error. It won't populate the datatable because of the error, no?
-
Apr 25th, 2017, 11:59 AM
#6
Thread Starter
Junior Member
Re: Stored Procedure/TableAdapter and Failed to enable contraints
So I have traced it down to something with the temp table and stored procedure within stored procedure.
-
Apr 25th, 2017, 12:47 PM
#7
Re: Stored Procedure/TableAdapter and Failed to enable contraints
I don't use stored procedures but I had that error many times and it usually means that I have a record in one of the child tables that is not in the master table.
example,
Master: Customers
Child: CustomerOrders
So if you have a record in the CustomerOrders with a Customer ID that is not in the Customers table, you would get that error. But like I said, I don't use SP so maybe that's your problem.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|