Displaying Database Tables & Columns list on a mashup

[aseaton]

I have an MSSqlServer based thing and when creating a new service, selected as SQL Query/Command, the tables/columns tab allows me to include these into my script. How can i get this list of tables and columns from the database on a mashup?

[supandey · Accepted solution]

Hi Andrew, not sure if you already tried this but I think you can simply write a service querying the schema for all it's tables and the columns. As to the script for getting all of that you could check this S/O link

[aseaton]

HI Sushant,

Thanks for reply. Yeah this gives me the set of tables that i require. Now i want to bind this data to a list and then make the list display as a dropdown box. When i do so and configure, the Display field and Value field cannot be selected.

I have given the service a datashape to format  the returned data but this has not helped. I know that when using a grid widget and binding data without a datashape that the 'Show All Columns' checkbox can be selected to correctly display the data in the mashup but there is no such checkbox with the list widget? is there any advice on this?

Thanks

[supandey]

To confirm if the Datashape is leading to right output I would test that service's output in a Grid just to see what is returned and how it's returned. I've not seen the code but it appears as though the services is likely not returning the result as expected.

[aseaton]

Actually when the data is bound to the grid the data is shown correctly, but not the dropdown at the moment.

[supandey]

Interesting. Would it be possible for you to share the DataShape Definition here may a screenshot of the DataShape. Also it would be helpful if you can elaborate a bit on what the service is returning like how many columns and so on.

Just to confirm when you are binding to the list are you taking All Data or are you binding only a particular column there? For DisplayField and ValueField to be configurable you'll need All Data.

[aseaton]

The datashape is just a simple one column: Name.

Here is the result from the service. Just a list of the table names:

Yes, I am binding with All Data. In the Display Field in am selecting Name. I have also tried with the Value field but the results are not displayed in the list on the mashup.

[aseaton]

I have corrected it now. The problem was that i had written the query as:

SELECT name FROM sys.tables

And the datashape is defined as having a column: Name

Since i have changed the query 'name' to 'Name' the list is visible.

Thanks for the Help.

Andy

[supandey]

Yeah it sounded something off with the datashape. Gald it worked out though.

Cheers.