Solved

Convert Variant to Char

Posted on 2000-03-31
5
821 Views
Last Modified: 2008-01-16
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
Comment
Question by:jerryross
5 Comments
 
LVL 2

Expert Comment

by:JHausmann
ID: 2674763
convert the char to varchar via the convert function?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2675606
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
 
LVL 7

Accepted Solution

by:
simonsabin earned 100 total points
ID: 2679331
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
 

Author Comment

by:jerryross
ID: 2680566
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
 

Author Comment

by:jerryross
ID: 2735340
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now