How to return value of stored-procedure to VB
Scott Auer
Private Sub cmdSoredProcOutput_Click()
Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRec As ADODB.Recordset
objConn.CursorLocation = adUseClient
objConn.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=False;User ID=sa;" & _
"Initial Catalog=Northwind;Data Source=(local)"
objConn.Open
With objCmd
.CommandText = "ups_GetCustomer"
.CommandType = adCmdStoredProc
.ActiveConnection = objConn
objCmd.Parameters.Append .CreateParameter("CustomerID", adVarChar,
adParamInput, 5, "ALFKI")
objCmd.Parameters.Append .CreateParameter("CompanyName", adVarChar,
adParamOutput, 40)
.Execute
End With
Text1.Text = objCmd.Parameters("CompanyName").Value
End Sub
and here is the stored procedure:
CREATE PROCEDURE ups_GetCustomer
@CustomerID varchar(5),
@CompanyName varchar(40) OUTPUT
AS
SELECT @CompanyName = Companyname
FROM Customers
WHERE CustomerID = @CustomerID
The program creates to parameters for the stored procedure based on the
order the stored procedure is expecting them. First the CustomerID parameter
is created that passes the stored procedure the value of "ALFKI". Second the
output field is passed with no return value. The storedprocedure with give
it this value. You will notice the "OUTPUT" on the CompanyName line of the
storedProcedure. This is how you tell SQL what it will be returning. Hope
this helps. SA
Ron HO <vron...@sinaman.com> wrote in message
news:OBU0HMYh$GA....@cppssbbsa02.microsoft.com...
> Hi all,
> I'd like to ask how to return a value from stored-procedure to VB6.
> for example.
> create procedure [sp_return_user_name]
> @user_id char(8)
> as
> select user_name from my_user_table where user_id = @user_id
>
> I'd like to pass back the user_name to VB6. How to handle, pls help
> Regards,
> Ron
>
>
Ron HO
Ron HO
Regards,
Ron
sloan
first, save the following code in a text file called "employee_delete.sql"
--here's and sql script for the pubs database
++++++++++++start sql code
/*
****************************************************************************
**********
*** This script handles all 'deletes' to the 'employee' table
****************************************************************************
**********
03/07/2000 --
****************************************************************************
**********
*/
Use pubs
GO
if exists (select * from sysobjects
where id = object_id('dbo.usp_10_employee_delete') and sysstat & 0xf = 4)
drop procedure dbo.usp_10_employee_delete
GO
CREATE PROCEDURE
usp_10_employee_delete
@strEmp_id char(9),
@int_return_value int output,
@str_return_message varchar(1000) output
)
AS
if (select emp_id from employee where emp_id = @strEmp_id) is NULL
begin
select @str_return_message = "this id does not exist"
select @int_return_value = 0
return
end
declare @int_error int
delete from
employee
where
emp_id
=
@strEmp_id
select @int_error = @@error
if @int_error = 0 -- error was not generated, insert sucessful
begin
select @int_return_value = -1 --true
select @str_return_message = "successfully deleted item with id = " +
convert(varchar(255), @strEmp_id)
return
end
else
--an error was generated
--print "@int_error : " + convert(varchar(20), @int_error)
begin
if @int_error = 245
begin
select @str_return_message = "Datatype Mismatch with parameters"
end
if @int_error = 544
begin
select @str_return_message = "Cannot insert explicit value for identity
column in table 'Suppliers' when IDENTITY_INSERT is set to OFF."
end
if @int_error = 547
begin
select @str_return_message = "Foreign Key Violation"
end
if @int_error = 2627
begin
select @str_return_message = "Primary Key Violation"
end
select @int_return_value = @int_error
return
end
GO
+++++++++++++++ end sql code
NEXT
open the above .sql file in query analyser (or isql_w), run the script to
create the stored procedure.
NEXT
create a new vb project, add reference "Microsoft ActiveX Data Objects 2.x
Library" (where x is either 1 or 5 for ADO 2.1 or ADO 2.5) (2.0 might work,
but i don't know)
add a command button called "cmdADOStorProc"
here is the code
++++++ start vb code
Private Sub cmdADOStorProc_Click()
On Error GoTo errorhandler:
Dim msg As String
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Set oConn = New ADODB.Connection
'connect to your server, replacing MyServer with the name of your server,
and entering userid and/or password if necessary
oConn.Open ("Provider=sqloledb;" & "Data Source=MyServer;Initial
Catalog=pubs;User Id=sa;Password=; ")
'NOTE for above line, you have to change your MyServer name to the server
you're trying to connect to.
Set oCmd = New ADODB.Command
Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "usp_10_employee_delete" 'Enter name of stored procedure
being called
oCmd.CommandType = adCmdStoredProc
'Define all parameters to be passed in that exist in stored procedure, in
order they are
'declared in stored procedure
oCmd.Parameters.Append oCmd.CreateParameter("strPrimaryKey", adVarChar,
adParamInput, 9)
'note, i call it strPrimaryKey, but you can call it whatever you want. "9"
is the length of the varchar (or char) in the database.
'my two standard return values
oCmd.Parameters.Append oCmd.CreateParameter("intReturnValue", adInteger,
adParamReturnValue)
'because adVarChar is a variable size, you append the criteria with 1000 (or
whatever length you need)
oCmd.Parameters.Append oCmd.CreateParameter("strReturnValue", adVarChar,
adParamReturnValue, 1000)
'Give parameters values
oCmd("strPrimaryKey") = "PMA42628M" 'this is where you set your value you
want to delete
'NOTE ON ABOVE LINE, you will delete the person above, change value if you
don't want to delete that person
'execute command
oCmd.Execute
'now the return values
msg = oCmd.Parameters("intReturnValue")
msg = msg & vbCrLf & oCmd.Parameters("strReturnValue")
MsgBox msg
Exit Sub
errorhandler:
Debug.Print Err.Number & " " & Err.Description
Resume Next
++++++ end vb code
End Sub
run the program.
it should work. that took me alot of effort to figure out, so if you use
it, how about a thanks?
this ougtta be a KB article.
..
sloan
sl...@ipass.net
One more additional note, if you have in your example code from another
source
the following:
oCmd.Parameters.Refresh
this will screw you up
Ron HO wrote in message ...
Ron HO
thanks for your detail explain.
regards,
ron
sloan <sl...@ipass.net> wrote in message news:umk5Y5Hi$GA.260@cppssbbsa05...