stored procedure problem

Discussion in 'Databases' started by DevDevil, Mar 9, 2006.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. I use the following stored procedure to verify that a username doesn't exst before creating another with the same name.
    CREATE PROCEDURE AddUniqueUser
    (
    @userID nVarChar(50),
    @password nVarChar(50),
    @dateAdded smallDateTime
    )
    AS
    If Exists
    (
    SELECT userID
    FROM Users
    WHERE userID = @userID
    )
    RETURN 1
    ELSE
    INSERT Users ( userID, password, dateAdded )
    VALUES ( @userID, @password, @dateAdded )
    RETURN
    @@identity
    GO
    2 Lines up you see--
    RETURN
    @@identity
    That kicks back the newly created 'accountNum'
    I'm using this--
    Dim connStr As SqlConnection
    Dim cmdAddUniqueUser As SqlCommand
    Dim paramReturnValue As SqlParameter
    Dim paramReturnValue2 As SqlParameter
    Dim dateAdded As DateTime
    dateAdded = DateTime.Now()
    Dim accountNum As Integer
    connStr = New SqlConnection(ConfigurationSettings.AppSettings("sqlCon.ConnectionString"))
    cmdAddUniqueUser = New SqlCommand("AddUniqueUser", connStr)
    cmdAddUniqueUser.CommandType = CommandType.StoredProcedure
    paramReturnValue = cmdAddUniqueUser.Parameters.Add("ReturnValue", SqlDbType.Int)
    paramReturnValue.Direction = ParameterDirection.ReturnValue
    cmdAddUniqueUser.Parameters.Add("@userID", txtUserName.Text)
    cmdAddUniqueUser.Parameters.Add("@password", txtPassword1.Text)
    cmdAddUniqueUser.Parameters.Add("@dateAdded", dateAdded)
    connStr.Open()
    cmdAddUniqueUser.ExecuteNonQuery()
    If cmdAddUniqueUser.Parameters("ReturnValue").Value = 1 Then
    Me.lblUserExists.Visible = True
    Me.lblUserExists.Text = "UserName already exists. Please try again."
    Else
    Response.Redirect("newUser2.aspx?aNum= I need accountNum here
    End If
    connStr.Close()
    Everything works fine, but I need to use the 'accountNum' in my Response.Redirect. Please, if anyone knows how to get this done let me know. I tried adding another 'paramReturnValue' and calling it 'paramReturnValue2' but all I got was '0' which was returned because I was submitting a new userID.
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page