Link to home
Start Free TrialLog in
Avatar of Abdicate
Abdicate

asked on

Saving an InfoPath form to SQL

Maybe I'm missing something...I love InfoPath and the power of SQL, but how can I take the contents of a form and dump it into an SQL database when they hit submit on the form? Why would I do this? Because I'm tasked with making a single form for 8 offices that need personal information (PII) from people arriving from other branches. Since this PII, the submitter is permitted to read it, but not all offices need access. So I want to save all the data to a single table in SQL and then read only those fields that are permitted for whichever office needs it. Like health information is only viewable by the medical unit; family and SSN information is only viewable by HR. Thoughts? Lastly, I CANNOT purchase  a third party product :( I can use SharePoint if that is an option, but then I cannot limit views when EVERYONE needs to write to a List...anyhow, any help would be great.
Avatar of chapmandew
chapmandew
Flag of United States of America image

infopath forms are just XML...so you could just store the whole form in an XML data type in a table....are you on 2005?
Avatar of Abdicate
Abdicate

ASKER

I have InfoPath 2007 and SharePoint portal 2007 (MOSS) - I know that the forms are XML, but I don't have a great deal of experience with XML. I'm trying to wean myself off of ASP and ASPX...see the problem I have is the company won't let us put development software onto the production network, so half the tools I could use, I can't :(
Sorry, yes, SQL 2005...how could I do this via the submit button in InfoPath?
Hmmm...Im not really familiar with it but I would assume you coudl all a procedure and then somehow get the text of the Infopath doc and pass that in as a param to the proc.  
I would review www.qdabra.com.

This is a sophisticated application, I have done medical note apps and other more so with InfoPath but it is really hard without webservices.

The other issue you need to consider is how many forms you will have.  Multiple locations, multiple users, etc.

Another consideration is your WAN connections. Webservices will perform much better than ODBC connections.

InfoPath can provide an awesome, customizable solution but you need some backend help.  With the data in SQL you can report and export the data if needed, etc.

Proper practice is to normailize your data, meaning I do not think you should or could put all of it in one table.

I would really plan and test what you need.
As I stated I am NOT authorized for 3rd party products...

As to your other comments...that's what I want to do...save an InfoPath form to SQL...how can I do this? I don't care about the form after it's completed. I just want to take the final product and save it to SQL.
You just start designing a new form select database type and bind it to the table.

You can use one Primary table with multiple child (related) tables.

Everyone would need access both via the network and permission to the database.
I created a new form from the database template...connected via the wizard to the database. Found some code online and put it in...doesn't work - what a suprise. I don't believe the code site gave me all the information I need to do this...here's the code just to query the database:

Sub CTRL1_5_OnClick(eventObj) 'this is the query button on the form
' Write your code here

dim strOrigSQLCommand
set strOrigSQLCommand = XDocument.QueryAdapter.Command 'this is line 25

XDocument.QueryAdapter.Command = "execute dbo.infopath"
 
'Query the Data Source.
XDocument.Query()
End Sub

When you click the button, you get this:

The following error occurred:

Object required: 'XDocument.QueryAdapter'
File:script.vbs
Line:25

Remember I cannot use (because I don't have it) Visual Studio to create a web service. My hands are very tied...
Any suggestions?
Anyone? Bueller? Bueller? Anyone? :)
Anyone have a suggestion?
ASKER CERTIFIED SOLUTION
Avatar of Abdicate
Abdicate

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You have to create the database first, then open a new InfoPath form from the database template icon. Then you can take your form and manipulate it within the confines of the table repeater that's created automatically.