Hi,
I am new to writing c# code in Infopath Form and have requirement to submit data from Infopath to sql server. The structure of the database is one to many relationship. For e.g. I want to store Header information in one table and Detail(repeating table) in another table.
In my stored procedure I get the ID of Header table using @@Identity and store it in local variable and insert to detail table as Foreign key. I am not sure if this method is correct/wrong.
I get error message as "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Detail_Header". The conflict occurred in database "xxx", table "dbo.Header", column 'HeaderID'.
The statement has been terminated.
Code is below
public void btnGo_Clicked(object sender, ClickedEventArgs e)
{
// Write your code here.
XPathNavigator DOM = this.MainDataSource.CreateNavigator();
XPathNavigator myNav = this.MainDataSource.CreateNavigator();
XPathNodeIterator myIterator = myNav.Select("my:myFields/my:Procedures/my:Procedure", NamespaceManager);
string strHeaderFirstName = DOM.SelectSingleNode("/my:myFields/my:FirstName", NamespaceManager).Value;
string strHeaderLastName = DOM.SelectSingleNode("/my:myFields/my:LastName", NamespaceManager).Value;
using (SqlConnection conn = new SqlConnection("Data Source=local;Initial Catalog=xxx;Integrated Security=True"))
{
conn.Open();
SqlCommand command = new SqlCommand("USPInsertHeader", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@FirstName", SqlDbType.VarChar).Value = strOrderFirstName;
command.Parameters.AddWithValue("@LastName", SqlDbType.VarChar).Value = strOrderLastName;
command.ExecuteNonQuery();
//Get repeating section data
while(myIterator.MoveNext())
{
SqlCommand cmd = new SqlCommand("USPInsertDetail", conn);
cmd.CommandType = CommandType.StoredProcedure;
string strDetailtype = myIterator.Current.SelectSingleNode("my:Type", NamespaceManager).Value;
string strDetailFor = myIterator.Current.SelectSingleNode("my:for", NamespaceManager).Value;
string strDetailDate = myIterator.Current.SelectSingleNode("my:dateRequiredBy", NamespaceManager).Value;
cmd.Parameters.AddWithValue("@Detailtype", SqlDbType.VarChar).Value = strDetailtype;
cmd.Parameters.AddWithValue("@DetailFor", SqlDbType.VarChar).Value = strDetailFor;
cmd.Parameters.AddWithValue("@DetailDate", SqlDbType.VarChar).Value = strDetailDateRequired;
cmd.ExecuteNonQuery();//I get the error message here
}
conn.Close();
}
}
}
Please Help.