Looking for basic info on queries

R

Ray

I think I'm mising something blindingly obvious, but how does one query data
from a database (SQL) using InfoPath? I understand the basics of databases
and can quite happily create queries, I just don't see how to link them to
the forms I design. The help files aren't helpful, they just keep telling me
I can do it (but not how) and all I can find here are more advanced
questions. Is there a simple guide to running queries on databases in
InfoPath?

I have a simple form which submits data to the database but I want to also
be able to query the database to find 'null' entries and to then be able to
enter that data.

Any guidance or suggestions would be appreciated. Thanks.
 
S

S.Y.M. Wong-A-Ton

Querying databases in InfoPath takes place through data connections. If your
SQL statement needs to be "dynamic", you can create a data connection to a
table and then retrieve and modify the SQL for the data connection through
code (for an example on how to query through code, see
http://enterprise-solutions.swits.net/infopath/query-access-database-list-numbers.htm).
If your SQL statement is "static", you can create a data connection to a
table and just edit the SQL statement for the data connection through "Tools
Data Connections > Modify > Edit SQL".
Click to expand...

You could also use either ADO code or web services (see
http://msdn.microsoft.com/library/d...03_ta/html/OfficeInfoPathWebServiceASPNET.asp)
to query databases and return results. Also see
http://support.microsoft.com/?scid=kb;en-us;826992&spid=2515&sid=229
 
R

Ray

Many thanks for this, I think I can see how it works and how I can use it.

Having said that, I've tried to adapt the script in your cookbook and I
can't quite get it to work. It has a problem with the syntax of my SQL
command:
"The query cannot be run for the following DataObject: Main connection
InfoPath cannot run the specified query.
[0x80040E14][Microsoft OLE DB Provider for SQL Server] Incorrect syntax near
the keyword 'WHERE'."
I expect this is because I'm trying to query using a text string rather than
numbers and I can't get the script to generate the correct syntax. I'm only
searching for a single record so I have:
//Retreive record to check
var lookupterm =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:search").text;

//construct new SQL command
var sqlCommand = origCommand;

if (lookupterm != "")
{
sqlCommand += " WHERE Name = " + lookupterm
}
---
It's obvious now, 'lookupterm' is a string not a variable, so this is
actually a very long way to ask the question, how do I define a string in
this script language? (it's late here and I'm sure I'll be able to work it
out in the morning, but an answer would be appreciated!)

Many thanks

Ray
 
R

Ray

I knew I'd be able to work it out at a more sensible time. It was a problem
with the syntax caused by the fact that the connection was ordering the
records (I can't remember if I set it up this way in InfoPath or in the
database itself):

select
"WID","Name","Name_long","Class","Volume","Average","Variance","Stock","Minimum","Maximum","Secondary_min","Secondary_max"
from "dbo"."Wares" as "Wares" order by "Name"

By removing the part 'order by "Name"' (I'm only looking for one record)
from the database connection and adjusting my code to include the ' to
identify a string, it now works
 
S

S.Y.M. Wong-A-Ton

Good job! :) Glad you got it too work.
---
S.Y.M. Wong-A-Ton


Ray said:
I knew I'd be able to work it out at a more sensible time. It was a problem
with the syntax caused by the fact that the connection was ordering the
records (I can't remember if I set it up this way in InfoPath or in the
database itself):

select
"WID","Name","Name_long","Class","Volume","Average","Variance","Stock","Minimum","Maximum","Secondary_min","Secondary_max"
from "dbo"."Wares" as "Wares" order by "Name"

By removing the part 'order by "Name"' (I'm only looking for one record)
from the database connection and adjusting my code to include the ' to
identify a string, it now works

Ray said:
Many thanks for this, I think I can see how it works and how I can use it.

Having said that, I've tried to adapt the script in your cookbook and I
can't quite get it to work. It has a problem with the syntax of my SQL
command:
"The query cannot be run for the following DataObject: Main connection
InfoPath cannot run the specified query.
[0x80040E14][Microsoft OLE DB Provider for SQL Server] Incorrect syntax near
the keyword 'WHERE'."
I expect this is because I'm trying to query using a text string rather than
numbers and I can't get the script to generate the correct syntax. I'm only
searching for a single record so I have:
//Retreive record to check
var lookupterm =
XDocument.DOM.selectSingleNode("/dfs:myFields/my:search").text;

//construct new SQL command
var sqlCommand = origCommand;

if (lookupterm != "")
{
sqlCommand += " WHERE Name = " + lookupterm
}
---
It's obvious now, 'lookupterm' is a string not a variable, so this is
actually a very long way to ask the question, how do I define a string in
this script language? (it's late here and I'm sure I'll be able to work it
out in the morning, but an answer would be appreciated!)

Many thanks

Ray
Click to expand...
Click to expand...
 
Top