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();70and 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.
