SQL Database Integration

Re: Submit only certain records to a database

.mond — Thu, 22 Jun 2006 13:49:00 GMT

Ok i think i've solved my own problem (haven't thoroughly tested it but it seems to work so far):


function XDocument::OnSubmitRequest(eventObj)
{
	// If the submit operation is successful, set
	// eventObj.ReturnStatus = true;
	
	var objDOMNode; // stores a node in the DOM
	var objNamedNodeMap; // stores a collection of node attributes 
	var objADOAdapter; // data adapter used to connect to the database
	var objDOMNodeList; // stores a list of DOM nodes
	var strSQLInsert; // stores an SQL insert statement
	var objDBConn; // connection to the MS SQL database
		
	// create a DOM Node List to put all the author nodes into
	try
	{
		objDOMNodeList = XDocument.DOM.selectNodes("/dfs:myFields/dfs:dataFields//d:authors2");
	}
	catch(ex)
	{
		XDocument.UI.Alert("Could not create XMLDOMNodeList object.\r\n" 
							+ ex.number + " - " + ex.description);
		// Return with eventObj.ReturnStatus == false (the default value) 
		return;
	}
	
	// get the ADO data adapter for the database connection
	objADOAdapter = XDocument.QueryAdapter;
	
	// create a new database connection
	objDBConn = new ActiveXObject("ADODB.Connection");
	
	// get the connection string
	objDBConn.ConnectionString = objADOAdapter.Connection;
	
	// open the connection
	objDBConn.Open();
		
	// loop through all the records to find the ones that have the resubmit field checked
	while( objDOMNode = objDOMNodeList.nextNode() )
	{
		// get the attribute list for the current DOM Node
		objNamedNodeMap = objDOMNode.attributes; 
		
		// <DEBUGGING>	
		//XDocument.UI.Alert("objNamedNodeMap.getNamedItem(\"submit\"): " + objNamedNodeMap.getNamedItem("submit").xml);
		// </DEBUGGING>
		
		// Filter out the records that are to be submitted to the database
		if( objNamedNodeMap.getNamedItem("submit").nodeValue == "True" )
		{
			// store the attributes of the current node
			ln = objNamedNodeMap.getNamedItem("lname").nodeValue;
			fn = objNamedNodeMap.getNamedItem("fname").nodeValue;		
			addr = objNamedNodeMap.getNamedItem("address").nodeValue;
			// if the string value is False assign 0 to avoid conversion problems when submitting to the database
			contract = ( (objNamedNodeMap.getNamedItem("contract").nodeValue == "False")? 0 : 1 );
			// always set to 0 so that when queries are run none of the records will have a check mark in the submit field
			// which could lead to new records being submitted by mistake
			submit = 0;
											
			strSQLInsert = "INSERT INTO authors2(lname,fname,address,contract,submit) VALUES(" + 
							"'"+ln+"'" + "," + "'"+fn+"'" + "," + "'"+addr+"'" + "," + contract + "," + submit + ")";
						
			//strSQLInsert = "execute addNewRecord " + "'"+ln+"'" + "," + "'"+fn+"'" + "," + "'"+addr+"'" + "," + contract + "," + submit;
			
			objADOAdapter.Command = strSQLInsert;
			
			// <DEBUGGING>	
			XDocument.UI.Alert("SQL command: " + objADOAdapter.Command);
			// </DEBUGGING>
			
			try
			{
				// query the data source
				objDBConn.Execute(strSQLInsert);
			}
			catch(ex)
			{
				XDocument.UI.Alert("Error executing insert query.\r\n" 
							+ ex.number + " - " + ex.description);
				// Return with eventObj.ReturnStatus == false (the default value) 
				return;	
			}
		} // END-IF
	} // END-WHILE
	
	// close the database connection
	objDBConn.Close();
	
	// set eventObj.ReturnStatus = true 
	eventObj.ReturnStatus = true;
	
	XDocument.UI.Alert("Submit was successful!");
}

The only thing i haven't gotten it to do is after submitting to open a new blank form like how you can specify this in the form submit options dialog (without using code).

Re: Submit only certain records to a database

gianca — Thu, 22 Jun 2006 09:59:42 GMT

I'm interesting on this topic too.

Re: Submit only certain records to a database

.mond — Fri, 16 Jun 2006 15:00:13 GMT

Hi again,

I would like to ask something in relation to my original post: how can i use JScript code to customize my submit button so that only records that have a tick in their checkbox field will be submitted to the database.

I'm assuming i have to use the ADO adapter in some way but i'm not sure how. Here's my ADO adapter object and my author object (contains a record to be submitted to the database)


// get the ADO data adapter for the database connection
objADOAdapter = XDocument.QueryAdapter;

// create a new Author object with the stored attributes
author = new Author(id,ln,fn,phone,addr,city,state,zip,contract,submit);

Any ideas?

Re: Submit only certain records to a database

Matt Faus — Wed, 14 Jun 2006 01:28:24 GMT

Congratulations on getting everything working!

Re: Submit only certain records to a database

.mond — Tue, 13 Jun 2006 16:14:25 GMT

ok, problem fixed, the final xpath is used is
"/dfs:myFields/dfs:dataFields/d:authors/@au_id"
(i changed it to access the author id this time)

I am now able to access the data.

Thanks for your help

Re: Submit only certain records to a database

.mond — Tue, 13 Jun 2006 14:19:18 GMT

Hey Matt,
I tried that but i still got the same error. I have no idea what the problem could be.

*edit:
ok i captured the xml after i run the query here it is:

quote:

<?xml version="1.0"?>
<?mso-infoPathSolution productVersion="11.0.6565" PIVersion="1.0.0.0"
href="file:///C:\Documents%20and%20Settings\raymond\Local%20SettingsApplication%20Data\Microsoft\InfoPath\Designer\58efc9fde1a0483c\manifest.xsf" solutionVersion="1.0.0.28" initialView="View 1" ?>
<?mso-application progid="InfoPath.Document"?>

<dfs:myFields 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/2006-06-06T19:29:15" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-us" xmlns:xdado="http://schemas.microsoft.com/office/infopath/2003/adomapping">

<dfs:queryFields>
<q:authors au_id="" au_lname="" au_fname="" phone="" address="" city="" state="" zip="" contract="False" addToDbase="">
</q:authors>
</dfs:queryFields>

<dfs:dataFields>
<d:authors au_id="341-22-1782" au_lname="Smith" au_fname="Meander" phone="913 843-0462" address="10 Mississippi Dr."
city="Lawrence" state="KS" zip="66044" contract="False" addToDbase="" xdado:ORrJv="0"/>
<d:authors au_id="527-72-3246" au_lname="Greene" au_fname="Morningstar" phone="615 297-2723"
address="22 Graybar House Rd." city="Nashville" state="TN" zip="37215" contract="False" addToDbase="" xdado:ORrJv="1"/>
<d:authors au_id="724-08-9931" au_lname="Stringer" au_fname="Dirk" phone="415 843-2991" address="5420 Telegraph Av."
city="Oakland" state="CA" zip="94609" contract="False" addToDbase="" xdado:ORrJv="2"/>
<d:authors au_id="893-72-1158" au_lname="McBadden" au_fname="Heather" phone="707 448-4982" address="301 Putnam"
city="Vacaville" state="CA" zip="95688" contract="False" addToDbase="" xdado:ORrJv="3"/>
</dfs:dataFields>

<my:xml_display>
</my:xml_display>

<xdado:originalData IdCount="4">
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
<s:Schema id="RowsetSchema">
<s:ElementType content="eltOnly" rs:updatable="true" name="authors">
<s:AttributeType rs:number="1" rs:writeunknown="true" rs:basecatalog="pubs" rs:baseschema="dbo" rs:basetable="authors"
rs:basecolumn="au_id" rs:keycolumn="true" name="au_id">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="11" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType rs:number="2" rs:writeunknown="true" rs:basecatalog="pubs" rs:baseschema="dbo" rs:basetable="authors"
rs:basecolumn="au_lname" name="au_lname"><s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="40" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType rs:number="3" rs:writeunknown="true" rs:basecatalog="pubs" rs:baseschema="dbo" rs:basetable="authors"
rs:basecolumn="au_fname" name="au_fname"><s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="20" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType rs:number="4" rs:writeunknown="true" rs:basecatalog="pubs" rs:baseschema="dbo" rs:basetable="authors"
rs:basecolumn="phone" name="phone">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="12" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType>
<s:AttributeType rs:number="5" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="pubs" rs:baseschema="dbo"
rs:basetable="authors" rs:basecolumn="address" name="address">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="40"/>
</s:AttributeType>
<s:AttributeType rs:number="6" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="pubs" rs:baseschema="dbo"
rs:basetable="authors" rs:basecolumn="city" name="city">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="20"/>
</s:AttributeType>
<s:AttributeType rs:number="7" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="pubs" rs:baseschema="dbo"
rs:basetable="authors" rs:basecolumn="state" name="state">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="2" rs:fixedlength="true"/>
</s:AttributeType><s:AttributeType rs:number="8" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="pubs"
rs:baseschema="dbo" rs:basetable="authors" rs:basecolumn="zip" name="zip">
<s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="5" rs:fixedlength="true"/>
</s:AttributeType><s:AttributeType rs:number="9" rs:writeunknown="true" rs:basecatalog="pubs" rs:baseschema="dbo"
rs:basetable="authors" rs:basecolumn="contract" name="contract">
<s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true" rs:maybenull="false"/>
</s:AttributeType><s:AttributeType rs:number="10" rs:nullable="true" rs:writeunknown="true" rs:basecatalog="pubs"
rs:baseschema="dbo" rs:basetable="authors" rs:basecolumn="addToDbase" name="addToDbase">
<s:datatype dt:type="boolean" dt:maxLength="2" rs:fixedlength="true"/>
</s:AttributeType><s:extends type="rs:rowbase"/>
</s:ElementType>
</s:Schema>
<rs:data>
<authors au_id="341-22-1782" au_lname="Smith" au_fname="Meander" phone="913 843-0462" address="10 Mississippi Dr."
city="Lawrence" state="KS" zip="66044" contract="False" xdado:ORrJv="0"/><authors au_id="527-72-3246" au_lname="Greene"
au_fname="Morningstar" phone="615 297-2723" address="22 Graybar House Rd." city="Nashville" state="TN" zip="37215"
contract="False" xdado:ORrJv="1"/>
<authors au_id="724-08-9931" au_lname="Stringer" au_fname="Dirk" phone="415 843-2991" address="5420 Telegraph Av."
city="Oakland" state="CA" zip="94609" contract="False" xdado:ORrJv="2"/>
<authors au_id="893-72-1158" au_lname="McBadden" au_fname="Heather" phone="707 448-4982" address="301 Putnam"
city="Vacaville" state="CA" zip="95688" contract="False" xdado:ORrJv="3"/>
</rs:data>
</xml>
</xdado:originalData>
</dfs:myFields>

Now i changed my xpath to: "/dfs:myFields/dfs:dataFields/d:authors[@au_fname]" and i don't get the error anymore but in the alert nothing is displayed i.e. the prompt comes up: "au_fname: "

I was thinking maybe i need to modify the xpath a bit more what do you think?

In the meantime i'll keep fiddling around some more.

Re: Submit only certain records to a database

Matt Faus — Mon, 12 Jun 2006 15:41:08 GMT

I think you want your XPath to be this:

q:authors/@au_fname

Re: Submit only certain records to a database

.mond — Mon, 12 Jun 2006 14:02:20 GMT

thanks for the help Matt, but i am getting an error: "Object required" on this line


// display the data in the node
XDocument.UI.Alert("au_fname: " + objDOMNode.text);

i'm assuming that the line before isn't getting the content correctly:


// store the first name node
objDOMNode = XDocument.DOM.selectSingleNode("authors[@au_fname]");

here's my template.xml for reference:


<?xml version="1.0" encoding="UTF-8"?>
<?mso-infoPathSolution name="urn:schemas-microsoft-
com:office:infopath:Authors:-dataFormSolution" href="manifest.xsf"
solutionVersion="1.0.0.19" initialView="View 1"
productVersion="11.0.6565" PIVersion="1.0.0.0" ?>
<?mso-application progid="InfoPath.Document"?>
<dfs:myFields 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/2006-06-06T19:29:15"
xmlns:xd="http://schemas.microsoft.com/office/infopath/2003">
<dfs:queryFields>
  <q:authors au_id="" au_lname="" au_fname="" phone="" address=""  
             city="" state="" zip="" contract="" addToDbase="">
  </q:authors>
</dfs:queryFields>
<dfs:dataFields>
  <d:authors au_id="" au_lname="" au_fname="" phone="" address=""  
             city="" state="" zip="" contract="" addToDbase="">
  </d:authors>
</dfs:dataFields>
</dfs:myFields>

any idea what the prob is?

Re: Submit only certain records to a database

Matt Faus — Sun, 11 Jun 2006 03:43:09 GMT

You've got the object, but now you've got to get the content, try this:

XDocument.UI.Alert("au_fname: " + objDOMNode.text);

Re: Submit only certain records to a database

.mond — Sat, 10 Jun 2006 19:17:36 GMT

Hey Patrick, i don't really have any code as yet that is what i am trying to get assistance with.
Right now i am trying to see if i can at least access the data from in the code like so. This code is in the OnClick method for my submit button:


...
var objDOMNode;

objDOMNode = XDocument.DOM.selectSingleNode("authors[@au_fname]");
XDocument.UI.Alert("au_fname: " + objDOMNode);

I wrote the code above to see if i could access the data source - here i am trying to display the author first name (au_fname) but nothing is displayed.

Re: Submit only certain records to a database

Patrick Halstead — Sat, 10 Jun 2006 08:48:18 GMT

Hi .Mond and welcome to our forum!
How about writing some code?

Submit only certain records to a database

.mond — Wed, 07 Jun 2006 17:23:57 GMT

Hi,

I am new to infopath and I have a form which is connected to the sample MS SQL database 'pubs' using the table 'Authors' for testing purposes.

I added another field to the table called 'submitToDbase' which is a bit field that specifies if a record will be submitted to the database or not. Now, what will happen is that records will be pulled from the database table and then some of them will be edited/updated and these ones will have the 'submitToDbase' option set true (by way of a checkbox control on the form).

Now i know i have to go through the records to see which ones are checked but then how do i submit only those checked ones back to the database as new records i.e. not just updating/overwriting the previous information but adding it in as a new record?