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!