Create Constraint Error

May 16, 2007

Hi.

I have a procedure with this in it (there are no other references to
asset_number_bak_tmp_pk in the procedure and it calls nothing else written by
me, just system calls or normal dml).

create table #asset_bak(
asset_number varchar(60) not null,
asset_desc varchar(100) null,
location varchar(40) null,
constraint asset_number_bak_tmp_pk primary key clustered (asset_number))

When I run the procedure, I get this message:

(1 row(s) affected)
Msg 2714, Level 16, State 4, Procedure updatenavharrierdb, Line 19
There is already an object named 'asset_number_bak_tmp_pk' in the database.
Msg 1750, Level 16, State 0, Procedure updatenavharrierdb, Line 19
Could not create constraint. See previous errors.

How can I find where else the system thinks this constraint exists?


I tried this but it only finds it in one place (one row in the result set),
i.e. my procedure:

select sysobjects.name, syscomments.text
from sysobjects, syscomments
where sysobjects.id = syscomments.id and
((lower(sysobjects.name) like '%asset_number_bak_tmp_pk%') or
(lower(syscomments.text) like '%asset_number_bak_tmp_pk%'))

Is this somehow a case where I need to do something dynamically, or purge some
information? I thought temp tables and their crony constraints disappeared
after the procedure exited.

thanks
Jeff Kish

View 2 Replies


ADVERTISEMENT

Unique Constraint Error When There Is No Constraint

May 13, 2008

We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error:
A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle
But the only PK on this table is RegTitleID.

The table structure is:
[RegTitleID] [int] IDENTITY(1,1) NOT NULL,
[RegTitleNumber] [int] NOT NULL,
[RegTitleDescription] [varchar](200) NOT NULL,
[FacilityTypeID] [int] NOT NULL,
[Active] [bit] NOT NULL,

The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number.
Has anyone else experienced this?

View 3 Replies View Related

How To Create A Constraint Like This:

Nov 8, 2006

Hi,

Suppose the following table definition in Sql Server 2005,

create table CompanySymbol
   CompanyId int,
   SymbolId int,
   IsPrimarySymbol bit

Primary Key (CompanyId, SymbolId)

How can I create a constraint which wil ensure that IsPrimarySymbol will be set to 1(true) only once per CompanyId while allowing it to be set to 0 an unlimited amount of time per CompanyId. i.e.:

CompanyId   SymbolId   IsPrimarySymbol
-----------------------------------------------------------
                  1                 1                                1   (IsPrimarySymbol to 1 for CompanyId 1)
                  1                 2                                0
                  1                 3                                0
                  2                 1                                1   (IsPrimarySymbol to 1 for CompanyId 2)
                  2                 2                                0
                  3                 1                                0
                  4                 1                                1   (IsPrimarySymbol to 1 for CompanyId 4)
                  4                 2                                0
                  4                 3                                0

 

Thanks

View 3 Replies View Related

Can't Create Unique Constraint

Nov 3, 2000

I am attempting to create a unique constraint on an nvarchar field named theology (it is not the primary key field)
that allows nulls. The table contains multiple rows with the value of null for
field theology. The documentation says one can create a unique constraint on a
field with all unique value except for null. Here is the error message:

'testtable1' table
- Unable to create index 'IX_testtable1'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX
terminated because a duplicate key was found. Most significant primary key
is ''.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See
previous errors.

Any ideas? I am creating a unique constraint and not a unique index. Is there
some other database option to set to allow this?

.

View 2 Replies View Related

Create CHECK Constraint

Mar 23, 2014

I need to create a check constraint for an email column/field, where the field must contain an "@" symbol. Does sql (oracle or SQL in general) let you do this.

1. Tried myself:
ALTER TABLE Q_Customer
ADD CONSTRAINT Q_chk_Cus_email CHECK (Cus_email LIKE '%@%');

...but
ORA-02293: cannot validate (C3267304.Q_CHK_CUS_EMAIL) - check constraint violated

2. Tried from a forum: alter table Q_CUSTOMER
add constraint Q_Chk_cus_email check (Cus_email like '%_@__%.__%')

...but
ORA-02293: cannot validate (C3267304.Q_CHK_CUS_EMAIL) - check constraint violated

View 6 Replies View Related

Create A Foreign Key To Unique Constraint?

Jul 3, 2015

why it is not possible to create a Foreign key to a Unique constraint?

Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.

He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;

The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.

Ok - these two columns ar no PK but the hold an Unique constraint......

View 2 Replies View Related

Create Constraint To Add Only Alphabet In Column

Aug 21, 2006

I need to create constraint in column to add only alphabet .

like "adc" ,"sdfsd" and not "1234adfd".plz reply soon.

View 4 Replies View Related

SQL 2012 :: How To Create Check Constraint On A View

Mar 12, 2015

I want to create a check constraint on a table but the constraint values depend upon another table column as well, now one possible way is to create a function to check the column value. But I don’t want to use the function.

Can I do this in a view if so then how can I achieve this.

View 5 Replies View Related

Replication :: Not Create New Constraint Range For Subscriber

Jul 14, 2015

I have a merge replication in place.  I increased the identity_range to 100000 for a table.  I have done this both ways, via the properties of the publication on SSMS, and via TSQL.  I have call sp_adjustpublisheridentityrange.  Then I recreated the sanpshot.  EXEC sp_adjustpublisheridentityrange @table_name = N'Label', @table_owner = N'caseup';but after synchronization, the range defined in the table's constraint has not changed and now all of the identity values are used up.  All inserts are failing.What needs to be done to force the publisher to recreate the identity range on a subscriber(s).

View 3 Replies View Related

How To Create A Unique Constraint On Composite Columns

May 5, 2008

I am trying to create a Unique Constraint on a SQL Server 2005 table where the uniqueness is based on 2 columns.

Could anybody provided some help on how I could enforce this on an existing table (link, or example) I have been looking around without luck.

Thanks in advance

John.

View 4 Replies View Related

How To Create A Constraint On A Header And Detail Tables?

Feb 5, 2008



Hello,

I have a header and detail table. I want to create a constraint on the detail table, based on a value it's linked to in the header table. If the bit is checked in header then a unique value is required , if it's not checked then a NULL value is acceptable.

Many thanks in advance.

View 3 Replies View Related

Unable To Create Unique Constraint On A NULL Column

Apr 5, 2004

Hi all,

I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.

CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.

Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.

Any help would be appreciated.
Thanks,
Amir

View 8 Replies View Related

SQL 2012 :: Create A Check Constraint Where Another Table Column Value Is Also Involved

Mar 12, 2015

Table ATable B
AIDBID
Col 1BCol1
Col 2
Col3

I want to put a check constraint on A.col3 that depends on the values of A.Col1, A.Col2 and B.BCol1

How can I achieve this without using function.

View 8 Replies View Related

Error Not Flowing Down Error Precedent Constraint Path

Sep 5, 2007

I have a dataflow task which keeps erroring, at which point the flow should go down the red path to my error handling SQL Task.

But it is failing to do so.

anyone know why?

View 5 Replies View Related

Strange Constraint Error

Feb 2, 2007

I have a table called subscribers with the following fields
CustID  (identity)email  (text) fname  (text)lname  (text) - may be emptysubDate  (smalldate)Confirmed (boolean) - initially will be emptyConDate  (smalldate) - initially will be empty
I have a strongly typed dataset that contains only the one table. It has the method GetDataByIsSubscribed
SELECT     emailFROM         SubscribersWHERE     (email = @email)
And I have a function which calls it
Public Function ValSubscribe(ByVal vEmailAddr As String, ByVal vFName As String, ByVal vLName As String) As String    Dim SubscriptionAdapter As New EmailSubscriptionsTableAdapters.SubscribersTableAdapter    Dim dF As Data.DataTable = SubscriptionAdapter.GetDataByIsSubscribed(vEmailAddr)    If dF.Rows.Count > 0 Then        ValSubscribe = "This email address is already subscribed!"    Else        SubscriptionAdapter.InsertSubscriber(vEmailAddr, vFName, vLName, Now)        ValSubscribe = "success"    End IfEnd Function
This is an existing table that contains duplicate email addresses, but I don't want to add any more dups. 
You can see I have another method to add records, which works fine, though I've only tried to add email addresses that I know aren't in the table.  If I run the above code which first checks to see if an email address is in the table, and I use an email address that is, I get the following error
System.Data.ConstraintException was unhandled by user code  Message="Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."  Source="System.Data"  StackTrace:       at System.Data.DataTable.EnableConstraints()       at System.Data.DataTable.set_EnforceConstraints(Boolean value)       at System.Data.DataTable.EndLoadData()       at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)       at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)       at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)       at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)       at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)       at EmailSubscriptionsTableAdapters.SubscribersTableAdapter.GetDataByIsSubscribed(String email) in C:WINNTMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileswap48b4654224232ecApp_Code.ihd33v1_.4.vb:line 872       at Validate.ValSubscribe(String vEmailAddr, String vFName, String vLName) in C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesWAPApp_CodeValidate.vb:line 90       at Validate.vCheckPage(String vEmailAddr, Int32 vLevel, String vFName, String vLName) in C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesWAPApp_CodeValidate.vb:line 28       at advertise.Page_Load(Object sender, EventArgs e) in C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesWAPform-advertise.aspx.vb:line 32       at System.Web.UI.Control.OnLoad(EventArgs e)       at System.Web.UI.Control.LoadRecursive()       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
I think I understand what this error is telling my, but I don't get why.  All the fields except CustID allow nulls.  There are no foreign-keys, and I don't see where any fild by custID requires unique entries.  If I run the query in the designer, I don't have a problem. 
What am I missing?
Diane

View 11 Replies View Related

Error When Using The SQL Distinct Constraint

Aug 16, 2007

hi guys,
i have a query that returns a column data with a Distinct constraint. it works fine when i preview data in the table. but when i call it from my application i keep following error
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
 
 

View 1 Replies View Related

Primary Key Constraint Error

Oct 30, 2001

Hello,

I am getting the following message when I am trying to insert into my table. Request is the primary keys and the Bus_Req_Id and Test_Case_Id are both foreign keys. The data that is being inserted into these fields are not unique.

This is the exact error message:

Violation of PRIMARY KEY constraint 'PK_REQUEST_BUS_REQ_TST'. Cannot insert duplicate key in object 'REQUEST_BUS_REQ_TST'.
The statement has been terminated.

I am trying to insert 700 - 900 rows based on certain criteria. If I delete the keys the inserts will work.

Any help would be most greatful.
Thank you in advance

Anne

View 1 Replies View Related

Foreign Key Constraint Error

Apr 9, 2008

Hi all, I'm new here so I hope this is the right place to post!!

I'm having a really big problem trying to make a table. I'm not new to the coding, but I'm no expert either.... as far as I can see I haven't done anything wrong...

I have many tables, but the first two are JobDetails and Promotions... in JobDetails, there is a primary key Job which needs to link to Promotions as a foreign key.... here is my code and errors, I'd really appreciate some help...


create table JOBDETAIL
(Job varchar2(15) primary key,
Dept varchar2(10) not null,
Salary number(7) not null);


create table PROMOTION
(PCID number(6) primary key,
PDate date,
Constraint fkey_p1 foreign key (Job) references JOBDETAIL(Job));

ERROR at line 4:
ORA-00904: "Job": invalid identifier



... any help would be appreciated, this is quite important... thank you!!!

View 1 Replies View Related

Error FOREIGN KEY Constraint

Dec 3, 2007



Hi,
i have two tables, one is department-Table(Master table) and other is employee-table(Child table).
DNO is a data field which is common in both the table. "DNO" is primary key in department-table and "DNO" is foreign key in child table.

I am trying to insert rows in the table i found the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Employee_Department". The conflict occurred in database "Project", table "dbo.Department", column 'Dno'.

What's the solution to replace this error

Thanks.

View 4 Replies View Related

Violation Of PRIMARY KEY Constraint Error

Apr 18, 2007

We are using web application, our server log this type error. some can help me out how to slove this type of error


4/16/2007 2:44:26 PM DECIMonitoring.DocTracking.Db:insertState [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Violation of PRIMARY KEY constraint 'idx_edcstate_p_cl_01'. Cannot insert duplicate key in object 'tbl_edc_state'. tech.deci.com:5555

4/16/2007 2:44:26 PM DECIMonitoring.DocTracking.Db:insertState [ADA.1.316] Cannot execute the SQL statement "INSERT INTO db_webmethods_support_edc.dbo.tbl_edc_state(stat_s tate_id, state_item_id, state_start_ts) VALUES (?, ?, ?)". "

(23000/2627) [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Violation of PRIMARY KEY constraint 'idx_edcstate_p_cl_01'. Cannot insert duplicate key in object 'tbl_edc_state'.

(HY000/3621) [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]The statement has been terminated."

[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Violation of PRIMARY KEY constraint 'idx_edcstate_p_cl_01'. Cannot insert duplicate key in object 'tbl_edc_state'. tech.deci.com:5555

4/16/2007 2:44:26 PM DECIMonitoring.DocTracking.Db:insertState [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Violation of PRIMARY KEY constraint 'idx_edcstate_p_cl_01'. Cannot insert duplicate key in object 'tbl_edc_state'. tech.deci.com:5555

View 3 Replies View Related

Constraint Error In Stored Proc

Dec 18, 2007

This query keeps giving me a Primary Key violation error and I am not sure what it is getting at. maybe you can help?

truncate table tbl_rtv_cosmetic_dif
go
insert tbl_RTV_COSMETIC_DIF
select d.fisc_yr
,d.fisc_pd
,d.fisc_wk
,d.fisc_dy
,substring(a.long_sku,1,4) as DeptNo
,substring(a.long_sku,7,3) as VendorNo
,substring(a.long_sku,10,5) as MarkStyleNo
,a.Loc as LocNo
,convert(int,a.RsnCd) as ReasonCode
,min(a.EAN_Nbr) as UPCEANNo
,coalesce(i.own_rtl,0) as OwnedCur
,sum(convert(int,a.units)) as UPCUnits


from november a
inner join generalinfo..tbl_fisc_date d
on a.date = d.cal_date

left outer join itemdata..tbl_item_import_history i
on substring(a.long_sku,1,4) = i.dept
and substring(a.long_sku,7,3) = i.vendor
and substring(a.long_sku,10,5) = i.mrk_style
and d.fisc_yr = i.amc_yr
and d.fisc_pd = i.amc_pd
and d.fisc_wk = i.amc_wk

group by fisc_yr,fisc_pd,fisc_wk,fisc_dy
,substring(long_sku,1,4),substring(long_sku,7,3),substring(long_sku,10,5)
,loc,rsncd,own_rtl

View 3 Replies View Related

Error Validating Any Check Constraint

Aug 19, 2006

Hey Guys,



I just this day started using SQL sever 2005. I created a database and
then created a table. Then I started adding some fields. I wanted to
add a check constraint to one of the fields called state but I keep
getting the same error. I right click on the field while editing the
table and select check constraint. I then click add on the check
constraint dialogue and in the expression caption I input the
following:



<code>

@State In('CA', 'AZ', 'UT', 'CO')

</code>


I am using a book and have straight copied the above example from
the book. However when I input the check constraint I get the following
error;



"Error validating constraint 'ck_myfirstdatabase'


I have tried this with other fields and other types of check
constraints and I still get the same error. I have tried to delete the
database and recreate it. I have tried everything I can think of and I
cannot seem to get check constraints to work. I have no idea why I keep
getting this message. I have checked the
examples a
thousand times, the syntax is definately correct. This is getting
extremely annoying as I cannot continue unless I do this. I'm all out
of ideas. Can anyone please tell me
why it could not be working? Any ideas would be greatly appreciated.

View 1 Replies View Related

DB Design :: Check Constraint Error

May 29, 2015

I ran the below INSERT COMMAND and it resulted into the following error message.You can also see the table structure below.Those are the only two tables that reference the Artist table.

SET IDENTITY_INSERT dbo.ARTIST ON
INSERT INTO ARTIST
(ArtistID,LastName,FirstName,Nationality,DateOfBirth,DateDeceased)
VALUES(1, 'Miro', 'Joan', 'Spanish', 1893, 1983);

[code]....

View 5 Replies View Related

Named Constraint Is Not Supported For This Type Of Constraint (not Null)

May 13, 2008

Hi, all.

I am trying to create table with following SQL script:





Code Snippet

create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) constraint NN_Prj_Name not null,
Creator nvarchar (255),
CreateDate datetime
);

When I execute this script I get following error message:

Error source: SQL Server Compact ADO.NET Data Provider
Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ]

I looked in the SQL Server Books Online and saw following:

CREATE TABLE (SQL Server Compact)
...
< column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ]

As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint.





Code Snippet

create table Projects(
ID smallint identity (0, 1) constraint PK_Projects primary key,
Name nvarchar (255) not null,
Creator nvarchar (255),
CreateDate datetime
);
This script executes correctly, however I want named constraints and this does not satisfy me.

View 1 Replies View Related

T-SQL (SS2K8) :: Constraint Error Message Handling

Jan 19, 2015

I have a constraint on a 3rd party app that I can't change the app but can add a constraint at the table level. The constraing works but the error message back is ugly. Is there a way to do some kind of raiserror on a constraint that could send better info back to the user, I have done this in triggers, is the an approach for constraints?

View 3 Replies View Related

How Can I Get The Row Number(error Row) While Constraint Failure At The OLE DB Destination?

Aug 7, 2006

my project is insert data to OLE DB destination from csv file.

my question is

how can I get the row number(error row) while constraint failure at the OLE DB destination?

thanks.

View 6 Replies View Related

Check Constraint With Custom Error Message

Mar 14, 2008

i need the t-sql statement for a check constraint that has a custom error message that shows the proper fromat for column entry.... i know its confusing...

example... if im using the authors table in the pubs database and the numbers in the au_id column reads xxx-xxxxxx where x represents numbers... what must i do so that the error message reads the proper format... ie. use proper format XXX-XXXXXX, is what i might want the error message to read.

thanks in advance

View 7 Replies View Related

Replication Error Message On Primary Key Constraint.

Oct 22, 2007

Hi, everyone,

I have problems to setup peer-to-peer replication. After I setup, I have an error message as below.

Violation of PRIMARY KEY constraint 'XPKSYS_SRTY_ADT'. Cannot insert duplicate key in object 'dbo.SYS_SRTY_ADT'. (Source: MSSQLServer, Error number: 2627)


Event ID 14151 in vent viewer.

Does anyone have a solution to resolve the problem?

Thanks a lot.

View 3 Replies View Related

SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key

Dec 23, 2007

The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name].
The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions.

1 ALTER PROCEDURE dbo.sp_CreateUser
2
3 @UserID uniqueidentifier,
4 @UserName nvarchar(128),
5 @Email nvarchar(50),
6 @FirstName nvarchar(25),
7 @LastName nvarchar(50),
8 @Teacher nvarchar(25),
9 @GradYr int
10
11 AS
12 SET NOCOUNT ON;
13 --DECLARE @UserID uniqueidentifier
14 --SELECT @UserID = NULL
15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId
16 INSERT INTO [table]
17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr 

View 2 Replies View Related

SP Causes The Error Violation Of UNIQUE KEY Constraint Cannot Insert Duplicate Key

Mar 14, 2008



I have a table with 0 records. When I try to insert records using a SP, it gives the following error.
Violation of UNIQUE KEY Constraint 'constraint name'. Cannot insert duplicate key in object 'Objectname'.
How do I resolve this.
Thanks.

View 1 Replies View Related

Integrity Constraint Error On SQL Server Column With No Constraints

Aug 6, 2007

I have created a simple package to load an Excel Spreadsheet into a SQL Server table. There is a one to one relationship between the columns in the .xls file and the columns in the DB record. I am getting integrity constraint errors when I try to load all numeric data from the spreadsheet (defined as Category General in excel, not defined as numeric but consisting of all numeric characters) into a column defined as (nvarchar(20), not null) in SQL Server Management Studio. There are no constraints on the column.

I have been able to temporarily bypass the offending rows, but I do need to load them into SQL Server. The problem column has a mixture of data, two examples would be: N255, 168050. It's the 168050 value that's causing the Task to bomb. How can I get this loaded into my table ?

I am running the package from within MS Visual Studio 2005 Version 8, Excel is version 2003 (11.8120.8122) SP2

Thanks,
Chris


View 15 Replies View Related

Getting An Error The DELETE Statement Conflicted With The REFERENCE Constraint FK_Detail_Header

May 14, 2008

Hello All, i have 2 files in excel which i am uploading them to a folder which is located in the root directory. then i am importing these two files into a sqldatabase. i needed some help in importing them and i got it from my previous post at http://forums.asp.net/t/1261155.aspx but now i have a problem. the import works perfectly first time but when i do it the second time i am getting an error on of the file (header). to explain clearly, what i am doing is , every time i upload a new file , i am deleting the data from the tables, so the new data can be inserted (  basically trying to achieve overwirting the existing data). now this technique works fine with the Detail table but not with the Header table. i think the reason is the header table has a primary key on OrderID and a relationship does exists between the Header and Detail. now how would i overcome this error. can some one please guide me. I really appreciate it.here is my code:  // connection for header file
protected OleDbCommand headExcelConnection() { // Connect to the Excel Spreadsheet
string headConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/headerorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string
OleDbConnection headXConn = new OleDbConnection(headConnStr); headXConn.Open(); // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$]

OleDbCommand headCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", headXConn); return headCommand; } // importing header information


protected void BtnImpHeader_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = headExcelConnection();

// Create a DataSet
DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data
objDataAdapter.Fill(objDataSet);

// deleting the exisitng table before copy
SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Header"; ---- showing error over on second time SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering the newer header information

SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Header values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13,@param14,@param15,@param16,@param17,@param18,@param19,@param20,@param21,@param22,@param23,@param24,@param25,@param26,@param27,@param28,@param29,@param30,@param31,@param32)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr[6].ToString()); cmd.Parameters.AddWithValue("@param8", dr[7].ToString()); cmd.Parameters.AddWithValue("@param9", dr[8].ToString()); cmd.Parameters.AddWithValue("@param10", dr[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr[12].ToString()); cmd.Parameters.AddWithValue("@param14", dr[13].ToString()); cmd.Parameters.AddWithValue("@param15", dr[14].ToString()); cmd.Parameters.AddWithValue("@param16", dr[15].ToString()); cmd.Parameters.AddWithValue("@param17", dr[16].ToString()); cmd.Parameters.AddWithValue("@param18", dr[17].ToString()); cmd.Parameters.AddWithValue("@param19", dr[18].ToString()); cmd.Parameters.AddWithValue("@param20", dr[19].ToString()); cmd.Parameters.AddWithValue("@param21", dr[20].ToString()); cmd.Parameters.AddWithValue("@param22", dr[21].ToString()); cmd.Parameters.AddWithValue("@param23", dr[22].ToString()); cmd.Parameters.AddWithValue("@param24", dr[23].ToString()); cmd.Parameters.AddWithValue("@param25", dr[24].ToString()); cmd.Parameters.AddWithValue("@param26", dr[25].ToString()); cmd.Parameters.AddWithValue("@param27", Convert.ToDecimal(dr[26].ToString())); cmd.Parameters.AddWithValue("@param28", Convert.ToDecimal(dr[27].ToString())); cmd.Parameters.AddWithValue("@param29", Convert.ToDecimal(dr[28].ToString())); cmd.Parameters.AddWithValue("@param30", Convert.ToDecimal(dr[29].ToString())); cmd.Parameters.AddWithValue("@param31", Convert.ToDecimal(dr[30].ToString())); cmd.Parameters.AddWithValue("@param32", dr[31].ToString()); cmd.ExecuteNonQuery(); // new SqlCommand (stmt, mysqlcon).ExecuteNonQuery();
LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); }// connection for detail file

protected OleDbCommand detExcelConnection() { // Connect to the Excel Spreadsheet
string detConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/imports/detailorder.xls") + ";" + "Extended Properties=Excel 8.0;"; // create your excel connection object using the connection string
OleDbConnection detXConn = new OleDbConnection(detConnStr); detXConn.Open(); // create your excel connection object using the connection string // use a SQL Select command to retrieve the data from the Excel Spreadsheet // the "table name" is the name of the worksheet within the spreadsheet // in this case, the worksheet name is "Sheet1" and is expressed as: [Sheet1$]
OleDbCommand detCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", detXConn); return detCommand; }// importing detail information

protected void ButtonImport_Click(object sender, EventArgs e) { PanelUpload.Visible = false; PanelView.Visible = false; PanelImport.Visible = true; LabelImport.Text = ""; // reset to blank // Create a new Adapter
OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(); // retrieve the Select command for the Spreadsheet
objDataAdapter.SelectCommand = detExcelConnection();

// Create a DataSet
DataSet objDataSet = new DataSet(); // Populate the DataSet with the spreadsheet worksheet data
objDataAdapter.Fill(objDataSet);

// deleting the exisitng table before copy
SqlConnection mycon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); SqlCommand SqlCmd = null; mycon.Open(); SqlCmd = mycon.CreateCommand(); SqlCmd.CommandText = "DELETE FROM Detail"; SqlCmd.ExecuteNonQuery(); mycon.Close(); // entering newer detail information
SqlConnection mysqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["ImportexcelConnectionString"].ConnectionString); mysqlcon.Open(); foreach (DataRow dr1 in objDataSet.Tables[0].Rows) { String sqlinsert = "insert into Detail values(@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11,@param12,@param13)"; SqlCommand cmd = new SqlCommand(sqlinsert, mysqlcon); cmd.Parameters.AddWithValue("@param1", dr1[0].ToString()); cmd.Parameters.AddWithValue("@param2", dr1[1].ToString()); cmd.Parameters.AddWithValue("@param3", dr1[2].ToString()); cmd.Parameters.AddWithValue("@param4", dr1[3].ToString()); cmd.Parameters.AddWithValue("@param5", dr1[4].ToString()); cmd.Parameters.AddWithValue("@param6", dr1[5].ToString()); cmd.Parameters.AddWithValue("@param7", dr1[6].ToString()); cmd.Parameters.AddWithValue("@param8", Convert.ToDecimal(dr1[7].ToString())); cmd.Parameters.AddWithValue("@param9", Convert.ToDecimal(dr1[8].ToString())); cmd.Parameters.AddWithValue("@param10", dr1[9].ToString()); cmd.Parameters.AddWithValue("@param11", dr1[10].ToString()); cmd.Parameters.AddWithValue("@param12", dr1[11].ToString()); cmd.Parameters.AddWithValue("@param13", dr1[12].ToString()); cmd.ExecuteNonQuery(); LabelImport.Text = "Rows Inserted"; } mysqlcon.Close(); } the error is as follows:
Server Error in '/WebSite6' Application.


The DELETE statement conflicted with the REFERENCE constraint
"FK_Detail_Header". The conflict occurred in database "C:DOCUMENTS AND
SETTINGSMEMY DOCUMENTSVISUAL STUDIO
2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column
'OrderID'.The statement has been terminated. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: The DELETE statement conflicted with the
REFERENCE constraint "FK_Detail_Header". The conflict occurred in database
"C:DOCUMENTS AND SETTINGSMEMY DOCUMENTSVISUAL STUDIO
2005WEBSITESWEBSITE6APP_DATADATABASE.MDF", table "dbo.Detail", column
'OrderID'.The statement has been terminated.Source Error:




Line 176: SqlCmd = mycon.CreateCommand();Line 177: SqlCmd.CommandText = "DELETE FROM Header";Line 178: SqlCmd.ExecuteNonQuery();Line 179: mycon.Close();Line 180:  again i really appreciate.Thanks 

View 1 Replies View Related

How To Trap DELETE Statement Conflicted With COLUMN REFERENCE Constraint Error

Oct 26, 2004

Hi,

On my aspx Web page, I want to delete a member from database table 'tblMember', but if this MemberID is used as FK in another table, I want to display a user friendlier message like "You cannot delete this member, ....." I am using Try, Catch blocks in my Web Page.

Currently it display this message:
"DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_..._....' The conflict occurred in database '...', table 'tblMembers', column 'MemberID'. The statement has been terminated. "

So how should I precisely trap this error? Does anybody know what Exception is it? or what error number in SQL server?


Thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved