asked on

ASP.NET recording user id in SQL Server

Hello,

I have a simple ASP.NET page that I want to capture the AD account a user is logged in with and record it in a SQL Server database. I have the following SQLDataSource defined below. I tried using SUSER_SNAME(), but realized that this only provided the account that I used to establish the database connection and not the AD account. Is there an easy way to get the AD account and pass it into a Insert Parameter? Does anyone have an example?

Thanks!
john

<asp:SqlDataSource ID="insTransaction" runat="server" ConnectionString="<%$ ConnectionStrings:MARTConnectionString %>"

InsertCommand="INSERT INTO TT_TRANS(ProjID, EmpID, TRANDATE, Hours, Description, Billed, CreatedBy) VALUES (@ProjID, @EmpID, @TDate, @Hours,@Desc,'N',SUSER_SNAME() )"
oninserted="insTransaction_Inserted">

<InsertParameters>
<asp:ControlParameter ControlID="DropDownList2" Name="ProjID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DropDownList1" Name="EmpID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="TDate" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox2" Name="Hours" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox3" Name="Desc" PropertyName="Text" />
</InsertParameters>
</asp:SqlDataSource>

YZlat

first you will need to disable anonymous access and then use User.Identity.Name to retrieve the windows logged in name

ASKER CERTIFIED SOLUTION

YZlat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jzlamal

ASKER

Sorry, but I am new to ASP.NET. How would I put this into the insert parameter for the SQLDatasource? Can you give me an example?

Thank you!
John

YZlat

to get username without domain use

Dim usr, arrusr() As String
        usr = System.Security.Principal.WindowsIdentity.GetCurrent.Name
        arrusr = usr.Split("\")
        usr = arrusr(1)

Open in new window

YZlat

try this:

InsertCommand="INSERT INTO TT_TRANS(ProjID, EmpID, TRANDATE, Hours, Description, Billed, CreatedBy) VALUES (@ProjID, @EmpID, @TDate, @Hours,@Desc,'N',System.Security.Principal.WindowsIdentity.GetCurrent.Name.Substring(System.Security.Principal.WindowsIdentity.GetCurrent.Name.IndexOf("\") + 1))" 
oninserted="insTransaction_Inserted">

Open in new window

YZlat

or try something like this:

InsertCommand="INSERT INTO TT_TRANS(ProjID, EmpID, TRANDATE, Hours, Description, Billed, CreatedBy) VALUES (@ProjID, @EmpID, @TDate, @Hours,@Desc,'N',@ADUser )" 
oninserted="insTransaction_Inserted">

<InsertParameters>
<asp:ControlParameter ControlID="DropDownList2" Name="ProjID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DropDownList1" Name="EmpID" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="TDate" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox2" Name="Hours" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox3" Name="Desc" PropertyName="Text" />
<asp:ControlParameter Name="ADUser" PropertyName="Text" DefaultValue='<% System.Security.Principal.WindowsIdentity.GetCurrent.Name.Substring(System.Security.Principal.WindowsIdentity.GetCurrent.Name.IndexOf("\") + 1))
oninserted="insTransaction_Inserted">
%>'> />
</InsertParameters>
</asp:SqlDataSource>

Open in new window

YZlat

or you can do that from code using

insTransaction.InsertParameters("ADUser").DefaultValue = System.Security.Principal.WindowsIdentity.GetCurrent.Name.Substring(System.Security.Principal.WindowsIdentity.GetCurrent.Name.IndexOf("\") + 1))

Open in new window

jzlamal

ASKER

Tried to use the example with the ADUser control parameter. Is there a different type of parameter I should be using? When I tried this, it said I needed to specify the controlid....

Thanks!
John

YZlat

can you post the code for the rest of your page?