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.
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?
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 :(
ASKER
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.
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.
ASKER
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.
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.
You can use one Primary table with multiple child (related) tables.
Everyone would need access both via the network and permission to the database.
ASKER
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.Com mand 'this is line 25
XDocument.QueryAdapter.Com mand = "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...
Sub CTRL1_5_OnClick(eventObj) 'this is the query button on the form
' Write your code here
dim strOrigSQLCommand
set strOrigSQLCommand = XDocument.QueryAdapter.Com
XDocument.QueryAdapter.Com
'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...
ASKER
Any suggestions?
ASKER
Anyone? Bueller? Bueller? Anyone? :)
ASKER
Anyone have a suggestion?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.