Querying secondary data connection - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Querying secondary data connection

Last post 02-28-2009 10:41 PM by Donna Kelly. 45 replies.
Page 3 of 4 (46 items) < Previous 1 2 3 4 Next >
Sort Posts: Previous Next
  • 01-30-2009 09:41 AM In reply to

    Re: Querying secondary data connection

    I follow up now Jimmy. However, I don't see the option to delete the code to reset it so that my first line is there.  And yes, I have a field1.  I wanted to first test it with minimal changes.

    Thanks a lot for your help on this.  I will let you know how it goes.

  • 01-30-2009 09:44 AM In reply to

    Re: Querying secondary data connection

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

  • 01-30-2009 10:05 AM In reply to

    Re: Querying secondary data connection

    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

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 01-30-2009 10:50 AM In reply to

    Re: Querying secondary data connection

    I will try that Jimmy.  Thanks again.

  • 01-30-2009 11:10 AM In reply to

    Re: Querying secondary data connection

    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?

  • 01-30-2009 04:21 PM In reply to

    Re: Querying secondary data connection

    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.

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 02-01-2009 02:55 AM In reply to

    Re: Querying secondary data connection

    Jimmy,

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

     

     

  • 02-01-2009 07:03 AM In reply to

    Re: Querying secondary data connection

    ZaiZai:

     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.

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 02-01-2009 10:15 PM In reply to

    Re: Querying secondary data connection

    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!

  • 02-02-2009 03:17 AM In reply to

    Re: Querying secondary data connection

    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 :-)

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 02-02-2009 03:52 AM In reply to

    Re: Querying secondary data connection

    Thanks again Jimmy!

  • 02-03-2009 05:17 AM In reply to

    Re: Querying secondary data connection

    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.

     

  • 02-04-2009 06:29 AM In reply to

    Re: Querying secondary data connection

    Jimmy,

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

  • 02-19-2009 10:27 PM In reply to

    Re: Querying secondary data connection

    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

    Jimmy Rishe / Software Developer / Microsoft MVP
    Qdabra Software
  • 02-28-2009 10:29 PM In reply to

    Re: Querying secondary data connection

     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

Page 3 of 4 (46 items) < Previous 1 2 3 4 Next >
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.