How to get data from SQL Database for specific logged in users : The Official Microsoft ASP.NET Forums

Reply

How to get data from SQL Database for specific logged in users

Nov 15, 2012 09:01 PM|LINK

Hello All,

Need help.

I created a simple user form, wherein users can enter their First Name, Last name, DOB, Location, etc..

I am able to Insert users input data from the HTML form to the SQL CE database.

I can also view the data using the webgrid helper.

I want to add an option to my page wherein users can login and edit the data they have provided.

But how do I pull up data from the database for a specific user.

I hope I have described my issue correctly.

Thanks.

Reply

Re: How to get data from SQL Database for specific logged in users

Nov 15, 2012 09:36 PM|LINK

I'll give you the following code to see how this is done:

@{
    var db = Database.Open("website");

    var selectQueryString = "SELECT * FROM webpages_UserProfile WHERE UserId = @0";

    var row = db.QuerySingle(selectQueryString, WebSecurity.CurrentUserId);
    var name = row.Name;
    var lastname = row.Lastname;
    
    if (IsPost) {
        
        name = Request.Form["Name"];
        lastname = Request.Form["LastName"];
        var update = @"UPDATE webpages_UserProfile SET Name = @0, Lastname = @1 WHERE UserId = @2";
        db.Execute(update, name, lastname, WebSecurity.CurrentUserId);
        Response.Redirect("/");
    }
}


                <form method="post" id="updateprofile">
                    
                    
                    <ul>
                        <li>Name :
                        <input type="text" name="Name" value="@name">
                        </li>
                        <li>Lastname :
                            <input type="text" name="LastName" value="@lastname">
                        </li>

                        <li>
                            <a href="#" onclick="$('#updateprofile').submit()">Update</a>
                        </li>
                    </ul>
                </form>

Edited by dow7 on Nov 15, 2012 09:36 PM
Edited by dow7 on Nov 15, 2012 09:39 PM
Edited by dow7 on Nov 15, 2012 09:39 PM

Reply

Re: How to get data from SQL Database for specific logged in users

Nov 16, 2012 03:26 PM|LINK

Thank you for the reply, But I am getting below error when I try running the code.

I have replaced the database name and table name, but still getting this error.

Cannot perform runtime binding on a null reference

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: Cannot perform runtime binding on a null reference

Source Error:


Line 5:
Line 6:      var row = db.QuerySingle(selectQueryString, WebSecurity.CurrentUserId);
Line 7:      var name = row.Name;

Reply

Re: How to get data from SQL Database for specific logged in users

Nov 16, 2012 03:29 PM|LINK

Read this: http://forums.asp.net/t/1239043.aspx/1

Alexei Fimine
_____________

"And though I have the gift of prophecy, and understand all mysteries, and all knowledge; and though I have all faith, so that I could remove mountains, and have not charity, I am nothing"

Reply

Aafi

Member

32 Points

53 Posts

Re: How to get data from SQL Database for specific logged in users

Nov 16, 2012 04:29 PM|LINK

fimine

Read this: http://forums.asp.net/t/1239043.aspx/1

Thank you for the link, but I am unable to understand anything on that link.

I am very new to programming and have just started learning ASP.Net Webpages from the forums using WebMatrix.

Let me try explaining exactly what I am trying to achieve.

I have made a table named ‘Details in a database named ‘UserDetails’. The table has following columns (UserID, FirstName, LastName, Hobbies, Location, MusicGenre, PNotes, DOB).

I have a made a HTML form from which users may input the above information and that gets added to the respected columns in the database table. Now on another HTML page I want to provide a link to user, clicking on which they will be directed to a profile edit page which should have their information already there and also gives them the option to edit the information.

It’s like we have a profile page on most of the popular website, wherein we register and update our profile.

I would really appreciate if someone could point to a tutorial or help me with codes.

Reply

aabruzzese

Contributor

2806 Points

759 Posts

Re: How to get data from SQL Database for specific logged in users

Nov 16, 2012 05:03 PM|LINK

Hi,

You have to decided which approach you will use to update the data, so basically some kind of link to open the profile update page and then do the actual work.

Here is a project that is simple enough to follow and has grids and details and also database updating.

http://www.codeproject.com/Articles/377291/An-Introduction-to-ASP-NET-Dynamic-Data-from-a-Beg

If you need help, maybe just post the structure of your user profile table and I could help you create a basic

page to display and update the info.

AngeloA

Reply

Aafi

Member

32 Points

53 Posts

Re: How to get data from SQL Database for specific logged in users

Nov 16, 2012 06:03 PM|LINK

aabruzzese

If you need help, maybe just post the structure of your user profile table and I could help you create a basic

page to display and update the info.

Ok,Starting with the HTML, below is the code for the HTML page(UserProfile) . Here users input their data and it gets updated in the database.

The Database contains 6 tables :

1. UserDetails - This is where the information from UserProfile page gets updated

2. Userprofile

3. WebPages_Membership

4. Webpages_OAuthMembership

5. Webpages_Roles

6. Webpages_UserInRoles

I want to create another page (ViewProfile). This is the page I need help with.

This page will first authenticate user and after that it will pull the users information from the database and display it a form format and will also give them the option to change and update information.

Code Below :

@{
if (!WebSecurity.IsAuthenticated) {
        Response.Redirect("~/Account/Login?returnUrl="
            + Request.Url.LocalPath);
    }
    
    
//Variables

    var firstname="";
    var lastname="";
    var hobbies="";
    var location="";
    var pnotes="";
    var musicgenre="";
    var dob_month="";
    var dob_day="";
    var dob_year="";
    var dob="";
    
    
//Validations
Validation.RequireField("firstname", "First Name required");
Validation.RequireField("lastname", "Last Name required");
Validation.RequireField("hobbies", "Please select at least one hobby");
Validation.RequireField("location", "Please enter your location");
Validation.RequireField("pnotes", "Personal Notes cannot be empty");
Validation.RequireField("musicgenre", "Select one option");
Validation.RequireField("dob_month", "Please select a Month");
Validation.RequireField("dob_day", "Please select a Day");
Validation.RequireField("dob_year", "Please select a Year Month");




 if(IsPost && Validation.IsValid()){
            firstname=Request["firstname"];
            lastname=Request["lastname"];
            hobbies=Request["hobbies"];
            location=Request["location"];
            pnotes=Request["pnotes"];
            musicgenre=Request["musicgenre"];
            dob_month=Request["dob_month"];
            dob_day=Request["dob_day"];
            dob_year=Request["dob_year"];
            dob=string.Concat(dob_month, "/", dob_day, "/", dob_year);
            

            var db=Database.Open("Profile");
            var insertcommand="INSERT INTO UserDetails(firstname, lastname, hobbies, location, musicgenre, pnotes, dob) values (@0, @1, @2, @3, @4, @5, @6)";
            db.Execute(insertcommand, firstname, lastname, hobbies, location, musicgenre, pnotes, dob);
            Response.Redirect("~/users/Profileupdate");
 }  
 }

<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Test Form</title>
    </head>
    <body>
        <h1>Complete your profile</h1>
        <Form method="post">
            <fieldset>
            <legend>Fill up the details below</legend>
                
            <p><label>First Name</label>
                <input type="text" name="Firstname" value="@Request.Form["firstname"]" />
                
            @Html.ValidationMessage("firstname")
            </p>

                <p><label>Last Name</label>
                <input type="text" name="lastname" value="@Request.Form["lastname"]" />
                @Html.ValidationMessage("lastname")
                </p>

                <p>
            <label>Date Of Birth</label>
              
            
                <select name="DOB_Month" @Html.ValidationMessage("dob_month")>
<option value="">Month:</option>
<option value="1">Jan</option>
<option value="2">Feb</option>
<option value="3">Mar</option>
<option value="4">Apr</option>
<option value="5">May</option>
<option value="6">Jun</option>
<option value="7">Jul</option>
<option value="8">Aug</option>
<option value="9">Sep</option>
<option value="10">Oct</option>
<option value="11">Nov</option>
<option value="12">Dec</option>
         
</select>

<select name="DOB_Day" @Html.ValidationMessage("dob_day")>
<option value="">Day:</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
    
</select> 

<select name="DOB_Year" @Html.ValidationMessage("dob_year")>
<option value="">Year:</option>
<option value="2012">2012</option>
<option value="2011">2011</option>
<option value="2010">2010</option>
<option value="2009">2009</option>
<option value="2008">2008</option>
<option value="2007">2007</option>
<option value="2006">2006</option>
<option value="2005">2005</option>
<option value="2004">2004</option>
<option value="2003">2003</option>
<option value="2002">2002</option>
<option value="2001">2001</option>
<option value="2000">2000</option>
    
</select>         
                       
            </p>                          
                <p>Location :
                <input type="text" name="location" value="@Request.Form["location"]" />
                @Html.ValidationMessage("location")
                </p>

                <p>Hobbies :<br>
                <input type="checkbox" name="hobbies" value="Drawing" />Drawing<br>
                <input type="checkbox" name="hobbies" value="Painting" />Painting<br>
                    <input type="checkbox" name="hobbies" value="Dancing" />Dancing<br>
                    <input type="checkbox" name="hobbies" value="Movies" />Movies<br>
                    <input type="checkbox" name="hobbies" value="Music" />Music<br>
                    @Html.ValidationMessage("hobbies")
                </p>
                
                <p>Fav Music: <br>
                    
                    <input type="checkbox" name="musicgenre" value="Rock" />Rock<br>
                    <input type="checkbox" name="musicgenre" value="Trance" />Trance<br>
                    <input type="checkbox" name="musicgenre" value="Hip-Hop" />Hip-Hop<br>
                    <input type="checkbox" name="musicgenre" value="Dubstep" />Dubstep<br>
                    <input type="checkbox" name="musicgenre" value="Lounge" />Lounge<br>
                    <input type="checkbox" name="musicgenre" value="House" />House<br>
                    <input type="checkbox" name="musicgenre" value="Don't want to specify" />Don't want to specify<br>
                @Html.ValidationMessage("musicgenre")
                
                </p>

                    <p>Personal Note :<textarea name="pnotes" cols="40" rows="10" maxlength="400"></textarea>
                        @Html.ValidationMessage("pnotes")
                        </p>
                <br>



                <input type="submit" />

            </fieldset>
        </Form>
    </body>
</html>

Reply

aabruzzese

Contributor

2806 Points

759 Posts

Re: How to get data from SQL Database for specific logged in users

Nov 16, 2012 06:53 PM|LINK

Hello Aafi ,

You can use the same page basically, just change the SQL command from INSERT to UPDATE,

I could post the code here, just do a quick search on the web or maybe look at this page:

http://www.csharp-station.com/Tutorial/AdoDotNet/lesson03

I cant do it here from my work, otherwise I would probably post the actual update command for you.

Just know that when you call an SQL UPDATE command, usually you do UPDATE TABLE SET COL1 = "SOMEVAL", COL2 = "SOMEVAL" ...

Anyways I know I could code it for you, if you need help getting deeper into it, for now take your current page and just rename it to updateprofile.aspx and then change the sql statement from insert to update.

AngeloA

Edited by aabruzzese on Nov 16, 2012 07:31 PM

Reply

Aafi

Member

32 Points

53 Posts

Re: How to get data from SQL Database for specific logged in users

Nov 16, 2012 08:04 PM|LINK

Hey Angelo,

The update command will update the database, but how will it know that it needs to update the information of that particular user who is logged in ?

Reply

Aafi

Member

32 Points

53 Posts

Re: How to get data from SQL Database for specific logged in users

Nov 16, 2012 08:17 PM|LINK

Hey Angelo,

Below is the link of a zip folder for my test website, all webpages and databases are in there..

https://docs.google.com/open?id=0B0EOR1V1lsEONERtMklnQ3ZqTms

Hope this helps

How to get data from SQL Database for specific logged in users RSS

20 replies

Aafi

How to get data from SQL Database for specific logged in users

dow7

Re: How to get data from SQL Database for specific logged in users

Aafi

Re: How to get data from SQL Database for specific logged in users

fimine

Re: How to get data from SQL Database for specific logged in users

Aafi

Re: How to get data from SQL Database for specific logged in users

fimine

aabruzzese

Re: How to get data from SQL Database for specific logged in users

Aafi

Re: How to get data from SQL Database for specific logged in users

aabruzzese

aabruzzese

Re: How to get data from SQL Database for specific logged in users

Aafi

Re: How to get data from SQL Database for specific logged in users

Aafi

Re: How to get data from SQL Database for specific logged in users

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft