SQL Database Integration

Re: Autonumbering in Infopath

slslusher — Wed, 01 Nov 2006 16:51:39 GMT

I've tried using this code in developing a form with an autonumbering field. I created an SQL DB table, with one field (FormID).
I'm getting and error when I try to preview and test this code.

"An item with the following index does not exist in the collection: qryGetFormID
File:script.js
Line:38"

I can't seem to find anything on this error to help me correct it.
Any suggestions? Thanks
Sherry

Re: Autonumbering in Infopath

Patrick Halstead — Thu, 09 Feb 2006 00:59:58 GMT

The .DOM. in the first line is the only difference.

Patrick Halstead [InfoPath MVP]
InfoPathDev

Re: Autonumbering in Infopath

Agni Jonnalagadda — Wed, 08 Feb 2006 08:55:17 GMT

objDataObject.DataObjects("qryGetTicketNumber").setProperty("SelectionNamespaces",
'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');

Should be

objDataObject.DataObjects("qryGetTicketNumber").DOM.setProperty("SelectionNamespaces",
'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');

Agni
InfoPath Dev India

Re: Autonumbering in Infopath

lisaspencer — Tue, 07 Feb 2006 21:49:40 GMT

I've been working on something else, sorry I'm so late.

function XDocument::OnLoad(eventObj)
{
if (XDocument.IsNew)
{
var objDataObject;
// Set a reference to the specified data object, have to use DataObjects because qryGetTicketNumber is a secondary data source.
//Run the query
objDataObject = XDocument.DataObjects("qryGetTicketNumber").Query;
objDataObject.DataObjects("qryGetTicketNumber").setProperty("SelectionNamespaces",
'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"
xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"');
XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:TicketData/@ticketNumber").nodeTypedValue = lastTicketNumUsed;
var lastTicketNumUsed = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:qryGetTicketNumber/@FirstofTicketID").nodeTypedValue;
lastTicketNumUsed = lastTicketNumUsed + 1;
XDocument.DOM.selectSingleNode( "/dfs:myFields/my:TicketNumber" ).text = XDocument.DOM.selectSingleNode( "/dfs:myFields/dfs:dataFields/d:TicketData/@TicketNumber" ).text;
}
}

Lisa Spencer
Programmer/Office Specialist

Re: Autonumbering in Infopath

ForumAdmin — Fri, 03 Feb 2006 02:14:26 GMT

Hi again Lisa,
Can you paste in your latest code so we can check it out?

Re: Autonumbering in Infopath

lisaspencer — Tue, 31 Jan 2006 14:54:18 GMT

I'm getting an "object doesn't support this property or method" error now.

Lisa Spencer
Programmer/Office Specialist

Re: Autonumbering in Infopath

Shiva — Tue, 31 Jan 2006 09:14:26 GMT

Hi Lisa Spencer,

Before selecting the node you have to set the property or namespace of "dfs" as below mentioned. You get the “dfs” prefix namespace from your “qryGetTicketNumber.xml”.

objDataObject.GetDOM("qryGetTicketNumber").setProperty("SelectionNamespaces", 'xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"');

I Hope this helps you.

Shiva
InfoPath Dev
INDIA

Re: Autonumbering in Infopath

Coita Thierry — Tue, 31 Jan 2006 08:30:57 GMT

Hi,

you need to make a:

.setProperty("SelectionNamespaces", "xmlns:dfs=\"http://schemas.microsoft.com/office/infopath/2003/dataFormSolution\"");

On your object.

Re: Autonumbering in Infopath

lisaspencer — Mon, 30 Jan 2006 15:58:30 GMT

I'm still working on it!

var objDataObject;
objDataObject = XDocument.DataObjects("qryGetTicketNumber");
XDocument.DataObjects("qryGetTicketNumber").Query();
XDocument.DOM.selectSingleNode( "/dfs:myFields/my:TicketNumber" ).text = XDocument.DOM.selectSingleNode ("/dfs:myFields/dfs:dataFields/d:TicketData/@TicketNumber" ).text;

I'm getting a "reference to undeclared namespace prefix:'dfs'" error. Any help is greatly appreciated!

Lisa Spencer
Programmer/Office Specialist

Re: Autonumbering in Infopath

Matt Faus — Thu, 19 Jan 2006 22:59:46 GMT

This XPath will access a ticketID, but you must specify which one you want. To use some of the SQL stuff that I suggested above check out the following code:

XDocument.QueryAdapter.Command = "SELECT CustomerID FROM Customers ORDER BY CustomerID DESC";
XDocument.Query();

XDocument.DOM.selectSingleNode( "/dfs:myFields/my:lastID" ).text = XDocument.DOM.selectSingleNode( "/dfs:myFields/dfs:dataFields/d:Customers/@CustomerID" ).text;

NOTE: The above code was something that I created after designing a new blank form with a connection to NWind.mdb (the Microsoft sample database). You will have to edit the XPath statements and the SQL statement to match your solution.

Once the above code is run it will set the helper node (my:lastID) to the ID that returns last with a natural ordering. In you case this will be the largest number.

I'm sorry for suggesting the LIMIT clause, it seems that Microsoft Jet SQL doesn't allow this. However, you could use a WHERE clause to achieve something similiar. The main reason for using a LIMIT clause is performance reasons. See the following link for further reference:
http://office.microsoft.com/en-us/assistance/HP010322871033.aspx

I hope this helps, if you want to e-mail me your e-mail address (the forum hides actual e-mail addresses from us) I will mail you the .xsn I just created so you can use it as an example.

---------------
Matt Faus
InfoPath Dev Texas

Re: Autonumbering in Infopath

lisaspencer — Thu, 19 Jan 2006 21:50:38 GMT

I have bought the "Programming in Infopath" book. It just takes so much
time to read a book, I'm just trying to get this one automatic field
done now. No, the error is when I am trying to set the variable to the
last ticket number in the database. I am trying to reference the database.
I have var lastTicketNumUsed = Document.DOM.selectSingleNode "/dfs:myFields/dfs:dataFields/d:qryGetTicketNumber/@FirstofTicketID" ).nodeTypedValue;
If I am way off, I think I'll just stop and read the book!

Lisa Spencer
Programmer/Office Specialist

Re: Autonumbering in Infopath

Matt Faus — Thu, 19 Jan 2006 21:38:39 GMT

I certainly do not mind answering questions, I just thought you might get faster and/or better response from a book or other refined resource. I spend maybe 2 minutes on these replies but people have poured over books for months and months to make them as easy-to-understand as possible and contain the best material.

If you are trying to access the contents of the expression box, you cannot do this. If you need to reference whatever an expression box stores you will have to add a new node to the schema.

And again, if you have any more questions please let us know! Good luck!

---------------
Matt Faus
InfoPath Dev Texas

Re: Autonumbering in Infopath

lisaspencer — Thu, 19 Jan 2006 21:22:16 GMT

Was that a hint not to ask anymore questions? LOL
Do you know how to resolve the error?

Lisa Spencer
Programmer/Office Specialist

Re: Autonumbering in Infopath

Matt Faus — Thu, 19 Jan 2006 20:19:40 GMT

You cannot access the contents of expression boxes (except with your eyes, haha). You will have to create a new node to store the content if you would like to reference it programmatically. With this XPath the node would have to be in the Access database. I suggest adding a helper node within Infopath to store the previous, current, and next IDs.

Add a helper node:
1) Right-click myFields and choose Add
2) Name the field correctly and choose the data type
3) Click OK.

Also, if you find yourself becoming too frustrated Autonomy Systems provides professional form consulting services to help you get going in the right direction.http://infopathdev.com/about/default.aspx . Our very own Patrick Halstead has even written a book titled "Developing Solutions with Microsoft Infopath". A link to this book can be found on the left sidebar of this page.

---------------
Matt Faus
InfoPath Dev Texas

Re: Autonumbering in Infopath

lisaspencer — Thu, 19 Jan 2006 19:57:45 GMT

Can I do an XPath to the Expression box? Would it be something like this?
/dfs:myFields/dfs:dataFields/d:qryGetTicketNumber/@FirstofTicketID I get
an error-invalid location path.Thanks,

Lisa Spencer
Programmer/Office Specialist