Displaying SQL Database Table Information On Form

[dmiller]

I am new to InfoPath and not sure how to do this.

I am trying to display all rows in a table automatically when the form
loads. Then, the user will have the ability to add/change/delete this
information from the table. I'm assuming that I should use a "repeating
table" control but I can't get this to work.

If anyone could help me with this, I would greatly appreciate it!

Thanks in advance,

Dave

 

[dmiller]

I have figured out how to display the table information buy adding a "run
query" button to the form but would still like to know how I can get the data
to be loaded automatically when the form is displayed. I am new to VB script
as well, and I am not sure exactly how to do this in code (which is where I
assume this would need to happen).

I also figured out how to change/delete the rows as well.

Again, thanks in advance for any help!

Dave

 

[Scott L. Heim [MSFT]]

Hi Dave,

Here is some sample steps:

- Open your InfoPath solution in Design View
- From the Tools menu choose Form Options, select the Advanced Tab and make
a note of the programming language (VBScript or JScript)
- From the Tools menu choose Programming and then select "On Switch Views"
event - you should now see the following:

- VBScript:

Sub XDocument_OnSwitchView(eventObj)
' Write your code here
End Sub

- JScript:

function XDocument::OnSwitchView(eventObj)
{
// Write your code here
}

- Add the following code:

- VBScript (this would be added on the line just before the End Sub text

XDocument.Query

- JScript (this would be added on the line just before the closing brace
("}"):

XDocument.Query();

This code is essentially the same as clicking the "Run Query" button.
However, there is a concern here I want to make you aware of: if you are
returning a lot of records from your table (i.e. in excess of, say,
200-300) then this will impact your performance. What you would want to do
in this case is to set a value in one or more of your "query fields" so
that records are only returned for those values. If you want to look at
this option, you can use the following steps:

- Open your InfoPath solution in Design View
- From the Tools menu choose Form Options
- Select the Open and Save tab
- Click the Rules button
- Click Add
- Click Add Action
- From the Action box choose Set a field's value
- Click the builder button next to "Field" and choose one of the fields
below "queryFields"
- In the "Value" box enter a valid default value (i.e. if you have an "ID"
field, enter a valid ID in the Value box)
- Click OK four times to get back to your form and test - the code should
still execute but should only return those records that match the "query"
information you added.

I hope this helps!

Best Regards,

Scott L. Heim
Microsoft Developer Support

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

 

[dmiller]

Thanks very much Mr. Heim! This is exactly what I was looking for.

Dave