JXJ

I know I'll probably never get an answer to this because it seems that no one uses the Data View Web Part and there is a dearth of documentation (sigh), but here goes...

In MOSS2007, I need to call a stored procedure and get the data back and display it in the Data View Web Part. I can do that.

The problem is enabling sorting. I can enable sorting in the DVWP and it allows me to click the column headings and supposedly sort the data - but it doesn't sort it. It does a postback, but doesn't affect the output.

I did some tracing with SQL Profiler, and found that if I use a SELECT statement, the sorting causes the web part to add the "ORDER BY" clause using the appropriate field.

However, since this is a stored proc, it does not seem to do anything - it just keeps calling the same stored proc over and over.

What I suppose I need it to do is pass the stored proc a parameter to tell it what order to sort the data - but I don't see any way to do that. I can pass the stored proc parameters via the DVWP, but how do I figure out what sort field was selected and pass that Anyone know



Re: SharePoint - Development and Programming Data View Web part, stored procedures, and sorting

Mayur Tendulkar

Hi,

I'm just trying to answer your question. Just let me know, if this works.

When you click on the column heading, just get the column that was clicked during post back, (you can get this information through event handler), then using this information and associating the same with your database, you'll get to know, by which parameter you have to sort out the data.

Then, for every column create a stored proc and execute accordingly.

I know, as this seems to be simpler, this can be a tough job. I just tried to figure it out.

Mayur Tendulkar

Microsoft Student Partner






Re: SharePoint - Development and Programming Data View Web part, stored procedures, and sorting

JXJ

Thanks - the problem is trying to figure out what to look at on the postback.

I think I have come up with a solution.

In the code for the web part (using Sharepoint Designer), I found the tag:

Code Snippet
<asp:SqlDataSource id="SqlDataSource1" runat="server" __designer:customcommand="true" ProviderName="System.Data.SqlClient" ConnectionString="Data Source=my_connect_string" SelectCommand="usp_myStoredProc" SelectCommandType="StoredProcedure" />

I edited this and changed the closing "/>" to ">".

Then within the tag, I added:


<WebPartPages:DataFormParameter PropertyName="ParameterValues" ParameterKey="dvt_sortfield" DefaultValue="my_default_sort_field" Name="SortField"></WebPartPages:DataFormParameter>
<WebPartPages:DataFormParameter PropertyName="ParameterValues" ParameterKey="dvt_sortdir" DefaultValue="ascending" Name="SortDirection"></WebPartPages:DataFormParameter>

This ends up passing parameters to my stored procedure of @SortField='field name' and @SortDirection='direction' where direction is 'ascending' or 'descending'.

When the web part posts back after clicking a heading column, the sort field is bound to "dvt_sortfield" and the direction is bound to "dvt_sortdir".

It took a lot of experimenting to get to that - I wish there was some sort of reference to how some of these tags/xsl can be used - I haven't found any.

Another strange thing - during several iterations of experimenting, I found that sometimes the data source tag was created as <aspTongue TiedqlDataSource> and other times it was created as <aspTongue TiedPSqlDataSource>. I don't know why or what the difference is...