Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 3.5 > ASP.NET 3.5 Basics
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 3.5 Basics If you are new to ASP or ASP.NET programming with version 3.5, this is the forum to begin asking questions. Please also see the Visual Web Developer 2008 forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 3.5 Basics section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 3rd, 2009, 02:29 PM
Authorized User
 
Join Date: Jan 2009
Posts: 23
Thanks: 10
Thanked 0 Times in 0 Posts
Default joining two databases

I have the skeleton of a website beginning to come together. I am building it in ASP.NET using Visual Web Developer 2008 Express.

I have been able to get the profile part working using the create new user wizard - this is working with the aspnetdb.mdf database which is created automatically.

I already have my own database and I want to link the two.

Firstly, let me ask - which is the better way:
a) adding my tables into the aspnetdb.mdf or
b) creating a link between the two databases (I have no idea how to do this!)

I have initially tinkered with adding my tables to the aspnetdb.mdf - I based the relationship I created on the relationship between tblUsers, tblRoles and tblUsersinRoles. I want my users to be able to sign in with their user name but then be able to access their own personal information. I think there needs to be a connection between the UserID and my own CustomerID.

I think I need to be able to get it to create a unique customerID when a new user is created. In Access I think this would have been created using Autonumber - but I cant see how to do this in SQLServer.

I'd appreciate any help on this topic

Thanks
  #2 (permalink)  
Old February 4th, 2009, 02:54 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Ah, the eternal question.

Let me give you a few of my thoughts on this. Hopefully, others will chime in with theirs as well.

Quote:
Firstly, let me ask - which is the better way:
a) adding my tables into the aspnetdb.mdf or
b) creating a link between the two databases (I have no idea how to do this!)
I don't think there is much doubt that a single db is far, far easier to implement, easier to maintain, and performs better.

"Linking" two databases is not as easy as it sounds, because you always have to consider the case in which the connection to one database succeeds, but the other fails. This would likely involve heavy use of Transactions and rollbacks. Transactions are tricky enough. Managing transactions across seperate databases is really, really tricky.

Quote:
I want my users to be able to sign in with their user name but then be able to access their own personal information.
That's what Profile is for. ASP.NET makes it real easy to get the current users profile in a page, by automatically populating the Profile object. You can always place any information you want in the user's profile.

If you decide you don't want to use Profile, you can certainly use your own tables. As you correctly point out, you need to link the tables with a foreign key. The easiest way to do this is to include either the UserName or the UserID (both of which are guaranteed to be unique) from the aspnet_Users table as a column in your custom table. You would then use that as the foreign key.

Quote:
I think I need to be able to get it to create a unique customerID when a new user is created.
You let SQL Server do this in an Identity column. That is the same as "Autonumber" in Access.

The other way to do it is to actually let the userID from the aspnet_Users table be the primary key of your separate Customer table. In other words, when a new customer record is created, you populate it's primary key column with the UserID from aspnet_Users. Then, you set up the FK relationship using the primary keys from both tables.

Of course, this means that the CustomerIDs will be GUIDs... but you have to ask yourself, "Does that really matter?" Think hard about that question. In many (I would say most) cases, integer-based "human-readable" IDs (HUIDs) are not truly necessary. Sure, they may make users more "comfortable", but they are actually a very outdated paradigm. If your app relies on having HUIDs for records, you may want to rethink things...

Just my $0.02.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
The Following User Says Thank You to Lee Dumond For This Useful Post:
Spider (February 10th, 2009)
  #3 (permalink)  
Old February 4th, 2009, 06:14 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

If you really think you need a "HUID" (nice term, Lee!), you can always add an IDENTITY column to your separate CUSTOMER table...but *still* use the GUID from aspnet_Users as the foreign key. That is, the "HUID" is just "sugar" to sweeten the display for your GUID-challenged human friends.
  #4 (permalink)  
Old February 4th, 2009, 10:39 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Quote:
Originally Posted by Old Pedant View Post
If you really think you need a "HUID" (nice term, Lee!), you can always add an IDENTITY column to your separate CUSTOMER table...but *still* use the GUID from aspnet_Users as the foreign key. That is, the "HUID" is just "sugar" to sweeten the display for your GUID-challenged human friends.
Yes, I guess this was "kinda" what I was trying to get at.

The point is, sometimes HUIDs don't do much except to add complexity to the schema. We've all worked with applications that implement HUIDs where they were not truly necessary.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
  #5 (permalink)  
Old February 10th, 2009, 02:50 PM
Authorized User
 
Join Date: Jan 2009
Posts: 23
Thanks: 10
Thanked 0 Times in 0 Posts
Default

Could you explain a bit more about using the 'Profile' in ASP.NET. I have tried to insert the UserID into the Profile table, but it is just giving a blank output.

Code:
INSERT INTO aspnet_Profile
SELECT     UserId
FROM         aspnet_Users
WHERE     (UserName = @UserName)
The error I get is that the column name or number of supplied values does not match the table definition. It does ask for the UserName parameter, but returns blank regardless of whether I supply parameter or not.

I have also gone into the profile table and set all fields except UserId to accept Null values.

Can you suggest what I am doing wrong?
Thanks
  #6 (permalink)  
Old February 10th, 2009, 04:15 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Could you copy/paste the *FULL* error message???

I *suspect* that the problem is that your aspnet_Profile table has more than one required field.

A required field is any field that is *NOT* specified as NULL and does not have a DEFAULT value assigned.

So say your table looks like this (just guessing!):
Code:
Table: aspnet_Profile
    UserId INTEGER REFERENCES aspnet_Users(UserID),
    UserName VARCHAR( 50 ),
    NumberOfPosts INTEGER DEFAULT 0,
    NumberOfPoints INTEGER DEFAULT 0,
    whenJoined DATETIME
That table definition essentially says that *AT A MINIMUM* you must provide the UserID and the UserName and whenJoined values when adding a record to that table. You *can* supply NumberOfPosts and NumberOfPoints, but if you don't they will default to zero, so no problem.

But your code here:
Code:
INSERT INTO aspnet_Profile
SELECT     UserId
FROM         aspnet_Users
WHERE     (UserName = @UserName)
does *NOT* supply a UserName or whenJoined, does it? You are only SELECT-ing *one* field from aspnet_Users. To wit, the UserId field.

A better way to do this is to make sure your INSERT specifies *ALL* the required fields, which will in turn force you to be aware of the fields to be copied. So maybe:
Code:
INSERT INTO aspnet_Profile (UserID, UserName, whenAdded)
SELECT UserId, UserName, getDate()
FROM    aspnet_Users
WHERE  UserName = @UserName
Now, I suspect your don't really have any aspnet_Profile.UserName field, but hopefully this gets you started. Just make sure you have all REQUIRED fields listed in your INSERT INTO line and things should take care of themselves.

Note that you *can* supply values for the non-required fields, if you like:
Code:
 INSERT INTO aspnet_Profile (UserID, UserName, NumberOfPosts, NumberOfPoints, whenAdded)
 SELECT UserId, UserName, 1, 3, getDate()
 FROM    aspnet_Users
 WHERE  UserName = @UserName
So plenty of flexibility.
  #7 (permalink)  
Old February 10th, 2009, 04:28 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

Hi Spider,

You're not really supposed (nor do you want to) use the various aspnet* tables directly. Their intended usage is through the ASP.NET API. Microsoft can (and might) change the structure of the tables in future updates. Additionally, the awkward way the data is stored for a user in a single row makes it near impossible to store data in that table yourself and make it accessible through the Profile class. So my advise is either:

1. Create your own Profile table with a name different from aspnet_Profile.

or

2., Use the Profile class from the .NET API. When done correctly, you can do something like this in code:

Profile.FirstName = txtFirstName.Text

No need to load or save anything explicitly. Alternatively, you can load data for a specific user:

Dim yourProfile As ProfileCommon = CType(Profile.CreateProfile(txtUserName.Text), ProfileCommon)
yourProfile.FirstName = txtFirstName.Text
yourProfile.Save()

Does that help?

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!

Last edited by Imar; February 10th, 2009 at 04:30 PM..
The Following User Says Thank You to Imar For This Useful Post:
Spider (February 15th, 2009)
  #8 (permalink)  
Old February 10th, 2009, 11:23 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Yes, Imar is right.

The ASP.NET profile system is provider-based. The Profile property of a page derives from the ProfileCommon class, which uses the profile provider to interact with the aspnet_Profile table. As Imar pointed out, you would use the methods of the ProfileCommon class to read, edit, and save profile data.

The aspnet_Profile table that is automatically created in an ASPNETDB,mdf database consists of five columns: UserID, PropertyNames, PropertyValuesString, PropertyValuesBinary, and LastUpdatedDate.

The reason you cannot query the aspnet_Profile table is that the properties are stored as blobs in this table, and not into separate columns of their own. The PropertyNames column contains one long serialized string of all the names of all the properties. The PropertyValuesString column contains all the property values that are text-serializable, like strings, dates, and integers. The PropertyValuesBinary column contains all the complex-type values that are binary-serializable.

You actually define the property names and types in the web.config file, then use the API to work with those properties. The provider then takes care of serializing and deserializing the data to and from the database.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
The Following User Says Thank You to Lee Dumond For This Useful Post:
Spider (February 15th, 2009)
  #9 (permalink)  
Old February 15th, 2009, 12:34 PM
Authorized User
 
Join Date: Jan 2009
Posts: 23
Thanks: 10
Thanked 0 Times in 0 Posts
Default

Lee and Imar
Thanks for your input on this one.

So, say I make my own Customer table, and I create it within the ASPNETDB, and I want to link / join it with a UserName which I have created with the CreateUserWizard.

Using the GUI in Visual Web Developer 2008 Express, I have created what I think are joins, including a UserName within the Customer table, with the same details as that in the User Table. (nvarchar256).

I thought that I could have the user create their profile (using CreateProfileWizard) then send them to another screen where they fill in additional personal information which is then stored in the Customer table, (based on the logged in UserName).

I think that to do this I need to use some sort of SQL statement which deposits the UserName in the Customer Table as well as the User table when the profile is created.

Is this possible to do, and if so, how do you write the sql statement and where do you put it? I am afraid I just cannot get to grips with the Profile class at all.

Once again, thanks for helping me out.
  #10 (permalink)  
Old February 15th, 2009, 02:41 PM
Lee Dumond's Avatar
Wrox Author
Points: 4,942, Level: 29
Points: 4,942, Level: 29 Points: 4,942, Level: 29 Points: 4,942, Level: 29
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2008
Location: Decatur, IL, USA.
Posts: 923
Thanks: 12
Thanked 166 Times in 162 Posts
Default

Well, first, there is no such thing as a "CreateProfileWizard" at all.

If you want to let people fill in their profile properties at the time the user is created, you will have to add extra steps to the CreateUserWizard. You'd add the textboxes or what not to let them do that there. You could let them insert data into the Customer table the same way, as an additional step in the CreateUserWizard.

Of course, you would probably want to have a page that lets users add or update profile data after the fact.
__________________
Visit my blog at http://leedumond.com
Follow me on Twitter: http://twitter.com/LeeDumond

Code:
if (this.PostHelpedYou)
{
   ClickThanksButton(); 
}
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Joining Two DataSets YiannisF General .NET 4 October 30th, 2008 06:38 AM
joining tables pit_bait SQL Language 2 September 28th, 2007 07:29 AM
Joining Nodes (HELP!!) MissHenesy XSLT 2 October 17th, 2006 05:26 PM
Joining Three Tables Alexpizzoferro Classic ASP Databases 5 September 1st, 2006 04:08 PM
Joining two tables rajustha SQL Server 2000 1 December 7th, 2003 07:38 AM



All times are GMT -4. The time now is 08:20 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.