SQL Database Integration

Re: Store query results in an array

adamrharding — Thu, 04 Oct 2007 14:29:42 GMT

Stanton

It is fairly simple to do but to display an array your control needs to be a repeating control or in a repeating table or section. That way it can display multiple results that your query generates.

Any questions feel free to get back to me

I hope this helps as I think your code will work then

Cheers

Adam

Re: Store query results in an array

Stanton Attree — Wed, 20 Jun 2007 09:19:06 GMT

Thanks Clay

OK. I can change the 'where clause' to compare individual dates with dates in the table - I was trying to be efficient by loading all the dates into memory and reducing the number of function calls and queries/network traffic.

Unfortunately 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 the use of office applications to meet business needs (did that sound like a rant?). I can't install any of the recommended software.

I can pull a single row back from a table, 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.

Please don't laugh at my code - I have been teaching myself over the last few weeks. 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

Re: Store query results in an array

Clay Fox — Tue, 19 Jun 2007 17:02:01 GMT

Hey Stanton,

Welcome to the forum.

I have done similar solutions.

You cannot query a secondary data connection other than load all information. Then you have to use filtering to extract just the data you want. So maybe you would say date where holiday name = "New Years" for example. This would return the date from the secondary data source in the row that had the holiday name New Years.

As far as best practices go I would try the Database Accelerator Web Services Suite that you can download here. Qdabra Database Accelerator Suite Version 2.0 - Developer

With this you can use webservices to connect to your data and can actually query your secondary tables. Web services free you from several of the limitations present in Infopath and interacting with Databases. It also includes an active directory web service so you can validate the user of your form. It also provides security so that you can control who sees what data.

A ton of features. You would just need to upgrade from Access to SQL Express, which is free, and have IIS and you would be ready to go.

I have done it with and without and it is much easier and you can create much better solutions using web services.

Store query results in an array

Stanton Attree — Tue, 19 Jun 2007 09:55:12 GMT

I have successfully developed a form linked with an access database. The form is for absence recording and I am looping through a date range excluding non-working days. I have one task remaining which is to retrieve a set of holiday dates from a table and exclude them.

I have set up the secondary connection and the query but I don't know how to query the table so that I can get rows back and store them in an array.

I don't know how to access the data except when it is updated in a form control - I have tried creating a dummy control bound to the table column but I can't seem to get this to work - I don't think this is a very good approach anyway.

Any guidance would be much appreciated.