SQL Database Integration

Re: Submitting and Retrieving from a database

chipdavis — Mon, 22 May 2006 15:36:46 GMT

Thanks for the information. I ended up adding a rule under "Form Options - Save and Open" to run a query when the file is opended. The query runs and fills the form with the information from the database. So far, it seems to be working.

I might try out the OnLoad event just to see how that works. When I created the form I selected "New form from a data connection."

Note: I did select "auto retrieve data when the form is opened," but when I open the SharePoint form (and don't run a query), I can't update the database.

Thanks again for your help.

Chip

Re: Submitting and Retrieving from a database

Abhishek — Mon, 22 May 2006 14:29:13 GMT

Hi ChipDavis,
When u create a data connection to ur database,there is a checkbox available in the final stage.
"Automatically retrieve data when the form is opened".
Checking this will enable u to retrieve data when the form opens.
Another option is to use code in the onload event of the form.
In the Tools menu, select Programming -->OnLoad Event.
In ur eventhandler,write the following code
thisXDocument.DataObjects["<DataConnection Name>"].Query();

How did u create this form?
"New blank form" or "New from a data connection?"

Re: Submitting and Retrieving from a database

chipdavis — Fri, 19 May 2006 14:13:52 GMT

I have an Access table set up with a primary key. When I open the form stored in the SharePoint library, I am changing a value other than the key. If I save the form, the table is not updated (only the SharePoint form is updated). If I submit the form, InfoPath is trying to insert a new row into the Access table instead of updating the existing row.

Is there a way I can run a query against the database when I open the form from the SharePoint library (repopulate the form with the database values)? I need to somehow tie the SharePoint document/form to the database record that was already saved. I want this to happen automatically when I open the document so I don't have to manually run a query.

Chip

Re: Submitting and Retrieving from a database

Abhishek — Fri, 19 May 2006 05:49:51 GMT

Hi ChipDavis,

The primary key for every row should be unique.
Are u by any chance repeating this primary key for any of ur rows when u update and resubmit the form.
Give unique values for the primary key field before u resubmit and let me know.
Regards,
Abhishek

Submitting and Retrieving from a database

chipdavis — Thu, 18 May 2006 21:06:08 GMT

I would like to fill out an InfoPath form on SharePoint and submit the form to a SharePoint library and a database (Access for now, SQL Server in the future). Once the form is submitted, I want to open the form from the SharePoint library and reestablish the data connection with the record in the database. That way I can make changes to the form in the SharePoint library and UPDATE the database with the new values.

So far, I have been able to submit the form to the SharePoint library and the database. However, if I want to update some values on an existing form by opening the SharePoint library form, the database does not update with the new values when I save. If I change a field value and try to resubmit, I get a duplicate row insert error (I'm trying to update, not insert).

Note: I don#%92t want to select a “query” button when I enter the form.

Any ideas on how this works?

Thanks.

Chip