SQL Database Integration

Re: Sending repeating Area to MSSQL ?

Jimmy — Wed, 25 Jul 2012 09:50:52 GMT

It looks like you've moved the braces for the first while loop to the wrong place. The opening brace has to be after the while line, not before it.

Re: Sending repeating Area to MSSQL ?

r.senby — Wed, 25 Jul 2012 08:00:59 GMT

Hi Jimmy

I tryed to do what you wrote but then i can only submit one repeating area. If i try to make 2 repeating areas it wont submit anything at all not even the first one.

Here is the complete Code page and i really dont understand what im doing wrong here.

using Microsoft.Office.InfoPath;

using System;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

using System.Xml;

using System.Xml.XPath;

using mshtml;

namespace Driftledarrapport_Ver2._

{

public partial class FormCode

{

#region Taggar

public string[] Data1Line = new string[15];

public string[] Data1LinePart = new string[15];

public string[] Data1Comment = new string[15];

public string[] QueryData = new string[15];

public string Data1Driftledare;

public string Data1DatumTid;

#endregion

public void InternalStartup()

{

((ButtonEvent)EventManager.ControlEvents["BtnSave"]).Clicked += new ClickedEventHandler(BtnSave_Clicked);EventManager.FormEvents.Sign += new SignEventHandler(FormEvents_Sign);

}

//***********************************************************

//Reads all the data from the form and puts it in the arrays

//***********************************************************

#region "Readdata"public void ReadData1()

{

XPathNavigator nav = MainDataSource.CreateNavigator();

XPathNodeIterator rows = nav.Select("/my:minaFlt", NamespaceManager);

Data1Driftledare = MainDataSource.CreateNavigator().SelectSingleNode("/my:minaFlt/my:Driftledare", NamespaceManager).Value;Data1DatumTid = MainDataSource.CreateNavigator().SelectSingleNode("/my:minaFlt/my:DatumTid", NamespaceManager).Value;

// Loop throu all taggs and save info... "Repeating Area"

XPathNodeIterator rows1 = nav.Select("/my:minaFlt/my:grupp5/my:Data1", NamespaceManager);

int i = -1;

{

while (rows1.MoveNext())

i = i + 1;

Data1Line[i] = rows1.Current.SelectSingleNode("my:Line", NamespaceManager).Value;

Data1LinePart[i] = rows1.Current.SelectSingleNode("my:LinePart", NamespaceManager).Value;

Data1Comment[i] = rows1.Current.SelectSingleNode("my:Comment", NamespaceManager).Value;

}

#endregion

//***********************************************************

//Sends all the data from the arrays to the database

//***********************************************************

public void SaveData()

{

#region "Savedata"

try

{

SqlConnection cs = new SqlConnection("Data Source=GBGCIT10; Initial Catalog=Pagen_Teknik; User Id = xxxxx; Password = xxxxx;");SqlDataAdapter da = new SqlDataAdapter();

cs.Open();

XPathNavigator nav = MainDataSource.CreateNavigator();

XPathNodeIterator rows1 = nav.Select("/my:minaFlt/my:grupp5/my:Data1", NamespaceManager);

XPathNodeIterator rows = nav.Select("/my:minaFlt", NamespaceManager);

Data1Driftledare = MainDataSource.CreateNavigator().SelectSingleNode("/my:minaFlt/my:Driftledare", NamespaceManager).Value;

Data1DatumTid = MainDataSource.CreateNavigator().SelectSingleNode("/my:minaFlt/my:DatumTid", NamespaceManager).Value;

int i = -1;

while (rows1.MoveNext())

{

i = i + 1;

da.InsertCommand = new SqlCommand("INSERT INTO Linje_Rapport_Main VALUES (@Line, @LinePart, @Comment, @Driftledare, @Time)", cs);

da.InsertCommand.Parameters.Add("@Line", SqlDbType.VarChar).Value = Data1Line[i];

da.InsertCommand.Parameters.Add("@LinePart", SqlDbType.VarChar).Value = Data1LinePart[i];

da.InsertCommand.Parameters.Add("@Comment", SqlDbType.VarChar).Value = Data1Comment[i];

da.InsertCommand.Parameters.Add("@Driftledare", SqlDbType.VarChar).Value = Data1Driftledare;da.InsertCommand.Parameters.Add("@Time", SqlDbType.DateTime).Value = Data1DatumTid;

da.InsertCommand.ExecuteNonQuery();

}

cs.Close();

}

catch

{

XPathNavigator myPO2 = MainDataSource.CreateNavigator();myPO2.SelectSingleNode("/my:minaFlt/my:Status", NamespaceManager).SetValue("Misslyckat frsk att spara Rapport");

}

XPathNavigator myPO3 = MainDataSource.CreateNavigator();

myPO3.SelectSingleNode("/my:minaFlt/my:Status", NamespaceManager).SetValue("Rapport Sparad");

#endregion

}

public void BtnSave_Clicked(object sender, ClickedEventArgs e)

{

// Skriv koden hr.

ReadData1();

SaveData();

}

public void FormEvents_Sign(object sender, SignEventArgs e)

{

// Den hr hndelsehanteraren krs bara i fullstndigt betrodda formulrmallar.

Signature thisSignature = e.SignedDataBlock.Signatures.CreateSignature();

// Om du vill lgga till andra informationsdelar fr signering

// ndrar du signaturmallen som returneras av

// thisSignature.SignatureBlockXmlNode.

// Skriv koden hr.

thisSignature.Sign();

e.SignatureWizard = false;

}

Re: Sending repeating Area to MSSQL ?

Jimmy — Tue, 24 Jul 2012 16:35:42 GMT

        while (rows1.MoveNext())
        {
            i = i + 1;

Data1Line[i] = rows1.Current.SelectSingleNode("my:Line", NamespaceManager).Value;

Data1LinePart[i] = rows1.Current.SelectSingleNode("my:LinePart", NamespaceManager).Value;

Data1Comment[i] = rows1.Current.SelectSingleNode("my:Comment", NamespaceManager).Value;

}

            while (rows1.MoveNext())
            {
                i = i + 1;

da.InsertCommand = new SqlCommand("INSERT INTO Linje_Rapport_Main VALUES (@Line, @LinePart, @Comment, @Driftledare, @Time)", cs);

da.InsertCommand.Parameters.Add("@Line", SqlDbType.VarChar).Value = Data1Line[i];

da.InsertCommand.Parameters.Add("@LinePart", SqlDbType.VarChar).Value = Data1LinePart[i];

                da.InsertCommand.Parameters.Add("@Comment", SqlDbType.VarChar).Value = Data1Comment[i];
                da.InsertCommand.Parameters.Add("@Driftledare", SqlDbType.VarChar).Value = Data1Driftledare;
                da.InsertCommand.Parameters.Add("@Time", SqlDbType.DateTime).Value = Data1DatumTid;
                da.InsertCommand.ExecuteNonQuery();
            }

Re: Sending repeating Area to MSSQL ?

r.senby — Tue, 24 Jul 2012 10:19:44 GMT

Sorry can you post what you meen ? My intention is to do the same as this VB but in C# Try conn.Open() Dim domNav As XPathNavigator = MainDataSource.CreateNavigator() Dim rows As XPathNodeIterator = domNav.Select("/my:Main/my:Recept/my:Flour", NamespaceManager) ' Loop throu all taggs and save info... "Dropdown list" Dim i As Integer = -1 While rows.MoveNext() i = i + 1 Dim tempFlour(50) As String ' Contains the Recipe DRY Query(s) tempFlour(i) = " INSERT INTO [PagenRecipes].[dbo].[PagenRecipeFlour]([RecipeID],[ArticleNr],[Weight],[Min],[Max]) values(@RecipeId,'" + datauxflourart(i).ToString + " ', '" + datauxflourweight(i).ToString + "', '" + datauxflourmin(i).ToString + "', '" + datauxflourmax(i).ToString + "') " temptempFlourQuery = temptempFlourQuery + tempFlour(i) End While But i cant seem to figure it out :/

Re: Sending repeating Area to MSSQL ?

Jimmy — Tue, 24 Jul 2012 10:09:25 GMT

It looks like the entire contents of both of your while loops is:

i = i + 1;

So the loops just go around and around until i is NumRows - 1. Is that intentional, or did you put the braces in the wrong place?

Sending repeating Area to MSSQL ?

r.senby — Tue, 24 Jul 2012 09:30:55 GMT

Hi ! Im quite new to this thing and now i have some troubles. I hope someone here can help me or atleast point me in the right direction. I have a InfoPath 2010 document that i includes an area with static fields and one are that is a repeating area with 3 fields. This 3 fields i try to so save in arrays in a C# code file. When everything is filled i have a submitbutton. That should execute reading the values and store them and then send them to my MSSQL. But the problem is that it only sends the latest input in the form. Here is the code public partial class FormCode { #region Taggar public string[] Data1Line = new string[15]; public string[] Data1LinePart = new string[15]; public string[] Data1Comment = new string[15]; public string[] QueryData = new string[15]; public string Data1Driftledare; public string Data1DatumTid; #endregion #region "Readdata" public void ReadData1() { XPathNavigator nav = MainDataSource.CreateNavigator(); XPathNodeIterator rows = nav.Select("/my:minaFält", NamespaceManager); Data1Driftledare = MainDataSource.CreateNavigator().SelectSingleNode("/my:minaFält/my:Driftledare", NamespaceManager).Value; Data1DatumTid = MainDataSource.CreateNavigator().SelectSingleNode("/my:minaFält/my:DatumTid", NamespaceManager).Value; // Loop throu all taggs and save info... "Repeating Area" //XPathNavigator nav1 = MainDataSource.CreateNavigator(); XPathNodeIterator rows1 = nav.Select("/my:minaFält/my:grupp5/my:Data1", NamespaceManager); int i = -1; while (rows1.MoveNext()) i = i + 1; { Data1Line[i] = rows1.Current.SelectSingleNode("my:Line", NamespaceManager).Value; Data1LinePart[i] = rows1.Current.SelectSingleNode("my:LinePart", NamespaceManager).Value; Data1Comment[i] = rows1.Current.SelectSingleNode("my:Comment", NamespaceManager).Value; } } #endregion public void SaveData() { #region "Savedata" try { SqlConnection cs = new SqlConnection("Data Source=GBGCIT10; Initial Catalog=Pagen_Teknik; User Id = XXXXXXXXXX; Password =XXXXXX;"); SqlDataAdapter da = new SqlDataAdapter(); cs.Open(); XPathNavigator nav = MainDataSource.CreateNavigator(); XPathNodeIterator rows1 = nav.Select("/my:minaFält/my:grupp5/my:Data1", NamespaceManager); XPathNodeIterator rows = nav.Select("/my:minaFält", NamespaceManager); Data1Driftledare = MainDataSource.CreateNavigator().SelectSingleNode("/my:minaFält/my:Driftledare", NamespaceManager).Value; Data1DatumTid = MainDataSource.CreateNavigator().SelectSingleNode("/my:minaFält/my:DatumTid", NamespaceManager).Value; int i = -1; while (rows1.MoveNext()) i = i + 1; { da.InsertCommand = new SqlCommand("INSERT INTO Linje_Rapport_Main VALUES (@Line, @LinePart, @Comment, @Driftledare, @Time)", cs); da.InsertCommand.Parameters.Add("@Line", SqlDbType.VarChar).Value = Data1Line[i]; da.InsertCommand.Parameters.Add("@LinePart", SqlDbType.VarChar).Value = Data1LinePart[i]; da.InsertCommand.Parameters.Add("@Comment", SqlDbType.VarChar).Value = Data1Comment[i]; da.InsertCommand.Parameters.Add("@Driftledare", SqlDbType.VarChar).Value = Data1Driftledare; da.InsertCommand.Parameters.Add("@Time", SqlDbType.DateTime).Value = Data1DatumTid; da.InsertCommand.ExecuteNonQuery(); } cs.Close(); } catch { XPathNavigator myPO2 = MainDataSource.CreateNavigator(); myPO2.SelectSingleNode("/my:minaFält/my:Status", NamespaceManager).SetValue("Misslyckat försök att spara Rapport"); } XPathNavigator myPO3 = MainDataSource.CreateNavigator(); myPO3.SelectSingleNode("/my:minaFält/my:Status", NamespaceManager).SetValue("Rapport Sparad"); #endregion } public void BtnSave_Clicked(object sender, ClickedEventArgs e) { ReadData1(); SaveData();