Searching a data connection's data

[Tim Philomeno]

Is there a good example of searching the data of a connection anywhere?

I have a need to allow a user to type in a value and then I want to validate
it against data used in a dropdown list...I want to validate it using code
and then use the error object to report if the entered value is not found...

Thanks!

Tim Philomeno, Sparling.com

 

[Scott L. Heim [MSFT]]

Hi Tim,

Here are some sample steps:

- Create a new, blank InfoPath form
- Add a data connection to the Customers table from the Northwind database
(Access or SQL Server)
- Add a text box to the form named: txtCustomerID
- Add a button to the form
- In the click event for the button, add this JScript code:

//Get a reference to the Customers data connection
var objCustomersConn = XDocument.GetDOM("Customers");

//Get a reference to the field on the form to enter the CustomerID
var objCustID =
XDocument.DOM.selectSingleNode("//my:myFields/my:txtCustomerID");

//Set the SelectionNamespaces property of the Customers data connection to
walk the DOM
objCustomersConn.setProperty("SelectionNamespaces",

'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSoluti
on" ' +

'xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"'
);

//Attempt to locate a customer with the entered CustomerID
var objFound =
objCustomersConn.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Customers
[@CustomerID = '" + objCustID.text + "']");

//If an invalid CustomerID was entered, display an error
if(objFound == null)
{
XDocument.UI.Alert("Not Found!");
}
//If a valid CustomerID was entered, display the CompanyName
else
{
XDocument.UI.Alert(objFound.selectSingleNode("@CompanyName").text);
}

//Clean up
objCustomersConn = null;
objCustID = null;
objFound = null;

If your secondary data connection is from a database, the
SelectionNamespaces property above should work for you as is.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Tim Philomeno]

Scott...

I got this to work just fine....now....I am using a webservice to fetch my
data and I am trying to adapt you example to reference the WS and not a
simple table...I feel like I am close but I am not sure how to set the
selectSingleNode xpath...can you help? For this example I have hardcoded
"10" into the xpath line...I figured if I could get this working, I could
substitue a var there...

Here is what I have:

---------------------------------------------------------------------------
Dim objStudioConn = thisXDocument.GetDOM("GetStudioDD")

objStudioConn.setProperty("SelectionNamespaces", _

"xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution""" & _
"
xmlns:tns=""http://tempuri.org/SparSysService/SparSysDAM""" & _
" xmlns:ns1=""http://www.tempuri.org/dsGetStudioDD.xsd""")

Dim xStr As String =
"/dfs:myFields/dfs:dataFields/tns:GetStudioDDResponse/tns:GetStudioDDResult/ns1:dsGetStudioDD/ns1:dbo_uspSYS_GetStudio_DD/Value[@Value=""01""]"

Dim objFound = objStudioConn.selectSingleNode(xStr)

If objFound Is Nothing Then
thisXDocument.UI.Alert("Not Found!")
Else

thisXDocument.UI.Alert(objFound.selectSingleNode("@Descr").text)
End If
---------------------------------------------------------------------------

Hi Tim,

Here are some sample steps:

- Create a new, blank InfoPath form
- Add a data connection to the Customers table from the Northwind database
(Access or SQL Server)
- Add a text box to the form named: txtCustomerID
- Add a button to the form
- In the click event for the button, add this JScript code:

//Get a reference to the Customers data connection
var objCustomersConn = XDocument.GetDOM("Customers");

//Get a reference to the field on the form to enter the CustomerID
var objCustID =
XDocument.DOM.selectSingleNode("//my:myFields/my:txtCustomerID");

//Set the SelectionNamespaces property of the Customers data connection to
walk the DOM
objCustomersConn.setProperty("SelectionNamespaces",

'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSoluti
on" ' +

'xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"'
);

//Attempt to locate a customer with the entered CustomerID
var objFound =
objCustomersConn.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Customers
[@CustomerID = '" + objCustID.text + "']");

//If an invalid CustomerID was entered, display an error
if(objFound == null)
{
XDocument.UI.Alert("Not Found!");
}
//If a valid CustomerID was entered, display the CompanyName
else
{
XDocument.UI.Alert(objFound.selectSingleNode("@CompanyName").text);
}

//Clean up
objCustomersConn = null;
objCustID = null;
objFound = null;

If your secondary data connection is from a database, the
SelectionNamespaces property above should work for you as is.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights

 

[Scott L. Heim [MSFT]]

Hi Tim,

Ah the web service...this gets a "bit" more complicated! <G> A web service
(as you have seen) will return different namespaces and obviously these
have to be incorporated in the "setProperty" line but also where to use
which one in the selectSingleNode call.

Let me give you an example of what I mean by this. In my web service, I
return the following namespaces:

dfs
tns
and a blank namespace!

The only way I was able to determine this was to set a break point in my
code (use the word: debugger and then execute the following line of code
in the Immediate window:

?objCustomersConn.selectSingleNode("//dfs:myFields").xml

I pasted this into Notepad and found my "Customers" table (which I had
specified as the table name when I filled my dataset in the web service
code) had a blank namespace. This appeared as follows in the XML I was
reviewing in Notepad:

<NewDataSet xmlns=\"\"><Customers diffgr:id=\"Customers1\"
msdata:rowOrder=\"0\">

As you can see, there is no namespace prefix here. So in order for me to
get the node I was looking for, my selectSingleNode line now reads as
follows:

var objFound =
objCustomersConn.selectSingleNode("//dfs:myFields/dfs:dataFields/tns:GetCust
omersResponse/tns:GetCustomersResult//Customers[CustomerID = '" +
objCustID.text + "']");

If you notice above, between tns:GetCustomersResult and Customers there are
2 slashes "/" - the second is for the empty namespace!

Unfortunately not knowing the XML that your web service returns I cannot
pinpoint the exact issue but with the information above, hopefully it will
help you isolate the problem.

For your reference, here is my entire code to test against my web service
secondary data source:

//Get a reference to the GetCustomers WS data connection
var objCustomersConn = XDocument.GetDOM("GetCustomers");

//Get a reference to the field on the form to enter the CustomerID
var objCustID =
XDocument.DOM.selectSingleNode("//my:myFields/my:txtCustomerID");

//Set the SelectionNamespaces property of the GetCustomers data connection
to walk the DOM
objCustomersConn.setProperty("SelectionNamespaces",

'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSoluti
on" ' +

'xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"
' +
'xmlns:tns="http://tempuri.org/NWTables/Service1"' );

//Attempt to locate a customer with the entered CustomerID
var objFound =
objCustomersConn.selectSingleNode("//dfs:myFields/dfs:dataFields/tns:GetCust
omersResponse/tns:GetCustomersResult//Customers[CustomerID = '" +
objCustID.text + "']");

//If an invalid CustomerID was entered, display an error
if(objFound == null)
{
XDocument.UI.Alert("Not Found!");
}
//If a valid CustomerID was entered, display the CompanyName
else
{
XDocument.UI.Alert(objFound.selectSingleNode("CompanyName").text);
}

//Clean up
objCustomersConn = null;
objCustID = null;
objFound = null;

I hope something here helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Tim Philomeno]

Scott,

That did it!!!

After I followed the prefixes all the way to the data, as you demostrated, I
was able to get this to work..

I wish I could find a good "primer" on how to read the xpath stuff, what
it's all used for, and how to apply it to Infopath....

This tip you just showed me is a good example of how to "teach me to Fish"
instead of "giving me the Fish"...

Thank You!!

Hi Tim,

Ah the web service...this gets a "bit" more complicated! <G> A web service
(as you have seen) will return different namespaces and obviously these
have to be incorporated in the "setProperty" line but also where to use
which one in the selectSingleNode call.

Let me give you an example of what I mean by this. In my web service, I
return the following namespaces:

dfs
tns
and a blank namespace!

The only way I was able to determine this was to set a break point in my
code (use the word: debugger and then execute the following line of code
in the Immediate window:

?objCustomersConn.selectSingleNode("//dfs:myFields").xml

I pasted this into Notepad and found my "Customers" table (which I had
specified as the table name when I filled my dataset in the web service
code) had a blank namespace. This appeared as follows in the XML I was
reviewing in Notepad:

<NewDataSet xmlns=\"\"><Customers diffgr:id=\"Customers1\"
msdata:rowOrder=\"0\">

As you can see, there is no namespace prefix here. So in order for me to
get the node I was looking for, my selectSingleNode line now reads as
follows:

var objFound =
objCustomersConn.selectSingleNode("//dfs:myFields/dfs:dataFields/tns:GetCust
omersResponse/tns:GetCustomersResult//Customers[CustomerID = '" +
objCustID.text + "']");

If you notice above, between tns:GetCustomersResult and Customers there are
2 slashes "/" - the second is for the empty namespace!

Unfortunately not knowing the XML that your web service returns I cannot
pinpoint the exact issue but with the information above, hopefully it will
help you isolate the problem.

For your reference, here is my entire code to test against my web service
secondary data source:

//Get a reference to the GetCustomers WS data connection
var objCustomersConn = XDocument.GetDOM("GetCustomers");

//Get a reference to the field on the form to enter the CustomerID
var objCustID =
XDocument.DOM.selectSingleNode("//my:myFields/my:txtCustomerID");

//Set the SelectionNamespaces property of the GetCustomers data connection
to walk the DOM
objCustomersConn.setProperty("SelectionNamespaces",

'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSoluti
on" ' +

'xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"
' +
'xmlns:tns="http://tempuri.org/NWTables/Service1"' );

//Attempt to locate a customer with the entered CustomerID
var objFound =
objCustomersConn.selectSingleNode("//dfs:myFields/dfs:dataFields/tns:GetCust
omersResponse/tns:GetCustomersResult//Customers[CustomerID = '" +
objCustID.text + "']");

//If an invalid CustomerID was entered, display an error
if(objFound == null)
{
XDocument.UI.Alert("Not Found!");
}
//If a valid CustomerID was entered, display the CompanyName
else
{
XDocument.UI.Alert(objFound.selectSingleNode("CompanyName").text);
}

//Clean up
objCustomersConn = null;
objCustID = null;
objFound = null;

I hope something here helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights

 

[Scott L. Heim [MSFT]]

That's great Tim - thank you for letting me know!

Take care,

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Steve]

I'm just starting out with InfoPath, and I couldn't figure out how to "Add a
text box to the form named: txtCustomerID." I tried menu->insert->text box,
and InfoPath didn't give me the option to name the text box. Instead it
asked for a field to associate with the text box, so I choose CustomerID but
that didn't help.

How do I create a named text box in the form?

 

[Paresh]

I'm just starting out with InfoPath, and I couldn't figure out how to "Adda
text box to the form named: txtCustomerID."  I tried menu->insert->text box,
and InfoPath didn't give me the option to name the text box.  Instead it
asked for a field to associate with the text box, so I choose CustomerID but
that didn't help.  

How do I create a named text box in the form?

:

Hi Tim,

Here are some sample steps:

- Create a new, blank InfoPath form
- Add a data connection to the Customers table from the Northwind database
(Access or SQL Server)
- Add a text box to the form named: txtCustomerID
- Add a button to the form
- In the click event for the button, add this JScript code:

   //Get a reference to the Customers data connection
   var objCustomersConn = XDocument.GetDOM("Customers");

   //Get a reference to the field on the form to enter the CustomerID
   var objCustID =
XDocument.DOM.selectSingleNode("//my:myFields/my:txtCustomerID");

   //Set the SelectionNamespaces property of the Customers data connection to
walk the DOM
   objCustomersConn.setProperty("SelectionNamespaces",

'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSoluti
on" ' +

'xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"'
);

   //Attempt to locate a customer with the entered CustomerID
           var objFound =
objCustomersConn.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Customer­s
[@CustomerID = '" + objCustID.text + "']");

           //If an invalid CustomerID was entered, display an error
           if(objFound == null)
           {
                   XDocument.UI.Alert("Not Found!");
           }
           //If a valid CustomerID was entered, display the CompanyName
           else
           {
                   XDocument.UI.Alert(objFound.selectSingleNode("@CompanyName").text);
           }

   //Clean up
   objCustomersConn = null;
   objCustID = null;
   objFound = null;

If your secondary data connection is from a database, the
SelectionNamespaces property above should work for you as is.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights- Hide quoted text -

- Show quoted text -

I guess, you are not trying to add a textbox in a blank template.
Rather, you are adding the textbox in a template whose datasource is
already created / present. Thats why, it is asking you for
association of this textbox with the fields in the datasource.

Do let me know if you need any further info,

Thanks,
Paresh