Query SQL Database using InfoPath form field

[Jake]

I have an InfoPath 2007 form where I would like to query an external SQL
database for information based off of a field the user fills in on the form.
I can hard-code the WHERE clause in my query for testing, but my goal is to
have the WHERE clause look at my InfoPath form.


Working Query:
select "CustomerID","CustomerNameFull","CustomerTaxID" from
"dbo"."Cust_Customers" as "Cust_Customers" WHERE "CustomerTaxID" = '12345'

Desired Query:
select "CustomerID","CustomerNameFull","CustomerTaxID" from
"dbo"."Cust_Customers" as "Cust_Customers" WHERE "CustomerTaxID" =
MY_INFOPATH_FIELD

What is the syntax for passing in a multi-level field? (main data source
myFields, group1, group2, field8) Has anyone done this before or have any
suggestions on how this can work? The database is too large to query without
the WHERE clause.

 

[sboyd]

I have an InfoPath 2007 form where I would like to query an external SQL
database for information based off of a field the user fills in on the form.  
I can hard-code the WHERE clause in my query for testing, but my goal is to
have the WHERE clause look at my InfoPath form.

Working Query:
select "CustomerID","CustomerNameFull","CustomerTaxID" from
"dbo"."Cust_Customers" as "Cust_Customers" WHERE "CustomerTaxID" = '12345'

Desired Query:
select "CustomerID","CustomerNameFull","CustomerTaxID" from
"dbo"."Cust_Customers" as "Cust_Customers" WHERE "CustomerTaxID" =
MY_INFOPATH_FIELD

What is the syntax for passing in a multi-level field?  (main data source
myFields, group1, group2, field8)  Has anyone done this before or have any
suggestions on how this can work?  The database is too large to query without
the WHERE clause.

I'm not exactly sure you want the same thing I did but I have an
infopath form that was created to correct items in a database. I
created the data connection to the database, dragged the query fields
I needed in a section and then dragged the data fields into a
section. The user can then query multiple fields, press submit and
the form will return the match that is in the database. They can then
change items and resubmit the changes.

 

[Jake]

sboyd, thanks for the reply. For some reason, the data connection does not
have query fields, only data fields. (I only want to get data from this SQL
data connection.)

When I created the data connection I selected Receive Data. After defining
the SQL server info and selecting the fields I want returned, I click on the
Edit SQL button and added the WHERE clause with a hardcoded value for
testing. Then I added a rule action 'Query using a data connection' to a
form field, and assign a returned value to another form field. It worked.
Now I want to replace the hardcoded value in the WHERE clause with the form
field I filled in. I tried '/my:myFields/my:field1'. The query test button
says the code is correct, but when the query rule fires when I fill in the
field, it doesn't return a value even though I enter the same value as was
hardcoded. There's no error information and I'm not sure how to see what
value is being passed to the WHERE clause through the field reference.

 

[sboyd]

sboyd, thanks for the reply.  For some reason, the data connection doesnot
have query fields, only data fields. (I only want to get data from this SQL
data connection.)

When I created the data connection I selected Receive Data.  After defining
the SQL server info and selecting the fields I want returned, I click on the
Edit SQL button and added the WHERE clause with a hardcoded value for
testing.  Then I added a rule action 'Query using a data connection' toa
form field, and assign a returned value to another form field.  It worked.  
Now I want to replace the hardcoded value in the WHERE clause with the form
field I filled in.  I tried '/my:myFields/my:field1'.  The query testbutton
says the code is correct, but when the query rule fires when I fill in the
field, it doesn't return a value even though I enter the same value as was
hardcoded.  There's no error information and I'm not sure how to see what
value is being passed to the WHERE clause through the field reference.






- Show quoted text -

I've had similar problems and used the normal space function to clear
out any extra spaces. Set the default value on your field by adding
this function
normalize-space(double click to insert field)

I'm using a table as my data connection and I created it to submit
data back to the table. By doing this you get two sets of fields on
your connection. One set is a set of query fields and the other set
is a set a data fields. This way you can drag the query fields over
to a section and add a button (query button) and then add the data
fields to a section. Running the query will return the data that
matches.

I've never created a data connection to another form so I don't know
if you get the same options. I've just been using InfoPath and
SharePoint for about a year so I'm a newie too.

 

[kozaryna]

The only solution I found was to do it in the code behind. Here is an example
of what the code would look like in jscript.

function btnSubmit::OnClick(eventObj)
{

var oQueryId;
oQueryId = XDocument.DOM.selectSingleNode("/dfs:myFields/my:FieldName").text;

myDataAdapter = XDocument.DataAdapters("Main connection");
myDataAdapter.Command = "select * from tablename WHERE FieldName = '" +
oQueryId + "'";
myDataAdapter.Query();
}

 

[sboyd]

The only solution I found was to do it in the code behind. Here is an example
of what the code would look like in jscript.

function btnSubmit::OnClick(eventObj)
{

var oQueryId;
oQueryId = XDocument.DOM.selectSingleNode("/dfs:myFields/my:FieldName")..text;

myDataAdapter = XDocument.DataAdapters("Main connection");
myDataAdapter.Command = "select * from tablename WHERE FieldName = '"+
oQueryId + "'";
myDataAdapter.Query();



}





- Show quoted text -

You're doing better with the code behind than me. I don't know how to
write code so I try everything else outside of using code. Sorry I
couldn't help more.

 

[Jake]

Kozaryna, I was asked not to use code so it would easier to support, but I'm
running out of ideas. I'll give this a try. If it works, maybe they'll
compromise a little code for progress.