custom Profile Table
-
I just started working on a custom profile table for a site i'm building. Where each user has their own profile with information that they put on their page. What i'm trying to do is when a User signs up for an account the UserId from (aspnet_Users) table.
Will get Inserted into my custom_Profile table column (UserId), i'm still learning T-SQL so please bare with me here is my section of script::
Thxs for any help or advice in Advance
create
table dbo.custom_Profile (
UserId
uniqueidentifier not null Primary Key,
InterestedIn
nvarchar(256) null,
IntroTitle
nvarchar(100) null,
TellOthers
nvarchar(MAX)null,
MaritalStatus
nvarchar(20) null,
Race nvarchar(20) null,
Smoking
nvarchar(20) null,
Drinking
nvarchar(20) null,
Drugs
nvarchar(20) null,
Education
nvarchar(256) null
go
CREATE
procedure [dbo].[getcustomProfile]
nvarchar(150)
AS
DECLARE
UserInsert CURSOR
KEYSET
FOR
SELECT UserID
FROM dbo.aspnet_Users
DECLARE
@User nvarchar(150)
OPEN
UserInsert
FETCH
NEXT FROM UserInsert INTO @User
WHILE
(@@fetch_status = 0)
BEGIN
INSERT dbo.custom_Profile (UserId)
VALUES (@User)
FETCH NEXT FROM UserInsert INTO @User
END
CLOSE
UserInsert
DEALLOCATE
UserInsert
-
First thing, remove that cursor. It's not necessary. This will do exactly the same as that entire cursor.
INSERT dbo.custom_Profile (UserId)
SELECT UserID FROM dbo.aspnet_Users
It's one of the big mentality shifts you'll need when you start with SQL, thinking in terms of sets, not individual rows. I'd suggest that you locate a basic SQL book and have a read through.
You are passing a parameter to that stored proc, but not using it anywhere. What's your intention for that procedure to do?
Another thing. I notice you have nvarchar everywhere. Do you need to store unicode data in this table? If not, make those plain varchar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Well like i mentioned in the orginal post i'm working on Inserting the UserId from the aspnet_User Table (UserId) into my custom _Profile Table column (UserId) for every user that joins the site. That way when each user posts information, it is unique in each data set.
-
I have noticed when a user makes a account that information gets populated across several aspnet Tables specially the UserId.
aspnet_Membership,aspnet_Users,aspnet_Profile,aspnet_UsersInRoles etc
I need that UserId to get populated into the UserId Column every time a person Registers on the site
-
Then all you're looking for is a simple, one row insert.
When the user registers fire off a stored proc that just inserts that userID into your custom table.
CREATE procedure [dbo].[getcustomProfile]
@UserId uniqueidentifier
AS
INSERT dbo.custom_Profile (UserId)
VALUES (@UserId)
That's it. Done.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply