SQL Database Integration

Re: Querying secondary data connection

Donna Kelly — Sun, 01 Mar 2009 06:41:06 GMT

Conent deleted.

Changing Dim dataFields As XPathNavigator = MainDataSource.CreateNavigator.SelectSingleNode("/my:myFields/my:QueryResults", to "dfs::myFields/my:QueryResults" fixed a separate problem with a null reference. But I didn't really understand what I was doing (in InfoPath terms). When I look in my data sources, everything is prefixed with dfs:

What is it with this my: and dfs: (and I've seen tns: as well)? Any clarification would be most welcome.

Cheers,

Donna

Re: Querying secondary data connection

Donna Kelly — Sun, 01 Mar 2009 06:29:55 GMT

Jimmy,

I'd appreciate a little help here. I've been able to retrieve data fine using an AdoQueryConnection on the secondary, but it's getting the data out and usable that has stymied me.

I do not really understand the reference to ResultRow_19. I I was trying to populate a single ResultRow in QueryResults, and after that was done, then use that data programatically to further calculate/populate control on the form (which are bound to the bit of the Main data source I want to actually submit). You mention in the comments that "the name that corresponds to ResultRow_19 here can be found on the Advanced tab . . . ". But isn't that the Advanced tab relevant to a Repeating Table control? Wouldn't that nean that the iner For Each loop is populating a control rather than the Main Data Source?

In general, what I am trying to do is manipulate data at the XML/data source level, rather than through controls (hidden or not). I want to be able to take some data from the secondary source, and pre-populate some controls which are part of the Main data fields with defaults (prior to display), and populate some other Main data fields with some other information copied directly from the secondary (things like key fields to ensure referential integrity, and so on).

I'd sure appreciate some clarification on this. If you have any examples, they would be most welcome, especially if they are VB.

Cheers,

Donna

Re: Querying secondary data connection

Jimmy — Fri, 20 Feb 2009 06:27:45 GMT

As a user in another thread has asked about a similar question but in Visual Basic, I have worked up a VB version of this solution. The underlined parts will likely need modification to be used with a specific solution. Here is the code:

Public Sub CTRL11_5_Clicked(ByVal sender As Object, ByVal e As ClickedEventArgs)
     If (RunQuery()) Then
          CopyResults()
     End If
End Sub

Private Function RunQuery() As Boolean
     ' a format string for the query. The {0}s will be replaced by field1's value
     Dim queryString As String = "select * from [TestTable1] where [OrderNum] = '{0}' OR [ID] = '{0}'"
     ' retrieve field1's value and store it in a string
     Dim field1 As String = MainDataSource.CreateNavigator.SelectSingleNode("/my:myFields/my:field1", Me.NamespaceManager).Value
     ' make sure that the search string isn't blank
     If (Not String.IsNullOrEmpty(field1)) Then
          ' instantiate an object to access the data connection. TestTable1 is the name of the data connection
          Dim dc As AdoQueryConnection = CType(DataConnections("TestTable1"), AdoQueryConnection)
          Dim originalCommand As String = dc.Command
          Try
               ' insert field1's value into the query and assign it to the data connection's query.
               ' The Replace() function call is to prevent SQL injection and unexpected behavior from
               ' the use of apostrophes
               dc.Command = String.Format(queryString, field1.Replace("'", "''"))
               ' run the query
               dc.Execute()
               Return True
          Finally
               dc.Command = originalCommand
          End Try
     Else
          ' show an error when field1 is blankMessageBox.Show("The search field cannot be blank!")
          Return False
     End If
End Function

Private Sub CopyResults()
     ' store the namespace URLs as strings for easy reference
     Dim myNamespace As String = NamespaceManager.LookupNamespace("my")
     ' the attributes on the secondary data source results have no prefix
     Dim blankNamespace As String = NamespaceManager.LookupNamespace(String.Empty)

     ' select the node where the results will be placed
     Dim dataFields As XPathNavigator = MainDataSource.CreateNavigator.SelectSingleNode("/my:myFields/my:QueryResults", NamespaceManager)

     ' select the secondary data source node where the results are located
     Dim queryDataFields As XPathNavigator = DataSources("TestTable1").CreateNavigator
     queryDataFields = queryDataFields.SelectSingleNode("/dfs:myFields/dfs:dataFields", NamespaceManager)

     ' remove all ResultRow children of the my:QueryResults node in the main data source
     While (dataFields.MoveToChild("ResultRow", myNamespace))
          dataFields.DeleteSelf()
     End While

     ' iterate through each results node in the secondary data source
     For Each queryResultRow As XPathNavigator In queryDataFields.Select("*")
          ' add a row to the results' repeating table/section. The name that corresponds to
          ' "ResultRow_19" here can be found on the Advanced tab of the repeating row/section's
          ' properties
          CurrentView.ExecuteAction(ActionType.XCollectionInsert, "ResultRow_19")
          ' Select all of the nodes in the newly created row and copy values into them.
          ' Their names must correspond precisely to the names of the nodes you want to copy over
          For Each resultNode As XPathNavigator In dataFields.Select("my:ResultRow[last()]/*", NamespaceManager)
               resultNode.SetValue(queryResultRow.GetAttribute(resultNode.LocalName, blankNamespace))
          Next
     Next
End Sub

Re: Querying secondary data connection

ZaiZai — Wed, 04 Feb 2009 14:29:03 GMT

Jimmy,

Don't worry about it. I found a workaround.
Thanks for all of your help.

Re: Querying secondary data connection

ZaiZai — Tue, 03 Feb 2009 13:17:54 GMT

Jimmy,

I have one final question on this topic if you don't mind. Everything works like a charm. However, rules don't seem to work on these fields under the group we created called "ResultRow". Basically, I need to place the values that the code brings back into appropriate fields that are connected to the main data source. I was going to simply apply a rule to each of the fields under the group "ResultRow" under "My:QueryResults" that would set the field values of the database fields.

I can set it up but it doens't do anything. It is strange actaully because I tested on other fields and it works fine. Did the code that we used by chance set these fields under "ResultRow" a certain way so that rules don't apply?

I need to be able to get the return values into different fields so that the SQL tables can be populated accordingly.

If you have the time to answer I would appreciate it. Thanks Jimmy.

Re: Querying secondary data connection

ZaiZai — Mon, 02 Feb 2009 11:52:16 GMT

Thanks again Jimmy!

Re: Querying secondary data connection

Jimmy — Mon, 02 Feb 2009 11:17:58 GMT

Great to hear that!

It looks like your main data source is based on a database query connection, rather than being built from scratch, so in such cases, the myFields node uses the dfs namespace. That's something I didn't really know either, so it's a good thing for us both to keep in mind :-)

Re: Querying secondary data connection

ZaiZai — Mon, 02 Feb 2009 06:15:04 GMT

Jimmy,

That was the fix. Why was it different in my form? What is the difference between "dfs" and "my"? Either way, it works now thanks to your willingness to assist a newbie. This was a very big help Jimmy and it is very appreciated. This forum is the best!

Re: Querying secondary data connection

Jimmy — Sun, 01 Feb 2009 15:03:35 GMT

ZaiZai:
<dfs:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:q="http://schemas.microsoft.com/office/infopath/2003/ado/queryFields" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-08-29T17:27:37" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003"

It looks like in your case, the XPath to the field1 field would be "/dfs:myFields/my:field1".

Please give that a try and see if that helps.

Re: Querying secondary data connection

ZaiZai — Sun, 01 Feb 2009 10:55:03 GMT

Jimmy,

Here is my file. Let me know what you think.

Re: Querying secondary data connection

Jimmy — Sat, 31 Jan 2009 00:21:43 GMT

If it's not too much trouble to share it, could you attach a copy of the template.xml file from your extracted template source (use the Options tab when you reply)? That will allow me to see how your main data source is structured and figure out why it's not finding that field1.

Re: Querying secondary data connection

ZaiZai — Fri, 30 Jan 2009 19:10:17 GMT

Everything has worked with the the removal of the code. I have done everything over, yet, the error about the null reference is happening again. I do have a field called field1 in my main form that I am using as the text control that captures the matternumber.

I feel that we are very close. What are your thoughts on this null reference?

Re: Querying secondary data connection

ZaiZai — Fri, 30 Jan 2009 18:50:48 GMT

I will try that Jimmy. Thanks again.

Re: Querying secondary data connection

Jimmy — Fri, 30 Jan 2009 18:05:51 GMT

I see. I guess you are using InfoPath 2003. Please back up your XSN file and try the following steps to remove the code from your form:

1. Save the template as its source files (from the File menu of the designer), close InfoPath and browse to the folder where you saved the files.
2. Open manifext.xsf in any text editor.
3. Delete the xsf:file node that refers to script.js (something like this):
<xsf:file name="script.js">
   <xsf:fileProperties>
      <xsf:property name="scriptType" type="string" value="userEvents"></xsf:property>
   </xsf:fileProperties>
</xsf:file>

4. Delete the xsf:scripts node (like this)
<xsf:scripts language=" jscript" enforceScriptTImeout="yes">
<xsf:script src=" script.js "></xsf:script>
</xsf:scripts>

5. Save changes and close manifest.xsf.
6. Delete the script.js file
7. Right-click manifest.xsf and choose Design

Re: Querying secondary data connection

ZaiZai — Fri, 30 Jan 2009 17:44:13 GMT

I don't see the option to remove code?>>>