How to Programmatically Obtain Data from SQL Source

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D Myers - 14 Apr 2006 16:10 GMT

I have a data connection to SQL source that I want to use to populate a
Bulletted List. Two problems exist: 1) I need to parse the data to remove
duplicate entries, and 2) I need to expand my Bulletted List with each item
added from the parsed data. So, how do I do this all programmatically?

Reply to this Message

Adam Harding - 18 Apr 2006 17:13 GMT

D Myers

Non-programatically i can do it in a repeating table.

Specify a repeating table with the datafields you want give the users a
button to pull back the data as well, even filter it using queryfields.

Now go into the properties of the repeating table and specify to populate
the table with the results of a query in SQL. Design the query in SQL to
filter out duplicates and that sorts it.

Hope this hleps

Adam

> I have a data connection to SQL source that I want to use to populate a
> Bulletted List. Two problems exist: 1) I need to parse the data to remove
> duplicate entries, and 2) I need to expand my Bulletted List with each item
> added from the parsed data. So, how do I do this all programmatically?

Reply to this Message

D Myers - 19 Apr 2006 00:01 GMT

Thank you, Adam.

Here is the query I am using for the Data Connection: select
"BugID","ProductReleaseName" from "dbo"."Report_Mitigations_Workarounds" as
"Report_Mitigations_Workarounds" I append "Where 'BugID' = 'ID' using the
queryAdapter in the code to get the specific entries I am seeking. Next, I
need to remove the duplicate ProductReleaseName results. I am somewhat of a
novice, so any hints regarding the query would be appreciated.

I have also setup a Bulleted List called Software. I have set the default
value to @ProductReleaseName[@BugID = ID]. The list contains only the first
item returned. It also does not expand. (This same problem exists without
using a repeating element. For example, I only see the first item in a text
box.) Again, any help here would be greatly appreciated.

> D Myers
>
[quoted text clipped - 15 lines]
> > duplicate entries, and 2) I need to expand my Bulletted List with each item
> > added from the parsed data. So, how do I do this all programmatically?

Reply to this Message

Adam Harding - 19 Apr 2006 09:27 GMT

Hi

Are you familiar with the Select DISTINCT function if not have a look at

www.1keydata.com/sql/sqldistinct.html

This returns only non-duplicate entries on one or more columns in your table.

As to your other problem, the bulleted list will never work since it does
not "repeat" a text box on its own cannot repeat either, add a repeating
section or table and that should work.
If it does not then yo uneed to check that your data source is set up
correctly to repeat.

Cheers Adam

> Thank you, Adam.
>
[quoted text clipped - 30 lines]
> > > duplicate entries, and 2) I need to expand my Bulletted List with each item
> > > added from the parsed data. So, how do I do this all programmatically?

Reply to this Message

D Myers - 20 Apr 2006 16:23 GMT

Hi Adam,

Thanks again for helping me through this problem. My current query is:

select "BugID","ProductReleaseName" from
"dbo"."Report_Mitigations_Workarounds" as "Report_Mitigations_Workarounds"
where 'BugID' = 'my:ID'

I also tried:

select distinct "BugID","ProductReleaseName" from
"dbo"."Report_Mitigations_Workarounds" as "Report_Mitigations_Workarounds"

The first one returns everything without the "where" statement, but once the
statement is added nothing is returned.

The second on seems to limit the results but I still need to find match up
the BugID with the ID in my form.

I used a repeating section to display the results. Is there a way to
capture the results into a column when the form is saved? That was my hope
when I tried using a repeating field (bulleted list). I was going to merge
the data in the column. I need the data in a format that I can later access
for reporting purposes. It's my understanding that it is embedded into the
XML with the repeating section.

Ideas?

> Hi
>
[quoted text clipped - 46 lines]
> > > > duplicate entries, and 2) I need to expand my Bulletted List with each item
> > > > added from the parsed data. So, how do I do this all programmatically?

Reply to this Message