[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

Convert Variant to Char

I have created my own vb function called 'RunSPNoRecords.'  It resides in an activeX DLL.  The function's purpose is to execute a stored procedure and return no recordset.  It's return value is the return value of the stored procedure. The function is being passed a variant array which contains the values for the stored procedure's parameters.  I'm using the cmd.Refresh method to retrieve the name, datatype, direction, and size of each stored procedure parameter.  This information is used to create a new command object. (using CreateParameter)  The new command object is then executed and the return value is retrieved.  

Everything works fine except that if the stored procedure contains a parameter of type 'Char' then I cannot assign the value of the parameter.  The value that I want to assign is coming from the variant array.  I'm trying to pass that value to the Value argument of the CreateParameter function.  The error is:

ADODB.Parameter error '800a0d5d'

The application is using a value of the wrong type for the current operation.

If I change the data type of the stored procedure parameters to varchar then it works fine.  That is not an option though.

Does anyone have a solution to this problem?  Thank you for your help.
Jerry
0
jerryross
Asked:
jerryross
1 Solution
 
JHausmannCommented:
convert the char to varchar via the convert function?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
If you try to assign the whole array, this is a normal error. Here you must change your code (either VB code or SP code) to be able to do what you want.
Otherwise, i recommend NOT to use the .Refresh, but to define your parameters by hand. This is annoying code typing, but your procedure will run faster as there is one network trip less (this costs most of the processing time for your database access)
0
 
simonsabinCommented:
The error message above appears if the length of the parameter is not specified. It could be that the refresh is not populating this.
Because variants are used to store the value ADO checks the datatype when you set it. It appears that if the length of string varibales is not set that error occurs. You could set it to a default value after the refresh method.
0
 
jerryrossAuthor Commented:
I'm using .refresh instead of specifically declaring the parameters because this allows me to write a more generic component and also keeps the ASP from being so closely tied to the database.  (easier maintenance etc)  It certianly is more expensive, but it is how I have been asked to write it.

I'm going to try setting the size as simonsabin has suggested.  Thanks everyone for their possible solutions..we'll see what works.
Regards,
Jerry
0
 
jerryrossAuthor Commented:
The data conversion problem was occuring because there was a change in the data model which was implemented in the tables, but not in the FSP's for that table.  The char field in question was changed from a 1 byte field to a two byte field.  Once I fixed the stored procedure to match the table, everything worked fine.  The problem was related to what value was bieng assigned to size.  Simonsabin, you were correct :)  Thank you!

Here's something else I had trouble with that may be useful information.

I had trouble passing decimal values to stored procedures also.  

There are two cmd.parameter variables that must be set when using decimal parameters.  They are Precision and NumericScale.  I am using two command objects (i call cmd.Refresh to get the parameter data) and a second command object which executes the query.  I wasn't setting NumericScale and Precision in the second command object.  Once I set those two parameter options, my conversion problem was solved and the stored procedure executed normaly.

I hope this information is useful to someone.  Thank you everyone for your time and effort...it's greatly appreciated!
Jerry
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now