there have a exception when I trying to run a insert query
the exception is occur on cmd.ExecuteReder(); and shows
Insert Query conflict with Foreign key..
why? and how to resolve it?
thank you
Hi;
I have 2 tables Users and DVDTestResults these tables have a relation over UserID
Users.UserID
DVDTestResults.UserName
both char
But when I try to insert in DVDTestResults I am having an error:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_DVDTestResults_Users'. The conflict occurred in database 'Test', table 'Users', column 'UserID'. The statement has been terminated.
can you help me why?
thank you....
Hi,
I am fairly new to SQL server Express and I have quite a simple database set up and I am trying to test it out with data. I have €œPain Reading€? and €œWorsen Factors€? tables with a 1: M relationship. The primary key in the Pain reading table is ReadingID which is being set using getdate() for the default value. I am getting the following error when attempting to add a new row to the €œWorsen Factors€? table:
No row was updated
The data in row 1 was not committed.
Error source:.Net SqlClient Data Provider
Error Message: The insert statement conflicted with the FOREIGN KEY constraint "FK_WorsenFactor_PainReading1". The confilct occurred in database "paindata", table "dbo.PainReading", column 'ReadingID'.
The statement has been terminated.
Any help or suggestions would be very helpful.
Thanks
I'm trying to create relational database with some triggers in SQL Server 7.0, but it doesn't work as expected. Let's say that I have 'Office' database with two tables, 'Users' and 'UserRights' (userRights table should have much more rights, but that's not relevant for this problem):
CREATE TABLE [Users] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FS_Password] [nvarchar] (32) NOT NULL ,
CONSTRAINT [PK_Users] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [UserRights] (
[FS_Username] [nvarchar] (8) NOT NULL ,
[FI_UserType] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_UserType] DEFAULT (1),
[FI_AllowLogin] [int] NOT NULL CONSTRAINT [DF_UserRights_FI_AllowLogin] DEFAULT (1),
CONSTRAINT [PK_UserRights] PRIMARY KEY NONCLUSTERED
(
[FS_Username]
) ON [PRIMARY] ,
CONSTRAINT [FK_UserRights_Users] FOREIGN KEY
(
[FS_Username]
) REFERENCES [Users] (
[FS_Username]
)
) ON [PRIMARY]
GO
Foreign Key CONSTRAIN above is created by adding both tables to the diagram and defining relationship between these two tables FS_Username field, where 'Enable relationship for INSERT and UPDATE' option is turned ON. You can easily see this if you create diagram youself and insert these two tables in it.
Next to this, I created two triggers that should handle inserting/deleting rows in UserRights table as consequence of inserting/deleting rows in Users table:
CREATE TRIGGER InsertUserRights ON Users
FOR INSERT
AS
BEGIN
INSERT INTO UserRights (FS_Username) (SELECT FS_Username FROM Inserted)
END
CREATE TRIGGER DeleteUserRights ON Users
FOR DELETE
AS
BEGIN
DELETE UserRights WHERE FS_Username IN
(SELECT FS_Username FROM Users)
END
Now, when (manually) I insert row in Users table, UserRights table gets updated accordingly. HOWEVER, when I try to delete one or more entries from Users table, I get error report. For example, if you try to execute following two commands:
Insert Into Users (FS_Username, FS_Password) VALUES ('John', 's')
Delete from Users
... first command will succede, but second one will fail with message:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_UserRights_Users'. The conflict occurred in database 'Office', table 'UserRights', column 'FS_Username'.
The statement has been terminated.
Does anyone know how to resolve this problem without loosing constrains and triggers ? (If I turn off 'Enable relationship for INSERT and UPDATE' option for relationship, things will work fine, but than I can make inconsistent data in UserRights table).
tnx a lot,
Dejan
Hi. i am updating a field on a table i have called tblNavproductId. when doing the insert, i get the error message that you know, it is is violation of a foreign key constraint on NavId. i see the foreign key, so i know it is there and i understand that. i read the books online and here is the article;
http://msdn2.microsoft.com/en-us/library/ms186247.aspx
does anyone have a good example of this type of query? i am putting 990k+ records in at once, so would this be a bulk insert? Thanks.
Dear all,
i've a small doubt....
i've a table like this....
create table t(sno nchar(2))
go
insert into t select 'Alt+9'
(here i'm taking the value which is the output of press alt key with 9 and release).
how can iget the exact result for this?
i'm getting the question mark there when i'm selecting the data.
for the rest of the characters, i'm getting the exact and expected result
Vinod
Even you learn 1%, Learn it with 100% confidence.
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
I get this message when doing an insert into :
Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ElementLocalCharacterised' with unique index 'ElementLocalCharacterised_uq'.
The statement has been terminated.
Now my question is : does this error mean that all inserts are done, despite those where the unique key produced a conflict? Or does it mean that none of the inserts are done (all are rejected because at least one gave a conflict) ?
If the second is the case I would like to know if it is possible to execute the insert into in a way that all inserts are done despite those that raise a conflict?
Thanks a lot for any suggestion !
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
Hi,
I want to upsize my Access backend to SQL Server, and there is a simple query which works fine in Access but not in SQL Server:
There are two tables: Person and ArtificialPerson (a more specific Person). They are linked through the Person_ID attribute (Primary key in Person and Foreign key in ArtificialPerson).
I have a form in Access frontend having as recordsource a Select statement based on the join between these two tables. (Although this relationship is defined as 1-n in Access, I use it just as 1-1 in my form, so after a adding a new entry in the form I have a new entry in both tables)
The problem is the following:
Using SQL Server as backend, when I try to insert a new entry in the form (or in query used as recordsource for the form) the Person_ID isn't automatically inserted as foreign key.
My question is why using Access tables the Foreign key is automatically inserted and using SQL Server it does not work ?
Thanks in advance
INSERT INTO X ...... ( A, B, C )INSERT INTO Y ...... ( J, K, L )If Y has a foreign key M which is the primary key D of X,is there an easy and/or efficient way to have SQL Serverassign D, and tell us what it is so we can add M to thelist for Y ?I know I can select D where A, B, C but I wondered aboutother tricks.--Wes GroleauAlive and Wellhttp://freepages.religions.rootsweb.com/~wgroleau/
View 1 Replies View RelatedHi all,
I have a query that I need to run where I join two tables that both
reside on different servers. I use an INNER JOIN statement to attempt
to join these tables, but for some reason I am getting the following
error message...
"
Cannot resolve collation conflict for equal to operation."
The query is as follows...
SELECT TABLE_NAME, LEFT(TABLE_NAME, CHARINDEX('_', TABLE_NAME + '_') - 1) AS Abbreviation
FROM mrcsmis.INFORMATION_SCHEMA.TABLES
INNER JOIN DEVELCAD1.Portal.dbo.dnl_db_names_log AS imp ON (imp.dnl_table_name = TABLE_NAME)
WHERE (TABLE_TYPE = 'BASE TABLE')
Thanks
Tryst
I am getting this error occasionally on my multi-user web app with the data stored in SQL Server. The key is an identity field incremented by 1 - like Access autonumber. It seems that when two people are writing to the table at the same time they try and use the same key. I didn't think this could happen in sql server ?
It is a one to many table situation and the error occurs in the one table
What is the solution please.
INSERT statement conflicted with the FOREIGN KEY constraint
I have a Brokers table and Trans (transactions) table. When I insert new transactions, I run a stored procedure or trigger to update the Brokers table since a transaction may have a new Broker. Is there an simpler way to do this than my query below?
INSERT INTO Brokers (Brokers.broker_id)
SELECT X.broker_id
FROM (SELECT DISTINCT broker_id FROM Trans) As X
WHERE NOT EXISTS (SELECT Brokers.broker_id FROM Brokers WHERE Brokers.broker_id = X.broker_id)
Hello Friends
How are you?? Friends i am getting problem in SQL Server 2005. I am deployng web application on production server as well as Databse also. In production server i inserted new field in all tables which is rowguid and its type is uniqueidentifier. The default binding for this field is newsequentialid(). In some pages it works ok but in some places it generates error like which is describe below:
Error:
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TransactionDetails_TransactionMain". The conflict occurred in database "dbname", table "dbo.TransactionMain", column 'TransactionMainID'. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. The statement has been terminated. at MyGeneration.dOOdads.BusinessEntity.LoadFromSql(String sp, ListDictionary Parameters, CommandType commandType) at MyGeneration.dOOdads.BusinessEntity.LoadFromSql(String sp, ListDictionary Parameters) at LCO.pname.DAL.BLL.AccountInfo.funName(Parameters)
In above mentioned error, foreign key is availabe in child table. Can anybody help me to solve this problem. Its urgent so plz reply me as soon as possible. I'll be very thankfull to you. Thanks in Advance.
Regards,
i'm using sql express, management studio express and a visual web developer starter kit.
i have 4 tables: items; categories; categorization; old_items
the old_items table has both the item id and category fields in it, the new db has them separated: items.id; categories.id; categorization.itemId, categorizaton.parentCategoryId, both of which are foreign keys.
i need to get the old_item.id and old_item.category values into the new categorization table but it keeps choking on the foreign keys and i can't get past it. as far as i've been able to figure out so far, i need to turn off the foreign keys to do the insert but i'm not sure and i haven't been able to find any sql query examples that demonstrate it or explain it well enough for my n00b self.
i've read http://msdn.microsoft.com/en-us/library/10cetyt6.aspx, "How to: Disable Foreign Key Constraints with INSERT and UPDATE Statements" but i don't get how that affects it, it seems like one of the other options would actually disable them.
can anyone help? i've been trying all the permutations of queries i can think of and not getting it.
thanks.
I'm trying to optimize a few batch import procedures we use in our processes.
It currently works like this:
1) Cursor loop cycles through all data to be imported from IMPORT table
2) For every record there is an attempted insert to PROD table in a TRY-CATCH check to see whether the record would pass all the primary key and foreign key constrains in PROD table
3) Only those that pass the TRY-CATCH check gets imported into PROD table
4) Every row gets logged into a separate LOG table, either with a comment like "Import OK" or "Error: foreign key violation in field 'my_id'"
The thing is, the procedure runs fine when I'm importing several thousands of records, but when it comes to hundreds of thousands, the speed becomes an issue, as I currently get 20 records per second and slowing...
There is no other code in that procedure, no queries. Just the Cursor cycle and the try-catch check.
PS: I'm using MS SQL Server 2005.
Hi,
I've been working as web dev for quite sometime now but there are still few things that i would like to clarify. Hope you guys can shed lights.
I currently have several tables and all this tables are having some 1 to many relationships. I know that how i insert value into my tables are very inefficient. I do it in this manner..
-> Insert a value in TableA
-> Query the latest ID that i have inserted in Table A then
-> Insert this value in TableB. And so on and so forth.
I believe there is an efficient way to to do this but i'm not sure how? Could anyone shed me a light on this matter?
Your reply would be highly appreciated. TIA.
Hi all,
I have a projects table with 2 foreign key fields that both link to the same employees table because a project has a Package Engineer (PkgEngineerID) and a Contract Administrator (PkgContrAdmin). When I try to insert a record with null values for either or both of these foreign keys I get an error:
The data in row xxx was not commited. The record can't be added or changed. Referential integrity rules require a related record in table 'tblEmployees'. The transaction ended in the trigger. The batch has been aborted.
An insert statement for the above would look something like the following:
INSERT INTO tblPackages (PkgNo, PkgName, PkgEngineerID, PkgContrAdmin, PkgRemark)VALUES (1234, 'My Package', NULL, NULL, 'My Package remark')
And the create table statements are:
USE [PASSQL]GO/****** Object: Table [dbo].[tblPackages] Script Date: 01/15/2008 23:25:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblPackages]( [ID] [int] IDENTITY(1,1) NOT NULL, [PkgNo] [nvarchar](20) NULL, [PkgName] [nvarchar](255) NULL, [PkgEngineerID] [int] NULL, [PkgContrAdmin] [int] NULL, [PkgRemark] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblPackages_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees] FOREIGN KEY([PkgEngineerID])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees]GOALTER TABLE [dbo].[tblPackages] WITH NOCHECK ADD CONSTRAINT [FK_tblPackages_tblEmployees1] FOREIGN KEY([PkgContrAdmin])REFERENCES [dbo].[tblEmployees] ([ID])GOALTER TABLE [dbo].[tblPackages] CHECK CONSTRAINT [FK_tblPackages_tblEmployees1]
And:
USE [PASSQL]GO/****** Object: Table [dbo].[tblEmployees] Script Date: 01/15/2008 23:28:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[tblEmployees]( [ID] [int] IDENTITY(1,1) NOT NULL, [EmpName] [nvarchar](255) NULL, [EmpShort] [nvarchar](255) NULL, [upsize_ts] [timestamp] NULL, CONSTRAINT [aaaaatblEmployees_PK] PRIMARY KEY NONCLUSTERED ( [ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
Any ideas on how to accomplish this would be great!
Thanks in advance!
Hi,
I am having 'INSERT STATEMENT CONFLICTED' error since I try to configure sql server 2005 Full Text Search for my database. Before everything was going fine but after doing some changings I mess up my all DB's. None of my database is working. The error only occur while inserting the records using web form. But I can insert directly from Management Studio interface. here is part of my error:
ERROR:
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Dept_LegalLaw_Dept_LegalMinistries1". The conflict occurred in database "LegalDB", table "dbo.Dept_LegalMinistries", column 'RegID'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
...............AND SO ON
INSERT QUERY:
INSERT INTO Dept_LegalLaw (
Agreement,Amendment,Attachment,Html,ID,IssueNo,LawDate,LawID,LawNo,Mistakes,Name,NameSearch,Pages,RegID,Scanned,SerialNo,Path) VALUES (
'1',NULL,NULL,'1','C0667','2666','5/26/2004 12:00:00 AM','2','22',NULL,N'تعميم بشأن عطلة ذكرى المولد النبوي الشريÙ?',N'مرسوم اميري رقم 29 لسنة 2001 بانشاء لجنة الاسكان والاعمار','1','RAC,',NULL,'5556','RCAB4505.htm')
--RETURN THE NEW IDENTITY VALUE
SELECT SCOPE_IDENTITY() MediaID
I really need urgent help. that how i can reverse this thing back.
Thanks
I was wondering how I do to insert values in two tables that are related each other by a FK?
That is the procedure that illustrate what I meant to be.
ALTER Procedure [dbo].[new_user]
@master nchar(10),
@nick nchar(10),
@fish nchar(10),
@e_mail nchar(30)
As
Begin
INSERT INTO users
(nick, fish, e_mail)
VALUES (@nick,@fish,@e_mail)
INSERT INTO friends
(user_id, e_mail)
VALUES ( Select user_id from users where nick=@master,@e_mail)
End
Thank you very much.
Hi,A conflict error occured when I insert data using Formview.The INSERT statement conflicted with the FOREIGN KEY constraint "FK_personal_personal_category". The conflict occurred in database "E:INETPUBWWWROOTWEBSITEAPP_DATADATABASE.MDF", table "dbo.personal_category", column 'cat_id'.The statement has been terminated. ----------------------------------------------------Personal TablepID (primary)pNamecat_id (foreign key from personal_category, which has a relationship with it's primary key cat_id)pID | pName | cat_id1 | Scott | 112 | John | 122 | Mick | 13-----------------------------------------------------Personal_Category Tablecat_id (primary)cat_Descriptioncat_id | cat_description11 | Dean12 | Student13 | Professor-----------------------------------------------------I'm inserting a new data on Personal Table while choosing a category usinga DropdownList which is binded to another DataSource (LinqDataSource2).... <asp:FormView ID="FormView1" runat="server" DataKeyNames="tut_id" DataSourceID="LinqDataSource1" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DefaultMode="Insert" ForeColor="Black" GridLines="Vertical"> <FooterStyle BackColor="#CCCC99" /> <RowStyle BackColor="#F7F7DE" /> <InsertItemTemplate> <asp:TextBox ID="pName" runat="server" Text='<%# Bind("pName") %>' /> <br /> <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="LinqDataSource2" DataTextField="cat_desc" DataValueField="cat_id" DataSource='<%# Bind("tut_cat") %>' > </asp:DropDownList> </InsertItemTemplate> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> </asp:FormView> <asp:LinqDataSource ID="LinqDataSource1" runat="server" ContextTypeName="PersonalsDataContext" TableName="Personal" EnableInsert = "true"> </asp:LinqDataSource> <asp:LinqDataSource ID="LinqDataSource2" runat="server" ContextTypeName="PersonalCategoriesDataContext" TableName="Personal_categories" EnableInsert="false" > </asp:LinqDataSource>... Hope you could help.cheers,imperialx
View 2 Replies View RelatedHi there,
I have a stored procedure which i pass a number of parameters into. One of these parameters is staffNo (only passed this in because i couldn't execute the query without it). The thing is this field can be Null, but when trying to pass null into it it comes up with an Foreign Key conflict. staffNo is a foreign key within the table i'm inserting the data into.
This is the error i get:
"INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'PropStaffFK'. The conflict occurred in database 'DewMountain', table 'TblStaff', column 'staffNo'. The statement has been terminated. The 'PropertyAdvert' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. "
Does anyone know of away around this? how to pass a null value to the stored procedure without it causing this error.
Thank you
Melanie
An SSIS task imports data from a flat file and inserts the data into a staging table. The staging table holds the data in its raw form. A second process then selects the data from the staging table, looking up the foreign key id's for raw data values, and then inserts the data into the live table.
SQL - Only key columns shown for clarity
-- Staging Table
CREATE TABLE Staging
(Information VARCHAR(10),
MachineName VARCHAR(10),
Status VARCHAR(10))
[code]...
The insert into the live table should look up the id for machine 1, and the id of status success and insert the foreign key values into the live table for the row.There could be 1000's of rows for the output of machine 1 all with different status's - (all pre set in the Status table, i.e success, failure, rerun) and the same for lots of other machines held in the machine table.
What is the best to insert this data all in one go, rather than reading each row of the staging table one by one, looking up the foreign key values depending on the machine and status values, then inserting the data.
I was thinking along the lines of:
INSERT INTO dbo.LiveTable
(Information, MachineID, StatusId)
SELECT Staging.Information, dbo.Machine.MachineId, dbo.Status.StatusId
FROM dbo.Staging
JOIN Machine ON Machine.MachineName = Staging.MachineName
JOIN STATUS ON Status.Status = Staging.Status But I notice the problem with this is, it doubles up the inserts!
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitiions:
CREATE TABLE [dbo].[Locker](
[lockerID] [int] IDENTITY(1,1) NOT NULL,
[schoolID] [int] NOT NULL,
[number] [varchar](10) NOT NULL,
[lockID] [int] NULL
CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[code]....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables?
In a special request run, I need to update locker and lock tables in a sql server 2012 database, I have the following 2 table definitions:
CREATE TABLE [dbo].[Locker](
 [lockerID] [int] IDENTITY(1,1) NOT NULL,
 [schoolID] [int] NOT NULL,
 [number] [varchar](10) NOT NULL,Â
 [lockID] [int] NULLÂ
 CONSTRAINT [PK_Locker] PRIMARY KEY NONCLUSTERED
[Code] ....
The locker table is the main table and the lock table is the secondary table. I need to add 500 new locker numbers that the user has given to me to place in the locker table and is uniquely defined by LockerID. I also need to add 500 new rows to the corresponding lock table that is uniquely defined in the lock table and identified by the lockid.
Since lockid is a key value in the lock table and is uniquely defined in the locker table, I would like to know how to update the lock table with the 500 new rows. I would then like to take value of lockid (from lock table for the 500 new rows that were created) and uniquely place those 500 lockids uniquely into the 500 rows that were created for the lock table.
I have sql that looks like the following so far:
declare @SchoolID int = 999
insert into test.dbo.Locker ( [schoolID], [number])
select distinct LKR.schoolID, A.lockerNumber
 FROM [InputTable] A
JOIN test.dbo.School SCH ON A.schoolnumber = SCH.type and A.schoolnumber = @SchoolNumber
JOIN test.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID
AND A.lockerNumber not in (select number from test.dbo.Locker where schoolID = @SchoolID)
order by LKR.schoolID, A.lockerNumber
I am not certain how to complete the rest of the task of placing lockerid uniquely into lock and locker tables? Thus can you either modify the sql that I just listed above and/or come up with some new sql that will show me how to accomplish my goal?
I get this error when inserting data..The INSERT statement conflicted with the FOREIGN KEY constraint FK_Participant_ Log_BiometricInstance_ Participant_ Activities". The conflict occurred in database "ProvantCustomerPortal", table "dbo.Activities", column 'Id'.The statement has been terminated.
My query looks like this :
insert into [dbo].[Participant_BiometricInstance](ParticipantId, ActivityId, ProviderTypeId, Fasting, ExternalSystemId, ResultsDate, ModifiedBy, ModifiedDate)
select participantID,'','','',NULL,getdate(),NULL,getdate() from [dbo].[Participant_Profile]
[code]....
Hi.
Please excuse the rookie question, but I am not sure how to get around this error.
"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_users_editprofile". The conflict occurred in database "MyDB", table "dbo.editprofile", column 'userid'.
The statement has been terminated."
I encountered the error when running a series of test INSERT statements after creating a bunch of FK/PK relationships
between the parent table DBO.USERS and several child tables. The one that this error complains about is DBO.EDITPROFILE.
Code Snippet
INSERT INTO users (lastname, firstname, email, alternateemail, password, role, securityquestion, securityanswer, zipcode, birthmonth, birthday, birthyear, gender, city, state, country, registerdate, editdate, confirmed)
VALUES('gore', 'albert', 'bigal@inconvenienttruth.org', null, 'hugatree', 'User', 'what is your school?', 'harvard', 06510, 1, 31, 1970, 'Male', 'Greenwich', 'Connecticut', 'USA', '2007-06-22', null, 1)
INSERT INTO precedence (userid, profileid, searchname, proximity)
VALUES(1, 3, 'random search', 1)
INSERT INTO userphotos (photoname, userid, location, size, caption, isdefault, ispublic, date, nophoto)
VALUES('random image', 1, 'x/y/z/', 23422, 'random', 0, 1, '1998-12-25', 0)
INSERT INTO editprofile(headline, aboutme, userid, date, interests)
VALUES('Random Headline', 'random', 1, '1998-12-25', 'I am interested in anything random')
DBO.USERS:
Code Snippet
CREATE TABLE [dbo].[users](
[userid] [int] IDENTITY(1,1) NOT NULL,
[lastname] [varchar](50) NULL,
[firstname] [varchar](50) NULL,
[email] [varchar](50) NOT NULL,
[alternateemail] [varchar](50) NULL,
[password] [varchar](50) NOT NULL,
[role] [char](50) NOT NULL,
[securityquestion] [varchar](50) NOT NULL,
[securityanswer] [varchar](50) NOT NULL,
[zipcode] [int] NOT NULL,
[birthmonth] [tinyint] NOT NULL,
[birthday] [tinyint] NOT NULL,
[birthyear] [int] NOT NULL,
[gender] [char](10) NULL,
[city] [varchar](50) NULL,
[state] [varchar](50) NULL,
[country] [varchar](50) NULL,
[registerdate] [datetime] NOT NULL,
[editdate] [datetime] NULL,
[confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)),
CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED
(
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_email] UNIQUE NONCLUSTERED
(
[email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [FK_users_editprofile] FOREIGN KEY([userid])
REFERENCES [dbo].[editprofile] ([userid])
GO
ALTER TABLE [dbo].[users] CHECK CONSTRAINT [FK_users_editprofile]
GO
ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [FK_users_lastlogin] FOREIGN KEY([userid])
REFERENCES [dbo].[lastlogin] ([userid])
GO
ALTER TABLE [dbo].[users] CHECK CONSTRAINT [FK_users_lastlogin]
GO
ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [FK_users_precedence] FOREIGN KEY([userid])
REFERENCES [dbo].[precedence] ([userid])
GO
ALTER TABLE [dbo].[users] CHECK CONSTRAINT [FK_users_precedence]
GO
ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [FK_users_savedsearches] FOREIGN KEY([userid])
REFERENCES [dbo].[savedsearches] ([userid])
GO
ALTER TABLE [dbo].[users] CHECK CONSTRAINT [FK_users_savedsearches]
GO
ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [FK_users_userphotos] FOREIGN KEY([userid])
REFERENCES [dbo].[userphotos] ([userid])
GO
ALTER TABLE [dbo].[users] CHECK CONSTRAINT [FK_users_userphotos]
DBO.EDITPROFILE
Code Snippet
CREATE TABLE [dbo].[editprofile](
[editprofileid] [int] IDENTITY(1,1) NOT NULL,
[headline] [varchar](50) NULL,
[aboutme] [text] NULL,
[userid] [int] NOT NULL,
[date] [datetime] NOT NULL,
[interests] [text] NULL,
CONSTRAINT [PK_EditProfile] PRIMARY KEY CLUSTERED
(
[date] ASC,
[userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Is there anyway to get the order in which data to be import on to tables when they have primary and Foreign Key relations?
For ex:We have around 170 tables and when tries to insert data it will throw error stating table25 data should be inserted first when we insert data in table 25 it say 70 like that.
For some reason, I'm getting this error, even without the DBCC Check:
INSERT statement conflicted with COLUMN FOREIGN KEY SAME TABLE constraint 'Category_Category_FK1'. The conflict occurred in database 'mydb', table 'Category', column 'CategoryID'.
The statement has been terminated.
The very first insert fails...it was working fine before:
DELETE Category;
-- Now, insert the initial 'All' Root Record
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 1, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'All'
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID )
SELECT 2, CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1 FROM CategoriesStaging WHERE CategoryName = 'Store'
/* Finally, insert the rest and match on the Parent
Category Name based on the CategoryStaging table
*/
WHILE (@@ROWCOUNT <> 0)
BEGIN
INSERT INTO Category
(ParentCategoryID, [Name], [Description], DisplayOrder, DisplayInExplorer, Keywords, Active, CreateDate, CreateUserID, UpdateDate, UpdateUserID)
SELECT c.CategoryID, s.CategoryName, '', 1, 1, '', 1, GETDATE(), 1, GETDATE(), 1
FROM Category c INNER JOIN CategoriesStaging s ON c.[Name] = s.ParentCategoryName
WHERE NOT EXISTS (SELECT 1 FROM Category c WHERE s.[CategoryName] = c.[Name])
AND s.CategoryName <> 'All'
Here's the schema:
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[ParentCategoryID] [int] NULL,
[Name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [mydatabase]
GO
ALTER TABLE [dbo].[Category] WITH NOCHECK ADD CONSTRAINT [Category_Category_FK1] FOREIGN KEY([ParentCategoryID])
REFERENCES [dbo].[Category] ([CategoryID])
I get the below error on the event log of my application server which uses SQL database.
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager",
table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.
The statement has been terminated.RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.The statement has been terminated..
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo. BaseManaged Entity", column 'BaseManagedEntityId'.The statement has been terminated..
Hi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
I am trying to query the database to get me the foreign key columns and the tables they belong to.I have: The name of the tableI need:The name of the column in the target tableThe name of the column in the referenced tableThe name of the referenced table Any help would be great, thanks
View 5 Replies View RelatedMy SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db?
SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID
This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.