evoking a storeprocedure with a datetime

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pedro - 26 Feb 2007 16:14 GMT

Hello,

I am not a VB6 programmer but I need to execute some changes to an existing
program.
My problem is evoking a storeprocedure with receives a datetime variable, I
send a Date but I always receive a Type Mismatch error

My code os the following

Dim Cmd As New Command
Dim pData As New Parameter
With pData
.Direction = adParamInput
.Type = adDate
.Value = Date
End With
Cmd.Parameters.Append pData

Cmd.ActiveConnection = db_H.Connection
Cmd.CommandText = "_GetEclipses "
Cmd.CommandType = adCmdStoredProc
Set Tb = Cmd.Execute

I am doing something wrong?
Thanks in advance

Pedro

Reply to this Message

Mark J. McGinty - 26 Feb 2007 20:00 GMT

> Hello,
>
[quoted text clipped - 23 lines]
> I am doing something wrong?
> Thanks in advance

Two things:

1. Use the Command.CreateParameter method to create new parameters.
2. The type should be adDBTimeStamp (yes the mnemonics are very confusing in
this case.)

And a couple of other little things, that may not actually break your code,
but are still worth mentioning... It's difficult to say without knowing what
db_H is, but Cmd.ActiveConnection is an object, and so should be assigned
using the Set keyword, e.g.,

Set Cmd.ActiveConnection = MyConnectionObject

And also, using the Set Object As New Type syntax with ADO objects is not a
well-regarded practice at all, it tends to cause memory leaks. I don't know
of a single experienced VB programmer that advocates using it; that it's
best avoided is a widely held viewpoint.

-Mark

> Pedro

Reply to this Message

MikeD - 28 Feb 2007 14:50 GMT

> And also, using the Set Object As New Type syntax with ADO objects is not
> a well-regarded practice at all, it tends to cause memory leaks. I don't
> know of a single experienced VB programmer that advocates using it; that
> it's best avoided is a widely held viewpoint.

I think you mean DIM Object As New Type.....and it's not just ADO that this
syntax is frowned upon.

Signature

Mike
Microsoft MVP Visual Basic

Reply to this Message

VB Forum / Database Access / February 2007

evoking a storeprocedure with a datetime