Re: Repeating table data to sql server.

Anna-Marie — Wed, 22 Jun 2011 10:04:11 GMT

How did you get this working?

Thanks

Re: Repeating table data to sql server.

ooty_max — Wed, 08 Jun 2011 19:11:41 GMT

Never mind, I got it working.

Repeating table data to sql server.

ooty_max — Tue, 07 Jun 2011 15:42:35 GMT

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.

General

Re: Repeating table data to sql server.

Re: Repeating table data to sql server.

Repeating table data to sql server.