General SQL question
-
I am new to SQL and to asp.net.
I have three tables:
1. Users
>userId
>Password
2. UsersRoles
>userId (joined with Users)
>RoleID
3. Roles
>RoleID (joined with UsersRoles)
>RoleName
I have created a new user page in asp.net. The form inserts all the information in the form into the Users table. Should it also auto populate the new user into the UserRoles table since they are joined? Should I expect the userId to be added to the UsersRoles table?
If not what is the best way to accomplish this? I can't add it from the new user form because the UserId will not exist until the record has been created.
Any help or suggestions would be greatly appreciated.
-
How do you insert the information into the Users table? If you use a stored proc, you can add an insert statement, or call another stored proc, after the insert into the Users table.
--------------------
Colt 45 - the original point and click interface
-
I use a stored procedure.
How will I pull the userId from the main table to insert into to the UsersRoles table
this is my stored procedure
ALTER PROCEDURE dbo.DBRegister
(
@FirstName Varchar( 100 ),
@LastName Varchar( 100 ),
@Email1 Varchar( 100 ),
@Address1 Varchar( 100 ),
@Address2 Varchar( 100 ),
@City Varchar( 100 ),
@State Varchar( 100 ),
@PostalCode Varchar( 100 ),
@HomePhone Varchar( 100 ),
@CellPhone Varchar( 100 ),
@WorkPhone Varchar( 100 ),
@UserName Varchar( 100 ),
@Password Varchar( 100 )
)
AS
IF EXISTS( SELECT User_ID
FROM dbo.User_Information
WHERE User_Name=@UserName )
RETURN - 1
ELSE
INSERT User_Information (
First_Name,
Last_Name,
Email_1,
Address_1,
Address_2,
City,
State_Province,
Postal_Code,
Home_Phone,
Cell_Phone,
Work_Phone,
User_Name,
Password
) VALUES (
@FirstName,
@LastName,
@Email1,
@Address1,
@Address2,
@City,
@State,
@PostalCode,
@HomePhone,
@CellPhone,
@WorkPhone,
@UserName,
@Password
)
RETURN @@Identity
Won't the userID that is created in the Users table only be available after this process has completed?
-
Instead of just returning the UserID by using @@IDENTITY. Assign the value to a local variable and the execute another INSERT statement with the new value.
Also, to avoid and multi-user and scope issues, you probably should use SCOPE_IDENTITY() instead of @@IDENTITY.
EG:
... rest of your procedure
INSERT User_Information ( First_Name,Last_Name,Email_1,Address_1,Address_2, City,State_Province,Postal_Code,Home_Phone, Cell_Phone,Work_Phone,User_Name,Password ) VALUES ( @FirstName,@LastName,@Email1,@Address1,@Address2, @City,@State,@PostalCode,@HomePhone,@CellPhone, @WorkPhone,@UserName,@Password)
SET @NewID = SCOPE_IDENTITY()
INSERT INTO UserRoles (UserID, RoleID) VALUES (@NewID, RoleID)
RETURN @NewID
You'll probably need to alter the INSERT INTO UserRoles statement to obtain the RoleID, or if you already have a stored procedure setup for UserRoles, then just execute it.
--------------------
Colt 45 - the original point and click interface -
Ok, I DECLARED @NewID as an INT.
I made your suggested changes to the stored procedure except I removed the RoleID because it has a default value associated to it.
The SCOPE_IDENTITY() returns the username of the person instead of the UserID which is created when a new record is entered.
I wasn't clear enough with the Users table - sorry
Users table
>UserID
>UserName
>Password
How do I change the SCOPE_IDENTITY() to the UserID instead of the UserName?
Am I supposed to create a text box on the registration form for the @NewID? Or, how is this value passed to the stored procedure?
Thank you for all your help!
-
The SCOPE_IDENTITY() returns the username of the person instead of the UserID which is created when a new record is entered.
I don't understand this. SCOPE_IDENTITY() returns the last IDENTITY value inserted into an IDENTITY column in the current scope. Is the username field your identity field?
Am I supposed to create a text box on the registration form for the @NewID? Or, how is this value passed to the stored procedure?
Which stored procedure are you referring to here? If you're referring to my comment "if you already have a stored procedure setup for UserRoles, then just execute it", then you just replace the insert statement with an "EXEC procname" statement.
--------------------
Colt 45 - the original point and click interface -
No the UserID is the IDENTITY column.
I don't have a stored procedure set up for UserRoles
This is my stored procedure that I call when the user hits "Register"
ALTER PROCEDURE dbo.DBRegister
(
@FirstName Varchar( 100 ),
@LastName Varchar( 100 ),
@Email1 Varchar( 100 ),
@Address1 Varchar( 100 ),
@Address2 Varchar( 100 ),
@City Varchar( 100 ),
@State Varchar( 100 ),
@PostalCode Varchar( 100 ),
@HomePhone Varchar( 100 ),
@CellPhone Varchar( 100 ),
@WorkPhone Varchar( 100 ),
@UserName Varchar( 100 ),
@Password Varchar( 100 )
)
AS
DECLARE @NewID INT
IF EXISTS( SELECT User_ID
FROM dbo.User_Information
WHERE User_Name=@UserName )
RETURN - 1
ELSE
INSERT User_Information (
First_Name,
Last_Name,
Email_1,
Address_1,
Address_2,
City,
State_Province,
Postal_Code,
Home_Phone,
Cell_Phone,
Work_Phone,
User_Name,
Password
)
VALUES (
@FirstName,
@LastName,
@Email1,
@Address1,
@Address2,
@City,
@State,
@PostalCode,
@HomePhone,
@CellPhone,
@WorkPhone,
@UserName,
@Password
)
SET @NewID = SCOPE_IDENTITY()
INSERT INTO UsersRoles (UserID) Values (@NewID)
RETURN @NewID
If I try and register a new user now It tells me the user name is already in use even though it is not. Completely comfused as to what is going on now.
-
Hmmm ... it may be because you'll need a BEGIN and END around the ELSE part of the IF statement. Without the BEGIN ... END, it would execute the SET and INSERT INTO UsersRoles every time the procedure executed.
ALTER PROCEDURE dbo.DBRegister ( @FirstName Varchar( 100 ),@LastName Varchar( 100 ),@Email1 Varchar( 100 ), @Address1 Varchar( 100 ),@Address2 Varchar( 100 ),@City Varchar( 100 ), @State Varchar( 100 ),@PostalCode Varchar( 100 ),@HomePhone Varchar( 100 ), @CellPhone Varchar( 100 ),@WorkPhone Varchar( 100 ),@UserName Varchar( 100 ), @Password Varchar( 100 ) ) AS DECLARE @NewID INT
IF EXISTS( SELECT User_ID FROM dbo.User_Information WHERE User_Name=@UserName ) RETURN - 1 ELSE BEGIN INSERT User_Information ( First_Name,Last_Name,Email_1,Address_1,Address_2,City, State_Province,Postal_Code,Home_Phone,Cell_Phone,Work_Phone,User_Name,Password ) VALUES (@FirstName,@LastName,@Email1,@Address1,@Address2,@City,@State, @PostalCode,@HomePhone,@CellPhone,@WorkPhone,@UserName,@Password ) SET @NewID = SCOPE_IDENTITY() INSERT INTO UsersRoles (UserID) Values (@NewID) RETURN @NewID END
--------------------
Colt 45 - the original point and click interface -
Phill, thank you!
It now ads the new user to the users table but it also gives me this error message:
Server Error in '/Global' Application.
--------------------------------------------------------------------------------
Invalid object name 'UsersRoles'.
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: System.Data.SqlClient.SqlException: Invalid object name 'UsersRoles'.
Source Error:
Line 59: cmdSelect.Parameters.Add("@Password", txtPassword.Text)
Line 60: conRegister.Open()
Line 61: cmdSelect.ExecuteNonQuery()
Line 62: intResult = cmdSelect.Parameters("RETURN_VALUE").Value
Line 63: conRegister.Close()
Source File: E:\Global\Register.aspx.vb Line: 61
It also doesn't ad the UserID to the UsersRoles table.
Thoughts?
Also can you reccomend a good SQL reference book?
-
Is "UsersRoles" the right name for the table?
Some good books I've got are,
With an ASP.Net and Database focus
- Building Web Solutions with ASP.NET and ADO.NET
- Beginning ASP.NET 1.1 with Visual C# .NET 2003
and with an Database focus
- Professional SQL Server 2000 Programming
- Mastering C# Database Programming
- Any of the "The Guru's Guide ..." books by Ken Henderson
--------------------
Colt 45 - the original point and click interface -
Yes, UsersRoles is the right name for the table. I don't know if this makes a difference but the Users table is listed as Users(dbo) and UsersRoles is just UsersRoles. Reaching for anything at this point, been working on getting this to work all day.
-
Where are you viewing the list of tables from?
The UsersRoles table is probably not owned by dbo. You can use sp_changeobjectowner to change it to dbo if need be.
--------------------
Colt 45 - the original point and click interface -
I am using Visual Studio.net and the web host has a sql manager page where the usersroles table was created. The users table already existed.
-
If you type sp_help 'UsersRoles' into Query analyzer you should be able to see who owns the table. If you can't change the owner, the just user the owner prefix in the query.
eg: INSERT INTO MyOwner.UsersRoles (...
--------------------
Colt 45 - the original point and click interface -
Will test it tomorrow. I am not able to access the SQL server from the IP.
Thank you for all of your help Phill
-Curtis
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply