Update, Add, Delete Records -- Sharpoint List and SQL

[Dan]

I am new to InfoPath, though not to Sharepoint (WSS). Trying to
understand some concepts and capabilities.

Want to set up a Forms Library to contain a single InfoPath form.

I *think* I want to use a submit button on the form to insert data into
BOTH SQL Server and a Sharepoint List. I intend to display only certain
columns in SPoint. I'm fairly comfortable on how to do that, though I
haven't tried yet.

I want to be able to update, add and delete records by way of the form.
Question: Will these modifications affect SPList as well as database?

Finally, I want users to be able to view and modify their own data
only, not that of others.

Are these things reasonable to achieve, that is in a straightforward
way? Any comments at all, and comments on best way to achieve are
certainly appreciated.

DAN

 

[wjasonstrutz]

Dan,

I've been trying to accomplish much the same thing, and there are a lot of
different approaches. One alternative is to use a document library instead
of a list. That way, you can submit to the database and then have code that
saves the form to a particular URL (in the document library). Then you can
create data view web parts to display information however you want. Or, you
could have data view web parts that connect directly to your database, and
not bother saving the form data at all (just have it query the DB).

See more response inline:

I am new to InfoPath, though not to Sharepoint (WSS). Trying to
understand some concepts and capabilities.

Want to set up a Forms Library to contain a single InfoPath form.

I *think* I want to use a submit button on the form to insert data into
BOTH SQL Server and a Sharepoint List. I intend to display only certain
columns in SPoint. I'm fairly comfortable on how to do that, though I
haven't tried yet.

The easiest method will be to setup either a list or the database as the
form's main data source (when you design the form, select New form from Data
Source). Then, instead of having a Submit button perform submission, have
code that calls XDocument.Submit() to submit to the main data source, and use
a secondary data source to submit to the list.

I believe the SharePoint list can be a submittable secondary data source,
but the database cannot be. However, you can submit directly to a database
from Infopath (if you're loose with security restrictions) by creating ADODB
connections and commands.

I want to be able to update, add and delete records by way of the form.
Question: Will these modifications affect SPList as well as database?

Yes, if you create the synchronization logic yourself. You cannot have
Infopath submit directly to two data sources, but using the method I
described above, you can work around this issue. You will also have a
problem submitting to the database when someone updates the list in
SharePoint; you may be able to get around this by extending SharePoint with
list events, but I haven't tried it.

Finally, I want users to be able to view and modify their own data
only, not that of others.

You'll need to be able to identify users; you can do that using
System.Environment.UserName from managed code, or in jscript:

var objNetwork = new ActiveXObject("WScript.Network");
objNetwork.UserName

Include a field in your database for CreatedBy, and add conditional
formatting and data validation checks in your form to only allow users who's
ID matches the CreatedBy field to make changes. For the SharePoint list, set
item level security permissions on the list.

Good luck with the solution; from experience I know this is a tough problem
to solve.

jason.

 

[Dan]

Excellent and considered response. Thank you Jason.

DAN