SQL Database Integration

Re: Storing query result in a variable

Greg Collins — Fri, 20 Jul 2007 20:59:37 GMT

Great! Glad your on the right track! Good luck!

Re: Storing query result in a variable

Stanton Attree — Fri, 20 Jul 2007 10:43:30 GMT

Thanks for your persistance Greg.

The namespaces are OK. I've tracked it down to my modified SQL command for the secondary data source. I am attempting to select on a date incorrectly somehow and this is causing the DOM to become invalid. If I select manually on the ID (numeric) field in the SQL, everything is OK.

I will endeavor to discover how infopath wants the SQL where clause to look when selecting a date on a date column.

Re: Storing query result in a variable

Greg Collins — Thu, 19 Jul 2007 23:55:39 GMT

The data source task pane unfortunately does not show all the namespaces. If you are using InfoPath 2007, a useful new feature I requested was implemented by the IP dev team which allows you to right-click on the field of interest and copy the complete XPath to that field to the clipboard. If you are using InfoPath 2003, then you should extract your form files, open the HolidayCalendar.xml file with a text editor, and disect that to pull out the appropriate namespaces for each node in your XPath.

Using the full set of namespaces from the main DOM is good as it will ensure that you are totally covered since it incorporates all namespaces for every DOM in the form template.

Re: Storing query result in a variable

Stanton Attree — Thu, 19 Jul 2007 10:35:58 GMT

Thanks again Greg.

I am suspecting that this might be a lack of understanding on my part of namespaces for secondary data sources. I have set the SelectionNamespaces to the same as the main data source. See code below:

XDocument.DOM.setProperty "SelectionNamespaces", "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:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-06-06T12:26:30"" xmlns:xd=""http://schemas.microsoft.com/office/infopath/2003"""

XDocument.GetDOM("HolidayCalendar").setProperty "SelectionNamespaces", "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:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-06-06T12:26:30"" xmlns:xd=""http://schemas.microsoft.com/office/infopath/2003"""

The Data Source Window shows the following:

- myFields

- dataFields

- d:HolidayCalendar

:ID

:BankHolidayDate

Have I got something wrong? When I click on the details of the data sources, the namespace information is the same as the main data source.

I only seem to be able to access data that is stored in the main data source.

Any help is much appreciated.

Re: Storing query result in a variable

Greg Collins — Wed, 18 Jul 2007 20:11:49 GMT

Verify that uCalendarDataSource is actually a valid object.
Get just the object without trying to access the .text property:
uHolidayDate = uCalendarDataSource.selectSingleNode("/dfs:myFields/dfs:dataFields/d:HolidayCalendar/@BankHolidayDate")
Check whether uHolidayDate is a valid object.

My guess is that uHolidayDate is not valid because something is not right in the XPath.

Re: Storing query result in a variable

Stanton Attree — Wed, 18 Jul 2007 10:48:26 GMT

Thanks Greg

Changed to using GetDom("HolidayCalendar") instead of DataObjects("HolidayCalendar").DOM but still getting an object required error when using selectSingleNode? I don't know why I can't get data into a variable from a secondard data source - it's frustrating and mystifying.

I've tried putting the field on the form and seen it update with the first row in the table, but still getting an object required error.

Either I am doing something wrong or infopath doesn't allow this?

Re: Storing query result in a variable

Greg Collins — Mon, 02 Jul 2007 16:26:59 GMT

You should be using GetDOM() rather than DataObjects() in the line two above the red one.

Storing query result in a variable

Stanton Attree — Wed, 27 Jun 2007 08:09:26 GMT

I have set up a query so that I pull a single row back from a table (secondary data source), but I don't know how to assign the value to a variable.

Do I have to bind a form field to a column in the database table and then retrieve the value from the field? I have tried this approach but I am getting an "Object Required" error when trying to get the value from the form field (see function code below). Similar code works elsewhere in the form - maybe there is something wrong with the form field? It is a text box bound to the table in question.

I work for an organisation that has locked-down the PC so that no applications can be installed that are not part of the corporate strategy - hence I can't install any of the intermediate software to simplify things - I have to use infopath and access as they are.

The 'object required' error occurs on the red line below. I hope there is an obvious mistake that can be addressed.

Thanks in advance.

Function IsBankHoliday(uSicknessDate)

Dim uCalendarDataSource

Dim uHolidayDate

Dim uOriginalCommand

Dim uSingleRowCommand

' Modify the SQL command

uOriginalCommand = xDocument.DataAdapters("HolidayCalendar").Command

uSingleRowCommand = uOriginalCommand + " where [BankHolidayDate] = " + FormatDateTime(uSicknessDate, 2)

' Query the table with the the single row where clause and then reset the command

XDocument.DataAdapters("HolidayCalendar").Command = uSingleRowCommand

XDocument.DataAdapters("HolidayCalendar").Query()

XDocument.DataAdapters("HolidayCalendar").Command = uOriginalCommand

Set uCalendarDataSource = xDocument.DataObjects("HolidayCalendar").DOM

uCalendarDataSource.setProperty "SelectionNamespaces", "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:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-06-06T12:26:30"" xmlns:xd=""http://schemas.microsoft.com/office/infopath/2003"""

uHolidayDate = uCalendarDataSource.selectSingleNode("/dfs:myFields/dfs:dataFields/d:HolidayCalendar/@BankHolidayDate").text

If Len(uHolidayDate) > 0 Then

IsBankHoliday = True

Else

IsBankHoliday = False

End If

End Function