 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / General MS InfoPath Questions / February 2006Secondary data source query
Hi, I would like to use a secondary data source to recieve data from SQL, but currently no query fields apear in my secondary data source in InfoPath all I can see are data fields and as such the form return everything when I run a query. How can I query a secondary data source and only return the row that I want...? thanks, ssaral You can filter the data in a secondary data source that is connected to SQL Server by editing the SQL statement through Tools > Data Connections, [Modify...] button, [Edit SQL...] button, and adding a WHERE clause to the SELECT statement. This will produce a "static" filter. You can also produce a "dynamic" filter by modifying the SQL statement for the secondary data source through code and composing a WHERE clause using the value from e.g. a field within your InfoPath form. --- S.Y.M. Wong-A-Ton > Hi, > [quoted text clipped - 5 lines] > > thanks, ssaral I would like to do it dynamicly, hard coding the filter won't be as helpful. What would be the proper syntax for that...in other owrds how do you tell SQL that this is a field in the InfoPath form and not a field in the SQL table. Can you provide an example... Thanks, ssaral > You can filter the data in a secondary data source that is connected to SQL > Server by editing the SQL statement through Tools > Data Connections, [quoted text clipped - 17 lines] > > > > thanks, ssaral Put the following code in the event handler of a button or field on your InfoPath form. I haven't tested this code, but with some minor tweaking you should be able to get it to work. --- // Retrieve the SQL statement of the data source var strSQL = XDocument.DataObjects["YourDataSourceName"].QueryAdapter.Command; // Retrieve the field in the InfoPath form whose value will be used in the WHERE clause var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1"); // Generate the new SQL statement with WHERE clause strSQL += " where tableFieldName = '" + node.text + "'"; // Populate the command with the new SQL statement XDocument.DataObjects["YourDataSourceName"].QueryAdapter.Command = strSQL; // Run the query XDocument.DataObjects["YourDataSourceName"].Query(); --- S.Y.M. Wong-A-Ton > I would like to do it dynamicly, hard coding the filter won't be as helpful. > What would be the proper syntax for that...in other owrds how do you tell SQL [quoted text clipped - 24 lines] > > > > > > thanks, ssaral I will give it a try and let you know....thanks > Put the following code in the event handler of a button or field on your > InfoPath form. I haven't tested this code, but with some minor tweaking you [quoted text clipped - 48 lines] > > > > > > > > thanks, ssaral I tired to use the code below but keep getting an error 'Object not found' on the line that reads strSQL += " where tableFieldName = '" + node.text + "'"; where it is not correctly putting together the updated SQL code; for tableFieldName I inserted dbo.table.fieldname and fieldname both didn't work, there seems to be syntax problem, can you help... thanks, ssaral > I will give it a try and let you know....thanks > [quoted text clipped - 50 lines] > > > > > > > > > > thanks, ssaral I'm guessing that the table cannot be found if you are getting 'Object not found'. If you are getting 'Object required', your XPath query to find the node is incorrect. Let's add some debugging information to your code to see what's going on, okay? Add the following line of code right after var strSQL = XDocument.DataObjects["YourDataSourceName"].QueryAdapter.Command; XDocument.UI.Alert(strSQL); // <= line to add What does it return? Add the following lines of code right after var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1"); XDocument.UI.Alert(node == null); // <= line to add XDocument.UI.Alert(node.text); // <= line to add What do they return? And my last question: What exactly did you enter for dbo.table.fieldname and fieldname? Please use the correct names you used and not "table" or "fieldname". --- S.Y.M. Wong-A-Ton > I tired to use the code below but keep getting an error 'Object not found' on > the line that reads [quoted text clipped - 61 lines] > > > > > > > > > > > > thanks, ssaral It is finding the table, the first window displays: shape {select "LOGICAL_NAME","WAP_APPL_ID","WAP_APPL_STTS_CDE" from "dbo"."PICCT_APPL" as "PICCT_APPL"} as "PICCT_APPL" append ({select "LOGICAL_NAME","VENDOR","VENDOR_ID","WDV_ASSET_DSC" from "dbo"."PICCT_DEVICE" as "PICCT_DEVICE"} relate "LOGICAL_NAME" TO "LOGICAL_NAME") as "PICCT_DEVICE" The other window says True; so I think the Where clause is not being formed correctly. The error message now is Object Required. The code I used looks like this: // Retrieve the SQL statement of the data source var strSQL = XDocument.DataObjects["PICCT_APPL"].QueryAdapter.Command; XDocument.UI.Alert(strSQL); // Retrieve the field in the InfoPath form whose value will be used in the // WHERE clause; created field1 under the main data source var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1"); XDocument.UI.Alert(node == null); XDOcument.UI.Alert(node.text); // Generate the new SQL statement with WHERE clause strSQL += " where WAP_APPL_ID = '" + node.text + "'"; // Populate the command with the new SQL statement XDocument.DataObjects["PICCT_APPL"].QueryAdapter.Command = strSQL; // Run the query XDocument.DataObjects["PICCT_APPL"].Query(); thanks, ssaral > I'm guessing that the table cannot be found if you are getting 'Object not > found'. If you are getting 'Object required', your XPath query to find the [quoted text clipped - 90 lines] > > > > > > > > > > > > > > thanks, ssaral Ah... I see you are using a join between two tables. Appending the WHERE clause to the SQL query isn't going to work then. The best thing to do in this case is to copy over the entire SQL SELECT string and add the WHERE clause in code. So instead of var strSQL = XDocument.DataObjects["PICCT_APPL"].QueryAdapter.Command; do something like var strSQL = "shape {select \"LOGICAL_NAME\",\"WAP_APPL_ID\",\"WAP_APPL_STTS_CDE\" from \"dbo\".\"PICCT_APPL\" as \"PICCT_APPL\" WHERE WAP_APPL_ID = " + node.text + "} as \"PICCT_APPL\" append ({select \"LOGICAL_NAME\",\"VENDOR\",\"VENDOR_ID\",\"WDV_ASSET_DSC\" from \"dbo\".\"PICCT_DEVICE\" as \"PICCT_DEVICE\"} relate \"LOGICAL_NAME\" TO \"LOGICAL_NAME\") as \"PICCT_DEVICE\"" Note: I removed the single quotes from around node.text, since I suspect that the WAP_APPL_ID is of a numeric type and not a string. If it's a string, put back the single quotes. Make sure to retrieve node before composing the SQL statement. So call var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1"); before var strSQL = ... Then put the follwoing code after var strSQL = .... // Populate the command with the new SQL statement XDocument.DataObjects["PICCT_APPL"].QueryAdapter.Command = strSQL; // Run the query XDocument.DataObjects["PICCT_APPL"].Query(); --- S.Y.M. Wong-A-Ton > It is finding the table, the first window displays: > [quoted text clipped - 126 lines] > > > > > > > > > > > > > > > > thanks, ssaral I forgot to mention that since the second message box returned "True", the XPath to your node containing the value for the WHERE clause is also incorrect. So you must fix the following line too var node = XDocument.DOM.selectSingleNode("my:myFields/my:field1"); and in particular "my:myFields/my:field1", before proceeding. XDocument.UI.Alert(node == null); // <- must return "False" --- S.Y.M. Wong-A-Ton > It is finding the table, the first window displays: > [quoted text clipped - 126 lines] > > > > > > > > > > > > > > > > thanks, ssaral OK, I get an error message that reads Un-terminated string constant for the line that starts with var strSQL = "shape... in InfoPath The code I used is below: // Retrieve the field in the InfoPath form whose value will be used var node = XDocument.DOM.selectSingleNode("//my:field1"); // Copy over the entire SQL SELECT string and add the WHERE clause var strSQL = "shape //<- error message refers to this line {select \"LOGICAL_NAME\",\"WAP_APPL_ID\",\"WAP_APPL_STTS_CDE\" from \"dbo\".\"PICCT_APPL\" as \"PICCT_APPL\" WHERE WAP_APPL_ID = " + 'node.text' + "} as \"PICCT_APPL\" append ({select \"LOGICAL_NAME\",\"VENDOR\",\"VENDOR_ID\",\"WDV_ASSET_DSC\" from \"dbo\".\"PICCT_DEVICE\" as \"PICCT_DEVICE\"} relate \"LOGICAL_NAME\" TO \"LOGICAL_NAME\") as \"PICCT_DEVICE\"" // Populate the command with the new SQL statement XDocument.DataObjects["PICCT_APPL"].QueryAdapter.Command = strSQL; // Run the query XDocument.DataObjects["PICCT_APPL"].Query(); I tried putting ; at the end of various lines but that didn't seem to fix the problem. As far as the Xpath for the form field goes I changed it to \\my:field1 above I don't know if that's correct. my:myFields/my:field1 should have worked before, when I look at the Data Source pane it's listed as: Data source: Main - myFields + queryFields + dataFields my:field1 thanks for all your help, ssaral > I forgot to mention that since the second message box returned "True", the > XPath to your node containing the value for the WHERE clause is also [quoted text clipped - 139 lines] > > > > > > > > > > > > > > > > > > thanks, ssaral > OK, I get an error message that reads Un-terminated string constant for the > line that starts with var strSQL = "shape... in InfoPath The un-terminated string constant error is probably being caused by the single quotes you added around node.text. They should be inside the double quotes. So instead of ..." + 'node.text' + "... you should use ...' " + node.text + " '... //<- spaces between quotes added only for clarity So this would be your new strSQL: var strSQL = "shape {select \"LOGICAL_NAME\",\"WAP_APPL_ID\",\"WAP_APPL_STTS_CDE\" from \"dbo\".\"PICCT_APPL\" as \"PICCT_APPL\" WHERE WAP_APPL_ID = '" node.text + "'} as \"PICCT_APPL\" append ({select \"LOGICAL_NAME\",\"VENDOR\",\"VENDOR_ID\",\"WDV_ASSET_DSC\" from \"dbo\".\"PICCT_DEVICE\" as \"PICCT_DEVICE\"} relate \"LOGICAL_NAME\" TO \"LOGICAL_NAME\") as \"PICCT_DEVICE\"" Since you don't have spaces in your table or field names, you can also try: var strSQL = "shape {select LOGICAL_NAME, WAP_APPL_ID, WAP_APPL_STTS_CDE from dbo.PICCT_APPL as PICCT_APPL WHERE WAP_APPL_ID = '" node.text + "'} as PICCT_APPL append ({select LOGICAL_NAME, VENDOR, VENDOR_ID, WDV_ASSET_DSC from dbo.PICCT_DEVICE as PICCT_DEVICE} relate LOGICAL_NAME TO LOGICAL_NAME) as PICCT_DEVICE" which is a little bit more readable. > As far as the Xpath for the form field goes I changed it to \\my:field1 > above I don't know if that's correct. my:myFields/my:field1 should have //my:field1 is the correct syntax. And you're right, it should have worked. Let me know how things work out with these minor changes. --- S.Y.M. Wong-A-Ton > OK, I get an error message that reads Un-terminated string constant for the > line that starts with var strSQL = "shape... in InfoPath [quoted text clipped - 179 lines] > > > > > > > > > > > > > > > > > > > > thanks, ssaral I see I missed a + sign. Correction: var strSQL = "shape {select \"LOGICAL_NAME\",\"WAP_APPL_ID\",\"WAP_APPL_STTS_CDE\" from \"dbo\".\"PICCT_APPL\" as \"PICCT_APPL\" WHERE WAP_APPL_ID = '" + node.text + "'} as \"PICCT_APPL\" append ({select \"LOGICAL_NAME\",\"VENDOR\",\"VENDOR_ID\",\"WDV_ASSET_DSC\" from \"dbo\".\"PICCT_DEVICE\" as \"PICCT_DEVICE\"} relate \"LOGICAL_NAME\" TO \"LOGICAL_NAME\") as \"PICCT_DEVICE\"" or var strSQL = "shape {select LOGICAL_NAME, WAP_APPL_ID, WAP_APPL_STTS_CDE from dbo.PICCT_APPL as PICCT_APPL WHERE WAP_APPL_ID = '" + node.text + "'} as PICCT_APPL append ({select LOGICAL_NAME, VENDOR, VENDOR_ID, WDV_ASSET_DSC from dbo.PICCT_DEVICE as PICCT_DEVICE} relate LOGICAL_NAME TO LOGICAL_NAME) as PICCT_DEVICE" -- S.Y.M. Wong-A-Ton > > OK, I get an error message that reads Un-terminated string constant for the > > line that starts with var strSQL = "shape... in InfoPath [quoted text clipped - 224 lines] > > > > > > > > > > > > > > > > > > > > > > thanks, ssaral I tried both code samples below, and both are still giving me the same error message 'Unterminated string constant' on the line same line. thanks, ssaral > I see I missed a + sign. Correction: > [quoted text clipped - 249 lines] > > > > > > > > > > > > > > > > > > > > > > > > thanks, ssaral Try cutting up the string in pieces like this: var strSQL = ""; strSQL += " shape "; strSQL += " {select LOGICAL_NAME, WAP_APPL_ID, WAP_APPL_STTS_CDE from "; strSQL += " dbo.PICCT_APPL as PICCT_APPL WHERE WAP_APPL_ID = '" + node.text + "'} "; strSQL += " as PICCT_APPL "; strSQL += " append "; strSQL += " ({select LOGICAL_NAME, VENDOR, VENDOR_ID, WDV_ASSET_DSC from "; strSQL += " dbo.PICCT_DEVICE as PICCT_DEVICE} "; strSQL += " relate LOGICAL_NAME TO LOGICAL_NAME) as PICCT_DEVICE "; That should solve the problem. --- S.Y.M. Wong-A-Ton > I tried both code samples below, and both are still giving me the same error > message 'Unterminated string constant' on the line same line. > > thanks, ssaral that did it...you are the man...thanks for all the help... ssaral > Try cutting up the string in pieces like this: > [quoted text clipped - 18 lines] > > > > thanks, ssaral Actually, it's woman. :) Glad you got it working now. --- S.Y.M. Wong-A-Ton > that did it...you are the man...thanks for all the help... > [quoted text clipped - 22 lines] > > > > > > thanks, ssaral sorry about that...that's even cooler > Actually, it's woman. :) Glad you got it working now. > --- [quoted text clipped - 26 lines] > > > > > > > > thanks, ssaral Forgot to mention: Do not just copy and paste the code, but make sure that each piece of the SQL statement enclosed by double quotes fits on 1 single line. strSQL += " ... "; //<- must fit on one line and NOT break to the following line --- S.Y.M. Wong-A-Ton > I tried both code samples below, and both are still giving me the same error > message 'Unterminated string constant' on the line same line. [quoted text clipped - 254 lines] > > > > > > > > > > > > > > > > > > > > > > > > > > thanks, ssaral A slight victory...I have the code working with one table...now if we get it working with the joined tables we'll be golden. > I forgot to mention that since the second message box returned "True", the > XPath to your node containing the value for the WHERE clause is also [quoted text clipped - 139 lines] > > > > > > > > > > > > > > > > > > thanks, ssaral So this means you can also retrieve node.text now? --- S.Y.M. Wong-A-Ton > A slight victory...I have the code working with one table...now if we get it > working with the joined tables we'll be golden. [quoted text clipped - 142 lines] > > > > > > > > > > > > > > > > > > > > thanks, ssaral Yes, I can retrieve the node when using only one table...changing my:myFields/my:field1 to //my:field1 seems to have fixed that. > So this means you can also retrieve node.text now? > --- [quoted text clipped - 146 lines] > > > > > > > > > > > > > > > > > > > > > > thanks, ssaral I am having the same type of problem...can't seem to properly reference the field value...I have a post dated 2/17 > Yes, I can retrieve the node when using only one table...changing > my:myFields/my:field1 to //my:field1 seems to have fixed that. [quoted text clipped - 149 lines] > > > > > > > > > > > > > > > > > > > > > > > > thanks, ssaral |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.