Odd problem with ADO CreateParameter

Imar · January 5th, 2005, 09:28 AM

Hi there,

I am having an odd problem with the ADO Command CreateParameter method. I get an error at an unexpected location. I am using the following code:

Code:

Dim myCommand As ADODB.Command
Set myCommand = New ADODB.Command
myCommand.ActiveConnection = myConnection
myCommand.CommandType = adCmdStoredProc
myCommand.CommandText = "sprocMySproc"
myCommand.Parameters.Append (myCommand.CreateParameter
    ("@mailingSequenceId", adInteger, adParamInput, , p_MailingSequenceId))
myCommand.Parameters.Append (myCommand.CreateParameter
    ("@eAlertId", adVarWChar, adParamInput, 100, p_eAlertId))

AFAIK, this is the most basic code for ADO Command parameters.

However, as soon as I reach the first call to .Append, my code crashes with a 424 - Object Expected error. When I then inspect the object myCommand, it already has all parameters from my stored procedure defined! This shouldn't be possible, right? It's like myCommand.Parameters.Refresh has been called implicitly... Is this how it's supposed to work?

I can work around this with this code:

Code:

Dim myCommand As ADODB.Command
Set myCommand = New ADODB.Command
myCommand.ActiveConnection = myConnection
myCommand.CommandType = adCmdStoredProc
myCommand.CommandText = "sprocMySproc
myCommand.Parameters(1).Value = p_MailingSequenceId
myCommand.Parameters(2).Value = p_eAlertId
myCommand.Parameters(3).Value = p_IntervalBetweenMailings

which would be the code I'd use after I had called Parameters.Refresh() explicitly.

Am I missing something? Anyone have ideas about this?

I am using this code inside a VB 6 ActiveX DLL with ADO 2.7 (although I tried 2.5 as well)

Thanks in advance for any help,

Imar

pgtips · January 13th, 2005, 06:02 AM

Hi Imar,

I've never heard of a command object automatically populating all the params.

But the 424 - Object Expected error is just the result of a basic syntax error. The Append method is a Sub so you shouldn't have () around the parameter (so easy to do I know...). VB is just getting a bit confused and giving you the "wrong" error message.

rgds
Phil

Imar · January 13th, 2005, 06:35 AM

Hi Phil,

Yeah, I agree with what you said. And I think you're right about the parentheses (doing too much work in .NET these days ;) )

But consider this:

Code:

Dim myCommand As ADODB.Command
Set myCommand = New ADODB.Command
myCommand.ActiveConnection = oConnectionRead
myCommand.CommandType = adCmdStoredProc
myCommand.CommandText = "sprocGetUsersForMailingByMailingId"

If I set a breakpoint on the line after myCommand.CommandText and then inspect the myCommand object, the parameters are all there!!!

That's strange, isn't it? I just set the name of the stored procedure and that's it..... Or am I being confused by the debugger? Does the debugger cause an implicit call to the Refresh method?

AFAICS, at the breakpoint, the myCommand object shouldn't have the parameters collection filled.....

Cheers,

Imar

pgtips · January 13th, 2005, 06:51 AM

Hi Imar,

I agree with you, it's strange! The params shouldn't be there. The debugger shouldn't refresh the params.

I can't replicate your problem - you've got gremlins :D

rgds
Phil

pgtips · January 13th, 2005, 07:04 AM

Hey Imar, after a bit of fiddling around I've managed to replicate your problem.

Looks like if you have:
myCommand.CommandType = adCmdStoredProc
myCommand.CommandText = "sprocGetUsersForMailingByMailingId"

then you get the params refreshed automatically(!)

but if you swap the order of those 2 lines to:
myCommand.CommandText = "sprocGetUsersForMailingByMailingId"
myCommand.CommandType = adCmdStoredProc

then you don't get the params.

I've never come across this before - maybe I've just never used the lines in that order - seems v strange though...

I'll have to have a dig around later when I've got more time.

rgds
Phil

Imar · January 13th, 2005, 07:24 AM

OK, great. I was just working on a simple code example to reproduce the problem....

If I swap those two lines, I don't get the params.... Very strange indeed

Imar

pgtips · January 13th, 2005, 07:31 AM

I couldn't leave it alone :D

After some more experimenting I've discovered that it is the debugger refreshing the params automatically, but only if you have the lines in the order of "CommandType" before "CommandText"!

All I could find in MSDN was this comment "If you have not defined your own Parameter objects and you access the Parameters collection before calling the Refresh method, ADO will automatically call the method and populate the collection for you." Which seems to suggest that if you have a debug window open viewing the locals, and that window is expanded so that the Parameters property is visible then you get the params refreshed.

If you don't have the debug window looking at the Parameters property then you don't get them refreshed.

So, if you hadn't made the original syntax error, you wouldn't have had the locals window open, and so you wouldn't have had the params auto-refresh.

A very strange combination of circumstances. I'm curious though, when you changed to your workaround:
Dim myCommand As ADODB.Command
Set myCommand = New ADODB.Command
myCommand.ActiveConnection = myConnection
myCommand.CommandType = adCmdStoredProc
myCommand.CommandText = "sprocMySproc
myCommand.Parameters(1).Value = p_MailingSequenceId
myCommand.Parameters(2).Value = p_eAlertId
myCommand.Parameters(3).Value = p_IntervalBetweenMailings

did this work in compiled code? I suppose it may have given that you are accessing the Parameters collection without having defined your own Parameter objects, so the MSDN snippet implies that you'll get a free Refresh...just curious really.

rgds
Phil

Imar · January 13th, 2005, 09:59 AM

Intriguing, isn't it.... ;)

The code with the work around works fine; in fact, it's what I have been using so far. This seems to indicate that the MSDN article is right about implicitly refreshing the params when you try to access the Parameters collection. Additionally, hidden calls in the debugger made things even more confusing.

I think if I hadn't made the mistake of the parentheses, I never would have ran into this. As I said earlier, I have never seen this before, and always created my param objects manually to avoid the overhead of calling refresh.

We learn something new every day.....

Thanks for taking the time to look into this...

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.