Cannot INSERT data to 3 tables linked with relationship (INSERT statement conflicted with

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 code

1DataSet ds =new DataSet();23SqlDataAdapter myCommand1 =new SqlDataAdapter("select * from SurveyTemplate", myConnection);4SqlCommandBuilder cb =new SqlCommandBuilder(myCommand1);5myCommand1.FillSchema(ds, SchemaType.Source);6DataTable pTable = ds.Tables["Table"];7pTable.TableName ="SurveyTemplate";8myCommand1.InsertCommand = cb.GetInsertCommand();9myCommand1.InsertCommand.Connection = myConnection;1011SqlDataAdapter myCommand2 =new SqlDataAdapter("select * from Question", myConnection);12cb =new SqlCommandBuilder(myCommand2);13myCommand2.FillSchema(ds, SchemaType.Source);14pTable = ds.Tables["Table"];15pTable.TableName ="Question";16myCommand2.InsertCommand = cb.GetInsertCommand();17myCommand2.InsertCommand.Connection = myConnection;1819SqlDataAdapter myCommand3 =new SqlDataAdapter("select * from Possible_Answer", myConnection);20cb =new SqlCommandBuilder(myCommand3);21myCommand3.FillSchema(ds, SchemaType.Source);22pTable = ds.Tables["Table"];23pTable.TableName ="Possible_Answer";24myCommand3.InsertCommand = cb.GetInsertCommand();25myCommand3.InsertCommand.Connection = myConnection;2627ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));28ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));2930DataRow dr = ds.Tables["SurveyTemplate"].NewRow();31dr["name"] = o[0];32dr["description"] = o[1];33dr["active"] = 1;34ds.Tables["SurveyTemplate"].Rows.Add(dr);3536DataRow dr1 = ds.Tables["Question"].NewRow();37dr1["questionIndex"] = 1;38dr1["questionContent"] ="q1";39dr1.SetParentRow(dr);40ds.Tables["Question"].Rows.Add(dr1);4142DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();43dr2["answerIndex"] = 1;44dr2["answerContent"] ="a11";45dr2.SetParentRow(dr1);46ds.Tables["Possible_Answer"].Rows.Add(dr2);4748dr1 = ds.Tables["Question"].NewRow();49dr1["questionIndex"] = 2;50dr1["questionContent"] ="q2";51dr1.SetParentRow(dr);52ds.Tables["Question"].Rows.Add(dr1);5354dr2 = ds.Tables["Possible_Answer"].NewRow();55dr2["answerIndex"] = 1;56dr2["answerContent"] ="a21";57dr2.SetParentRow(dr1);58ds.Tables["Possible_Answer"].Rows.Add(dr2);5960dr2 = ds.Tables["Possible_Answer"].NewRow();61dr2["answerIndex"] = 2;62dr2["answerContent"] ="a22";63dr2.SetParentRow(dr1);64ds.Tables["Possible_Answer"].Rows.Add(dr2);6566myCommand1.Update(ds,"SurveyTemplate");67myCommand2.Update(ds,"Question");68myCommand3.Update(ds,"Possible_Answer");69ds.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.

The insert into Possible_Answer does not set questionId. The insert into question does not set surveyTemplateId.

It is better to do this with stored procedures, you can then see more clearly what values need to be supplied.

|||

I thought SetParentRow method cares for retrieving IDs and putting them into children rows...

Can't that be done form within these code or stored procedures are the only way I can do that ?

thanks

|||

You will need to read up on SetParentRow - a brief look on the MSDN site indicates that it only sets a relationship between objects, not copy the data value.

You do not have use stored procedures, they are just more effeicient and I respectfully submit easier to understand what data is required.

|||

TATWORTH, thank You for the advice concerning stored procedures.

Just done it and work fine

For Those that might read this post in the future here's a nice tutorial on how to user stored procedure with realationships:

http://www.codeproject.com/cs/database/relationaladonet.asp?df=100&forumid=14883&exp=0&select=1214027

Thanks again.

|||

TATWORTH, thank You for the advice concerning stored procedures.

Just done it and works fine

For Those that might read this post in the future here's a nice tutorial on how to user stored procedure with realationships:

http://www.codeproject.com/cs/database/relationaladonet.asp?df=100&forumid=14883&exp=0&select=1214027

Thanks again.

Cannot Generate SSPI Contect

I am getting error once in a while. It can be while either updating/inserting/selecting a record. I may have about 100000 hits give or take a few to the database.

The VB Application identified by the event source logged this Application MODE: Thread ID: 3300 ,Logged: -------------------------- Error Number :-2147467259 Error Description :Invalid connection string attribute Error Source :Microsoft OLE DB Provider for SQL Server -------------------------- System Error Number: 0 System Error Description: System Error Source: -------------------------- Context/Detail Error Description : :: updateOpsRecord :: ADO Error :: Error #-2147467259 Cannot generate SSPI context (Source: Microsoft OLE DB Provider for SQL Server) (SQL State: HY000) (NativeError: 0) No Help file available

This is intresting to see. I tried looking up in all the forums and help file but no ansewers. Help would be appreciated.

Thanks,

Karanwhat version are u on?
u can try this:
In the Enable protocols by order move named pipes to the top of the list using the arrows and click apply. It can also help to set up a named pipe alias for the server.

http://support.microsoft.com/support/kb/articles/q269/5/41.asp

This KB article (although it refers to SQL7) is relavent. Make sure the relavent registry key exists on the client and that the 'NT LM Security Support Provider' service is started and running.

More info can be found by searching google groups for SQL XP SSPI
http://groups.google.com/groups?as_q=XP%20SSPI&as_ugroup=microsoft.public.sqlserver. &lr=&hl=en

Regards,
Harshal