InfoPath Dev

Hey all, I'm trying to check a SQL database for an existing value (the primary key) and return whether it is there or not. The query I am using is listed below:

function CTRL415_7::OnClick(eventObj)
{
/*
//WORKS BUT CANNOT RETURN VALUE
oConn = new ActiveXObject("ADODB.Connection");
oConn.ConnectionString = XDocument.QueryAdapter.Connection;
oConn.ConnectionTimeout = XDocument.QueryAdapter.Timeout;
oConn.Open();
oConn.Execute("EXEC dbo.Test");
*/

	// Get the default SQL command for the form.
  var strOrigSQLCommand = XDocument.QueryAdapter.Command;

  //Set the value of the datafield.
  var queryName = XDocument.DOM.selectSingleNode("/dfs:myFields/dfs:dataFields/d:applicantTable/@name");

  // Construct the full query string.
  var strSQLQuery = "select name '" + queryName.text + "' from applicantTable";

  // This is the query.
  XDocument.UI.Alert(strSQLQuery);

  // Run the query.
  XDocument.QueryAdapter.Command = strSQLQuery;
  try
  {
	XDocument.Query();
	XDocument.UI.Alert("Value for name in database.");
  }
  catch(ex)
  {
	XDocument.UI.Alert("Name not in database or invalid return./n/n" + ex.number + " " + ex.description);
  }

  //Restore the default table command (for the next time).
  XDocument.QueryAdapter.Command = strOrigSQLCommand;

}

The error message I get on runtime is:

The following error occurred:

The query method on the Document object failed.
InfoPath cannot run the specified query.
The XML data returned from the data source does not match the XML Schema for this form. This discrepancy might be caused by the data source returning multiple rows when InfoPath expects only one.
Attribute 'James_Fogarty' is not allowed on element 'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields}applicantTable', according to Schema.


File:script.js
Line:150

The value is in the table and it occurs only once. Any ideas?

Thanks.

James

Hi.

If i understand you correct.
You want to determine if the name James_Fogarty exists in the applicantTable table?

If so, i believe you want to change your current SQL statemen:
var strSQLQuery = "select name '" + queryName.text + "' from applicantTable";

TO

var strSQLQuery = "select name from applicantTable WHERE name = '" + queryName.text + "'";

Cheers. Scott.

Thank you, Scott! Works great, you've helped me a lot -

James