> Use pubs
> 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
> CREATE PROCEDURE
> delete from
> --an error was generated
> column in table 'Suppliers' when IDENTITY_INSERT is set to OFF."
> +++++++++++++++ end sql code
> open the above .sql file in query analyser (or isql_w), run the script to
> create the stored procedure.
> 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
> 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
> 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,
> 'because adVarChar is a variable size, you append the criteria with 1000
> 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
> 'now the return values
> msg = oCmd.Parameters("intReturnValue")
> msg = msg & vbCrLf & oCmd.Parameters("strReturnValue")
> MsgBox msg
> Exit Sub
> 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.
> One more additional note, if you have in your example code from another
> the following:
> 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]
> >I'd like to pass back the user_name to VB6. How to handle, pls help