Casey,

Here's how I do it.

A couple of observations.

First, the syntax in VFP is a little different than in VB.

Secondly, I found that when I set the fourth parameter to a 1 or 0 (one or zero) after setting the integer type to 3 I got problems. I found that by leaving it empty (the 4th position) that the 2nd position defaulted correctly and all was well.

ex: oCreate=oCommand.CreateParameter('@nActive',3,1,,nActive) <--works
ex: oCreate=oCommand.CreateParameter('@nActive',3,1,1,nActive) <--doesn't work
ex: oCreate=oCommand.CreateParameter('@nActive',3,1,0,nActive) <--doesn't work

Go figure... I suspect that there is either some typing issue or I just plain missed a small feature.

You have to create the adodb connection then create a parameter set that matches what the SQL stored procedure wants EXACTLY. I've enclosed a sample SQL stored procedure as well as the syntax is a little different.

I use the following to log people in and out for security issues.

Here's the VFP program:
*
** Call SQL Stored Procedure 'EFM_Logon' here
*
*
** SQL Parameter data type enumerators
*
** char = 129
** int = 3
** boolean - 11
** datetime = 135 (or 133?)
*
RELEASE oLogin
oLogin = CREATEOBJECT("adodb.connection")
IF TYPE("oLogin") = "O"
oLogin.ConnectionString = cConnectString
oLogin.ConnectionTimeout = 15
oLogin.OPEN

oCommand=CREATEOBJECT('adodb.command')
IF TYPE('oCommand') = 'O'
oCommand.ActiveConnection = oLogin
oCommand.CommandType = 4
oCommand.CommandText = 'dbo.EFM_Login'

oCreate=oCommand.CreateParameter('@cEmployeeID',129,1,10,cEmployeeID)
oCommand.PARAMETERS.APPEND(oCreate)
oCreate=oCommand.CreateParameter('@nActive',3,1,,nActive)
oCommand.PARAMETERS.APPEND(oCreate)
oCreate=oCommand.CreateParameter('@cAppVersion',129,1,9,cAppVersion)
oCommand.PARAMETERS.APPEND(oCreate)
oCreate=oCommand.CreateParameter('@cWorkstation',129,1,36,cWorkstation)
oCommand.PARAMETERS.APPEND(oCreate)
oCreate=oCommand.CreateParameter('@cLogAction',129,1,3,cLogAction)
oCommand.PARAMETERS.APPEND(oCreate)
oCreate=oCommand.CreateParameter('@cLogLocation',129,1,10,cLogLocation)
oCommand.PARAMETERS.APPEND(oCreate)
oCreate=oCommand.CreateParameter('@cNetID',129,1,40,cNetID)
oCommand.PARAMETERS.APPEND(oCreate)
oCreate=oCommand.CreateParameter('@nIdentity',3,2,4,nIdentity)
oCommand.PARAMETERS.APPEND(oCreate)

oCommand.Execute
nIdentity = oCommand.PARAMETERS(7).VALUE

RELEASE oCommand


Here's the SQL Stored procedure:

/*Procedure : EFM_Login

Parameters : Employee ID
Logon Date/Time
Application Version
Workstation GUID
Logon Location ("Desktop" or "Internet")
Net ID
Active (default = 1 = True)
Identity (return value placeholder)

Created : Doug Dodge
Created By : Doug Dodge

Revisions : 10
Last Revised : 6/24/99

Description : Adds Employee ID and other information to LOGONS table
which then lets us track users

*/
CREATE procedure EFM_Login
@cEmployeeID char(10),
@nActive int=1,
@cAppVersion char(9),
@cWorkStation char(36),
@cLogAction char(3),
@cLogLocation char(10),
@cNetID char(40),
@nIdentity int output

as

set nocount on
set quoted_identifier off
set ansi_padding on

begin transaction

begin
insert into LogonHistory
select EmployeeID,
0 as Active,
LogonDate,
AppVersion,
Workstation,
LogonId,
LogLocation,
NetID,
LogAction,
getdate()
from Logons where Logons.EmployeeID = @cEmployeeID and Logons.LogAction = 'OUT'

delete from Logons where Logons.EmployeeID = @cEmployeeID and Logons.LogAction = 'OUT'
end

begin
insert Logons
(EmployeeID,
Active,
LogonDate,
AppVersion,
Workstation,
LogAction,
LogLocation,
NetID)

values
(@cEmployeeID,
@nActive,
getdate(),
@cAppVersion,
@cWorkstation,
@cLogAction,
@cLogLocation,
@cNetID)

select @nIdentity = @@Identity
end

if @@error = 0
commit transaction
else
rollback transaction
return @@error





Best regards,