How to use the submit command to update a recordset instead

[oghebrial]

of creating a new recordset?


I would like to know how I can an update a recordset in a SQL server
database instead of creating a new recordset when using the submit button; I
have a form which uses a custom submit command to submit the recordset to a
SQL server database and then e-mails the form to a different user; once that
person receives the form they should be able to add comment and populate
additional fields; currently when this person who received the form via
e-mail tries to submit the form to the database, infopath will try to create
a new record instead of updating the current record which will result in an
error because the database will enforce uniqueness for the primary key.

Can I build a custom submit command to update the record instead of trying
to create a new record?

 

[Scott L. Heim [MSFT]]

Hi,

I am not 100% sure I understand what you are asking but it sounds like you
have built an InfoPath solution from a SQL Server database and when you
click the Submit button to send the changes back to SQL Server, you wind up
with a new, blank form...is this correct?

If so, take a look at the following:

- Open your InfoPath XSN (template) in Design View
- From the Tools menu choose Submitting Forms
- Click the Submit Options button

Under the title "After users submit the form, do one of the following" -
what is this set to? It sounds like you may have the "Create a new, blank
form" option selected. If so, just change this to "Leave the form open" and
now when you submit the data the current record will remain.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[oghebrial]

Hello,

Here's what I am trying to do:

I created an infopath form which writes back it's data to several tables in
a SQL server database. The database contains multiple tables and I use an
"Invoice Number" as a primary key which is linked to foreign keys in other
tables. I have a submit command which performs the following:

1. Submit the form to e-mail for a different user to review then
complete/update some fields.
2. Submit the record to the database.

When the form is initially completed, the submit command works fine because
it is a new record; however, when a person receives the form in e-mail, then
open it and update the fields they need to update and then click the submit
command, the submit command fails because it tries to insert a record into
the database with an existing primary key (same invoice number).

My question is how to create a submit command that updates an existing
record instead of inserting a new record?

Here's the error message I get:

InfoPath cannot submit the form.
An error occurred while the form was being submitted.
[0x80040E2F][Microsoft OLE DB Provider for SQL Server] Violation of PRIMARY
KEY constraint 'PK_tbl_Invoices'. Cannot insert duplicate key in object
'tbl_Invoices'.
[0x80040E2F][Microsoft OLE DB Provider for SQL Server] The statement has
been terminated.

I hope this helps explain my problem a little better.

 

[Scott L. Heim [MSFT]]

Hi,

Thank you for the clarification.

I believe I understand the behavior you are seeing. In order for InfoPath
to recognize you want to "update" an existing record, a query needs to be
executed against the database.

So if you complete these basic steps:

- Double-click an XSN
- Enter information on a new record
- Click a submit button that submits the data to SQL and sends an e-mail
- The e-mail recipient simply opens the form from e-mail, makes changes and
clicks Submit again

This will cause the primary key failure as InfoPath is seeing this as a new
record since the database has not been re-queried. What you will need to do
is issue a query for the new record ID after opening it in e-mail and then
you will be able to modify the existing data.

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[oghebrial]

Thanks a million. This is really helpful; Here how I implemented it:

1. I created a query view with the invoice number and a query button
2. I created a role to auto populate the invoice number on the query view
when the invoice number on the data view changes.
3. I created a form open role to switch to the query view if the invoice
number is not blank so the e-mail recepients open the form in the query view.

I have a couple of questions:

1. Do you have any recommendations to invoke the query when the user opens
the form so they don't have to click the see the query view and click the
query button?
2. When I submit to e-mail a summary e-mail message appears for
confirmation; is there a way to suppress this confirmation message.
3. Would you implement this in a different way?

Thanks.

 

[Scott L. Heim [MSFT]]

Hi,

OK - see if these steps work for you as they appear to work as needed in my
sample scenario:

- Rule #1 on your Submit button:
- Submit the form to your SQL Server

- Rule #2 on your Submit button:
- Add a condition that checks to see if your "query field" is blank. Then
add the Action: "Set a field's value" where you set the query field value
to the same data field. (In my sample, I have the ShipperID field as the
query field. So my condition checks to see if the ShipperID queryField is
blank and if so, sets the value of this field to the ShipperID dataField
which is now present since we submitted to SQL.)

- Rule #3 on your Submit button:
- Submit using your e-mail adapter

In my testing, I can now complete the following without error:

- Double-click the XSN
- Add a new record
- Click my Submit button (it contains the 3 rules noted above)
- Open the form from my e-mail
- Modify the displayed record
- Click the submit button again

Let me know what you find...

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[oghebrial]

Thanks a million. Your approach worked like a charm.... Your assistance is
greatly appreciated. I haveone more quick question.

1. I would like to submit the form to a Sharepoint form library for quick
access and custom views. I use the invoice number and part of the vendor
name as part of the form name when I submit this form to the Sharepoint form
library. If I try to submit a form which was previously submitted, the
system will fail since the form name already exists and it tries to create a
new record instead of updating the existing form (similar to the database
submit issue). Do you have any recommendations to hand the sharepoint submit?

2. I would like to determine the current AD user id when the form is
opened; do you have any recommendations for this?

Thanks a lot for all of your help.

 

[oghebrial]

I guess I can address the Sharepoint issue if I can either indicate that I
can overwrite the form frox Infopath or if I can delete the existing form
before I submit the latest form. Is this doable?

 

[Scott L. Heim [MSFT]]

Hi,

When you setup your "submit" adapter to Sharepoint, one of the screens has
the option: "Allow overwrite if file exists" - it sounds like this is what
you need to enable.

With regard to the current AD user, there are a couple of options:

- Setup a "WhoAmI" web service (you can search this forum for loads of info
on this)
- Use custom AD code

If you use custom AD code, you will need to have your form "fully trusted."
If this is fine, here is a sample VBScript function that I call from the
forms "Load" event. In the Load event, I check to see if the field used to
store this information is blank and if so, then execute this procedure:

Function GetADInfo
Dim objADSystemInfo
Dim objUser
Dim objManager
Dim strLogin
Dim strMail
Dim strAlias
Dim objGroup
Dim strGroups

Stop
Set objADSystemInfo = CreateObject("ADSystemInfo")
Set objUser = GetObject("LDAP://" + objADSystemInfo.UserName)
Set objManager = GetObject("LDAP://" + objUser.Get("manager"))

'Get all the groups the user is enrolled in
'For Each objGroup In objUser.Groups
' strGroups = strGroups & vbcrlf & objGroup.name
'Next

strLogin = objUser.Get("cn")
strMail = objUser.Get("mail")
strAlias = left(strMail, instr(1, strMail, "@")-1)

'XDocument.UI.Alert "User: " & strLogin & vbcrlf & "Alias: " & strAlias &
vbcrlf & "Manager: " & objManager.Get("name") & _
'vbcrlf & "E-mail: " & strMail
GetADInfo = strAlias
End Function

I hope this helps!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.