How to return value of stored-procedure to VB

How to return value of stored-procedure to VB

Post by Scott Aue » Sat, 04 Mar 2000 04:00:00



Here is the VB code:

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


AS

 FROM Customers

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


> 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]

> as

> I'd like to pass back the user_name to VB6. How to handle, pls help
> Regards,
> Ron

 
 
 

How to return value of stored-procedure to VB

Post by Ron H » Sun, 05 Mar 2000 04:00:00


Hi all,
    I'd like to ask how to return a value from stored-procedure to VB6.
for example.

as

I'd like to pass back the user_name to VB6. How to handle, pls help
Regards,
Ron

 
 
 

How to return value of stored-procedure to VB

Post by Ron H » Tue, 07 Mar 2000 04:00:00


Thanks Scott
Regards,
Ron
 
 
 

How to return value of stored-procedure to VB

Post by sloa » Wed, 08 Mar 2000 04:00:00


Here's an example based on the pubs database.

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




    )
AS


begin


  return
end


delete from
  employee
   where
    emp_id
     =



begin



  return
end
else
--an error was generated

begin

   begin

   end

   begin

column in table 'Suppliers' when IDENTITY_INSERT is set to OFF."
   end

   begin

   end

   begin

   end

  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

One more additional note, if you have in your example code from another
source
the following:
oCmd.Parameters.Refresh

this will*you up


>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]

>as

>I'd like to pass back the user_name to VB6. How to handle, pls help
>Regards,
>Ron

 
 
 

How to return value of stored-procedure to VB

Post by Ron H » Thu, 09 Mar 2000 04:00:00


Sloan,
  thanks for your detail explain.
regards,
ron

> Here's an example based on the pubs database.

> 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

> /*

****************************************************************************
Quote:> **********
> *** This script handles all 'deletes' to the 'employee' table

****************************************************************************
Quote:> **********
> 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




>     )
> AS


> begin


>   return
> end


> delete from
>   employee
>    where
>     emp_id
>      =



> begin



>   return
> end
> else
> --an error was generated

> begin

>    begin

>    end

>    begin

identity
> column in table 'Suppliers' when IDENTITY_INSERT is set to OFF."
>    end

>    begin

>    end

>    begin

>    end

>   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

> One more additional note, if you have in your example code from another
> source
> the following:
> oCmd.Parameters.Refresh

> this will*you up


> >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]

> >as

> >I'd like to pass back the user_name to VB6. How to handle, pls help
> >Regards,
> >Ron