Data retrieval for logged in Username only? RRS feed

  • Question

  • Like many here, I am a newbie...regarding this question, while this has to be one of the easiest things to do-I cannot find direction anywhere.  There is one reference to it through the VWD for Dummies site by MSDN that directs you to use the configuration wizard by setting up a SELECT, WHERE SQL statement designating the 'UserName' to be pulled from the PROFILE with the property of  'UserName'.  Seems simple enough, just doesn't work.  I have tried it on the table I have added to the asp.mdf as well as pre-existing tables from the profile.  The best I can achieve is a default value that doesn't change on Login when displayed through my gridview control.  Works great if I want to show every Username's data but that defeats the purpose.  I am trying to present read-only data to a logged in user. Please help!

    Sunday, August 26, 2007 5:58 PM

All replies

  • One approach that I discuss in my book is to limit access to data via specific SQL Server (not SSPI) credentials. If you're working with an ASP page, it's possible to pickup the Windows user credentials, but getting Windows, the browser and the remote database to register, pass through and manage these can be complex. An easier approach is to use SQL Server credentials for the application. These permit the application to gain access to the database but only when using the application. The security configuration on the database is programmed to grant access the specific application credentials, but only to specific stored procedures and views--never the base tables. Once connected you can run a query that passes the user credentials as a parameter, validates them and grants access on functionality in the application based on the rights this specific user needs.


    "Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)"



    Sunday, August 26, 2007 6:35 PM
  • Thanks for the quick reply, unfortunately I am already using the SQL server credentials created through the web configuration application and the query you are referring to that passes the user credentials as a parameter is also the one that will not work.  I have tried going straight to the table, and also tried using a new saved 'view' that represents a query of the data...well nothing works.  I am guessing the answer is very simple but I dont have the know-how. The interesting thing is that when I enter a default value for the "test" query-it always works, but won't through the application and at best can get the default table data to show on what I have called Testresults.aspx page, never the info specific to a logged in user.

    Sunday, August 26, 2007 7:37 PM
  • hey hii

    First of all have u been able to retrive the name of the login prson.

    Monday, August 27, 2007 9:37 AM
  • If yes then..


    constr="your datbase connection parameter";

    cmdstr="Select * from xyz where name like "Loginname" ";

    sqlconnection conn = new sqlconnection(Connstr);

    sqlcommand cmd =new aqlcommand(cmdstr,conn);


    using(sqldatareader dr=execute.query(cmd))








    you have to add a datagrid from your toolbox to the page and name it datagrid.

    hey if any problem arise or if u need something else tell me may be i can help u.




    Monday, August 27, 2007 9:45 AM
  • hi! i am a newbie too and apparently u've gone farther than i've been able to. i feel ashamed asking this question cos i feel its supposed to be simple but i have searched for 3 weeks and i cant find an answer. my prob is,i cant save any data at all to my gridview! i've tried all sorts but it doesnt work. i collect a username and other details from textboxes but cant get them to be saved or displayed. how did u save your data to your database?



    Monday, August 27, 2007 10:50 AM
  • ok cool

    dont woory there is first time for everybody nothig to woory.


    first of all make a database and add a table to it. That must contain all the fields u want ie the data u recived from users in textboxes


    to create a new database go to the solution explorer then right click the soln and click add new item then select database and name it. then go to server explorer there u will find the database u just made then go to tables add new one by right clicking it then add the fields to it....!



    Do this part if u have any problem then tell me.

    Monday, August 27, 2007 11:03 AM
  • dim con as sqlconnection,dr as sqldatareader
    dim cmd as sqlcommand

    con=new sqlconnection
    con.connectionstring=specify connection string here
    cmd=new sqlcommand("select username,password  from users where username=" & " ' "& trim(txtuser.text) & " ' " & " and password=" & " ' " & trim(txtpwd.text) & " ' "
    dr = cmd.ExecuteReader

     if dr(0)=txtuser.text and dr(1)=txtpwd.text then
      label1.text="Invalid user or password"
    end if

    Thank u

    Monday, August 27, 2007 11:10 AM
  • I know to look in the membership tables for some of the automatically generated data from registered users such as asp_Users.  However I do not know how to look at the login for the current user.  I am truly just trying to learn by reading and bumping my head into it...thanks for the reply.

    Tuesday, August 28, 2007 3:27 AM
  • Unfortunately that may just be a roadblock I will encounter in the near future.  The data that I try to look at has been manually entered through the "show table data" just to give myself something to try to retrieve, then I assigned columns and even set up relationships with the pre-formed tables that contain 'username' from the ASPMDF. database (using a newly added table to the same database) but alas no luck yet.  Thanks for checking in.

    Tuesday, August 28, 2007 3:31 AM
  • Hello again,

    Thanks for helping those less trained....I have tried adding a table to the ASPMDF. database and I also have built a brand new 'Members' database and tried working through that as well.  It would seem that part of my dilemma is that the "UserName" info does not 'pull' from the automatically generated tables built through the std membership roles and the web configuration app.  Even if I assign a foreign key relationship, so I must be missing something there because to even get the UserNames in my manually entered test data I had to cut n paste them into my new table to avoid the error re "null" values.


    You are right to start at the beginning with the new database, I am a true novice...I would def appreciate the next logical step! thank you


    Tuesday, August 28, 2007 3:36 AM
  • Although a bit daunting, I may be able to figure that code out but I am needing to know 'where' you place that code??  Would it be in the main aspx source code?  The code behind file .vb?  The web.config file?  Thanks...

    Tuesday, August 28, 2007 3:38 AM
  • hii

    Sorry for delay...

    You always put the event in the .vb file. all the codes that u wanna run on server on a perticular event is coded on that page.

    The Source aspx code is for the client side it contain the html tags 

    and be aware of web.config file it contains all the info needed to run ur webpages it also contain connection string to ur database..!




    Wednesday, August 29, 2007 5:30 AM
  • Ok, thanks.  Now the task is to figure out exactly what that code means Wink  I tried entering the sample code that was suggested with my connection string:


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim con As sqlconnection

    Dim dr As sqldatareader

    Dim cmd As sqlcommand


    con = New sqlconnection

    con.connectionstring=specify connection string here

    cmd = New sqlcommand("select username,password from users where username=" & " ' " & Trim(txtuser.text) & " ' " & " and password=" & " ' " & Trim(txtpwd.text) & " ' ")

    dr = cmd.ExecuteReader


    If dr(0) = txtuser.text And dr(1) = txtpwd.text Then



    label1.text = "Invalid user or password"

    End If

    End Sub


    However my VWD seems to force me to make the following changes:


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim conn As New Data.SqlClient.SqlConnection

    Dim dr As Data.SqlClient.SqlDataReader

    Dim cmd As Data.SqlClient.SqlCommand


    conn = New Data.SqlClient.SqlConnection

    conn.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Members.mdf;Integrated Security=True;User Instance=True"

    cmd=new Data.SqlClient.SqlCommand("select username,password from users where username=" & " ' "& trim(textboxUserName.text) & " ' " & " and password=" & " ' " & trim(textboxPassword.text) & " ' "

    dr = cmd.ExecuteReader


    If dr(0) = textboxUserName.text And dr(1) = textboxpassword.text Then



    label1.text = "Invalid user or password"

    End If

    End Sub


    So I still get the little purple squiggly of death under "textboxUserName", "textboxPassword" and "label1.text" with the error that these items have not been declared.  I don't understand because I added the label and used the two textboxes from my login control. 


    I run it anyway for fun and get the following error: LoginPage: LayoutTemplate does not contain an IEditableTextControl with ID UserName for the username.  Any ideas??


    I am also curious as to if I need to add a sql select statement that is different than the current cmd=new Data.SqlClient.SqlCommand (......


    Thanks again for the reply...

    Thursday, August 30, 2007 3:27 AM
  • ok

    hell lot of code.lets take a deep dip into it.

    see the error u are getting is because the name of the textboxes are different from what u have on ur page

    so lets do it right

    first go to the design mode and select the text box in which u wanna to enter the user his loggin name and then go to the properties of that txtbox and then go to name property and change it to textboxUserName and simillarly for the password txtbox and name it txtboxPassword.

    now u have to tell me how many labels u have at ur page.



    Thursday, August 30, 2007 5:29 AM
  • Hmmm, well that is exactly what I have set up now for those textboxes-they match what is in this code.  I also only have the one "Label1" label which ironically I added just to see what this code will do.  It seems as though there is some disconnect between the code-behind and the aspx page, which is strange.  Given that the textboxes are correct, any inclination as to what else could be the problem?  If it did work, what would I be looking for and where would I look to make sure that it is pulling the username correctly?  Would that automatically cause my data return from the table to retrieve only for the one username?


    Thanks for your time and patience...

    Friday, August 31, 2007 3:20 AM
  • Ok, I can see one serious problem. You're creating a web-based application but you're trying to use User Instance=True. This is why we wanted to see your connectionstring.


    See http://msdn2.microsoft.com/en-us/library/ms143684.aspx. While it's not clear from this article, User Instance handling of the SQL Express instance does not really make sense for ASP applications. Consider that the "user" in an ASP application is IUSER<machine name> not the user name of the windows user at the other end of the browser. If it was, where would SQL Server place the user-specific instance of the database? There is no user-specific area on the IIS server.


    See http://www.odetocode.com/Articles/427.aspx for more information.
    Friday, August 31, 2007 4:02 AM

    In what way will this affect the creation or running of the application at this point?  Are these problems I am having related to this issue or is it something that will be a problem in the future?  Does that mean I should immediately change it or does this mean I must start over completely with it set to "false"?


    Thanks for the headsup...

    Saturday, September 1, 2007 12:48 AM