none
Get OUTPUT parameter value from Stored procedure in VB.NET

    Question

  • hello all,

    I am trying to get value of an output parameter from stored procedure. I am doing this for the first time.

    I am still not able to get the value that I set for the OUTPUT parameter in the stored procedure using IF condition.

    Here is my stored procedure:

    ALTER PROCEDURE dbo.InsertUser

    (

    @UserName VARCHAR(50),

    @Password VARCHAR(50),

    @FirstName VARCHAR(50),

    @LastName VARCHAR(50),

    @City VARCHAR(50),

    @Province VARCHAR(50),

    @Country VARCHAR(50),

    @State VARCHAR(50),

    @ZipCode VARCHAR(50),

    @PhoneNo VARCHAR(50),

    @StreetNo VARCHAR(50),

    @DOB VARCHAR(50),

    @Emailadd VARCHAR(50),

    @WebSite VARCHAR(50),

    @Status BIT,

    @ActivateKey VARCHAR(50),

    @Level INT,

    @Opp INT OUTPUT

    )

    AS

    DECLARE @TRec NUMERIC

    SELECT @TRec=COUNT(*)

    FROM Login INNER JOIN

    [User] ON Login.USERID = [User].UserID

    WHERE ([User].Emailadd = @Emailadd) OR (Login.UserName = @UserName)

    IF(@TRec>0)

    BEGIN

    SET @TRec=3

    END

    ELSE

    BEGIN

    DECLARE @UID INT

    INSERT INTO LOGIN

    (UserName, Password)

    VALUES (@UserName,@Password)

    IF(@@IDENTITY<>0)

    BEGIN

    SELECT TOP 1 @UID=USERID FROM LOGIN ORDER BY USERID DESC

    INSERT INTO [User]

    (UserID, FirstName, LastName, City, province, Country, State, ZipCode, PhoneNo, StreetNo, DOB, Emailadd, Website, Status, ActivateKey, [Level])

    VALUES (@UID, @FirstName, @LastName, @City, @Province, @Country, @State, @ZipCode, @PhoneNo, @StreetNo, @DOB, @Emailadd, @WebSite, @Status, @ActivateKey, @Level)

    END

    END

    RETURN @TRec

     

    Also my VB.NET code is as below:

    Con = New SqlConnection("Server=(local); Initial Catalog=onlinejob; User ID=sa; Password=sa")

    Con.Open()

    Cmd = New SqlCommand

    Cmd.CommandText = "InsertUser"

    Cmd.CommandType = CommandType.StoredProcedure

    Cmd.Parameters.Add("@UserName", SqlDbType.VarChar, 50)

    Cmd.Parameters("@UserName").Value = username

    Cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50)

    Cmd.Parameters("@Password").Value = password

    Cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50)

    Cmd.Parameters("@FirstName").Value = firstname

    Cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50)

    Cmd.Parameters("@LastName").Value = lastname

    Cmd.Parameters.Add("@City", SqlDbType.VarChar, 50)

    Cmd.Parameters("@City").Value = city

    Cmd.Parameters.Add("@Province", SqlDbType.VarChar, 50)

    Cmd.Parameters("@Province").Value = province

    Cmd.Parameters.Add("@Country", SqlDbType.VarChar, 50)

    Cmd.Parameters("@Country").Value = country

    Cmd.Parameters.Add("@State", SqlDbType.VarChar, 50)

    Cmd.Parameters("@State").Value = state

    Cmd.Parameters.Add("@ZipCode", SqlDbType.VarChar, 50)

    Cmd.Parameters("@ZipCode").Value = zipcode

    Cmd.Parameters.Add("@PhoneNo", SqlDbType.VarChar, 50)

    Cmd.Parameters("@PhoneNo").Value = phoneno

    Cmd.Parameters.Add("@StreetNo", SqlDbType.VarChar, 50)

    Cmd.Parameters("@StreetNo").Value = streetno

    Cmd.Parameters.Add("@DOB", SqlDbType.VarChar, 50)

    Cmd.Parameters("@DOB").Value = dob

    Cmd.Parameters.Add("@Emailadd", SqlDbType.VarChar, 50)

    Cmd.Parameters("@Emailadd").Value = emailadd

    Cmd.Parameters.Add("@WebSite", SqlDbType.VarChar, 50)

    Cmd.Parameters("@WebSite").Value = website

    Cmd.Parameters.Add("@Status", SqlDbType.Bit)

    Cmd.Parameters("@Status").Value = status

    Cmd.Parameters.Add("@ActivateKey", SqlDbType.VarChar, 50)

    Cmd.Parameters("@ActivateKey").Value = activatekey

    Cmd.Parameters.Add("@Level", SqlDbType.Int)

    Cmd.Parameters("@Level").Value = level

    Cmd.Parameters.Add("@Opp", SqlDbType.Int)

    Cmd.Parameters("@Opp").Direction = ParameterDirection.Output

    Cmd.Connection = Con

    Dim result As Integer

    result = Convert.ToInt32(Cmd.ExecuteScalar)

     

     

    What I want is that when the first IF condition is true, then the @Opp should contain the value 3 which I set, so that I may show the user at the front end that the UserName or Email already exists.

    Anyone please help me!

    thanks in advance

    Wednesday, July 12, 2006 7:47 AM

Answers

  • G'day,

    Your doing everything right except with the last line.

    result = Convert.ToInt32(Cmd.ExecuteScalar)

    ExecuteScalar is used to run queries that return a single value like for example SELECT Count() .....

    What you need to do is once the stored procedure is executed with Cmd.Execute is to go to the @Opp parameter and get it's value...

    Cmd.Execute()

    result = Cmd.Parameters("@Opp").Value

    The parameter holds the value.

    Hope I've got the syntax right there and that it helps.

    Wednesday, July 12, 2006 9:38 AM

All replies

  • G'day,

    Your doing everything right except with the last line.

    result = Convert.ToInt32(Cmd.ExecuteScalar)

    ExecuteScalar is used to run queries that return a single value like for example SELECT Count() .....

    What you need to do is once the stored procedure is executed with Cmd.Execute is to go to the @Opp parameter and get it's value...

    Cmd.Execute()

    result = Cmd.Parameters("@Opp").Value

    The parameter holds the value.

    Hope I've got the syntax right there and that it helps.

    Wednesday, July 12, 2006 9:38 AM
  • thanks Derek for replying to me and also encouraging me for I doing right.

    By Cmd.Execute(), you mean Cmd.ExecuteNonQuery? As there is no method Execute of SqlCommand object.

    I am waiting for your reply!

    Thanks again!

    Wednesday, July 12, 2006 9:45 AM
  • Yes, it means that.

    Now my job is done.

    Thank E. Smyth for helping me out!

    Thanks indeed!

    Wednesday, July 12, 2006 9:55 AM