General SQL question
-
Hey Phil,
I have another form that follows the first one you helped me with. I wanted to make sure I am on the right track with my stored procedure.
ALTER PROCEDURE Profile
(
@ClientAge Varchar( 100 ),
@MaritalStatus Varchar( 100 ),
@SpFirstName Varchar( 100 ),
@SpLastName Varchar( 100 ),
//...
)
AS
DECLARE @NewID INT
//check to see if the user has already filled out the form, if so send a message letting them know
IF EXISTS( SELECT User_ID
FROM dbo.User_Information
WHERE User_ID=@NewID)
RETURN -1
ELSE
BEGIN
INSERT Profile (
ClientAge,
MaritalStatus,
SpFirstName,
SpLastName,
)
Values (
@ClientAge,
@MaritalStatus,
@SpFirstName,
@SpLastName,
)
//Enter the UserID that was previoulsy created by the first form
SET @NewID = SCOPE_IDENTITY()
INSERT INTO Profile (UserID) Values (@NewID)
RETURN @NewID
End
Is this the best way of doing it and will the Scope_Identity still be the same value from the previous form? Even if they fill out the first form log out and login later to fill out the second form?
Thank you!
-
SCOPE_IDENTITY() will only return the last identity value created within the current scope. In this case the stored procedure.
I'm presuming that the profile table is related to the user table via the UserID field?? In that case you should pass it to this stored procedure as a parameter.
--------------------
Colt 45 - the original point and click interface
-
I figured that was the case with the Scope_Identity()
All of my tables are linked with the UserID which is the Identity field in the User table.
So I would just have @user-id in my stored procedure and insert that value into the UserID in the profile table. I would pass the value to the storedprocedure using cmdSelect.Parameters.Add("@UserID", txtUserID.Text)
How do I populate the txtUserID with the UserID from the User table based on the User_Name that is signed in? In other words how do I filter out the correct UserID?
Thank you!
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply