SQL multiple tables, web service or stored proc?

[dw]

Hello -

I am new to InfoPath (i am a sql database developer), but i need to support
an InfoPath project.

I am wondering if an InfoPath form can support multiple, but related tables
in sql server? For example, I have an employee table and also some
associated tables like department, projects, etc. Someone mentioned that
InfoPath might not support multiple, related tables...and that it really
works best with just a single table. Is that accurate?

Also, rather than using the built-in InfoPath functionality to generate sql
statements, i am thinking about creating a web service to a stored proc (http
endpoint) and have that as my data source - one for queries and one for
inserting/updating data. Are there any issues that i should be aware of to
handle this situation?

thanks,
- will

 

[Don Reamey \(MSFT\)]

It depends on what you mean by support multiple tables. If you create a data
connection based on a query that joins mulitiple tables then that works
fine. However, that would only work for a query not a submit.

Can you provide more detail on what you are trying to do?

Also you can right code if you have a complex submit.

Will this form run on InfoPath Forms Service?

Don Reamey
Software Development Engineer
Microsoft
http://blogs.officezealot.com/dreamey

 

[dw]

Hi -

By supporting multiple tables, I meant I have a parent table with several
child/related tables that i would like to be able to update/insert via the
submit. From what you wrote, it sounds like InfoPath is very limited in that
it will let me query multiple tables via a Stored Proc/web Service. Is that
right? But I cannot use the submit functionality to add or update data if I
need to touch multiple tables? When you state that i can write code if i
have a complex submit, do you have any examples on the web or articles? Is
that a work-around for being able to add/update multiple tables?

I will have to check on the InfoPath forms service question, I am not sure.

Thanks
- will

 

[Don Reamey \(MSFT\)]

Basically what you will do is create a clicked event for your button.

With in the event you will grab the data for all fields that are going to be
submitted i.e.

XPathNavigator nav =
MainDataSource.CreateNavigator().SelectSingleNode("/my:myFields/my:group1/my:field1",
NamespaceManager);

Then you can build an insert statement into your tables i.e.

"Insert into table values "+nav.InnerXml.

Since the code is .Net code you can use any type of data connection API to
send the insert statement back to you database.

InfoPath, like many other applications do not support the update because it
is extremly difficult to know how to update a database with out having a
complex rules that dictate the insert.

I can try to put a more complete example on my blog.

 

[dw]

Hi Don,

Thanks for the reply and help. If you have a chance to put an example on
your blog, that would be awesome and it will really help me understand what
you are describing.

Where is your blog located?

thanks
- will

 

[Don Reamey \(MSFT\)]

I will try to create an example for you.

 

[dw]

Thanks Don...that will be great.

I will try to create an example for you.
--
Don Reamey
Software Development Engineer
Microsoft Corporation
http://blogs.officezealot.com/dreamey