|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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.
|
|
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
[Quoted Text] > Data Connections > Modify > Edit SQL".
You could also use either ADO code or web services (see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ip2003_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
---
S.Y.M. Wong-A-Ton
"Ray" wrote:
> 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.
|
|
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
|
|
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" wrote:
[Quoted Text] > 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
|
|
Good job! :) Glad you got it too work.
---
S.Y.M. Wong-A-Ton
"Ray" wrote:
[Quoted Text] > 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" wrote:
>
> > 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
|
|
|