ADO Command Date parameter: idiocy 
Author Message
 ADO Command Date parameter: idiocy

Hi

I've been trying to execute a SQL Server stored procedure through ADO.
 Most of the procedures work fine, but as soon as there's a
smalldatetime parameter involved...

ACcess 97 VBA
MDAC 2.6
SQL Server 7.00.1063

In the end I've just chucked the whole thing and rewritten my SP to
accept varchar parameters which it then converts to smalldatetimes.
But WHY do I have to do this?

Whenever I try to execute the procedure through ADO, I get the error

Here's the code:

-- START CODE EXCERPT
Set mADOCmd = New ADODB.Command
mADOCmd.CommandType = adCmdStoredProc
mADOCmd.CommandTimeout = 0

mADOCmd.CommandText = "sp_GenerateCDDHDD_ADO"

Set l_param = mADOCmd.CreateParameter("RETURN_VALUE", adInteger,
adParamReturnValue, , 0)
mADOCmd.Parameters.Append l_param
mADOCmd.Parameters("RETURN_VALUE").Value = -1


adParamInput)
mADOCmd.Parameters.Append l_param

[Other parameter definitions omitted: irrelevant]

Set mADOCmd.ActiveConnection = mConn
Set mADOrs = New ADODB.Recordset
mADOrs.CursorType = adOpenStatic
mADOrs.CursorLocation = adUseClient
mdblExecStart = Now()
mbooExecuting = True
Set mADOrs = mADOCmd.Execute(, , adAsyncExecute)

-- END CODE

NOTES:
a) The Parameter objects are appended in exactly the same order as
defined in the CREATE PROC statement.  The only exception is the
RETURN_VALUE parameter.  I've tried moving the CreateParameter for
this one to the end rather than the start, because I saw someone
suggest it: no effect.
b) I've seen adDate and adDBTimeStamp (and adDBDate Size=8, and
adDBTimeStamp size=8) suggested as datatypes for a smalldatetime
parameter.  I've tried all these.
c) I've also tried setting the Parameter's Value to the sensible,
unequivocal format the SQL Server normally understands i.e. 19971003.
SQL Server might well like this, but VBA objects to it ("wrong
datatype for the operation" error).
d) The connection is WithEvents.  If I halt execution on the
WillExecute event and take a look at the Command object, I get this:

CommandText={ ? = call sp_GenerateCDDHDD_ADO(?, ?, ?, ?, ?) }
Parameters.Count = 6

Parameter(1).Value=3/10/1997
[... all other parameters correctly set]

doubt SQL Server would accept this: which is the problem.

If I try to set the parameter value to "19971003", VBA objects.
If I try to define the Parameter object with a Varchar type, and set
it
to "19971003", SQL Server objects with the same "...which was not
supplied"
error.

Frustratingly, I DID get a test stored procedure to work through ADO a
few days ago, with a smalldatetime parameter.  God knows why it
worked.  I think I'd dropped my razor in the bath three times that
morning rather than twice - must be that that did it.  And it was full
moon.

Conclusion: passing smalldatetime parameters to SPs using ADO is not
worth it, unless you're feeling bored and have a few days on your
hands.  Use varchars instead.

Seb



Sat, 07 May 2005 12:39:19 GMT  
 ADO Command Date parameter: idiocy
Seb,

Could you please post your SP declaration with date?

--
Val Mazur
Microsoft MVP


Quote:
> Hi

> I've been trying to execute a SQL Server stored procedure through ADO.
>  Most of the procedures work fine, but as soon as there's a
> smalldatetime parameter involved...

> ACcess 97 VBA
> MDAC 2.6
> SQL Server 7.00.1063

> In the end I've just chucked the whole thing and rewritten my SP to
> accept varchar parameters which it then converts to smalldatetimes.
> But WHY do I have to do this?

> Whenever I try to execute the procedure through ADO, I get the error

> Here's the code:

> -- START CODE EXCERPT
> Set mADOCmd = New ADODB.Command
> mADOCmd.CommandType = adCmdStoredProc
> mADOCmd.CommandTimeout = 0

> mADOCmd.CommandText = "sp_GenerateCDDHDD_ADO"

> Set l_param = mADOCmd.CreateParameter("RETURN_VALUE", adInteger,
> adParamReturnValue, , 0)
> mADOCmd.Parameters.Append l_param
> mADOCmd.Parameters("RETURN_VALUE").Value = -1


> adParamInput)
> mADOCmd.Parameters.Append l_param

> [Other parameter definitions omitted: irrelevant]

> Set mADOCmd.ActiveConnection = mConn
> Set mADOrs = New ADODB.Recordset
> mADOrs.CursorType = adOpenStatic
> mADOrs.CursorLocation = adUseClient
> mdblExecStart = Now()
> mbooExecuting = True
> Set mADOrs = mADOCmd.Execute(, , adAsyncExecute)

> -- END CODE

> NOTES:
> a) The Parameter objects are appended in exactly the same order as
> defined in the CREATE PROC statement.  The only exception is the
> RETURN_VALUE parameter.  I've tried moving the CreateParameter for
> this one to the end rather than the start, because I saw someone
> suggest it: no effect.
> b) I've seen adDate and adDBTimeStamp (and adDBDate Size=8, and
> adDBTimeStamp size=8) suggested as datatypes for a smalldatetime
> parameter.  I've tried all these.
> c) I've also tried setting the Parameter's Value to the sensible,
> unequivocal format the SQL Server normally understands i.e. 19971003.
> SQL Server might well like this, but VBA objects to it ("wrong
> datatype for the operation" error).
> d) The connection is WithEvents.  If I halt execution on the
> WillExecute event and take a look at the Command object, I get this:

> CommandText={ ? = call sp_GenerateCDDHDD_ADO(?, ?, ?, ?, ?) }
> Parameters.Count = 6

> Parameter(1).Value=3/10/1997
> [... all other parameters correctly set]
> BUT: if I look up the Command's Parameters in the Locals pane, the

> doubt SQL Server would accept this: which is the problem.

> If I try to set the parameter value to "19971003", VBA objects.
> If I try to define the Parameter object with a Varchar type, and set
> it
> to "19971003", SQL Server objects with the same "...which was not
> supplied"
> error.

> Frustratingly, I DID get a test stored procedure to work through ADO a
> few days ago, with a smalldatetime parameter.  God knows why it
> worked.  I think I'd dropped my razor in the bath three times that
> morning rather than twice - must be that that did it.  And it was full
> moon.

> Conclusion: passing smalldatetime parameters to SPs using ADO is not
> worth it, unless you're feeling bored and have a few days on your
> hands.  Use varchars instead.

> Seb



Sun, 08 May 2005 03:00:03 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Date type parameter in ADO command

2. Need help on ADO commands and parameters

3. multiple ADO command parameters

4. ADO command parameter syntax

5. ADO: Command with parameters

6. ADO: commands and parameters

7. ADO Command with parameters

8. 2nd Request - Passing parameters to ADO Data Environment command

9. DataEnvironment - Changing ADO Command Execute method parameters

10. How to create a parameter field in Command object (ADO/VB 6.0)

11. ADO: commands and parameters

12. MySQL and ADO Command Parameters

 

 
Powered by phpBB® Forum Software