?
Solved

Saving an InfoPath form to SQL

Posted on 2010-01-08
13
Medium Priority
?
572 Views
Last Modified: 2012-05-09
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.
0
Comment
Question by:Abdicate
  • 9
  • 2
  • 2
13 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 26212818
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?
0
 

Author Comment

by:Abdicate
ID: 26212884
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 :(
0
 

Author Comment

by:Abdicate
ID: 26212896
Sorry, yes, SQL 2005...how could I do this via the submit button in InfoPath?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 60

Expert Comment

by:chapmandew
ID: 26212907
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.  
0
 
LVL 28

Expert Comment

by:clayfox
ID: 26275931
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.
0
 

Author Comment

by:Abdicate
ID: 26280253
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.
0
 
LVL 28

Expert Comment

by:clayfox
ID: 26284015
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.
0
 

Author Comment

by:Abdicate
ID: 26295562
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...
0
 

Author Comment

by:Abdicate
ID: 26315211
Any suggestions?
0
 

Author Comment

by:Abdicate
ID: 26350022
Anyone? Bueller? Bueller? Anyone? :)
0
 

Author Comment

by:Abdicate
ID: 27302788
Anyone have a suggestion?
0
 

Accepted Solution

by:
Abdicate earned 0 total points
ID: 27305073
I found the problem by myself...thanks anyhow...
0
 

Author Comment

by:Abdicate
ID: 34917727
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question