INSERT statement conflicted with COLUMN CHECK constraint.

Hi,
I'm attempting to insert a new row into an SQL table using ADO written
with c# and stored procedures.
The ADO code is running OK, and i know it should work as i have used
equivilent code succeffully for other tables. However i am getting the
following error:
{"INSERT statement conflicted with COLUMN CHECK constraint 'CK
tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
table 'tblPatient', column 'pntStage'.\r\nThe statement has been
terminated." }
pntStage has data type NVarChar, and maximum length 8. The values i am
attempting to input do not violate these criteria. I have deleted the
complete row and added it again incase there was some hidden input
mask, this has not solved the problem.
Any ideas what the problem might be? Here's my stored procedure if
taht's any help.
CREATE PROCEDURE proc_InsertPatient
(@.patientNo int output,
@.pntUnitID nvarchar(15),
@.pntTitle nvarchar(4),
@.pntFName nvarchar(20),
@.pntLName nvarchar(30),
@.pntDOB nvarchar(8),
@.pntSex nvarchar(1),
@.pntAddress1 nvarchar(150),
@.pntAddress2 nvarchar(150),
@.pntAddress3 nvarchar(150),
@.pntCountryNo int output,
@.pntPostcode nvarchar (10),
@.pntHPhone nvarchar (14),
@.pntWPhone nvarchar (14),
@.pntMobPhone nvarchar (14),
@.pntEmail nvarchar (50),
@.pntStage nvarchar (8),
@.pntT tinyint,
@.pntN tinyint,
@.pntM tinyint,
@.pntPreviousTreatments char (1000),
@.pntFurtherNotes char (1000)
)
AS
INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
pntDOB, pntSex, pntAddress1,
pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
pntWPhone,
pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
pntPreviousTreatments, pntFurtherNotes)
VALUES
(@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
@.pntAddress1,
@.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
@.pntWPhone,
@.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
@.pntPreviousTreatments, @.pntFurtherNotes)
SELECT @.patientNo=@.@.IDENTITY
GO
Thanks.Hi
pntStage might have a check constraint that specifics that the values can
only be in a certain range or of a certain patters. Look at the Column's
check constrains though EM to see what has been setup.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122817569.583719.19100@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm attempting to insert a new row into an SQL table using ADO written
> with c# and stored procedures.
> The ADO code is running OK, and i know it should work as i have used
> equivilent code succeffully for other tables. However i am getting the
> following error:
> {"INSERT statement conflicted with COLUMN CHECK constraint 'CK
> tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
> table 'tblPatient', column 'pntStage'.\r\nThe statement has been
> terminated." }
> pntStage has data type NVarChar, and maximum length 8. The values i am
> attempting to input do not violate these criteria. I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
> Any ideas what the problem might be? Here's my stored procedure if
> taht's any help.
> CREATE PROCEDURE proc_InsertPatient
> (@.patientNo int output,
> @.pntUnitID nvarchar(15),
> @.pntTitle nvarchar(4),
> @.pntFName nvarchar(20),
> @.pntLName nvarchar(30),
> @.pntDOB nvarchar(8),
> @.pntSex nvarchar(1),
> @.pntAddress1 nvarchar(150),
> @.pntAddress2 nvarchar(150),
> @.pntAddress3 nvarchar(150),
> @.pntCountryNo int output,
> @.pntPostcode nvarchar (10),
> @.pntHPhone nvarchar (14),
> @.pntWPhone nvarchar (14),
> @.pntMobPhone nvarchar (14),
> @.pntEmail nvarchar (50),
> @.pntStage nvarchar (8),
> @.pntT tinyint,
> @.pntN tinyint,
> @.pntM tinyint,
> @.pntPreviousTreatments char (1000),
> @.pntFurtherNotes char (1000)
> )
> AS
> INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
> pntDOB, pntSex, pntAddress1,
> pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
> pntWPhone,
> pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
> pntPreviousTreatments, pntFurtherNotes)
> VALUES
> (@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
> @.pntAddress1,
> @.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
> @.pntWPhone,
> @.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
> @.pntPreviousTreatments, @.pntFurtherNotes)
> SELECT @.patientNo=@.@.IDENTITY
> GO
>
> Thanks.
>|||Please post the table DDL (including the CHECK constraint) and the @.pntStage
value you are trying to insert. This will help us identify the cause of
your problem.

> I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
I'm not sure I understand what you mean by 'deleted the complete row'.
Since the insert failed, I wouldn't expect you would find the row in
tblPatient.
Hope this helps.
Dan Guzman
SQL Server MVP
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122817569.583719.19100@.g43g2000cwa.googlegroups.com...
> Hi,
> I'm attempting to insert a new row into an SQL table using ADO written
> with c# and stored procedures.
> The ADO code is running OK, and i know it should work as i have used
> equivilent code succeffully for other tables. However i am getting the
> following error:
> {"INSERT statement conflicted with COLUMN CHECK constraint 'CK
> tblPatient pntStage'. The conflict occurred in database 'YLCdbSQL',
> table 'tblPatient', column 'pntStage'.\r\nThe statement has been
> terminated." }
> pntStage has data type NVarChar, and maximum length 8. The values i am
> attempting to input do not violate these criteria. I have deleted the
> complete row and added it again incase there was some hidden input
> mask, this has not solved the problem.
> Any ideas what the problem might be? Here's my stored procedure if
> taht's any help.
> CREATE PROCEDURE proc_InsertPatient
> (@.patientNo int output,
> @.pntUnitID nvarchar(15),
> @.pntTitle nvarchar(4),
> @.pntFName nvarchar(20),
> @.pntLName nvarchar(30),
> @.pntDOB nvarchar(8),
> @.pntSex nvarchar(1),
> @.pntAddress1 nvarchar(150),
> @.pntAddress2 nvarchar(150),
> @.pntAddress3 nvarchar(150),
> @.pntCountryNo int output,
> @.pntPostcode nvarchar (10),
> @.pntHPhone nvarchar (14),
> @.pntWPhone nvarchar (14),
> @.pntMobPhone nvarchar (14),
> @.pntEmail nvarchar (50),
> @.pntStage nvarchar (8),
> @.pntT tinyint,
> @.pntN tinyint,
> @.pntM tinyint,
> @.pntPreviousTreatments char (1000),
> @.pntFurtherNotes char (1000)
> )
> AS
> INSERT INTO tblPatient (pntUnitID, pntTitle, pntFName, pntLName,
> pntDOB, pntSex, pntAddress1,
> pntAddress2, pntAddress3, pntCountryNo, pntPostcode, pntHPhone,
> pntWPhone,
> pntMobPhone, pntEmail, pntStage, pntT, pntN, pntM,
> pntPreviousTreatments, pntFurtherNotes)
> VALUES
> (@.pntUnitID, @.pntTitle, @.pntFName, @.pntLName, @.pntDOB, @.pntSex,
> @.pntAddress1,
> @.pntAddress2, @.pntAddress3, @.pntCountryNo, @.pntPostcode, @.pntHPhone,
> @.pntWPhone,
> @.pntMobPhone, @.pntEmail, @.pntStage, @.pntT, @.pntN, @.pntM,
> @.pntPreviousTreatments, @.pntFurtherNotes)
> SELECT @.patientNo=@.@.IDENTITY
> GO
>
> Thanks.
>|||Mike,
Thanks for the quick reply.
I've looked in EM at the tblPatient table. Within it's properties i can
only see row Name (pntStage), Data Type (nvarchar), Size (8), Nulls
(not allowed), Default (blank).
Is this what you mean? Is there another way to check more detailed
constraints?
Thanks again.|||Hi
In EM, when you are in the Table Edit screen, top left next to the save
button is the Properties button. On the check constraints tab, you can see
what columns have constraints and what they are.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122818859.232265.305760@.g14g2000cwa.googlegroups.com...
> Mike,
> Thanks for the quick reply.
> I've looked in EM at the tblPatient table. Within it's properties i can
> only see row Name (pntStage), Data Type (nvarchar), Size (8), Nulls
> (not allowed), Default (blank).
> Is this what you mean? Is there another way to check more detailed
> constraints?
> Thanks again.
>|||Dan,
Excuse my ignorance, but where do i find the table DDL?
As regards the input value, i have tried a few "Unknown", "I", "II",
"Ia", to name a few.
By the deleting row comment, i was just meaning that it didn't work, so
i completely removed that particular row from the table, then recreated
it. I thought perhaps i might have put some sort of input mask
constraint or something on it that i had forgotten about. By doing this
it would remove that possibility.
Thanks.|||Mike,
I've just done that, there were some constraints on it. not sure how
they got there, but i've altered them, problem solved! :)
Thank you very much!|||One method to generate the table DDL is to navigate to the table using the
Query Analyzer Object Browser and then right-click on the table and select
script to clipboard as create. You can then paste into your post.
Hope this helps.
Dan Guzman
SQL Server MVP
"Assimalyst" <c_oxtoby@.hotmail.com> wrote in message
news:1122819272.357946.52430@.f14g2000cwb.googlegroups.com...
> Dan,
> Excuse my ignorance, but where do i find the table DDL?
> As regards the input value, i have tried a few "Unknown", "I", "II",
> "Ia", to name a few.
> By the deleting row comment, i was just meaning that it didn't work, so
> i completely removed that particular row from the table, then recreated
> it. I thought perhaps i might have put some sort of input mask
> constraint or something on it that i had forgotten about. By doing this
> it would remove that possibility.
> Thanks.
>|||You might want to find out WHY someone put constraints on the data.
Having a default of a blank on what should be a code is a sign that
someone did not do much design work. Of course we know that when we
saw the "tbl;" and "pnt-" prefixes that violation basic rules for
naming data elements. The "tbl-" prefix is silly in a language with
one data structure; the "pnt-" tells us the location f one occurence of
a data element, not what it is. When you wrote "pntSex" did you mean
"sex_code", "sex_frequency", "sex_preference", "sex_total"? Again,
name it for what it is, never for where it is.
And the use of NVARCHAR(n) in codes is usually a sign the nobody
designed the encodings; we prefer CHAR(n) so can add constraints and do
validation. Do you really use a lot of Chinese characters? If you
allow it, it will come.