Ask a Question

CreateADOStoredProc and a adVarChar Parameter

CDurkin
Contributor

CreateADOStoredProc and a adVarChar Parameter

I am trying to call a Oracle Stored proc which returns a VarChar.  But it seems to be having trouble recognising the  type "adVarChar".



I get the following error:

     "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"



My code is something like below.   If I change it to "ftFixedChar" it seems to work.



How do I get it working for Oracles' VARCHAR2.



Thanks

Chris





function CreatingStoredProcedure()

{

var SProc;

  SProc = ADO.CreateADOStoredProc();

  SProc.ConnectionString = "....

  SProc.ProcedureName = "....

    // Adding a return parameter

  SProc.Parameters.AddParameter();

  SProc.Parameters.Items(0).name = "RETURN_VALUE";

  SProc.Parameters.Items(0).DataType = adVarChar;     <<< Complains about this line

  SProc.Parameters.Items(0).Size = 255; 

  SProc.Parameters.Items(0).Direction = adParamReturnValue;

  SProc.Parameters.Items(0).Value = null;
6 REPLIES 6
radov
Staff

RE: CreateADOStoredProc and a adVarChar Parameter

Hi Chris,



How do I get it working for Oracles' VARCHAR2.




According to the information from this page, you should specify the adVariant constant as the data type if your stored procedure returns a VARCHAR2 value:





SProc.Parameters.Items(0).DataType = adVariant;





It seems that the adVarChar data type actually corresponds to the VARCHAR Oracle data type, not to the VARCHAR2 type. Please try using adVariant. Does this help?
Best regards,
Vladimir

I am not a member of the SmartBear Support Team, however I do attempt to help members of this community with my broad knowledge of SmartBear tools. My responses do not reflect an official SmartBear Support answer.
artur0409
Occasional Contributor

RE: CreateADOStoredProc and a adVarChar Parameter

I have similar problem with SQL Server 2005



I'm trying to execute following command


exec ZipStoreConversions @ProductId=N'GMVE 000057',@ParameterCollectionID=N'FH',@ConvertToProduct=N'VIT 000100',@ConvertFromProduct=N''



parameters definiod from stored procedure


@ProductID varchar(11),


@ParameterCollectionID varchar(50),


@ConvertToProduct varchar(11),


@ConvertFromProduct varchar(11)



I use following code  - it always give me error in line 




param1["DataType"] = DB["adVarChar"];


"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"





Do I do something wrong?


Cmd["CommandText"]= "dbo.ZipStoreConversions"


Cmd["CommandType"] = DB["adCmdStoredProc"];



var param1 = Cmd["Parameters"]["AddParameter"]();


param1["Name"] = "ProductId";


param1["DataType"] = DB["adVarChar"];


param1["Direction"] = DB["adParamInput"];


param1["Value"] = "GMVE 000057";


 


var param2 = Cmd["Parameters"]["AddParameter"]();


param2["Name"] = "ParameterCollectionID";


param2["DataType"] = DB["adVarChar"];


param2["Direction"] = DB["adParamInput"];


param2["Value"] = "FH";


var param3 = Cmd["Parameters"]["AddParameter"]();


param3["Name"] = "ConvertToProduct";


param3["DataType"] = DB["adVarChar"];


param3["Direction"] = DB["adParamInput"];


param3["Value"] = "VIT 000100";


 


var param4 = Cmd["Parameters"]["AddParameter"]();


param4["Name"] = "ConvertFromProduct";


param4["DataType"] = DB["adVarChar"];


param4["Direction"] = DB["adParamInput"];


param4["Value"] = "";


 


RecSet = Cmd["Execute"]();


AlexeyK
Staff

RE: CreateADOStoredProc and a adVarChar Parameter

Arthur,


...

I use following code  - it always give me error in line

param1["DataType"] = DB["adVarChar"];

...


I'm not a big specialist in this, but perhaps the property name is Type, not DataType. At least, MSDN and some sites I found in Google use Type.


As far as I can see, you are using C++Script or C#Script, and DataType is a property. However, the JScript engine (that is used for C++/C# scripting) treats it as a parameter/argument that is passed in brackets. That is why, the engine gives the "Arguments are of the wrong type..." error.

I’m not a member of the SmartBear Support Team, I’m just helping users in this community.
Any views or opinions expressed do not necessarily represent those of SmartBear Software.

-----
artur0409
Occasional Contributor

RE: CreateADOStoredProc and a adVarChar Parameter

I got answer from SmartBear on my previous case to use such syntax  - so DataType seems to be correct  -  quote:



Hello,


Here is code that should work for you


[C#Script]


  // ...   


  Cmd["CommandText"] ="dbo.Bin_DeleteItems";


  Cmd["CommandType"] = DB["adCmdStoredProc"];


  var param = Cmd["Parameters"]["AddParameter"]();


  param["Name"] = "XMLListOfIds";


  param["DataType"] = DB["adPersistXML"];


  param["Direction"] = DB["adParamInput"];


  param["Value"] = XmlString;


  RecSet = Cmd["Execute"]();


  // ...


 


Best regards,


Jay

AlexanderM
Staff

RE: CreateADOStoredProc and a adVarChar Parameter

Hi Artur,




I have replied to your support case with this question, and I will post a short summary here so that others have this information as well:




The problem with the code is that you are using ADO constants for the types. However, the CreateADOStoredProc method does not return an instance of the "ADODB.Command" object - it returns a VCL wrapper object instead. That is why the "CreateADOStoredProc Method" help topic gives a reference to VCL documentation:







The IAQAADOStoredProc object is an analogue of a Borland VCL TADOStoredProc object. Methods and properties are identical.


For detailed information on the underlying TADOStoredProc object, see VCL documentation on ADODB classes.







The help topic gives this link to the VCL documentation:


http://docwiki.embarcadero.com/VCL/XE/en/ADODB






When using CreateADOStoredProc, you need to use the data types defined in VCL (Data.Win.ADODB.TDataType):


http://docwiki.embarcadero.com/Libraries/en/Data.Win.ADODB.TDataType




So, in your code, you should use the ftString type for your VarChar parameters - it works fine for me:




  Cmd["Parameters"]["Items"](0)["DataType"] = ftString;









SUMMARY:




- When using ADO.CreateADOStoredProc, use the VCL data type constants:


http://docwiki.embarcadero.com/Libraries/en/Data.Win.ADODB.TDataType




- When creating an instance of the "ADODB.Command" object to invoke a stored procedure, use the ADO data type constants:


http://www.w3schools.com/ado/ado_datatypes.asp
-----

Alexander
Customer Care Manager
artur0409
Occasional Contributor

RE: CreateADOStoredProc and a adVarChar Parameter

Great answer!!

 My problem is solved now



Thanks for your time
cancel
Showing results for 
Search instead for 
Did you mean: