Data View Web part, stored procedures, and sorting

Question

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?

 

Answer 1

 

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

Answer 2

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 <aspqlDataSource> and other times it was created as <aspPSqlDataSource>.  I don't know why or what the difference is...

 

Answer 3

Hi JXJ,
can you please explain little bit? i really need your help regarding this.
i am facing the same problem.
i am using the SPROC binding to the Data view web part. and enabled sorting. it's failed to sort.
can you please help me out? how did you solve this problem?
it seems, you are passing two extra parameters to the SPROC?
When i add the below code between sqldatasource tag, it is giving me the errror...

<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>

please help me out!!!

-PRaveen.

---

.net developer

Answer 4

Hi,

I am also having the same requirement to sort Data View with datasource being SQL.
I added <WebPartPages:DataFormParameter  .. /> inside <asp:SqlDataSource> tag as given above and called stored proc, but I am getting error. Just using select query is also not sorting data view.

Also, i am not sure how the stored proc can be udpated to accept sort parameter. We are not able to add sort field and direction as variable parameter as procedure compilation fails.

ALTER PROCEDURE [dbo].[GetSQLData] (
@SortField varchar(20),
@SortDirection varchar(50))
as
BEGIN

SELECT * FROM employee ORDER BY @SortField @SortDirection  -- gives error

END

Any help on this will be really appreciated.

Answer 5

Hi Ramesh,

You can't write SQL query like that.

and dynamically you can't pass column names in where, order by, or aggregate functions.

What you need to do here is, build the query in a string and using execute function execute it.

So, your query would look like this.

Execute ('SELECT * FROM employee ORDER BY '+@SortField+' '+@SortDirection);

Please let me know, if you have any issues...

-Praveen.

---

ASP.NET and SharePoint developer
Company: http://www.rampgroup.com/
Blog: http://praveenbattula.blogspot.com

Answer 6

Thanks Praveen. Your suggestion worked.

Just for benefit of others who want to have sort ability in data view webpart where data source is SQL server. I spent a long time in resolving this. Using SQL profiler helped me to understand what is hapening. So, use SQL profiler in case you have any issues.

For benefit of others:

Steps:
1. Create procedure with your query and input parameter for sort filed an sort direction.
eg:
CREATE PROCEDURE [dbo].[SP_GetEmployee] (
@SortField varchar(30),
@SortDirection varchar(20))
as
BEGIN

IF(@SortDirection = 'ascending')
set @SortDirection = 'ASC'
else
set @SortDirection = 'DESC'

Execute ('SELECT  * from Employee ORDER BY ' + @SortField+ ' ' + @SortDirection);

END

2. Enable sorting and filtering in header column in Data View Properties.

3. Modify the SqlDataSource  in aspx page using designer to following

<asp:SqlDataSource runat="server" ID="SqlDataSource5" SelectCommandType="StoredProcedure" SelectCommand="SP_GetEmployee" ConnectionString="Data Source=dbserver;User ID=login;Password=pwd;Initial Catalog=dbname;" ProviderName="System.Data.SqlClient" __designer:customcommand="true"><SelectParameters><WebPartPages:DataFormParameter PropertyName="ParameterValues" ParameterKey="dvt_sortfield" DefaultValue="LastUpdated" Name="SortField"></WebPartPages:DataFormParameter><WebPartPages:DataFormParameter PropertyName="ParameterValues" ParameterKey="dvt_sortdir" DefaultValue="descending" Name="SortDirection"></WebPartPages:DataFormParameter></SelectParameters>
</asp:SqlDataSource>

Note: selectparameters tag was missing in above thread. Its required for sort/filter to work. Else, it throws error that "SPSqlDataSource does not have a public property named DataFormParameter"

Answer 7

Splendid. that's good to know it worked for you.

Nice buddy. I like the way you have posted the whole solution here.

We have resolved this, but didn't get time to write complete steps. I appreciate your work here. Surely this will help to so many people around the world.

thanks

---

ASP.NET and SharePoint developer
Company: http://www.rampgroup.com/
Blog: http://praveenbattula.blogspot.com