3

I have a .NET assembly that is being consumed by a classic ASP page. I've created a method that returns a ADODB recordset. In my ADODB command object I'm supplying parameters using the following format to a adCmdStoredProc CommandType property...

With ADODBCmd 
.ActiveConnection = ADODBConn
.Prepared = True
.CommandType = CommandTypeEnum.adCmdStoredProc
.NamedParameters = True
.CommandText = Sql_GetMyBook   
.Parameters.Append(.CreateParameter("@book", DataTypeEnum.adChar, ParameterDirectionEnum.adParamInput, 50, MyBook))
End With

I get a casting error ...

System.Exception was unhandled
Message=System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'ADODB.InternalParameter'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

at line:

.Parameters.Append(.CreateParameter("@book", DataTypeEnum.adChar, ParameterDirectionEnum.adParamInput, 50, MyBook))

Any ideas?

Stored Proc:

ALTER PROCEDURE [dbo].[GetMybook]
    -- Add the parameters for the stored procedure here
    @book char(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT BookTitle, Author, PulishedDate
    FROM        Library
    WHERE       BookTitle=@book
John Saunders
  • 160,644
  • 26
  • 247
  • 397
ihillVT
  • 121
  • 2
  • 9

2 Answers2

7

There is a difference (unintended or not) between the return value of the ".CreateParameter" method in Microsoft ActiveX Data objects Library

2.7 - Returns "ADODB.InternalParameter" (which is expected by the ADODB.Command object)

2.8 - Returns "System.__ComObject" (which the ADODB.Command can't handle or doesn't know what to do with)

For my purposes I had to change my reference from 2.8 to 2.7 library in order to append parameters created to the command object.

Thanks to Chris Behrens for helping me narrow down my search for a solution.

ihillVT
  • 121
  • 2
  • 9
0

I think it has to do with the value of "MyBook". We should expect it to be a single character, from the data type, but the error message seems to be indicating that it's a full-blown COM object. Maybe it should be something like "MyBook.Id"?

Chris B. Behrens
  • 6,255
  • 8
  • 45
  • 71
  • MyBook is actually a string, probably should have been more clear on that. – ihillVT Jan 27 '11 at 16:15
  • Try changing the type enum to adVarChar instead, see if that makes any difference. – Chris B. Behrens Jan 27 '11 at 16:18
  • Can you post the proc contents? – Chris B. Behrens Jan 27 '11 at 16:35
  • Another idea...I'm wracking my brain to remember the classic ASP string cast function...I think it was cstr()? Try running MyBook through that like so: `.Parameters.Append(.CreateParameter("@book", DataTypeEnum.adChar, ParameterDirectionEnum.adParamInput, 50, CStr(MyBook)))` – Chris B. Behrens Jan 27 '11 at 16:38
  • appreciate the effort trying to recall classic. CStr created the same error. – ihillVT Jan 27 '11 at 16:44
  • Break the code into two lines, one the cast, and one the param assignment, and see which line generates the error. I suspect it will be the cast. The essence of the problem here is that for some reason, the classic asp engine is interpreting MyBook as a COM object rather than a simple variant, which it should be able to cast to a System.String with no problem. The next thing I would try is hard-coding the value of MyBook to a value you know should work, and see what happens. – Chris B. Behrens Jan 27 '11 at 16:47
  • Broke the code out it actually error on the param assignment and not the casting for the MyBook value. – ihillVT Jan 27 '11 at 16:59
  • Okay...try the second suggestion, hardcoding MyBook to something like "1", or "A", or whatever. – Chris B. Behrens Jan 27 '11 at 17:03
  • Tried hardcoding no luck. I narrowed it down to the ".CreateParameter" method returning "System.__ComObject" which it doesn't like when appending the parameter. It's looking for "ADODB.InternalParameter". I'm still trying to figure out how to change that. – ihillVT Jan 27 '11 at 17:08
  • I don't know man...it all looks right to me. The last thing I can think of is to change the proc to accept a type of varchar instead of char. I can't see why that would make a difference, but there you go. – Chris B. Behrens Jan 27 '11 at 17:34
  • Thanks for checking into. I'll keep researching and post the answer once I find it. – ihillVT Jan 27 '11 at 17:57