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

Reply

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

Nov 16, 2012 08:20 PM|LINK

I will look at it this weekend, I cant for now need to head home.

Do some more research into the way to check current user, so for now just google How do I know which user just logged in c#

and see if that helps you get an idea on how to know which user you are so you can update the proper table.

I will try to help you sometime on sunday.

AngeloA

Marked as answer by aafi on Nov 18, 2012 09:18 PM

Reply

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

Nov 17, 2012 08:18 AM|LINK

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

You have to replace the row.Name and other table columns with your own table columns.

Marked as answer by aafi on Nov 18, 2012 09:18 PM

Reply

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

Nov 17, 2012 11:59 AM|LINK

This is the code for currentLoggedIn user

WebSecurity.CurrentUserId

You can use it in every field of SQL. If you want to get a result from SQL you can try out this word as the WHERE to get the values for the current logged in user. And then you can try your code to show the values.

Please "Marks As Answer" if any answer helped you out!
~~! FIREWALL !~~

Marked as answer by aafi on Nov 18, 2012 09:17 PM

Reply

Aafi

Member

32 Points

53 Posts

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

Nov 18, 2012 09:16 PM|LINK

Thankyou all for your valuable inputs.

After getting different hints from all of the above post, I finally managed to create the page I wanted to.

Below is my code, which is working, fine, request you all to please go through to it and point out any corrections if needed.

@{
    if (!WebSecurity.IsAuthenticated) {
        Response.Redirect("~/Account/Login?returnUrl="
            + Request.Url.LocalPath);
    }
    
    var UserId="";
    var firstname="";
    var lastname="";
    var hobbies="";
    var location="";
    var pnotes="";
    var musicgenre="";
    
    
     

    if(!IsPost){
        if(!Request.QueryString["ID"].IsEmpty() && Request.QueryString["ID"].IsInt()){
            
            UserId = Request.QueryString["ID"];
            var db = Database.Open("Profile");
            var dbCommand = "SELECT * FROM UserDetails WHERE UserID =@0";

            var row = db.QuerySingle(dbCommand, WebSecurity.CurrentUserId);
            
            if(row != null) {



    firstname= row.firstname;
    lastname= row.lastname;
    hobbies= row.hobbies;
    location= row.location;
    pnotes= row.pnotes;
    musicgenre= row.musicgenre;
    
    
    
}

    }
    }

    if(IsPost){
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");


        firstname = Request.Form["firstname"];
        lastname = Request.Form["lastname"];
        hobbies = Request.Form["hobbies"];
        location= Request.Form["location"];
        pnotes = Request.Form["pnotes"];
        musicgenre = Request.Form["musicgenre"];
        UserId = Request.Form["UserID"];


        if(Validation.IsValid()){
            var db = Database.Open("Profile");
            var updateCommand = "UPDATE UserDetails SET firstname=@0, lastname=@1, hobbies=@2, location=@3, pnotes=@4, musicgenre=@5 WHERE UserId=@6";
            
            db.Execute(updateCommand, firstname, lastname, hobbies, location, pnotes, musicgenre, WebSecurity.CurrentUserId);
            Response.Redirect("~/Users/ProfileUpdate");
       }
       }
    }





<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Test Form</title>
    </head>
    <body>
         @if (WebSecurity.IsAuthenticated) {
                            
                            <text>    Hello, <a href="~/users/viewprofile2?id=@WebSecurity.CurrentUserId">@WebSecurity.CurrentUserName</a>! | </text>
                                <a href="~/Account/Logout">Log out</a>
}
        <h1>Edit 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="@firstname" />
                
            @Html.ValidationMessage("firstname")
            </p>

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

                                       
                <p>Location :
                <input type="text" name="location" value="@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="hidden" name="Userid" value="@UserId" />

                <input type="submit" />

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

I have another question, How do I get the values from database into checkboxes and textarea elements. The above code will display values in the Input Text feild, but how do I get checkboxes already checked with the values in the database and also the texarea field populated with the value from database.

Reply

Afzaal.Ahmad...

Contributor

2759 Points

1060 Posts

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

Nov 19, 2012 04:56 PM|LINK

You can use the values from database. Such that the names

If you have a name of user or anything you can add that to the page using razor code as @row.Name

<textarea name="area-to-write" rows="2" cols="5">@row.Name</textarea>

This will automatically get the value from SQL database and add it here..

Now for the checkbox you can use if else statement by using the value from SQL database.

foreach (var row in resultOfYourQuery) {
<form method="post">
if(valueFromDatabase == ConditionApplied) {
<input type="checkbox" value="boy" checked="checked" />
} else {
<input type="checkbox" value="girl" checked="checked"
}

This way you will create you checkboxes for every result and for every condition equal to database value you get an automatically checked checkbox.

Please "Marks As Answer" if any answer helped you out!
~~! FIREWALL !~~

Reply

Aafi

Member

32 Points

53 Posts

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

Nov 19, 2012 09:02 PM|LINK

Afzaal.Ahmad.Zeeshan
foreach (var row in resultOfYourQuery) {
<form method="post">
if(valueFromDatabase == ConditionApplied) {
<input type="checkbox" value="boy" checked="checked" />
} else {
<input type="checkbox" value="girl" checked="checked"
}
This way you will create you checkboxes for every result and for every condition equal to database value you get an automatically checked checkbox.

Thanks Afzaal,

The textarea code just works fine.

I am confused using the above code, can you please also explain what it does/how it works and elaborate a little bit more.

Thank you

Reply

Afzaal.Ahmad...

Contributor

2759 Points

1060 Posts

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

Nov 20, 2012 09:43 AM|LINK

What this one ?

Its simple.

The foreach will loop through the results. And the row is used to set the values and the values are from resultOfYourQuery

Than in the form there is a form. In it you have if else statement which will cause server to write the data as provided. If the conditions are true the first condition is applied. Otherwise the other code is shown.

Please "Marks As Answer" if any answer helped you out!
~~! FIREWALL !~~

Reply

Aafi

Member

32 Points

53 Posts

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

Nov 20, 2012 12:32 PM|LINK

Afzaal,

Can you please explain with an example, I would request you to explain using my code above.

How would I implement the foreach code in my code ?

Reply

Afzaal.Ahmad...

Contributor

2759 Points

1060 Posts

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

Nov 20, 2012 03:03 PM|LINK

<form method="post">
if (Condtions == Conditions) {
<input type="checkbox" name="hobbies" value="@row.Value" checked="checked" />@row.Value<br>
} else {
<input type="checkbox" name="hobbies" value="@row.Value" />@row.Value<br>
</form>

Simple as making a cup of tea.

You need to put the if statement. In it you need to set a value for it for example for every name == Afzaal your checkbox will be checked and for those where name is not equal to Afzaal box will be unchecked.

You can use an integer for it.

Please "Marks As Answer" if any answer helped you out!
~~! FIREWALL !~~

Reply

Aafi

Member

32 Points

53 Posts

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

Nov 22, 2012 02:12 PM|LINK

Hey Afzaal,

I tried the above code but it gives a compilation error (Compiler Error Message: CS1061: 'char' does not contain a definition for 'drawing' and no extension method 'drawing' accepting a first argument of type 'char' could be found (are you missing a using directive or an assembly reference?)

below is my code, What am I doing wrong.. I have a cloumn name 'Hobbies' in my database where all the values from the form are stored with comma seprated viz: (Drawing, Painting, dancing)

@{
    if (!WebSecurity.IsAuthenticated) {
        Response.Redirect("~/Account/Login?returnUrl="
            + Request.Url.LocalPath);
    }
    
    var UserId="";
    var firstname="";
    var lastname="";
    var hobbies="";
    var location="";
    var pnotes="";
    var musicgenre="";
    
    
     

    if(!IsPost){
        if(!Request.QueryString["ID"].IsEmpty() && Request.QueryString["ID"].IsInt()){
            
            UserId = Request.QueryString["ID"];
            var db = Database.Open("Profile");
            var dbCommand = "SELECT * FROM UserDetails WHERE UserID =@0";
            var row = db.QuerySingle(dbCommand, WebSecurity.CurrentUserId);
            
            if(row != null) {



    firstname= row.firstname;
    lastname= row.lastname;
    hobbies= row.hobbies;
    location= row.location;
    pnotes= row.pnotes;
    musicgenre= row.musicgenre;
    

    
    
}

    }
    }

   
    }





<!DOCTYPE html>

<html lang="en">
    <head>
        <meta charset="utf-8" />
        <title>Test Form</title>
    </head>
    <body>
         @if (WebSecurity.IsAuthenticated) {
                            
                            <text>    Hello, <a href="~/users/viewprofile2?id=@WebSecurity.CurrentUserId">@WebSecurity.CurrentUserName</a>! | </text>
                                <a href="~/Account/Logout">Log out</a>
}
        <h1>Edit 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="@firstname" />
                
            @Html.ValidationMessage("firstname")
            </p>

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

                                       
                <p>Location :
                <input type="text" name="location" value="@location" />
                @Html.ValidationMessage("location")
                </p>

                                <p>Hobbies :<br>


@foreach (var row in "SELECT * FROM UserDetails WHERE UserID =@0")
{

if(hobbies == "drawing") {
<input type="checkbox" name="hobbies" value="@row.drawing" checked="checked" />@row.drawing
}
else
{
    <input type="checkbox" name="hobbies" value="Dancing" />
}
     }
                </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">@pnotes</textarea>
                        @Html.ValidationMessage("pnotes")
                        </p>
                <br>

                <input type="hidden" name="Userid" value="@UserId" />

                <input type="submit" />

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

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

20 replies

aabruzzese

Re: 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

Afzaal.Ahmad...

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

Afzaal.Ahmad...

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

Afzaal.Ahmad.Zeeshan

Afzaal.Ahmad...

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

Afzaal.Ahmad...

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