none
Retrieve data by logged in UserName RRS feed

  • Question

  •  

    All I want to do is to have a member login, then have their set of records show up in a gridview table and possibly their picture as well.  This is how every dating site operates however I have spent a month now searching and googling for what is likely a very simple answer.

     

    I am using the std visual web developer in VB with a SQL server express database, specifically the one auto generated through the web configuration option dealing with roles and members.  I have added a table that holds member data and tried querying this data by username or by the auto generated userID.  I have set up the gridview and a sqldatasource that is connected to this table.  If I configure the datasource to show all data in the table, the test query works and then it works in the application as well.

     

    Once I add the WHERE clause to the SQL command in the datasource confiuration wizard such as:

     

    SELECT [UserName], [TestDate], [TestType], [TestResults] FROM [MembersTestData] WHERE ([UserName] = @UserName)

     

    the 'test query' step won't work unless I enter a default real member username like "frank".  Then the test query will work, BUT the application will not.  If I leave it blank like so many articles and posts do, nothing works.  So should I be selecting a source from the profile, control or string  (or none) parameters?  Then that being selected, what should I use for a default value that will work not only in the test query but also when the application runs?  I have also tried adding a control (i.e. label or textbox) to use as the parameter properties such as member.text with no luck either.  Bottom line, anytime I use the WHERE clause everything stops working, if I wanted to look at every members' data in the gridview I would be fine, but simply want that for the current user only.

     

    PLEASE HELP!

    Saturday, September 15, 2007 9:14 PM

Answers

  • Hi mate,

     

    This is something I should be able to help you with but I am completely out of practise with ASP.NET. So instead I recommend you try the ASP.NET forums, there will be someone there who can help. This is something that, as you said, will be fairly straightforward.

     

    http://forums.asp.net/

     

     

    Monday, September 17, 2007 6:58 PM

All replies

  • Hi mate,

     

    This is something I should be able to help you with but I am completely out of practise with ASP.NET. So instead I recommend you try the ASP.NET forums, there will be someone there who can help. This is something that, as you said, will be fairly straightforward.

     

    http://forums.asp.net/

     

     

    Monday, September 17, 2007 6:58 PM
  •  

    Not sure if this is an option in your project timetable, but have you looked at Roles and Memberships in ASP.NET 2.0?
    Monday, September 17, 2007 7:37 PM
  • Ok, thanks for the response...i didnt realize I was in the wrong place!

    Tuesday, September 18, 2007 3:41 AM
  • Yes, that is exactly what I have used to maintain roles and memberships.  I have tried selecting the 'profile' option for the WHERE clause relating to the profile built and maintained in this area but like the other options it does not work as soon as I designate a WHERE part to the SQL command.

    Tuesday, September 18, 2007 3:43 AM
  • Hope this helps get you started.

     

    Code Snippet

    [DataObjectMethod(DataObjectMethodType.Select, true)]

    public DataTable FetchByUserName(String myName)

    {

    String myConnectString = ConfigurationManager.ConnectionStrings["AuthSystem"].ConnectionString;

     

    DataTable myDataTable = new DataTable();

     

    using (System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(myConnectString))

    {

    using (System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand())

    {

     

    myCommand.Connection = myConnection;

    myCommand.CommandType = CommandType.StoredProcedure;

    myCommand.CommandText = "GetUserInfo";

    myCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("Usr_Name", myName));

     

    try

    {

    myCommand.Connection.Open();

    myDataTable.Load(myCommand.ExecuteReader());

    }

    catch (Exception) {}

    }

    }

     

    return myDataTable;

    }

     

     

    While this uses a stored procedure you could change it to a parameterized sqltext easily
    Tuesday, September 18, 2007 3:14 PM
  •  

    I like jgalley's response on this, so I'm only going to offer a suggestion referencing a project I used where I created a custom Profile management layout where rather than using the default profile structure by managing the aspnet_* tables to extract client data, I followed the suggestions in the following article in doing so.  When it comes to searching for user data, this makes it much easier (IMHO) especially when you need to get several records, this eliminates the need to loop through a profile collection.

     

    http://www.theserverside.net/tt/articles/showarticle.tss?id=CreatingProfileProvider

     

    HTH!

    -Rich

    Tuesday, September 18, 2007 3:34 PM
  • Hi,


    I know that jgalleys code is only an example but I want to highlight something important...

     

    try

    {

    myCommand.Connection.Open();

    myDataTable.Load(myCommand.ExecuteReader());

    }

    catch (Exception) {}

     

    Do not catch exception an exception like this, catching an exception but doing nothing with the exception is not a good idea, if an exception was raised there would be no way to know that it happened, where it happened, and why it happened. If you catch an exception at all then do something with it, fix it, log it, wrap it, don't do nothing.

    Tuesday, September 18, 2007 5:27 PM
  • Could not agree more.

     

    I did not include the handle code to keep the example simple and have something that one could mostly cut and paste to try out.  In the past I have found that including anything that does not generally cut and paste often generates follow up questions such as:

     

    "Your example does not work.. I can't find foo()"

     

    At the same time though, I wanted to highlight that the connection open could easily fail and that one likely want to catch and handle that situation.

     

    Would you recommend in the future:

     

    a) examples without try{} blocks at all

    b) example with catch(){} that call functions that do not exist for the reader

    c) examples with catch(){} that re throw the exception

    Tuesday, September 18, 2007 5:49 PM
  • Hey jgalley,

     

    You know that is a very good question, there is no real control on the forum to determine what standard of code is being posted. Suppose it would be a very difficult thing to impose, who is going to perform code reviews over all the posts in the forum, perhaps FXCop could be applied to posts that have code.  

     

    I have seen some code examples, catching an exception and doing nothing with it being the most common, that unintentionally are teaching some people some bad habits, sure they are only examples but even still if the questioner was a newbie then they don't know any different and don't expect anything. Some experienced posted don't like to hear bad things about their code and generally get grumpy if corrected, glad you didn't.

     

    If I was to recommend an approach then I suppose the following would do, or at least a variation on it, one that only uses the throw keyword and not throw e....

     

    try

    {

    //do stuff

    }

    catch (Exception e) {

         throw;     //do not use throw e; here

    }

     

    This would identify that an exception could happen, but would still propogate the exception while maintaining original stack trace, people would still complain that the code might not work, but that will teach them about exception handling.

     

    Using throw e in the example above, I believe although I cannot find the document where I read it, replaces the call stack inside the exception to the currently called method. So for example if you catch an exception and then rethrow it using throw e it looks like the exception occurred in the method that rethrow it rather than the method that did.

     

    So I guess I would recommend that, guess you need to play it by ear, use the above for new developers, don't bother if the developer is comfortable with the concept of program, you could base that on the complexity of the question being asked.

     

    Nice one.
    Tuesday, September 18, 2007 7:20 PM
  •  

    Many thanks Derek.  I will try to include your feedback when I post examples in the future.
    Tuesday, September 18, 2007 7:26 PM
  • While I greatly appreciate all your assistance, I think I may have found some answers over in the asp.net forums as suggested.  These code examples seem confusing to my little rookie nugget! Thank you all.

    Wednesday, September 19, 2007 2:48 AM