Newbie Questions

Re: Communicating with SQL Server from InfoPath

wmholt — Thu, 14 Apr 2011 13:32:19 GMT

Skarn,

Thanks a lot for the detailed reply. I believe I have it working now based on Part 1. I'm using this inside of SharePoint, so I'm grabbing the CustomerID as part of the Query String to the page that displays the form, using the SharePoint Query String filter web part.

Thanks again!

William

Re: Communicating with SQL Server from InfoPath

Skarn — Thu, 14 Apr 2011 10:36:12 GMT

You would need to understand SQL Views and basic SQL syntax to implement the below suggestion.

PART 1 - be able to query the SQL table with 'parameters'

You can 'emulate' a web-service type effect with a secondary data connection to your SQL database.

Create a 'receive data' secondary data connection to your 'Client Info' table (which I assume has their logonId as a field/unique key)
Use a filter on the logonid field and put a sample logonid value in it
have a look at the 'SQL' that Infopath uses for that data connection (you can see it by pressing 'Edit SQL' in the data connection wizard in IP2003
You will see somewhere in that 'SQL' the clause for filtering the data by the logonId you entered.
Manually replace it with a 'placeholder' like <inputLogonId>
Save the data connection

Now the 'QueryString' value of the dataconnection will hold the 'SQL' you saw earlier - you can use JScript or VBScript behind the form to alter this value (replace the <inputLogonId> with the value of the logged on user.) and query the secondary data-connection.

This *might* roughly match how you've done it already - where the user inputs their userId and their details are returned back.

PART 2 - Send the logonId and retrieve data on form open.

The 'tricky' part might be how you determine the logonId of the user of the form. I've used a couple of methods:

1. Use JScript/VBScript and retrieve the users UserName environment variable, or NetworkObject's user properties.

2. Use SQL Server to 'identify' the user.
If your SQL server uses Windows Authentication for its connections then the user sends their logonId to the SQL server whenever a query is run.

Set up a 'View' on the server which purely holds a single field value - the 'USER' function in SQL.
Not exactly sure on the SQL but it might be as simple as just "SELECT USER" as the View definition.
Set up a secondary data connection in Infopath to this View
Query it immediately the user opens the forms (Open rules) and the returned value will be their LogonId !
Query the UserInfo secondary data connection from Part 1 with the returned value

Hope this gives you some more ideas - I came up with these methods because I had no scope in my business role to create Web-Services or other methodologies.

Re: Communicating with SQL Server from InfoPath

wmholt — Tue, 12 Apr 2011 10:39:56 GMT

Thank you again, Patrick. When you say, "pass a field to the query", do you mean to have the query field version of the CustomerID field placed on the form? Then that will be sent through the web service data connection to the stored procedure?

I understand the web service and the stored procedure, I'm just not sure about the InfoPath technique of passing a field to the query.

Thanks!

William

Re: Communicating with SQL Server from InfoPath

Patrick Halstead — Mon, 11 Apr 2011 17:46:58 GMT

Hi William,

If you parameterize the query you would pass a field to the query data connection (receive data connection) and the web service would query SQL using a sproc or dynamic sql (parameterized ADO). DBXL uses the latter since it is generic it can't rely on sprocs for every form template.

Thanks!

Re: Communicating with SQL Server from InfoPath

wmholt — Mon, 11 Apr 2011 15:31:24 GMT

Hi Patrick,

That makes a lot of sense. So, if I use the Web Service connection, I can use a parameterized query, and I would be passing the parameter to a SQL stored procedure to complete my query?

I'll also take a look at DBXL.

Thanks for the help!

William

Re: Communicating with SQL Server from InfoPath

Patrick Halstead — Mon, 11 Apr 2011 14:15:17 GMT

Hi WH,

Parameterized SQL queries require a Web Service or form code. Passing to a SQL end-point doesn't work so well. You can try to make those endpoints Web methods, but there are problems because MS didn't really implement them well.

If you need a web service, Qdabra provides a free trial DBXL (Database Accelerator) that will allow parameterized queries of SQL. You should take a look at that since it will be the fastest and lowest cost solution. If you can't use third party solutions, I'd try to create a SQL receive data connection that pulls all of the info into the form. The issue with out-of-box SQL data connections is the fact that they don't filter the data before querying the server, so if you have a ton of data (logins) you will have to pull it all down to the client and then filter. A Web service will allow you to filter on a server that has a fast connection to your SQL DB.

Hope this makes some scintilla of sense. If not, I apologize.

Communicating with SQL Server from InfoPath

wmholt — Mon, 11 Apr 2011 10:26:42 GMT

Hi,

I have a client information form, and I have had the client able to enter his/her ID and then pull up their profile information from SQL Server 2008.

I am now being asked to pull up the same information by passing the Client's LoginID, which will be the same as the ClientID and somehow passing that to SQL Server and retrieving the client data. This way the client data is filled out on the form when the client opens the form.

I'm not clear on the part where I'm passing the ClientID to SQL Server - would this be somehow passed by InfoPath to a stored procedure in SQL Server? What would be the mechanism to do that? Sorry, but I'm new to InfoPath 2010. I've read about the QueryString Filter web part, but I don't see how passing it to the form gets the ClientID sent to SQL Server. I think I'm missing a piece of the puzzle.

Thanks,

William H.