Re: pass parameter to FE

From: Val Mazur (group51a_at_hotmail.com)
Date: 03/27/04

  • Next message: Val Mazur: "Re: URGENT : PB Delete Row in Northwind.mdb" 
    Date: Fri, 26 Mar 2004 23:41:50 -0500

    Hi,

    How do you return your value from SP? Is it output parameter or return
    value. In both cases you need to declare parameter and add it to the
    command's Parameters collection. Your code should look like

    for the return value (also use Set to point to the opened connection)

    Public Function InsertPhoneRecored () As Long
        Set cmdNew = New ADODB.Command
        With cmdNew
            Set .ActiveConnection = CurrentProject.Connection
            .CommandText = "procPhoneInsert"
            .CommandType = adCmdStoredProc
            .Parameters.Append .CreateParameter("@NewPhoneID", adInteger,
    adParamReturnValue)
            .Execute

            InsertPhoneRecord = .Parameters("@NewPhoneID", ).Value

        End With

    for output parameter, you need to change adParamReturnValue to adParamOutput

    For future postings, use microsoft.public.vb.database.ado newsgroup. It
    covers questions related to VB+ADO 

    -- 
Val Mazur
Microsoft MVP
"smk2" <anonymous@discussions.microsoft.com> wrote in message 
news:2B1D67B7-9D71-4942-875F-2FE675410A08@microsoft.com...
>I have SQL 2k BE with Access 2002 FE and need to pass an output value from 
>a stored procedure back to Access.
> Here is my current syntax:
>
> Public Function InsertPhoneRecored () As Long
>    Set cmdNew = New ADODB.Command
>    With cmdNew
>        .ActiveConnection = CurrentProject.Connection
>        .CommandText = "procPhoneInsert"
>        .CommandType = adCmdStoredProc
>        .Execute
>    End With
>
>    InsertPhoneRecord = NewPhoneID
>
> End Function
>
> Where "NewPhoneID" is the parameter being passed from the stored 
> procedure. How do I capture and use that value?
> Thanks so much!
> SMK
>
  • Next message: Val Mazur: "Re: URGENT : PB Delete Row in Northwind.mdb"