Member Avatar for ryan311 
cmd.ActiveConnection = cn
        cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
        cmd.CommandText = "sp_login"
        cmd.Parameters.Append( _
        cmd.CreateParameter("result", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamOutput))
        cmd.Parameters.Append( _
        cmd.CreateParameter("user", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 15, TextBox1.Text))
        cmd.Parameters.Append( _
        cmd.CreateParameter("passuser", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 15, TextBox2.Text))

        rs.Open(cmd, , ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
        If rs.Fields!result.Value = 1 Then
            AddProduct.Show()
        Else
            MsgBox("Your Username or Password is Incorrect", MsgBoxStyle.Information, "AEB Gasul System")
            Exit Sub
        End If

this is my stored procedure code

CREATE PROCEDURE sp_login
(
@user varchar(15),
@passuser varchar(15),
@result integer OUTPUT
)
AS

if exists (select userlogin, pass from dbo.tbl_login where userlogin = @user and pass = @passuser)
begin
	Select 1 as result
	
end
GO

how can i get the result value in stored procedure?

  • 2 Contributors
  • 3 Replies
  • 282 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by Pgmer

Dani AI

Generated

The stored procedure as posted by declares an OUTPUT parameter but never assigns it, and the client opens a Recordset then tries to read rs.Fields!result.Value. As noted, an OUTPUT parameter is the right approach — however two things must be fixed: the procedure must assign a value to the output parameter, and the ADODB Command should be executed and the value read from the Command.Parameters collection (or, alternatively, return a SELECT and read the recordset). Also add SET NOCOUNT ON to avoid extra "rows affected" messages that can confuse ADO.

Example of a minimal stored procedure that sets the output value:

CREATE PROCEDURE sp_login
  @user varchar(15),
  @passuser varchar(15),
  @result int OUTPUT
AS
SET NOCOUNT ON;

IF EXISTS (SELECT 1 FROM dbo.tbl_login WHERE userlogin = @user AND pass = @passuser)
  SET @result = 1;
ELSE
  SET @result = 0;

-- optional: return a resultset if the client prefers rs.Open
-- SELECT @result AS result;

Example ADODB client pattern (read output param after Execute):

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = "sp_login"
cmd.CommandType = adCmdStoredProc

cmd.Parameters.Append cmd.CreateParameter("user",    adVarChar, adParamInput,  15, TextBox1.Text)
cmd.Parameters.Append cmd.CreateParameter("passuser",adVarChar, adParamInput,  15, TextBox2.Text)
cmd.Parameters.Append cmd.CreateParameter("result",  adInteger, adParamOutput)

cmd.Execute  ' execute the proc
Dim loginResult As Integer
loginResult = cmd.Parameters("result").Value
If loginResult = 1 Then
  AddProduct.Show
Else
  MsgBox "Username or password is Incorrect", vbInformation, "AEB Gasul System"
End If

Troubleshooting notes:

  • Parameter names must match the proc; some providers accept names with or without a leading "@". Try both if not working.
  • Specify varchar sizes for input parameters; do not omit size for string types.
  • If the proc returns both a resultset and an output param, ensure any Recordset is closed before reading Parameters.
  • Prefer storing hashed passwords (security note) rather than plaintext password checks.

Recommended Answers

All 3 Replies

Member Avatar for Pgmer

Use output parameters..
like declare @result int
@result output.
In ADO.net method also u need to have one variable of same data type. and u have to use parameter.direcection method.

Member Avatar for ryan311

thank you for your reply :) but can you put the whole code on how to do that? :) i'm new in adodb stored procedure i always use sql command.

Member Avatar for Pgmer 
CREATE PROCEDURE [dbo].[pr_OutputPara,]            
                (            
                @test as varchar(10) 
                @out VARCHAR(10) OUTPUT          
                )            
AS  
BEGIN 
--Write  your business logic here
Select @out 
END

This is at the sp level..
In ado .net code

Dim output as string""
command.Parameters.Add("@Out", SqlDbType.VarChar, 10).Direction = ParameterDirection.Output
'After execution of query
 output = command.Parameters("@out").Value.ToString
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.