Infopath Form does not work in Sharepoint

Question

I have a form that has 3 Data Connections.

Main Data Source (Active Directory) Secundary Data Source (SOAP Web Service) Secundary Data Soruce (SQL Database)

I fetch data from AD, from a Web Service and I am trying to add a SQL Server source. However I am faces with issues. I have created the following method to fetch data once a value from the AD is outputed

Since the SQL DAtabase source does not have query fields I implemented the following methd:

public void CTRL645_5_Clicked(object sender, ClickedEventArgs e) 
{ 
    XPathNavigator MDMid = CreateNavigator().SelectSingleNode("/my:myFields/my:sectCommon/my:MDMid", NamespaceManager);

    XPathNavigator custName = CreateNavigator().SelectSingleNode("/my:myFields/my:sectCommon/my:custName", NamespaceManager);

    XPathNavigator RetCustName = CreateNavigator().SelectSingleNode("/dfs:myFields/dfs:dataFields/d:DAL_PARTY/@CUST_NAME", NamespaceManager);


    string MDMidNum = MDMid.InnerXml;

    AdoQueryConnection myAdoQueryConnection = (AdoQueryConnection)(this.DataConnections["DAL_PARTY"]);

    myAdoQueryConnection.Command = "select CUST_NAME from [DAL_PARTY] where [ROWID_OBJECT] = " + MDMidNum;

    myAdoQueryConnection.Execute();

    custName.SetValue(RetCustName.Value);

}

However I am getting an exception regarding connection user and password data. I then converted my SQL Connection to a UDCX file, but I still get the same error.

Is it possibe to query more than one SQL Database?

How do I overcome this user and password exception? (Exception MSG: The current configuration settings prohibit embedding username and password in the connection string to the database.)

Also my Form keeps growing bigger and bigger even when I do nothing in terms of code, what could be the soruce if this strange behavior? (My XSN file is now 14Mb !!!)

Answer 1

You do not show the connection string here and you have not posted the exception message so i will ask the obvious stuff.

Have you made sure that your connection string is correct? Have you made sure that your username and password for the database is correct? Have you made sure that the server that is executing the command has access to the SQL server?

Edit:

SharePoint Central Admin -> Application Management -> Configure InfoPath Forms Services. Check the box next to Allow embedded SQL authentication and click the OK button.