none
create an infopath form that receive and submit data to sql server database in the same form

    Question

  • hi,

    i want to create an infopath form that can receive the data out of the slq server database and edit the data in the database and then submit it.

    here is what i did so far.

    i created a data connection to a sql server database to SUBMIT the data.

    also i created a data connection to the same sql server database to RECEIVE the data.

    when i open the form the user and the user's details are already filled in the form, now i need to let the user edit the data in the fields or add a new data in a field and then the form need to be submitted to a temporary database to be checked first by an approver and after approval the form must be submitted to the real sql server database.

    i tought about that the approver just need to click a button and the data will be submitted to the the real database.

    both the temporary and the real database has the same tables and fields.

    i just need to know how to edit and submit the data to the temporary later to the real database

    thnx

     

    Wednesday, September 29, 2010 9:38 PM

Answers

  • submit the form to sharepoint first and add a approve submit button that submit the data to the sql db , but my question is, what data connection do i have to use in the form before submit it to SP, or do i have to create a main datasource with fields created in the form itself

    Yes, exactly.  You would start with a blank template, create the fields with proper names in the main data source, create your receive/submit data connections for the DB, then figure out how you want to display the data.  the user selects an ID or name from the dropdown, then you can set all the main data source fields to the data from the database by using rules with filters.  Then, users can edit those fields.  Next, you create an Approve button that first sets all the values of all the fields in your submit data connection to the values of their respective main data source fields and then it submits.  This will write the data back to the database within the same row (if you have the unique identifier) or a new row if you are doing an insert with a new row.

    Oh, and for the first submit, it would just submit to SharePoint for approval.  Then, the Approve button would actually do the database submit and would only be visible to the approver by way of User Roles.


    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force

    • Marked as answer by jenfifi Monday, October 04, 2010 3:31 PM
    Thursday, September 30, 2010 3:03 AM

All replies

  • It was a normal request until you threw in that whole temporary database.  That wouldn't work at all the way you're describing it.  To work directly with a database, you would base your schema off that database directly, not create separate receive/submit data connections.  However, if you must have this interim step, then my recommendation is to simply submit the form initially to SharePoint, notify the approver via SPD workflow email, then have a custom approve button in the form that performs the submit to the DB.  The problem here is that when you don't base the form schema off the database, it requires you to do extra work to push the form data into the fields of the DB data connection.

    I don't recommend incorporating two separate database submit connections when you could just use SharePoint to submit the form and display it to the user.

    Also, it's a very poor practice to go directly against the DB.  You should be using web services as an intermediate layer.


    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Thursday, September 30, 2010 1:28 AM
  • that temporary database is because the user is not allowed to insert data in the real database until it is approved.

    how can i use sharepoint to submit and display data out of an existing database? all i know is that sharepoint can get data out of the wss_content database. 

    the all first thing i've try was create a form with database connection, i try to auto populate data from that dataconnection by filling one field with data and let it display the matching data in the nect field. it is not working,

    then i have created a main data source with some fields created in the infopath self and autopopulate data from the sql dataconnection, it works but i dont know how to let the user edit the data.

     

    i have thought about submit the form to sharepoint first and add a approve submit button that submit the data to the sql db , but my question is, what data connection do i have to use in the form before submit it to SP, or do i have to create a main datasource with fields created in the form itself?

     

    Thursday, September 30, 2010 2:40 AM
  • submit the form to sharepoint first and add a approve submit button that submit the data to the sql db , but my question is, what data connection do i have to use in the form before submit it to SP, or do i have to create a main datasource with fields created in the form itself

    Yes, exactly.  You would start with a blank template, create the fields with proper names in the main data source, create your receive/submit data connections for the DB, then figure out how you want to display the data.  the user selects an ID or name from the dropdown, then you can set all the main data source fields to the data from the database by using rules with filters.  Then, users can edit those fields.  Next, you create an Approve button that first sets all the values of all the fields in your submit data connection to the values of their respective main data source fields and then it submits.  This will write the data back to the database within the same row (if you have the unique identifier) or a new row if you are doing an insert with a new row.

    Oh, and for the first submit, it would just submit to SharePoint for approval.  Then, the Approve button would actually do the database submit and would only be visible to the approver by way of User Roles.


    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force

    • Marked as answer by jenfifi Monday, October 04, 2010 3:31 PM
    Thursday, September 30, 2010 3:03 AM
  • Ok, many thanx. i will try this tomorrow in the morning

    thnx again!

    Thursday, September 30, 2010 3:11 AM
  • hi Clayton,

    its me again.

    can you explain to me a little more about the submit and approve button, i have put 2 buttons one for the user and one for the approver.

    and i hide the approver button from the user. but how can i seperate the submition from the 2 buttons? if i go to the properties of the buttons they both have the same place to be submitted. if i change it to database for the approver button the button for the user also change

    Thursday, September 30, 2010 7:02 PM
  • never mind, i have done it myself.

    thnx

    Thursday, September 30, 2010 7:44 PM
  • You didn't use any of the methods I gave you?
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Friday, October 01, 2010 12:47 AM
  • never mind, i have done it myself.

    thnx


    Jen, you keep asking tons of questions all over the place about every thing you could possibly do in InfoPath, and then I'm spending a lot of free time helping you, but you're not marking answers nor even voting posts as helpful.  That is very selfish.  You're using us to do your job for you without even marking answers - that's pretty lame.
    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Saturday, October 02, 2010 9:07 PM
  • i'm not using anyone to do my job for me. i'm trying to learn how to use these tools. i'm using them for the first time. isn't this forum to ask question?

    sorry if it took so long, when i get an answer i go an try to fix the problem thats why i forget to come back and mark the answer. and i had many other question marked. i'm not being selfish on purpose

    Monday, October 04, 2010 8:27 PM