Re: InfoPath +Webservice +Cascading dropdown on browser enabled form. Please help!

Mark Jones — Fri, 23 May 2008 13:53:58 GMT

I got it working with this:

Public Sub ItemAnalysisCode_Changed(ByVal sender As Object, ByVal e As XmlEventArgs)

Dim purposeField As XPathNavigator

Dim nav As XPathNavigator = getSibbling(e.Site, "my:PurposeList")

Dim currentNodeValue As String = e.Site.Value.ToString()

Dim navFind As XPathNavigator = nav

purposeField = nav.SelectSingleNode("my:ItemPurposes", Me.NamespaceManager)

Dim getData As New athena.AnalysisCodeData

Dim purposeList As New List(Of String)

purposeList.AddRange(getData.GetPurpose(currentNodeValue))

If purposeField.HasChildren Then

Dim InnerXml As String = "<my:ItemPurposes xmlns:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-05-13T07:51:49""></my:ItemPurposes>"

navFind.SelectSingleNode("my:ItemPurposes", Me.NamespaceManager)

Dim first As XPathNavigator = navFind.SelectSingleNode("my:ItemPurposes[1]", Me.NamespaceManager)

Dim last As XPathNavigator = navFind.SelectSingleNode("my:ItemPurposes[last()]", Me.NamespaceManager)

navFind.MoveTo(first)

navFind.DeleteRange(last)

purposeField.SetValue("")

navFind.InnerXml += InnerXml

End If

For Each purpose As String In purposeList

If purpose IsNot Nothing Then

Dim newPurpose As XPathNavigator = nav.SelectSingleNode("my:ItemPurposes[last()]", Me.NamespaceManager).Clone()

newPurpose.InnerXml = purpose

nav.SelectSingleNode("my:ItemPurposes[last()]", Me.NamespaceManager).InsertAfter(newPurpose)

End If

End Sub

Private Function getSibbling(ByVal node As XPathNavigator, ByVal nameSibbling As String) As XPathNavigatorDim it As XPathNodeIterator = node.SelectAncestors(XPathNodeType.Element, False)

it.MoveNext()

Dim nodefarther As XPathNavigator = it.Current

Dim sibblings As XPathNodeIterator = nodefarther.SelectChildren(XPathNodeType.Element)

For Each sibbling As XPathNavigator In sibblings

If sibbling.Name.Equals(nameSibbling) Then

Return sibbling

End If

End Function

InfoPath +Webservice +Cascading dropdown on browser enabled form. Please help!

Mark Jones — Tue, 20 May 2008 12:02:05 GMT

hi all,

I am both new here and new to infopath / sharepoint development. Infact I am fairly new to development in general.

I am writing a new Purchase requisition system. The infopath forms will be part of a workflow atached to a sharepoint list, and must load in the browser.

I have been reading this blog post that seems to cover what I want to do: http://msmvps.com/blogs/benwalters/archive/2008/02/26/filtered-drop-down-lists-101-part-2.aspx?CommentPosted=true#commentmessage.

first my infopath data structure is as follows (relevent part in bold)

PurchaseOrders
- some fields
-(repeating group) POItems
    -ItemDescription
    -ItemAnalysisCode (dropdown 1)
    -(group)AnalysisList
        -(repeating field) ItemAnalysisCodes
    -ItemPurpose (dropdown 2)
    -(group)PurposeList
        -(repeating field) ItemPurposes
-some more fields

Here is the code for the webservice that provides data to ItemAnalysisCode, the second method recieves the analysis code from the ItemAnalysisCode field.

<WebMethod()> _
Public Function GetAnalysisCode() As String()
Dim connStr As String = "Data Source=ARIES;Initial Catalog=AxLive;User ID=sharepoint;Password=password;"
Dim sqlStr As String = "SELECT ACCOUNTNAME, BLOCKEDINJOURNAL, ACCOUNTNUM " & _
"FROM LEDGERTABLE " & _
"WHERE BLOCKEDINJOURNAL = 0 AND ACCOUNTNUM BETWEEN 40000 " & _
"AND 69999 ORDER BY ACCOUNTNUM ASC"
Dim returnString As String() = Nothing
Dim sqlConn As SqlConnection = New SqlConnection(connStr)
Dim SqlCmd As SqlCommand = New SqlCommand(sqlStr, sqlConn)
Dim sqlReader As SqlDataReader
Try
sqlConn.Open()
sqlReader = SqlCmd.ExecuteReader()
ReDim Preserve returnString(0)
Do While sqlReader.Read
ReDim Preserve returnString(UBound(returnString) + 1)
returnString(UBound(returnString)) = sqlReader("ACCOUNTNAME")
Loop
sqlReader.Close()
sqlConn.Close()
Catch ex As Exception
ReDim Preserve returnString(1)
returnString(1) = "failed"
End Try
Return returnString
End Function

<WebMethod()> _
Public Function GetPurpose(ByVal AnalysisCode As String) As String()
Dim sqlStr As String
Select Case AnalysisCode.ToLower
Case "repairs - commercial vehicles"
sqlStr = "SELECT [DESCRIPTION],[NUM],[DIMENSIONCODE],[DATAAREAID] FROM [DIMENSIONS] WHERE [DIMENSIONCODE] = 2 AND [NUM] LIKE 'C%'"
Case "repairs - private vehicles"
sqlStr = "SELECT [DESCRIPTION],[NUM],[DIMENSIONCODE],[DATAAREAID] FROM [DIMENSIONS] WHERE [DIMENSIONCODE] = 2 AND [NUM] LIKE 'P%'"
Case "repairs - extruders"
sqlStr = "SELECT [DESCRIPTION],[NUM],[DIMENSIONCODE],[DATAAREAID] FROM [DIMENSIONS] WHERE [DIMENSIONCODE] = 2 AND [NUM] LIKE 'EL%'"
Case "repairs - co extruders"
sqlStr = "SELECT [DESCRIPTION],[NUM],[DIMENSIONCODE],[DATAAREAID] FROM [DIMENSIONS] WHERE [DIMENSIONCODE] = 2 AND [NUM] LIKE 'EL%' OR [NUM] LIKE 'PC%'"
Case "repairs - calibration tables"
sqlStr = "SELECT [DESCRIPTION],[NUM],[DIMENSIONCODE],[DATAAREAID] FROM [DIMENSIONS] WHERE [DIMENSIONCODE] = 2 AND [NUM] LIKE 'EL%'"
Case "repairs - haul off"
sqlStr = "SELECT [DESCRIPTION],[NUM],[DIMENSIONCODE],[DATAAREAID] FROM [DIMENSIONS] WHERE [DIMENSIONCODE] = 2 AND [NUM] LIKE 'EL%'"
Case Else
sqlStr = "SELECT [DESCRIPTION],[NUM],[DIMENSIONCODE],[DATAAREAID] FROM [DIMENSIONS]"
End Select
Dim connStr As String = "Server=ARIES;Initial Catalog=AxLive;Integrated Security=SSPI;"
Dim returnString As String() = Nothing
Dim sqlConn As SqlConnection = New SqlConnection(connStr)
Dim SqlCmd As SqlCommand = New SqlCommand(sqlStr, sqlConn)
Dim sqlReader As SqlDataReader
Try
sqlConn.Open()
sqlReader = SqlCmd.ExecuteReader()
ReDim Preserve returnString(0)
Dim count As Integer = 0
Do While sqlReader.Read
ReDim Preserve returnString(UBound(returnString) + 1)
returnString(UBound(returnString)) = sqlReader("DESCRIPTION")
count += 1
Loop
sqlReader.Close()
sqlConn.Close()
Catch ex As Exception
End Try
Return returnString
End Function

this works fine when i test it. ItemAnalysisCode drop down gets it's data from the first method and has the following rule:

when ItemAnalysisCode is not blank, set the querystring for GetPurpose to the value of ItemAnalysisCode, then query using data connection GetPurpose.

Next as per the Url above, I have added an onchange event to ItemAnalysisCode. This is intended to populate the ItemPurposes with the results of the second web method.

Public Sub ItemAnalysisCode_Changed(ByVal sender As Object, ByVal e As XmlEventArgs)
Dim currentRow As XPathNavigator = e.Site()
currentRow.MoveToParent()
Dim getPurpose As XPathNavigator = Me.CreateNavigator()
If getPurpose.SelectSingleNode("/dfs:myFields/dfs:dataFields/tns:GetPurposeResponse/tns:GetPurposeResult/tns:string", Me.NamespaceManager) IsNot Nothing Then
If currentRow.Select("../my:PurposeList/my:ItemPurposes", Me.NamespaceManager).Count > 1 Then
currentRow.SelectSingleNode("../my:PurposeList/my:ItemPurposes[2]", Me.NamespaceManager).DeleteRange(currentRow.SelectSingleNode("../my:PurposeList/my:ItemPurposes[last()]", Me.NamespaceManager))
currentRow.SelectSingleNode("../my:PurposeList/my:ItemPurposes[1]", Me.NamespaceManager).SetValue("")
End If
getPurpose.MoveToNamespace("/dfs:myFields/dfs:dataFields/tns:GetPurposeResponse/tns:GetPurposeResult/tns:string")
For Each purpose As XPathNavigator In getPurpose.Select("/dfs:myFields/dfs:dataFields/tns:GetPurposeResponse/tns:GetPurposeResult/tns:string", Me.NamespaceManager)
If currentRow.SelectSingleNode("../my:PurposeList/my:ItemPurposes", Me.NamespaceManager).Value Is String.Empty Then
currentRow.SelectSingleNode("../my:PurposeList/my:ItemPurposes", Me.NamespaceManager).InnerXml = purpose.InnerXml
Else
Dim newPurpose As XPathNavigator = currentRow.SelectSingleNode("../my:PurposeList/my:ItemPurposes[last()]", Me.NamespaceManager).Clone()
newPurpose.InnerXml = purpose.InnerXml
currentRow.SelectSingleNode("../my:PurposeList/my:ItemPurposes[last()]", Me.NamespaceManager).InsertAfter(newPurpose)
End If
Next
End If
End Sub

finally ItemPurpose is set to lookup its values from ItemPurposes

The problem is that when I debug this code, it seems to fail on the first IF. It is as though the web service is not sending the data, or this code is not picking it up. Like I said though, when I test the web service it works fine. can anyone see what I have done wrong or point me in the right direction? Please please help!