I've been having a really difficult time finding out how to run a query which selects records from an SQL database for the current logged on user. I've tried a whole manner of different approaches but here is where I am at now:
<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Hello.I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:http://forums.asp.net/p/1159666/1913519.aspx#1913519http://forums.asp.net/p/1161930/1924264.aspxhttp://forums.asp.net/p/1116601/1732359.aspx#1732359http://forums.asp.net/t/1104718.aspxhttp://forums.asp.net/p/1096290/1655706.aspx#1655706http://forums.asp.net/p/1110162/1707952.aspx#1707952 Basically, I need a DropDownList to display only projects for which the logged in user is assigned as leader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code? * Here is the definition of the SqlDataSource: <asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>" SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @Leader)" OnSelecting="SqlDataSource5_Selecting"> <SelectParameters> <asp:Parameter Name="Leader" Type="Object" /> </SelectParameters> </asp:SqlDataSource> * Here is the definition of the SqlDataSource5_Selecting method: protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { e.Command.Parameters("@Leader").Value = loggedInUserId; } where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey; Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId; in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.
Hi! I would like to select the 10 latest logged in users and then display them in a gridview-control.... But Iam not good at SQL, so I would really appreciate if someone could help me to write the select-part for this!! (I use the database 'ASPNETDB.mdf', which is automatically created by VVD 2005 Express Edition)
I have a database in Simple mode from which I wish to delete several million rows of one table (not all rows so 'TRUNCATE TABLE' is no good), without making the transaction log file grow to several GB in size. Any ideas??? I'm almost at the point where I might create a new table and copy the records I want to keep instead... Thanks, Simon.
Dear GroupI wondered whether there's a function or script that will show mewhether a user is currently logged-on to a MSSQL 2000 database? I'musing SQL Authentication.Thanks very much for your help & efforts!Have a nice day!Martin
Hi, I wanted to know How can i find out that with which user i am logged in. Is there any command to find out the same. ( like in oracle, we have command -- showuser, which will return the logged in user of your session).
One of them literally has hundreds of sql logins, obviously i need to clean these up, however i a not sure how to determine when a user last logged in ?
How can i find out the last login date of a user ?
NOTE: I am talking about roles in my sql server - NOT in asp.net. I need to create a stored procedure that retrieves the roles that the currently logged in sql user has for a different database. I have the code that gets the roles for the user, but it only works if the user is in the database. I want to be in one database, and get the roles for a different database. I have tried using USE DATABASE, but this is not allowed in a store procedure.
I have created a SharePoint external list using SQL Server, the data source is SQL Server. Is there way that I could get the logged-in SharePoint user and stored in SQL Seever?
I need to get the computer details of the person that logged in. I have a client that needs me to write a computer asset logging system for them. This is a one table database that will keep all the computers in the company's details like serial number, bios serial number, ram size disk size, mother board info etc. This is a vast number of details and what they want to do is when for instance the ram changes e.g. taken out or added they need the program to update the table with the new information once the computer starts again.
I have created table called Login in sql server where i have column usercode, email and login_date (login_date is datetime type)So, i created web application using .net. whenever user logged in, i am allowing based userLoged table and  i am inserting into login table.
login table usercode email    login_date 001      a@gmail.com   2015-11-18 22:02:41.153 001      a@gmail.com   xxx . . .
I have another table called userLoged where i have column usercode,email and web_accessÂ
UserLoged table usercode email     web_access 001     a@gmail.com  Y
Now, if a@gmail.com (001) is not logged in for 45 days, i need to update web_access to be 'N' how to know if he /she not logged for 45 days.
Now, I have a report where whoever is logged in will only see data for themselves and those below them, so John Smith would see everyone including himself, but Lisa Andres would only see herself and Bob Thompson. James Jones would be able to see everyone except John Smith.
How do I go about implementing this code, for example in an asp.net page where one of the user's logs on to view the report. Currently, there is a T-SQL function that creates a user heirarchy table, but it is very slow and I am curious if SSRS 2005 has any new capabilities in handling this.
I have these columns. TicketID, User, Date...I want to select 10 random tickets for each user for the past 30 days. I not sure whether to do this via sql or using VB in the report design.
How do I insert the UserName of the Logged in user into the DB field UserID. I created a web form page, and added a form view control set the page default to Insert Mode. The Page is used to insert data. I have a hidden field called UserID that I would like to capture the Logged in user. I have logged in to the default page with userID and password, added a new record, and the db table field UserID is empty. I have been trying to figure out what I am doing worng, but no luck, Please suggest the best way to do this. I have listed what I have done thus far? I have added the login Control from Login and added it to the page as a hidden field thinking that I needed to have this field on the page to get this to work where you copy the value in the Loginname field to the useridTextbox on the formview1 insert template. I read on line where the method below is better. Please tell me what I am doint wrong. System.aspx---------------------------------------------------------------------------------------------------------------1) <asp:FormView ID="FormView1" runat="server" AllowPaging="True" DataKeyNames="SystemID" DataSourceID="SystemSqlDataSource1" DefaultMode="Insert" Width="583px">2) <asp:TextBox ID="UserIDTextBox" runat="server" Text='<%# Bind("UserID") %>' Visible="False"></asp:TextBox><br />3) <asp:SqlDataSource ID="SystemSqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RCMISConnectionString %>" DeleteCommand="DELETE FROM [tblSystem] WHERE [SystemID] = @SystemID" InsertCommand="INSERT INTO [tblSystem] ([SystemID], [SystemDesc], [Inactive], [TimeStampEntry], [TimeStampUpdate], [UserID]) VALUES (@SystemID, @SystemDesc, @Inactive, GETDATE(), @TimeStampUpdate, @UserID)"4) <InsertParameters> <asp:Parameter Name="SystemID" Type="String" /> <asp:Parameter Name="SystemDesc" Type="String" /> <asp:Parameter Name="Inactive" Type="Boolean" /> <asp:Parameter Name="TimeStampEntry" Type="DateTime" /> <asp:Parameter Name="TimeStampUpdate" Type="DateTime" /> <asp:Parameter Name="UserID" Type="String" /> </InsertParameters> </asp:SqlDataSource>System.aspx.vb---------------------------------------------------------------------------------------------------------5) Partial Class MemberPages_RCM_frmSystem Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'UserIDTextBox.Text = Membership.GetUser().ProviderUserKey.ToString() End Sub Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted 'Set the UserID Value to the currently logged on user's ID e.Values("UserID") = Membership.GetUser().ProviderUserKey End SubEnd ClassThank you in advance,
I have some sensitive and non-sensitive info/fields in one of my reports. Is there a way to hide/show the info based on the logged in user? or do I have to create separte reports for each type of info like one for sensitive and another one for non-sensitive info.
We are using Windows authenication within our system, and I was wondering how it would be possible to determine if the user conected to the SQL SERVER instance was a member of a particular active directory security group?
I have a table in SQLCE with ProductCode, Age, Rate, Gender, EffectiveDate. I need to get the list of records with the latest effectivedate not later than the current date for the combination of the other fields.
I have a problem with a date in my sql view, I need the records from the 3 last months but without the current month, if I execute my view right now I have the records from January to april but I just need from January to march, I must have always the 3 previous month but I don't know how I can do it
Am using SQL Server 2000, and am writing some audit triggers. How do I getthe the name of the user, eg. user_id or current_user. Just spent an hourlooking through the SAMS book for this, and can't find any reference.
My question is concerned with the three columns below (customerID, RepairDate, CompletedRepair (Yes or No). The column name "CompletedRepair " is blank initially. I need to update the CompletedRepair column with this logic below:
- A customer comes to our store to fix their car, if we fix their problem on the first time and they don’t return later for this same issue, then the •CompletedRepair column = Y
- If a customer needs to come back to our store to re-fix the same issue within 7 days windows based on the RepairDate on the previous transaction then •On the last return transaction: CompletedRepair = Y (example: RepairDate =6/12/2006) •On all previous transactions: CompletedRepair = N (example: RepairDate =6/8/2006, 6/9/2006, 6/10/2006)
- If a customer needs to come back to our store to re-fix the same issue but out of the 7 days windows based on the RepairDate then •On the last return transaction: CompletedRepair = Y (RepairDate =6/12/2006) •On the previous transaction: CompletedRepair = Y (RepairDate =6/1/2006)
Every time customer comes to for car repair shop for a new issue or an old issue, we create a new repair transaction in our SQL db. The update on the "CompletedRepair " column will be run every day. Today's records will be run against with last 7 days records (based on Repair Date) to check when customer has been really fixed: the last fix counted Y, the previous fix counted as N but comparison in only 7 days. In other words, a repair today is considered as a completed repair when comparing with last 7 day repairs but it might become not a completed repair if this same customer would come back within next 7 days for the same issue.
The CompletedRepair column is dynamic column and is updated daily by using the logic above.
Below is the expected outcome after we update the Completed Repair column:
CustomerID Repair DateCompleted Repair
ab1 06/12/06 Y ab1 05/28/06 Y ab1 05/18/06 Y ab105/15/06 N ab1 05/12/06 N
Initially 5/12/06 had Y, when 5/15/06 transaction came, it took the Y and made the 5/12/06 become N. The 5/18/06 transaction did the same to 5/15/06 transaction, made itself Y and converted 5/15/06 into N. The 5/28/06 is Y because comparing with 5/18/06, it is out of 7 days window. The 6/12/2006 is Y because comparing with 5/28/06, it is out of 7 days window.
ab2 06/02/06 Y ab2 05/28/06 N ab2 04/19/06 Y ab2 04/14/06 N
The 4/14/06 transaction initially was Y, it became N when new transaction on 4/19/06 came. Same thing with transactions on 5/28/06 and 6/2/06
ab3 05/11/06 Y ab3 03/29/06 Y ab3 03/23/06 N ab3 03/12/06 Y
The 3/23/06 was Y, when new transaction on 3/29/06 came, it became N and the new transaction is Y. The 5/11/06 is Y because comparing back to 3/29/06, they are out of 7 days window.
ab4 05/11/06 Y
This ab4 customer came to fix her car only one time and don't come back. We supposed the fix was sucessfully and so we mark the CompletedRepair as Y.
I think that I would need to use SQL cursor or case statement for this but I really don't know how to start. Please advice and help me out. Any ideas and suggestion are really appreciated! If you need more information, please let me know!
I am using the following query in a view to retrieve the latest 24 hourly records for a site.This returns 24 hourly records for the last day of measurements at a Site.This works great. However, I now need to retrieve the latest hourly records from the current hour. For example, hours will run from 00:00 to 23:00 and if the query is executed at 15:00, I will return only hourly records for 00:00 to 15:00 etc. I believe I need to filter the result set or modify the query to exclude records greater than the current hour.
Hi, I am using SQL server 2005 (express). I have a user like "test" with some password. After login with this userid and password i tried to get the current user executing
Print CURRENT_USER but its displaying the result as 'dbo' instead of 'test'. In server roles for this particular login i have marked all the options.
Can anyone tell me why i am getting like this? What permission/setting i am missing?
I have some my below requirment to loading some last year and currnet year records for some ID's in my table,
We have to load the ID's that are active at the end of the year for the prior year and ID's that are active as of today for the current year.Here is the scenario when the ID is currently terminated but active at the end of the prior year and the record is not in the table.so, we didn’t load the count for the prior year
Here prior year is 2015-2015 and Current year is 2015-2016
CREATE TABLE remp_year (ID INT, STATUS NVARCHAR(100) NULL, START_DATE DATE NULL, END_DATE DATE NULL, date_year nvarchar(10) NULL)INSERT INTO remp_year VALUES (10,'Active','2015-05-26','2015-12-31','2015-2016');
[Code] ...
Here ID 20 and 50 for terminated records is the prior year records so it should count for the last year and those are active in this year those will count for this year.
After some help from the folks in the Security forum, I have some of the answer I need. Hopefully someone here can help me figure out the best method of using that information to find a final solution. To start, here's a brief synapsis: I'm developing an App in ASP.NET 2.0, using forms-based authentication and the Login Control. I am able to login fine as far as I can tell, since I am able to proceed to the destinationURL without error. From there, I button click to the application in question. Here is where I have the problem. I want to run a select query on a SQL table where a field = the current user's Username. I cannot find any examples of doing this. I have managed to figure out how to retrieve the current user's username via the Membership.GetUser method, but I can't seem to figure out how to apply that to my SqlDataSource Control and get a valid response. a snippet of my control's code follows: <script runat="server"> - This is run inline, not code-behindProtected memUser As MembershipUser
Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)memUser = Membership.GetUser()End Sub</script> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SWGToolsetConnectionString %>"SelectCommand="SELECT [CharacterName] FROM [Characters] WHERE ([UserID] = '<% =Server.HtmlEncode(memUser.Username) %>')"></asp:SqlDataSource> If anyone can tell me what I can or should be doing differently, I would appreciate it.
I have a SQL 2005 question for you. In the SQL server Management Studio. When I right click on the database and use the SQL Server Import and Export wizard, I can import databases but cannot export them.
The error says:
The current user is not an owner of the selected database. This might cause some operations with this database to fail.
I use sa to loginto the server Management Studio, does anyone know how to create a database where the user and owner are the smae so I can export?
Hi all... Iam a newbie and i have one question want to ask experts ... . I am current working on one App which have some user connect to database at the same time. And i want to know: how many user connect or use (read , update) to one row in a table? Could it be possible to know that??? If you have one solution to solve this problem , please let me know :) ... Could it be done by Software , T-SQL or anything ...., iam happy to know. Thanks all.
