Re: Visual Basic/ADO/Stored Proc

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