How to execute a C# sql insert after the submit to database and close button is clicked

pauljtay — Wed, 05 Jan 2011 20:21:49 GMT

I have an InfoPath 2007 (IP) form that is opened from a MOSS 2007 SP site with a primary data connection to an Access 2007 Database (BD). When the form is opened, a number (#) is manually entered and the DB is queried to see if there is an entry in the DB for that particular #. If the query returns "true" (the # exists in the DB), then IP switches to the "Continued Form" view and everything works great. If the query returns "false" (the # doesn't exist in the DB), IP switches to the "Start New Form?" view. Once the "Start New Form?" button is clicked, the C# code behind pulls information from multiple outside sources, opens the "New Form" view and populates some of the fields with that information.

The first problem occures with IP returning a "false" query. Once IP recognizes the "false" return, it fails to show any of the repeating sections in the "New Form" view, or any view for that matter. These repeating sections allow the user to fill in information in the form that is then saved back to the Access DB. Without the repeating sections the form is usless. Through the use of rules I was able to work around this issue. That process is a follows:

Once the "Query the Database" button is clicked and the query returns false I write the queried # to a # holder field, set the original # field to “0” and query the DB again. That returns the ”0” placeholder I added to the DB which is just an empty "New Form". I then switch the view to the "New Form Start Page" and repopulate the # field with the original # that I placed in the # holder field. At this point all of the repeating sections are available and the form is good to go.

Unfortunately, a new problem appears once the form is submitted back to the DB and closed.

Since I retrieved the “0” placeholder from the DB and populated it with the original #, the placeholder gets written over when the form is submitted and closed. So the next time the # query returns false there isn’t a “0” placeholder in the DB anymore and the original problem comes back.

So after all that here are my questions.

Is there a solution to the original problem (missing repeating sections)? If not, how do I add a new “0” placeholder into the DB once the “Submit and Close the Checklist” button has been clicked (I'm pretty sure that I will need to use code behind to accomplish this)? Or could the “0” placeholder be added to the DB only after the DB query returns "false"? Could the “0” placeholder be added to the database as soon as the form is opened as long as it doesn’t already exist?

I apologize for the lengthy post and thanks in advance for any help.

SQL Database Integration

How to execute a C# sql insert after the submit to database and close button is clicked