Data type of storedProc parameters. adVarWChar adn BSTR. 
Author Message
 Data type of storedProc parameters. adVarWChar adn BSTR.

When I call a stored procedure that takes 'nvarchar' arguments (Which I
guess is supposed to be like unicode BSTRs) using the Execute function like
this:
Command1.Execute(,Array("Helo", "world"))
it works fine.
but when I try to construct the paramteres using CreateParameter and I set
the type to be adBSTR, it trims the string and sends only the first letter.
When I look at the query in the SQL Profiler I see that it looks like this:
exec NameOfProc N'H', N'W' (instead of ***exec NameOfProc 'hello',
'world'***)

So I tried using other types instead of adBSTR but all of them gave me the
error: "Parameter object is improperly defined...".
BTW, I'm not using the Refresh method on the command object.

How can I make it work?

Actually the real function I'm trying to implenent is something like this:
public function ExecuteSP(Name as string, params as variant) as recordset

This function will eventually return a recordset given the approriate stored
procedure name and arguments.
Inside the implementation, I go over the Array using ForEach and adding each
variable in the array as a Parameter. I'm doing this instead of just calling
Execute with the array because I have change the lock type which means I
have to invoke using RS.Open(Command,,,,).
When I build the Parameter objects, I use VarType(vPrm) to get the type of
the param. Vartype actually returns an enum type which is different thatn
what CreateParameter wishes to receive, but since the two enumerations agree
on the values of the types (BSTR = String = 8) I think it will work.
Besides, I don't know any other way of doing this.

Wheeuuh.
I hope you read all the way through and you have the answer for me...

Thanks.

--
Assaf Lavie.



Sun, 13 Jul 2003 01:24:03 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Crystal Web Reports Server 7 string parameters arent being passed to SQL Server StoredProc

2. host data type in data environment command parameters

3. BSTR's, BSTR->string, and maps

4. BSTR as a parameter

5. BSTR type

6. BSTR type

7. Passing Array or a user defined data type as a parameter to a storedprocedure in Oracle

8. data types in parameter query

9. VB 5.0 and Inter-language Data Type Conversions (COBOL) and Parameter Passing

10. menu control array = what parameter data type?

11. Parameter Data Type

12. Using number data type parameters

 

 
Powered by phpBB® Forum Software