?
Solved

Convert Variant to Char

Posted on 2000-03-31
5
Medium Priority
?
838 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

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

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 400 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

777 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