InfoPath Dev

Is there somewhere I can find examples of these commands?  I have looked through the forums, and through http://www.qdabra.com/en/products/docs/qRules.aspx and could not find any further documentation for these commands. 

Thanks. 

Hi Keadlard,

I apologize but we currently don't have separate documentation for these commands besides the basics, covered in the qRules User Guide. We'll add a request to create documentation for these. In the meantime, can you let us know if you are encountering an issue with the commands, if you need assistance, etc. Please post your questions here and we'll try to address them as best we can.

Thank you.

 OK, my command is this:

ModifySqlQuery /dsname=Pb_VW_Units /where=where Forecaster='Summary'

This is the message I get when trying to run. 

The query cannot be run for the following DataObject: Pb_VW_Units
InfoPath cannot run the specified query.
[0x80040E14][Microsoft OLE DB Provider for SQL Server] Incorrect syntax near the keyword 'where'.

Here the SQL statement in Management studio. 

SELECT field1, field2, field 3, Forecaster FROM  PB_VW_Units
WHERE (Forecaster = N'Summary')

thanks for your help,

Kathy

Hi Kathy,

If the SQL statement works in the SQL Server Management Studio, I would suggest modifying the /where parameter to match. In other words, please try:

ModifySqlQuery /dsname=Pb_VW_Units /where=WHERE (Forecaster = N'Summary')

Let us know if this fails. You are using the command correctly, so it's just a matter (I think) of getting the /where parameter to a correct syntax.

Thank you.

I copied the where statement and still got the message

Incorrect syntax near the keyword 'WHERE'.

Is there a way to display the SQL statement that qrules created? 

OK, I got it working.  Took me a bit but I remembered the note that said "Assumes that this SQL data connection does not contain a where clause already, no sorting and no joins" .  I thought I had removed the sorting but when back and double checked and had not. 

Thank you for your help.  :)

I am now trying to be more complicated.   I have changed the formula to:

concat("ModifySqlQuery /dsname=PB_InProgress_FC_Units /where=where (Forecaster = N'", QueryChange, "'")

I'm getting the datasource not found.  This is the main dataconnection.  Below is the SQL code from the main connection.   I didn't think the problem was the concat, wouldn't the error be different? 

select "Part#" as "Part_","Forecaster","ForecastDate","ForecastMonth","Forecaster_Lvl","M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11","M12","Total12Month","Transation_Old","Transation_New","TProduct","ID" from "dbo"."PB_InProgress_FC_Units" as "PB_InProgress_FC_Units"

Thanks.

 Kathy

Isn't your main data connection named "Main"?

Actually it wasn't, but I see what you are saying, I did change the name to main unstead of main connection and now get this error message:

The query cannot be run for the following DataObject: Main
InfoPath cannot run the specified query.
[0x80040E14][Microsoft OLE DB Provider for SQL Server] Incorrect syntax near 'OMCanada'.

OMCanada is the value in the parameter QueryChange. 

 is it the "'"? 

concat("ModifySqlQuery /dsname=Main /where=where (Forecaster = N'", QueryChange, "'")

This command isn't very sophisticated - it is just grabbing whatever your current command is and slapping the passed in Where clause on it, so you know.

You seem to be missing a closing parenthesis, tho:

You've got an open parenthesis before "Forecaster", but no closing. Try this:

concat("ModifySqlQuery /dsname=Main /where=where (Forecaster = N'", QueryChange, "')")

Those Pesky Parenthesis!  I am always forgeting them.  Thanks for your help with this.  by the way Qrules ROCKS! 

I'm still having trouble with this and I believe it's because I need to put quotes around the field name. 

If I use ModifySqlQuery /dsname=Main /where=where ("Forecaster" = 'Summary') this retrieves data but ModifySqlQuery /dsname=Main /where=where (Forecaster = 'Summary') does not.

How do I put quotes in the concat?  I tried concat("ModifySqlQuery /dsname=Main /where=where ("Forecaster" = '", QueryChange, "')")  and I get an error.  What do I use to tell Qrules that the Quotes around Forecaster are part of what needs to be concatenated?  Quotes around the quotes? 

I asked our tester if he recalled how he tested this - with a concat or not - he couldn't recall. We'll check it out and post back - it may be a day or so, okay?

ok, thank you. 

I'm a little OCD, so I just tried this. And my concat looks like:

As you can see, I'm not using parenthesis around my where clause, and I don't have double quotes around my column name. This worked in my environment.

However, I am wondering about one thing - your main data connection - your form is database bound? Why wouldn't you just use the built in query fields?