Solved

Can't get my stored proc OUTPUT parameters to come back in my ASP.NET application

Posted on 2007-03-30
7
237 Views
Last Modified: 2013-11-26
I'm unable to get my OUTPUT parameters to come back properly. I have ran my stored proc with the values I'm using in my app in query analyzer and it works fine. I'm going crazy.

-----------------------------------------------------------------------------------------

Dim myConnection As New SqlConnection("server=(local);database=uw_data;uid=jrmcintosh;pwd=ncr3170")
            myConnection.Open()

            Dim myCommand As New SqlCommand("usp_sel_getuser", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure
            Dim uwuser As New SqlParameter("@user", SqlDbType.VarChar, 1)
            uwuser.Value = txtEmail.Text
            myCommand.Parameters.Add(uwuser)
            Dim pass As New SqlParameter("@password", SqlDbType.VarChar, 1)
            pass.Value = txtPassword.Text
            myCommand.Parameters.Add(pass)
            Dim success As New SqlParameter("@success", SqlDbType.Int, 4)
            success.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(success)
            Dim userid As New SqlParameter("@userid", SqlDbType.VarChar, 1)
            userid.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(userid)
            myCommand.ExecuteNonQuery()

            Dim count As Int16 = success.Value
            Session("userid") = userid.Value

            lblcount.Text = count

            myConnection.Close()
0
Comment
Question by:jrmcintosh
  • 4
  • 3
7 Comments
 
LVL 11

Expert Comment

by:dready
ID: 18825401
Did you declarre the parameters as output in the stored procedure usp_sel_getuser ????
0
 

Author Comment

by:jrmcintosh
ID: 18825462
yes

CREATE PROCEDURE dbo.usp_sel_getuser

@user varchar(100),
@password varchar(100),
@success int OUTPUT,
@userid varchar(100) OUTPUT

AS

SELECT * from uw_users where userid = @user  AND pass = @password

SET @userid = (SELECT userid from uw_users where userid = @user AND pass = @password)

SET @success = (select COUNT(*) from uw_users where userid = @user AND pass = @password)

GO
0
 

Author Comment

by:jrmcintosh
ID: 18825467
This is my first time using this method of calling a stored procedure. I have been using the enterprise library method for the past year so this is new to me...
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 11

Expert Comment

by:dready
ID: 18825477
So, your stp should look something like

CREATE PROCEDURE usp_sel_getuser
@user varchar(50),   -- This is a input parameter.
@password varchar(50),
@success int OUTPUT,
@userid varchar OUTPUT
AS  
-- here you do something.

Also, I am  not sure wetheryou set the length of your params correctly in the VB code
0
 

Author Comment

by:jrmcintosh
ID: 18825498
we must've put messages at the same time, I have my stored proc above. I also do not know if I'm setting my lengths correctly.
0
 
LVL 11

Accepted Solution

by:
dready earned 250 total points
ID: 18825573
I think you set the size wrong, if im not mistaken, the third param is the lengh, so try this: (I chanced the 3rd param from 1 to 100 in 3 places.)

     myConnection.Open()

            Dim myCommand As New SqlCommand("usp_sel_getuser", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure
            Dim uwuser As New SqlParameter("@user", SqlDbType.VarChar, 100)
            uwuser.Value = txtEmail.Text
            myCommand.Parameters.Add(uwuser)
            Dim pass As New SqlParameter("@password", SqlDbType.VarChar, 100)
            pass.Value = txtPassword.Text
            myCommand.Parameters.Add(pass)
            Dim success As New SqlParameter("@success", SqlDbType.Int, 4)
            success.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(success)
            Dim userid As New SqlParameter("@userid", SqlDbType.VarChar, 100)
            userid.Direction = ParameterDirection.Output
            myCommand.Parameters.Add(userid)
            myCommand.ExecuteNonQuery()

            Dim count As Int16 = success.Value
            Session("userid") = userid.Value

            lblcount.Text = count

            myConnection.Close()
0
 

Author Comment

by:jrmcintosh
ID: 18825780
That was it, thanks.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Update (December 2011): Since this article was published, the things have changed for good for Android native developers. The Sequoyah Project (http://www.eclipse.org/sequoyah/) automates most of the tasks discussed in this article. You can even fin…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now