3

I have a 2010 Enterprise Farm, and I am attempting to place a Data View Web Part in the Ribbon to display content from an external SQL database as part of a gamification initiative. Basically, I need the DVWP to show a blank/bronze/silver/gold badge icon if the [Current User] has earned one, per the results of a query that hits that external SQL database.

I have created a custom td on the masterpage, and have successfully inserted the DVWP into it. I have successfully displayed the results of a query in that DVWP. Where I'm stuck is on the last two pieces: 1.) Just displaying data for the [Current User] 2.) Displaying the blank/bronze/silver/gold badge icon

I'm relatively new to code, so I suspect I'm missing something obvious. Here's a mockup of what I'm trying to do:

enter image description here

Here's as far as I've gotten:

enter image description here

Any suggestions? Your help is greatly appreciated!!

Improve this question

2 Answers 2

Reset to default
1

The Dataview web part knows who the current user is, there should be a parameter defined as such.

<ParameterBinding Name="UserID" Location="CAMLVariable" DefaultValue="CurrentUserName"/>

You can leverage that to pass into your query to your external source. Another option is to use the IIS Server variable REMOTE_USER as an available parameter.

The way I'd do the badges in the DVWP is to put all 4 of them in the markup and use conditional formatting to show/hide based on the results of your query.

Improve this answer
3
  • That's perfect; thank you! Although I think I've stumbled onto a new issue - the connection string I've been given is using a SQL function, and to follow your advice, I need to parameterize it. So, what I have is this: Select VotesDB.dbo.GetVoteeBadge('domain\username') and what I understand from your comment, I need: Select VotesDB.dbo.GetVoteeBadge where username = @UserID Is that possible/even at all correct?
    – Paul
    Feb 21, 2013 at 19:39
  • Since UserID is a variable, you'd use $UserID. UserID typically returns Firstname Lastname. Hopefully there is a mapping/field on the SQL side to accomodate.
    – Eric Alexander
    Feb 21, 2013 at 21:04
  • Added an additional option of IIS server variables.
    – Eric Alexander
    Feb 21, 2013 at 22:58
1

Problem solved! It wound up being easiest to use a view, not a stored procedure or function. Once I made that change, there were no more “The server returned a non-specific error when trying to get data from the data source. Check the format and content of your query and try again. If the problem persists, contact the server administrator.” errors when attempting to apply a filter.

Once the view was created, I established a Database Connection in SharePoint Designer pointed at said view. Next, I inserted a DVWP into the td I had created in the masterpage, between s4-pagedescription and s4-socialdata-notif.

Then I defined a parameter using the IIS server variable LOGON_USER (thank you PirateEric!), and filtered the DVWP using said parameter.

Finally, I modified the XSL in to places: the td class “ms-vb” and the XSL template dvt_1.empty. My changes conditionally display a badge contingent upon a particular query result for the currently-logged-in user (e.g. 3,3 if a vote was cast and received), and effectively hides the badge area if no results are returned. I imagine that there’s a more elegant way to accomplish the same thing, but what I’ve done appears to be working the way I need it to for now.

Conditional XSL markup for badges

Modified dvt_1.empty template

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.