You need a SQL Select statement. How that should be constructed depends on your database tables. In your select statement you use WHERE to filter the results to just the current user.
It sounds like the poster is trying to relate the login user table (in one db) to some other table (in another db) containing data and/or relationships pertinent to that user. He sounds frustrated because the StarterSite security tables contain very little
info on the user other than the email (which suffices as the userid/username). He is looking to either:
(1) Extend the User Profile table to include additional (and possibly redundant) attributes of the user or
(2) Find some way to implement the Security functionality in his own tables/database or
(3) Find some way to establish a relationship (across two databases) between the user's row in the UserProfile table in the security db and the user's row in his yet-unspecified application database.
None of these seems to be the best way to do this. The target audience of WebMatix is very likely to not have the knowldge and'or fortitude to diddle with the security database.
Is it possible to implement the StarterSite database's security tables in the site's application database and then create an enforceable/maintainable relationship between the two tables that would now be in the same database?
If some similar solution to this exists, the guru who can write a comprehensible tutorial to illustrate the solution would be doing a great service to the WebMatrix user community!
Only thing I have really been asking him to show me is a visual rep of his database. If he can do that, we can probably code some good examples for him. Since he hasn't really shown us much, we can't do much other than guide. I would say add one column to
the table that holds the content to display and in that table, reference the ID number of the user in the aspnet_user table. He could easily build a gridview that would allow him to set page x to user y. Then when the user logs in, simply reference that ID
Number from the content table and display their "page" as a response write.
I'm using the starter site on webmatrix. I have changed some of the design eg added more tabs and removed things which i don't need. So I've got the log in form and database from the starter site, this works all fine. So, what I want is a dedicated page
for when a person logs in. This page will include information on how much holiday they have etc. For this i built (tried) another database with the following fields: name, department, manager, email, start date and amount of holiday. So a person logs in, this
will then bring up a web page dedicated to them only. Any help would be grateful. Thanks in advance.
Just add suitable tables or fields to the existing membership database. There is no need to create a separate database. The WebPages_membership table includes an ID for each user. If I were you, I'd look to add extra fields to the UserProfile table. You
can store the departement etc in that for each user, and you already have their email address. When a user logs in, redirect them to a page which obtains the holiday etc info that relates to the current user's ID.
Here is an example that I have put together on the fly (untested) - I have based this off of the
Starter Kit (as it already has all the working components). I will stage out the steps to help clarify what my code is doing. We are going to pull data from a table that relates back to the username - so its along the same lines as to what
you want to achieve... You can model my example around it :)
Lets get the database sorted out before I go through it. Open the Starter Site or Create a site from the Starter Kit template. Create a new table called
UserData for this example. Add a column.. First one to be called
id make sure its the primary key and the identity as well as set its type to
int. Now create a column called userid and also make this
int but dont worry about the other settings. Continue to create another column called
flavour and set the type to ntext and one more column called
colour and also set the type to ntext.
You should have a table called UserData with 4 columns (id, userid, flavour, colour) - Let's create an account and populate this database!
Step 1: Register a username (http://localhost:xxxx/Account/Register) or click the link in the top right.
Step 2: Log into my website using the credentials I just registered (http://localhost:xxxx/Account/Login) or click the link on the top right - You will be re-directed back to the home page, but the top right hand corner will display your
email/username to show you are logged in. We are going to edit this page in a minute to pull some data out that relates to your username!
Step 3: Go to the Databases. First of all open up the
UserProfile table and look at the data. You will see your email address in one column and in the other a number.. Most likey the number
1.
Step 4: Go to the table we created earlier UserData and we are going to add some data in there. Ignore the first column (id), in the
userid column put in the number from the above step (1), in the
flavour column put in chocolate and in the colour
column put in blue.
Now that you have some details that can be linked back via your UserId - lets get the page to pull the data out!
Step 5: We want to create a copy (Right Click > Copy, then Paste) of default.cshtml
and rename the copy to default2.cshtml. Open it up
default2.cshtml and the current code should look like this:
@{
Layout = "~/_SiteLayout.cshtml";
Page.Title = "Welcome to my Web Site!";
}
<p>
ASP.NET Web Pages make it easy to build powerful .NET based applications for the web.</p>
We want to REMOVE all of this and replace it with this:
@{
Layout = "~/_SiteLayout.cshtml";
Page.Title = "Welcome to my Web Site!";
@* Open or select the database we want *@
var db = Database.Open("StarterSite");
@* Set the ID variable as the UserId from the UserProfile table *@
var id=@WebSecurity.CurrentUserId;
@* Run a query to grab the data and create some variables! *@
var SQLSELECT = "SELECT * FROM UserData where userid=@0";
var Details = db.QuerySingle(SQLSELECT,id);
var DetailsFlavour=Details.flavour;
var DetailsColour=Details.colour;
}
<p>
ASP.NET Web Pages make it easy to build powerful .NET based applications for the web.<br/><br/>
My favourite flavour of ice cream is @Details.flavour.<br/>
The color I like the most is @Details.color.
</p>
Step 6: Save the page. Now run the website, make sure you are logged in and go to
http://localhost:xxxx/default2 - you should see the two things we put in the database!
Please let me know how you go - this is a bit rough and I am still new to Razor as well.. Hopefully this helps you understand how you can pull data from seperate tables and how it goes about doing it.
Cheers, Aaron
Dont forget to Mark as Answer if it has solved your problem!
Mikesdotnett...
All-Star
154887 Points
19862 Posts
Moderator
MVP
Re: User ID and Password
Apr 06, 2011 06:42 AM|LINK
You need a SQL Select statement. How that should be constructed depends on your database tables. In your select statement you use WHERE to filter the results to just the current user.
http://www.firstsql.com/tutor2.htm
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
rrrsr7205
Participant
1304 Points
313 Posts
Re: User ID and Password
Apr 06, 2011 09:33 PM|LINK
It sounds like the poster is trying to relate the login user table (in one db) to some other table (in another db) containing data and/or relationships pertinent to that user. He sounds frustrated because the StarterSite security tables contain very little info on the user other than the email (which suffices as the userid/username). He is looking to either:
(1) Extend the User Profile table to include additional (and possibly redundant) attributes of the user or
(2) Find some way to implement the Security functionality in his own tables/database or
(3) Find some way to establish a relationship (across two databases) between the user's row in the UserProfile table in the security db and the user's row in his yet-unspecified application database.
None of these seems to be the best way to do this. The target audience of WebMatix is very likely to not have the knowldge and'or fortitude to diddle with the security database.
Is it possible to implement the StarterSite database's security tables in the site's application database and then create an enforceable/maintainable relationship between the two tables that would now be in the same database?
If some similar solution to this exists, the guru who can write a comprehensible tutorial to illustrate the solution would be doing a great service to the WebMatrix user community!
bbcompent1
All-Star
32992 Points
8508 Posts
Moderator
Re: User ID and Password
Apr 07, 2011 06:00 PM|LINK
Only thing I have really been asking him to show me is a visual rep of his database. If he can do that, we can probably code some good examples for him. Since he hasn't really shown us much, we can't do much other than guide. I would say add one column to the table that holds the content to display and in that table, reference the ID number of the user in the aspnet_user table. He could easily build a gridview that would allow him to set page x to user y. Then when the user logs in, simply reference that ID Number from the content table and display their "page" as a response write.
richt
Member
24 Points
15 Posts
Re: User ID and Password
Apr 07, 2011 06:41 PM|LINK
Hi,
I'm using the starter site on webmatrix. I have changed some of the design eg added more tabs and removed things which i don't need. So I've got the log in form and database from the starter site, this works all fine. So, what I want is a dedicated page for when a person logs in. This page will include information on how much holiday they have etc. For this i built (tried) another database with the following fields: name, department, manager, email, start date and amount of holiday. So a person logs in, this will then bring up a web page dedicated to them only. Any help would be grateful. Thanks in advance.
Mikesdotnett...
All-Star
154887 Points
19862 Posts
Moderator
MVP
Re: User ID and Password
Apr 07, 2011 07:37 PM|LINK
Just add suitable tables or fields to the existing membership database. There is no need to create a separate database. The WebPages_membership table includes an ID for each user. If I were you, I'd look to add extra fields to the UserProfile table. You can store the departement etc in that for each user, and you already have their email address. When a user logs in, redirect them to a page which obtains the holiday etc info that relates to the current user's ID.
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
richt
Member
24 Points
15 Posts
Re: User ID and Password
Apr 10, 2011 10:23 AM|LINK
Thanks for this, I'm going to give it a go and see what happens.
richt
Member
24 Points
15 Posts
Re: User ID and Password
Apr 12, 2011 09:33 PM|LINK
Hi Mike,
I've tried all sorts but nothing seems to be working. How do i redirect them. Thanks.
a-rad
Member
444 Points
156 Posts
Re: User ID and Password
Apr 15, 2011 02:02 PM|LINK
Richt,
Here is an example that I have put together on the fly (untested) - I have based this off of the Starter Kit (as it already has all the working components). I will stage out the steps to help clarify what my code is doing. We are going to pull data from a table that relates back to the username - so its along the same lines as to what you want to achieve... You can model my example around it :)
Lets get the database sorted out before I go through it. Open the Starter Site or Create a site from the Starter Kit template. Create a new table called UserData for this example. Add a column.. First one to be called id make sure its the primary key and the identity as well as set its type to int. Now create a column called userid and also make this int but dont worry about the other settings. Continue to create another column called flavour and set the type to ntext and one more column called colour and also set the type to ntext.
You should have a table called UserData with 4 columns (id, userid, flavour, colour) - Let's create an account and populate this database!
Step 1: Register a username (http://localhost:xxxx/Account/Register) or click the link in the top right.
You can also add the account as an admin if you need to but no nessasary for this. To learn more about that go to: http://www.asp.net/webmatrix/tutorials/16-adding-security-and-membership
Step 2: Log into my website using the credentials I just registered (http://localhost:xxxx/Account/Login) or click the link on the top right - You will be re-directed back to the home page, but the top right hand corner will display your email/username to show you are logged in. We are going to edit this page in a minute to pull some data out that relates to your username!
Step 3: Go to the Databases. First of all open up the UserProfile table and look at the data. You will see your email address in one column and in the other a number.. Most likey the number 1.
Step 4: Go to the table we created earlier UserData and we are going to add some data in there. Ignore the first column (id), in the userid column put in the number from the above step (1), in the flavour column put in chocolate and in the colour column put in blue.
Now that you have some details that can be linked back via your UserId - lets get the page to pull the data out!
Step 5: We want to create a copy (Right Click > Copy, then Paste) of default.cshtml and rename the copy to default2.cshtml. Open it up default2.cshtml and the current code should look like this:
@{ Layout = "~/_SiteLayout.cshtml"; Page.Title = "Welcome to my Web Site!"; } <p> ASP.NET Web Pages make it easy to build powerful .NET based applications for the web.</p>We want to REMOVE all of this and replace it with this:
@{ Layout = "~/_SiteLayout.cshtml"; Page.Title = "Welcome to my Web Site!"; @* Open or select the database we want *@ var db = Database.Open("StarterSite"); @* Set the ID variable as the UserId from the UserProfile table *@ var id=@WebSecurity.CurrentUserId; @* Run a query to grab the data and create some variables! *@ var SQLSELECT = "SELECT * FROM UserData where userid=@0"; var Details = db.QuerySingle(SQLSELECT,id); var DetailsFlavour=Details.flavour; var DetailsColour=Details.colour; } <p> ASP.NET Web Pages make it easy to build powerful .NET based applications for the web.<br/><br/> My favourite flavour of ice cream is @Details.flavour.<br/> The color I like the most is @Details.color. </p>Step 6: Save the page. Now run the website, make sure you are logged in and go to http://localhost:xxxx/default2 - you should see the two things we put in the database!
Please let me know how you go - this is a bit rough and I am still new to Razor as well.. Hopefully this helps you understand how you can pull data from seperate tables and how it goes about doing it.
Cheers, Aaron
richt
Member
24 Points
15 Posts
Re: User ID and Password
Apr 19, 2011 07:49 PM|LINK
Brilliant, works like a dream. I just need to work it into my college work (lol). Thanks for the input.
a-rad
Member
444 Points
156 Posts
Re: User ID and Password
Apr 19, 2011 10:42 PM|LINK
Be sure to give it a go first - should you get stuck, I will try and assist where I can :)