Error - Type Name is Invalid 1

[eveCalypso]

Can anyone tell me what I am doing wrong here?
I have a Command with two parameters set with values:

Parameter1:
.Name = "@testInput1"
.Type = adInteger
.Direction = adParamInput
.Value = 1
and
Parameter2:
.Name = "@testOutput2"
.Type = DataTypeEnum.adBigInt
.Direction = ParameterDirectionEnum.adParamOutput

My Command type & text is also correct before I Execute the command.

Any help would be appreciated.
Regards,
EvE

 

[cmmrfrds]

Is the error on parameter1 or parameter2?

See this thread for an example.
thread705-759242

 

[eveCalypso]

That is a very good question.
I don't know on which Param it is or if it is on a Param at all.
My error object values are:

ErrNo: -2147217872
ErrDesc: "Type name is invalid."
Source: "Microsoft OLE DB Provider for SQL Server"
Help Contect: 1000440
Help File: "C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\1033\VbLR6.chm"

I know that my "Select" etc of my stored procedure works - tested that in the query analyzer.
Here is my Stored Proc:

CREATE PROCEDURE myTest
@testInput1 integer,
@testOutput1 bigint OUTPUT
As
select @testOutput1 = sum(stockItemCurrBalance)
from [Stock Items], [Stock Categories]
where [Stock Items].StockItemID = @testInput1
and [Stock Items].StockItemCategory = [Stock Categories].categoryID
GO

Regards,
EvE

 

[cmmrfrds]

One thing to maybe try is initializing the output parameter with say a zero. Output parameters function as input/output parameters and maybe it is being initialized with a NULL.

 

[eveCalypso]

Hello,

I tried the initialization. The output parameter value was NULL in fact NULL, but it is zero now.
However I still get the exact same error.

Any other ideas?
Thank you in advance.
EvE

 

[cmmrfrds]

Parameter2:
.Name = "@testOutput2"

In the stored procedure the name is
@testOutput1 bigint OUTPUT

Did you try making these names the same?

Also, do you iterate through the error collection to pick up all the possible error lines or just the last line. ADO will report multiple errors if they occur. Sometimes there is an underlying error that is the real problem.

 

[eveCalypso]

Hello,
yes, I have changed the names too, but I still get the error.
I retrieved the error line after the cmd.Execute line - so immediately after Execute fails.

It must be something small - but I'll be damned if I can find it!
Thank you for your help so far.

Regards,
EvE

 

[cmmrfrds]

Put in an error handler that looks at the error collection and see if give more description.

On Error GoTo ErrHandler
cmd.Execute

ErrHandler:
Dim er As adodb.Error
Debug.Print " In Error Handler "; Err.Description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.Description
Debug.Print "err source = "; Err.Source
Next

 

[eveCalypso]

Ah - handy

I have done that and I only got this output:
err num = -2147217872
err desc = Type name is invalid.
err source = Microsoft OLE DB Provider for SQL Server

Eeeek - I am going to pull my hair out!

EvE

 

[cmmrfrds]

This is what jumps out at me
err desc = Type name is invalid.

I have not used bigint in ADO. Is it possible to change to int for a test?

 

[eveCalypso]

Hello
Sorry about the delay in answering.
I have changed the code call to
spParams(1).Type = DataTypeEnum.adInteger instead of
spParams(1).Type = DataTypeEnum.adBigInt
as you suggested - but kept the stored proc as bigint

Lo-and-behold, the error disappeared!
I do have the minor problem of not getting my required result back though (Value = 0), but at least that solved the initial problem.

So if adBigInt is a dropdown option for class ParameterDirectionEnum - and i get this error, is that a bug?

Can I expect to access the output value by saying:
myRecordset.Fields(1).Value ?
Let me see if making the stored procedure Int as well, gives me a value back.

THANK YOU SO MUCH!
Regards,
EvE

 

[eveCalypso]

Hang on,
cmd.Parameter("@testOutput1&quot.Value gives me the correct value!

EvE

 

[eveCalypso]

Oh dear, I am really confused now!

Command.Execute returns a recordset...
however, must I assume that is for a normal multi-row select in a stored procedure, whereas
if I specify a return (output) parameter - I must pick that up from the Parameters collection and it won't be present in the returning recordset?

So, taking that further, if I leave out the OUTPUT parameter and just select say ... SUM(myRows)..., will that be a result in the recordset?

I will go test it quickly.
Regards,
EvE

 

[cmmrfrds]

I suspect a bug in the bigint type on the ADO side.

A stored procedure can return both a recordset and output parameters.

A Select @parm = something in the SP will return an output parameter.
A Select * from table will return a recordset. Both can happen. It sounds like you really want the value from a recordset not the @parm. So, the value could be read from the recordset.

If you are trying to use both, there may be an order involved on accessing on the ADO side. I think the recordset first then the parm, but you would need to verify.

 

[eveCalypso]

Thank you so much.
I reckon I will always try and use the recordSet as I have my special function which returns the recordset.

You have been MOST helpful - thanks again! I have learnt from this exercise.

Regards,
EvE

 

[eveCalypso]

One last question!

Will any value in my recordset be helpful in determining whether the operation was successful?
As I am updating stock (which has no return values), I would like to test for success.

Any help would be appreciated!

Regards,
EvE

 

[cmmrfrds]

There is a Parameter on the Execute method that will return the records affected by an SQL action statement i.e.Update, Insert, Delete.

Here is the definition of the Execute Method on the Command Object.
Function Execute([RecordsAffected], [Parameters], [Options As Long = -1]) As Recordset

If you updated 6 records then RecordsAffected should be set to 6 on the return to ADO.

Good luck. I am off now to take my 11 year old son to a hockey tournament for the weekend. It was -30 here last night but it is supposed to warm up to zero for the weekend.

 

[eveCalypso]

Eeek!!!
Good luck!!!!
beanies , scarves etc in order
I will let you know on Monday how I got on.
Thanks again.
EvE