FoxPro Help and Support - Cursor Adaptor and Stored Procedures with Output parameters

Author

Cursor Adaptor and Stored Procedures with Output parameters

2005-05-10, 3:24 am

I want to call a SQLserver SP that has both input and output parameters with
a cursor adaptor, how do I write the selectCmd?

VFP8

thanks

LH

Thierry

2005-05-10, 9:24 am

hi,

this.in_param = "ABC"
this.out_param = space(10)
this.selectCmd = "{call your_sp(?this.in_param,?@this.out_param)}"

--
Thierry

"lh" <lh@nowhere.com> a écrit dans le message de news:
r0Vfe.11141$Le2.70108@nasal.pacific.net.au...
>I want to call a SQLserver SP that has both input and output parameters
>with a cursor adaptor, how do I write the selectCmd?
>
> VFP8
>
> thanks
>
> LH
>

2005-05-11, 3:24 am

Xref: TK2MSFTNGP08.phx.gbl microsoft.public.fox.programmer.exchange:196155 microsoft.public.fox.helpwanted:33597

Thanks but I still need and would appreciate more help.

Procedure starts like:
CREATE PROCEDURE ProcessSwipe @person INTEGER,
@swipe_unit INTEGER,
@ErrorNum INTEGER OUTPUT,
@ErrorMsg Varchar(100) OUTPUT AS
.....

It also returns a value, I don't know if this matters.

In VFP I do:
PUBLIC lnP
PUBLIC lnS
lnP = 2
lnS = 1

PUBLIC lnEN
PUBLIC lcET
lnEN = 0
lcET = SPACE(100)

In the cursor BeforeCursorFill I set:
cSelectCmd = "call processswipe(?lnP, ?lnS, ?@lnEN, ?@lcET)"

The error I get is 1435, Microsoft OLE DB Provider for SQL Server : No value
given for one or more required parameters.

"Thierry" <tper_NOSPAM@vfemail.net> wrote in message
news:u90dqUWVFHA.2960@TK2MSFTNGP15.phx.gbl...
> hi,
>
> this.in_param = "ABC"
> this.out_param = space(10)
> this.selectCmd = "{call your_sp(?this.in_param,?@this.out_param)}"
>
> --
> Thierry
>
>
> "lh" <lh@nowhere.com> a écrit dans le message de news:
> r0Vfe.11141$Le2.70108@nasal.pacific.net.au...
>
>

Thierry

2005-05-11, 3:24 am

Don't forget the {}

cSelectCmd = "{call processswipe(?lnP, ?lnS, ?@lnEN, ?@lcET)}"

--
Thierry

"lh" <lh@nowhere.com> a écrit dans le message de news:
Y3ige.11706$Le2.71598@nasal.pacific.net.au...
> Thanks but I still need and would appreciate more help.
>
> Procedure starts like:
> CREATE PROCEDURE ProcessSwipe @person INTEGER,
> @swipe_unit INTEGER,
> @ErrorNum INTEGER OUTPUT,
> @ErrorMsg Varchar(100) OUTPUT AS
> ....
>
> It also returns a value, I don't know if this matters.
>
> In VFP I do:
> PUBLIC lnP
> PUBLIC lnS
> lnP = 2
> lnS = 1
>
> PUBLIC lnEN
> PUBLIC lcET
> lnEN = 0
> lcET = SPACE(100)
>
> In the cursor BeforeCursorFill I set:
> cSelectCmd = "call processswipe(?lnP, ?lnS, ?@lnEN, ?@lcET)"
>
> The error I get is 1435, Microsoft OLE DB Provider for SQL Server : No
> value given for one or more required parameters.
>
>
> "Thierry" <tper_NOSPAM@vfemail.net> wrote in message
> news:u90dqUWVFHA.2960@TK2MSFTNGP15.phx.gbl...
>
>

Thierry

2005-05-11, 3:24 am

If you have a return value in your SP (other than OUTPUT parameters)

PUBLIC lnReturnValue
lnReturnValue=0

cSelectCmd = "{?@lnReturnValue=call processswipe(?lnP, ?lnS, ?@lnEN,
?@lcET)}"

--
Thierry

"Thierry" <tper_NOSPAM@vfemail.net> a écrit dans le message de news:
OXjQ3ifVFHA.544@TK2MSFTNGP15.phx.gbl...
> Don't forget the {}
>
> cSelectCmd = "{call processswipe(?lnP, ?lnS, ?@lnEN, ?@lcET)}"
>
> --
> Thierry
>
>
> "lh" <lh@nowhere.com> a écrit dans le message de news:
> Y3ige.11706$Le2.71598@nasal.pacific.net.au...
>
>

Christian Ehlscheid

2005-05-11, 8:25 pm

Hello,

Thierry's answer's are all correct, but they only work if you use the ODBC
driver
from the error message you posted ... i could see that you were using the
OLE DB driver ..

if you can - use ODBC, works stable for years now, is faster, and easier to
use (better integrated into FoxPro) than OLE DB ..
if you stick with OLE DB.. you have to use ADO.Command object's Parameters
Collection to pass parameters ..

Regards
Christian

"lh" <lh@nowhere.com> schrieb im Newsbeitrag
news:Y3ige.11706$Le2.71598@nasal.pacific.net.au...
> Thanks but I still need and would appreciate more help.
>
> Procedure starts like:
> CREATE PROCEDURE ProcessSwipe @person INTEGER,
> @swipe_unit INTEGER,
> @ErrorNum INTEGER OUTPUT,
> @ErrorMsg Varchar(100) OUTPUT AS
> ....
>
> It also returns a value, I don't know if this matters.
>
> In VFP I do:
> PUBLIC lnP
> PUBLIC lnS
> lnP = 2
> lnS = 1
>
> PUBLIC lnEN
> PUBLIC lcET
> lnEN = 0
> lcET = SPACE(100)
>
> In the cursor BeforeCursorFill I set:
> cSelectCmd = "call processswipe(?lnP, ?lnS, ?@lnEN, ?@lcET)"
>
> The error I get is 1435, Microsoft OLE DB Provider for SQL Server : No
value

> given for one or more required parameters.
>
>
> "Thierry" <tper_NOSPAM@vfemail.net> wrote in message
> news:u90dqUWVFHA.2960@TK2MSFTNGP15.phx.gbl...
>
>

Sponsored Links