What Is The Best Way To Select A Row Of Data In A Datasource Filtered By Username?
Oct 10, 2007
I'm using the asp.net login controls and am storing the user data in the sql table that get's automaticaly built by visual studio. I display records based on who is logged in. Currently i use a Session parameter in my datasource for the user name and store the username in a session variable on the page load event, Session["UserName"]= User.Identity.Name.
Is there a way i can use a Profile parameter in my data source and skip setting session variable all together? I tried putting Name and UserName and User.Identity.Name in the Profile property but nothing seems to work. Thanks for any help.
Hi I need to create an sqlDataSource that filters a table based on the users ID – in other words, I have a table with a list of users, and when a logged in user loads the page, the results should only show records with their user name. How would I do this? Thanks for any help in advanceRichard
Hi, I am using SQL Server 2005. I use the query SELECT * FROM APPOINTMENT WHERE [DATE] = '2006-01-15' and it doesn't work. However, this query SELECT * FROM APPOINTMENT WHERE [DATE] = '2006-01-15 19:55:44.000' is working. Is there a way for me to select data field by not having to specifying the time coz it is annoying. Thanks for any reply.
Hi.I bet this is a 101 question, but i'd appreciate any help!I am in the 'where...' section of the configure data source wizzard .Column: I grab 'UserName' Operator: I select '='BUT how do I get the UserName (The user is signed into the app) Is it from the Form? Profile? Session?Ive tried profile.name..... THANKS In advance.... Dan
(I asked this question in another forum, but think it may be more of asql question)Hi.I bet this is a 101 question, but i'd appreciate any help!I am in the 'where...' section of the configure data source wizzard .(Visual Studio 2005) Column:(From my table) I select 'UserName' from drop-downOperator: I select '=' from drop-downBUT how do I get the UserName (The user is signed into the app) to pass to the where clause?Is it from the Form? Profile? Session?Ive tried Form.Page.User.Identity.Name.ToString; Form.NamingContainer.Page.User.Identity.Name.ToString; Profile.UserName; NOTHING works. IF I set the default value to a user name (eg joe_user) it retrieves fine, but I need it to retrieve for the currently signed in user. THANKS In advance.... Dan
Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1. Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence. Can you do this thru SSRS? Any help would be great. Thanks for your time in advance. Kent
In asp.net 3.5..... To get the number of rows returned when the select is executed....... Protected Sub SqlDataSource1_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Selected Session("MessageText") = e.AffectedRows & " Profile(s)"End Sub How do you get the number of rows returned when you apply a filter expression to a selection of rows? Thanks Craig
VB ASP.NET 2.0 How do I get the Authenticated UserName passed to a Select Command of a data control ? I'm not clear on how to get the logged in username from User.Identity.Name as a string or how to pass it to the Select Command of a control. I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL. I want to show the user data from a database, based on who is logged in . Help, greatly appreciated. Chris
Can someone tell me why the syntax checker is choking on the following code? It doesn't like the SQLDataSource1 reference. Protected Sub cmdSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click SqlDataSource1.SelectCommand = "SELECT * FROM users WHERE username='" + Trim(txtUsername.Text) + "' AND password='" + Trim(txtPassword.Text) + "'" End Sub
Hi! I am trying to do something that I would think should be rather simple.I have a SQLDataSource that has a simple select statement.I have a String Variable st_tempI know my SQLDataSource returns only one value, and it's a string.HOW DO I DO somehitng like st_temp=SQLDataSource.Select;I am getting nothing but errorrs Thanks in advance Dan
I've a custom database with created by Dynamics CRM setup. When i access this database using the user who installed it, It returns full data. But when i try to access the same tables using some other username e.g. [sa] / [any other user], it doesn't return me even a single record. Database instance is set to mix mode authentication, and i can connect using username [sa], but no data is available from 1 perticular database.
I've even given select rights to [public] on that specific database, but no results. Can anyone help me getting results using these usernames?
I am trying to achieve the below problem statement, however I am getting stuck at the looping part.
EX: We have three columns
First Name |Â Last Name | Mothers Name
I want to generate the username field using above columns, so lets consider, I have following data
First Name | Last Name | Mothers Name a                   b                 cdef a                   b                 cdfg a                   b                 cdfj
Expected Usernames:
1: a.b 2: a.b.c 3. a.b.cd
Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)
Im trying to insert some values into a table but i get the following error: System.Data.SqlClient.SqlException: Must declare the scalar variable "@username". the strange thing is that the variable username is declared at the beginning and acording to my debugger the variable username has a value (that it gets from a textbox when a button is pressed). Here is my code so please feel free to point out what im doing wrong. Im a beginner to using asp.net.1 protected void ButtonSubmit_Click(object sender, EventArgs e) 2 { 3 string @username = TextBoxUsername.Text; 4 string @company = TextBoxCompany.Text; 5 string @password = TextBoxPassword.Text; 6 string @mail = TextBoxMail.Text; 7 string @adr = TextBoxAdr.Text; 8 string @phone = TextBoxPhone.Text; 9 string @contact = TextBoxContact.Text; 10 string myConnectionString; 11 12 myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"; 13 14 SqlConnection myConnection = new SqlConnection(myConnectionString); 15 string myInsertQuery = "INSERT INTO company (username, password, company, mail, adr, phone, contact) Values(@username, @password, @company, @mail, @adr, @phone, @contact)"; 16 SqlCommand myCommand = new SqlCommand(myInsertQuery); 17 myCommand.Connection = myConnection; 18 myConnection.Open(); 19 myCommand.ExecuteNonQuery(); 20 myCommand.Connection.Close(); 21 22 }
Hi I’ve got a simple SQLdataSource which is wired up to a gridView. When the page loads, it’s sometimes likely the data source would contain no data – how can I test for that in the code behind (no data)? I’d like to only show a button if there is data? I’m using ASP.NET 2.0 with C# Many thanksRichard
I have an SQL database with a number of fields, one of which is for storing the date (datetime).When I insert data into the table, I only supply the date because I don't want or need the time to be listed. However, when I execute a query, the data that shows up in my gridview is (e.g) 13/10/2007 12:00:00 AM. What I want ti know is, how can I format the datetime when I run the query so that it only shows 13/10/2007?This is how I'm retrieving the data at the moment: SqlCommand command = new SqlCommand("SELECT * FROM tblMail", sqlConn);conn.Open()SqlDataReader reader = command.ExecuteReader();dataGrid.DataSource = reader;dataGrid.DataBind();conn.close() In my dataGrid, I have a number of fields that are bound to the data source, such as Date, Name, Address etc etcAnyway, any ideas on how I can format the SQL DateTime so it only shows the date in my datagrid?
I have lots of problems with the connection. I've connected many dropdownlists to the database and i didn't have problems at all. I also try an example of an ASP.NET book: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString= "<%$ ConnectionStrings:CIPPEC%>" InsertCommand= "INSERT INTO [member] ([first_name], [last_name], [malefemale], [yearborn]) VALUES (@first_name, @last_name, @malefemale, @yearborn)"> <InsertParameters> <asp:FormParameter Name="first_name" Type="String" FormField="FirstTextBox" /> <asp:FormParameter Name="last_name" Type="String" FormField="LastTextbox"/> <asp:FormParameter Name="malefemale" Type="Int32" FormField="MFRadioButton" /> <asp:FormParameter Name="yearborn" Type="Int32" FormField="YearDropDown" /> </InsertParameters> </asp:SqlDataSource> And it worked well. But when i try to connect my page with: Dim connect As New Data.SqlClient.SqlConnection( _ "Server=glgrand-arSQLEXPRESS;UID="WindowsStartupUser";password="WindowsStartupPass"; database=CIPPEC") connect.Open() Dim cmd As New Data.SqlClient.SqlCommand( _ "INSERT PRUEBA (PersonaJuridica, NombreRazonSocial, Nombre, Segundo_nombre, Apellido) " & _ "VALUES (txtPerJur, txtRazSoc, txtName, txtSecondName, txtApellido)", _ connect) cmd.ExecuteNonQuery() connect.Close() The error message says: Login failed for user 'username'. I tried lots of things but it didn't work: In SQL Configuration Manager: Client Protocols: TCP/IP and Named Pipes are enabled. In SQL 2005 Services: Server Properties: Built-in Account: Local System SQL Server Surface Area Configuration: Remote connections: Using both TCP/IP and Name Pipes connection is selected. In SQL Management Studio i created a new login glgrand-ar/UserStartupName , with sysadmin server role and user mapping my CIPPEC database. I don't know what else i could try, i tried it at home and at work and i couldn't connect to my database and make an INSERT. It stops at Connect.Open() with the error that i wrote in this subject message. It's evident that i'm doing something wrong (because i'm new): Could you please help me with a solution and explain me what are the data that i have to put on the Data.SqlClient.SqlConnection ?? I'm using the Windows authentication: i'm putting my username and password and it doesn't worked. Thank you!!
1. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B).  Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source
Hello,Can I import an OLTP (Reltional DB) as a Data Source into SQL ServerAnalysis Services 2005 and then use the Cube Wizard and the new DataSource View feature to create the OLAP model ?Or do I have to first design an OLAP Data Warehouse with a Star Schemaand then import this DW as a Data Source into my Analysis ServicesProject.With SQL Server 2000 , OLAP would be the way to go..but with SQLServer 2005 , it seems as though the wizard and data source viewfeatures do half the work for you.I have an OLTP DB and am not sure which route I should take ! Anysuggestions / input would be much appreciated.Thanks in Advance...RegardsRusszee
I have a datasource view DSV1. It points to a datasource DS1 that is considered the "primary".
I have created a Report Model that uses DSV1 (and thus uses DS1)
I created a new datasource, DS2 that I would like to use instead of DS1. (I can't just modify DS1 because if I modify it, it will overwrite it when we go to our Production environment and break that datasource)
So, I can go into DSV1 and change all the references from DS1 to DS2.
But that's where the problem lies.
When I try to build, I get the following error:
"The Table property of the Entity "E1" refers to the Table "dbo_View", which is not in the primary data source."
Somehow, the entity is tied to the "primary" datasource. When I change it back to DS1, everything works fine. Any thoughts? What can I do?
I have created database and OLAP cube in Analysis services using SSAS.In SSAS I have used a datasource which is using SQL tables to populate OLAP cube.Now when I added some more data to my SQL tables and trying to deploy cube,the newly added is not getting populated in the cube.So i want run SSIS package which will import data from SQL tables to this OLAP cube.
Can you please help me how to write this SSIS package to import data from SQL tables to OLAP cube.(Very urgent issue)
I am using sqljdbc41.jar to connect with MSSQL database, it is working fine on my local machine.Where as on the remote server, same class giving me error
Login failed for user '<domain><windows loginID>' My connection string is URL...
I am using sqljdbc41.jar and on 64 bit processor , I am using following command which included path for sqljdbc_auth.dll java -Djava.library.path= C: sqljdbc_4.1enuauthx64 TestDao and error is Login failed for user '<domain><windows loginID>' why it is not picking up username passed in connection string. I have 2 machines, one is local and other is remote. on both machine I login using my domain, it is working absolutely fine on local then why the error is coming on remote machine.Both the machines are identical.
I need to include pre k kids in the is script for one school and exclude them for the others. I have them all excluded with the last statement in the where clause. How would I go about accomplishing this.
Code: SELECT DISTINCT C.Student_ID, isnull(left(dbo.capfirst(rtrim([first_name])),12),' ') as First_Name, isnull(left(dbo.capfirst(rtrim([last_name])),17),' ') as Last_Name, LEFT(Middle_Name, 1) as Middle_Initial, RTRIM(CONVERT(CHAR,Birth_Date,101)) AS DOB,
I am having some difficulty in constructing outer joins. I havesimplified what I need to do and have included sample SQL statements:create table tab_a (id int, descr varchar(10), qty int)insert into tab_a values (1, 'item one', 10)insert into tab_a values (2, 'item two', 20)insert into tab_a values (3, 'item three', 30)insert into tab_a values (4, 'item four', 40)create table tab_b (id2 int, descr2 varchar(10), qty2 int)insert into tab_b values (1, 'item one', 10)insert into tab_b values (2, 'item two', 20)insert into tab_b values (3, 'item three', 30)insert into tab_b values (4, 'item four', 40)Here is the statement that I have:SELECT tab_a.id,tab_a.descr,tab_a.qty,tab_b.id2,tab_b.descr2,tab_b.qty2FROM tab_a LEFT OUTER JOIN tab_bON (tab_a.id = tab_b.id2 )WHERE tab_a.qty <= 30 ANDtab_b.qty2 > 20What I am trying to do is left outer join between tab_a and tab_b afterthey have been filtered based on the qty column. (for tab_a: qty <=30; and for tab_b: qty > 20).How would I go about that? I would like to do this efficiently sincethe two tables have about a million records and several other columnseach.
Hi all,I’m a bit new to this so hope this is not too obvious!I am running a query like so (simplified form)Select Cus_no, Cust_Name, course_idWhere Cust_id = 2From course tableHowever not all the Cust_Id’s have been entered therefore so using a twofilters out the information I might need.Is there a way I can get around this?Many thanksSam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm just trying something that I haven't tried before, and I'm wondering if anyone has done it before. I'm building a report with three charts. This chart is going to go inside of Microsoft CRM. One of the charts has to use CRM filtered views, so that a manager can see his whole team's numbers, but a team member will only see his numbers. I can't do this any other way because of the complexity of the query, which has 3 select statements joined together as tables. It would be too complex to try to use a parameter throughout all of those tables, and I'm not sure how I would set up a parameter to show all of the data if the user were the manager, and only the single user's data if it were someone else.
The other two charts use filtered views as well, but they have similar joins, and I had to hard code a column with meaningless data into each SELECT table so that I could use it to join, as the tables had no other similarities.
The problem is that when you upload a report into CRM, if it uses filtered views, you don't have to go into Report Manager and change the data source from a shared to a custom data source. But on reports where I don't use the filtered views, I find that they always break when upload them, because they use the shared data source. This is the case even when I have created the report without the shared data source. So I usually have to go in and change the data source from shared to custom every time I upload a report without filtered views.
Because of the situation I described, this report uses both filtered views and non-filtered views (for the hard-coded columns). When I upload it into CRM, it won't work either way, with a shared or a custom data source.
What are driving criteria for creating filtered indexes on SQL server. I am trying to analyze the index stats through DMV,histogram and have to analyze if the filtered indexes should be created on tables. This exercise has to be done for all the transaction tables on the database. What are the approaches I should be looking on?
There was a deadlock on the DB because of huge writes on one of the big tables. Having filtered index on this table for the effected column would reduce the time taken for write operations. Hence we are looking for creating filtered indexes appropriately
I have a query that I'm filtering using Customer ID, CustomerID = '12345', even though I need the query to filter that data, I don't need to see that column in my results. I tried removing it from my Select Distinct group but I'm guessing it needs to be there or the filter won't work(like I said, very green). Is there something that I can add to hide this column?
I am relatively new to SSRS and having some problems showing the right results from my subreport. I am using a cube for my datasets. I have created a report parameters where I can filter out year and months to see the sales in certain month. I also want to add a calculation to display the sum for the choosen period.
f.exs. if I choose january 2004 my result look like this:
Key Product Amount 41999 prod. x 5,000 42999 prod.y 2,000
Totals: [results from subreport showing total of 7,000]
What I would like to do is to add a total that calculates the total according to the filter. I tried to add a subreport beneath, but I don´t know how to link the filtered condition to the subreport, so it displayes always the total amount for all years from the cube.
