none
HOw to pass Current Logged in User to Sql Store Procedure

    Question

  • Hi Guys,
    I have Employee Information in Sql Tables.Now i want to display Information to Employee who logged in Sharepoint.
    I have ID in Tables so what i think create Store procedure and Pass current logged in User as parameters.

    Please some one point me to Direction to do this



    Thanks Ron
    Thursday, January 07, 2010 3:43 PM

Answers

  • OK Guys,I have Figured it Out.

    Let me Post the Solutions so it will Help someone Out there.

    Goal : You want to Filter Data from Sql Server Based on Current Logged in User.

    • Create Table with Column User_Id to store only User_id Part  from Domain\User_id.
    • Create a Data source which get Data from Sql Server including the column we have Created before.
    • Now Time to Create DVWP and Display Data from Previously created Datasource.
    • Create a Server Veriable called Logon_User and name its lets say LogonUser.
    • Now Time to Filter,Find <xsl:variable name="Rows" select="/dsQueryResponse/NewDataSet/Row " /> and Replace it with /dsQueryResponse/NewDataSet/Row[@JM_ID = substring-after($LogonUser,'\')] ,Where JM_ID is Column in Sql Table which hold User_id.
    • You have done.

    Thanks Ron
    • Marked as answer by RonCe Monday, January 11, 2010 10:42 PM
    Monday, January 11, 2010 10:41 PM

All replies

  • Ron - I haven't done this yet, but theoretically this should be possible. Create a dataview using SPD to get info from your SQL Procedure. I think you can use use Server Variable "LOGON_USER" but you'll have to parse the value because it contains the domain name in the value. Take a look at this from SPD Team's blog:

    http://blogs.msdn.com/spdsupport/archive/2008/07/25/data-view-data-form-parameters-you-don-t-know-about.aspx

    Hope this helps....


    Pman
    http://www.pmansLab.com/
    Friday, January 08, 2010 2:58 AM
  • HI Ron,

    You can pass the logged in user's information to the SQL SP. You can use:
    HttpContext.Current.User.Identity.Name 
    to get current user's name and pass the value to the SP as an input parameter
    Friday, January 08, 2010 5:54 AM
  • Thanks PL for Reply i know this Process but my question is how i map this Veriable to SP veriable in SPD.
    This where i am stuck 

    any idea on this

    Thanks 

    Thanks Ron
    Friday, January 08, 2010 4:06 PM
  • Hi Farhan thanks 
    but i dont have VS 2008/2005 to Write Managed Code.



    Thanks Ron
    Friday, January 08, 2010 4:07 PM
  • Hi Ron,

    Here's a pseudo guide. See if it helps. I didn't do this before and I can't try this out at the moment, so won't be able to give you exact step by step instruction. Not even sure if it'll work.

    1. Create a normal dataview that gets data from your sql table. For now, eliminate the condition in your query that will return data for a specific user only
    2. Once the dataview is set and you are getting data in your view, create a parameter in your dvwp that'll give you the current user as mentioned above (LOGON_USER)
    3. The output of that parameter will be "domainname\username"
    4. In the code view of SPD, after the paramter is set, create an xsl variable that will parse the above to give you just the username and store it in that variable
    5. Now see if you can use this xsl variable in your query so that only results relevant to this user is returned
    6. If you can't use it in your query, at least you should be able to filter the data using this variable and then display the results to the user

    I hope this helps...

    Pman
    http://www.pmansLab.com/
    • Marked as answer by RonCe Monday, January 11, 2010 5:24 PM
    • Unmarked as answer by RonCe Monday, January 11, 2010 9:16 PM
    Saturday, January 09, 2010 1:40 AM
  • OK Guys,I have Figured it Out.

    Let me Post the Solutions so it will Help someone Out there.

    Goal : You want to Filter Data from Sql Server Based on Current Logged in User.

    • Create Table with Column User_Id to store only User_id Part  from Domain\User_id.
    • Create a Data source which get Data from Sql Server including the column we have Created before.
    • Now Time to Create DVWP and Display Data from Previously created Datasource.
    • Create a Server Veriable called Logon_User and name its lets say LogonUser.
    • Now Time to Filter,Find <xsl:variable name="Rows" select="/dsQueryResponse/NewDataSet/Row " /> and Replace it with /dsQueryResponse/NewDataSet/Row[@JM_ID = substring-after($LogonUser,'\')] ,Where JM_ID is Column in Sql Table which hold User_id.
    • You have done.

    Thanks Ron
    • Marked as answer by RonCe Monday, January 11, 2010 10:42 PM
    Monday, January 11, 2010 10:41 PM
  • Hi Ron,

    Thanks for sharing the result. Could you please clarify the first step?

       > Create Table with Column User_Id to store only User_id Part  from Domain\User_id.

    I'm assuming that you added another column in your sql table that holds the user id. Is that right?


    Pman
    http://www.pmansLab.com/
    Tuesday, January 12, 2010 1:39 AM
  • Yes Pl,I did.

    I have Employee Table with column name User_id to store User_id after \ from current logged in User.
    I need to store bcoz all of employee data is in Sql Server.

    The only trick was to do /dsQueryResponse/NewDataSet/Row[@JM_ID = substring-after($LogonUser,'\')] which i came to know after reading some XSLT.

    anyway its working like charm



    Thanks Ron
    Tuesday, January 12, 2010 2:08 AM