Re: Visual Basic/ADO/Stored Proc

[Tom Brennan]

Hi,

I have been having difficulty with the aforementioned subjects...here's what 
I did,
create table telephone_book
(
   tel_id integer,
   fname varchar(50) ascii,
   lname varchar(50) ascii,
   address varchar(255) ascii,
   telephone varchar(30) ascii,
   email_address varchar(255) ascii,
   primary key(tel_id)
)
//
insert into telephone_book values(0, 'Dummy First Name', 'Dummy Last Name', 
'Dummy Address', 'Dummy Telephone', 'Dummy Email')
//
create dbproc add_telephone_entry(in first_name varchar(50), in last_name 
varchar(50), in address_details varchar(255), in telephone_details 
varchar(30), in email_details varchar(255), out RV integer) as
   var new_tel_id integer;
begin
   select
       tel_id
   from xxxxx.telephone_book
   order by tel_id desc;

   if $rc = 0 then begin
       fetch into :new_tel_id;
       set new_tel_id = new_tel_id + 1;
       insert into xxxxx.telephone_book values
       (
           :new_tel_id,
           :first_name,
           :last_name,
           :address_details,
           :telephone_details,
           :email_details
       );
       if $rc = 0 then
           set RV = 0
       else
           set RV = 1;
   end;
end;
and here's the vb code to do this...
Private Sub cmdAddEntry_Click()
   Dim Cmd As New ADODB.Command
   Dim Param As ADODB.Parameter
   Dim RS As ADODB.Recordset
   On Error GoTo cmdAddEntry_Error
   Set Cmd.ActiveConnection = Conn
   Cmd.CommandType = adCmdStoredProc
   Cmd.CommandText = "add_telephone_entry"
   '
   Set Param = New ADODB.Parameter
   Param.Name = "FName"
   Param.Type = adVarChar
   Param.Value = Text1
   Param.Size = 50
   Param.Direction = adParamInput
   Cmd.Parameters.Append Param
   '
   Set Param = New ADODB.Parameter
   Param.Name = "LName"
   Param.Type = adVarChar
   Param.Value = Text2
   Param.Size = 50
   Param.Direction = adParamInput
   Cmd.Parameters.Append Param
   '
   Set Param = New ADODB.Parameter
   Param.Name = "Address"
   Param.Type = adVarChar
   Param.Value = Text3
   Param.Size = 255
   Param.Direction = adParamInput
   Cmd.Parameters.Append Param
   '
   Set Param = New ADODB.Parameter
   Param.Name = "Telephone"
   Param.Type = adVarChar
   Param.Value = Text4
   Param.Size = 30
   Param.Direction = adParamInput
   Cmd.Parameters.Append Param
   '
   Set Param = New ADODB.Parameter
   Param.Name = "Email"
   Param.Type = adVarChar
   Param.Value = Text5
   Param.Size = 255
   Param.Direction = adParamInput
   Cmd.Parameters.Append Param
   '
   Set Param = New ADODB.Parameter
   Param.Name = "Return_Value"
   Param.Type = adInteger
   Param.Direction = adParamReturnValue
   Cmd.Parameters.Append Param
   '
   Set RS = Cmd.Execute
   Debug.Print RS("Return_Value")
cmdAddEntry_Escape:
   Set Param = Nothing
   Set Cmd = Nothing
   Exit Sub
cmdAddEntry_Error:
   Resume cmdAddEntry_Escape
End Sub
The Return_Value corresponding to the RV variable in the stored proc is 
returning nothing...how can I get the output parameter to work?

Thanking you!
Tom.


/*
** #define p printf
** p("Better to reign in Hell, than to serve in Heaven");
** p("Email - [EMAIL PROTECTED]");
*/
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. 
http://join.msn.com/?page=features/virus

_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general