Bizarre error in cmd.CreateParameter

[Kevin Audleman]

I am trying to pass a text string into a stored procedure via a
parameter, and am getting this baffling error while trying to create
one of the parameters:

adErrDataConversion 3421 You are using a value of the wrong type for
the current operation.

I know for sure that the data type I've selected is correct. The code
looks like this:

Set plsShare = cmd.CreateParameter("@shareIdeas", adVarChar,
adParamInput, 500, trim(Request("Please share your ideas")))

The crazy thing is that it only throws this error 1/20 times the
procedure is submitted. The last time it failed, the text string passed
in was:

always share a brief inspirational reading/statistic at beginning or
end of the class and establish a theme around which you teach your
material. Everything has a story.

Does anybody have insight into this error? A big thank you for anyone
who can solve this. It's been baffling me for a month!!

Thank you,
Kevin

 

[Guffa]

Does the maximum size that you specified in the parameter (500) correspond to
the maximum size of the data field?

 

[Evertjan.]

=?Utf-8?B?R3VmZmE=?= wrote on 19 apr 2006 in
microsoft.public.inetserver.asp.general:

Does the maximum size that you specified in the parameter (500)
correspond to the maximum size of the data field?

Please quote what you are replying to.

If you want to post a followup via groups.google.com, don't use the
"Reply" link at the bottom of the article. Click on "show options" at the
top of the article, then click on the "Reply" at the bottom of the article
headers. <http://www.safalra.com/special/googlegroupsreply/>

 

[Kevin Audleman]

=?Utf-8?B?R3VmZmE=?= wrote on 19 apr 2006 in
microsoft.public.inetserver.asp.general:

Yes. Sorry, I should have made that more clear. The input parameter is

@shareIdeas varchar(500)

and the ASP is

cmd.CreateParameter("@shareIdeas", adVarChar, adParamInput, 500,
trim(Request("Please share your ideas")))

I checked that many, many times. @shareIdeas is identical to a few
other input parameters in the function which are also varchar(500) and
never have any problems. And the function works most of the time.

Could it have something to do with the value of the input string?
Nobody has ever come close to using the 500 character limit...

Kevin

 

[Bob Barrows [MVP]]

Yes. Sorry, I should have made that more clear. The input parameter is

@shareIdeas varchar(500)

and the ASP is

cmd.CreateParameter("@shareIdeas", adVarChar, adParamInput, 500,
trim(Request("Please share your ideas")))

I checked that many, many times. @shareIdeas is identical to a few
other input parameters in the function which are also varchar(500) and
never have any problems. And the function works most of the time.

Could it have something to do with the value of the input string?
Nobody has ever come close to using the 500 character limit...

Kevin

Maybe. Could there be international characters involved? You may need to use
nvarchar instead of varchar.

 

[Aaron Bertrand [SQL Server MVP]]

Geez, I'm here for 2 minutes and already this guy is spending more time
complaining about other people's posts than doing anything remotely involved
with what this newsgroup is here for in the first place. I suppose I'm
sorry I stopped by. :-(

 

[Evertjan.]

Aaron Bertrand [SQL Server MVP] wrote on 21 apr 2006 in
microsoft.public.inetserver.asp.general:

Geez, I'm here for 2 minutes and already this guy is spending more
time complaining about other people's posts than doing anything
remotely involved with what this newsgroup is here for in the first
place. I suppose I'm sorry I stopped by. :-(

Hi Aaron, nice you are back. This NG hasn't been the same without you.

Also nice you are concerned how I spend those two minutes of my time.

Don't be sorry. Be happy.

 

[Kevin Audleman]

Maybe. Could there be international characters involved? You may need to use
nvarchar instead of varchar.

No, the problem occurs with plain ol' english. Any more ideas?

Kevin

 

[Bob Barrows [MVP]]

I am trying to pass a text string into a stored procedure via a
parameter, and am getting this baffling error while trying to create
one of the parameters:

adErrDataConversion

I have never seen this constant passed as part of the error-reporting
process. Could you show a little more of the code?

3421 You are using a value of the wrong type for
the current operation.

What line of code is throwing this error? The CreateParameter statement? Or
the cmd.Execute statement?
Usually I see this error when Nulls are involved.

I know for sure that the data type I've selected is correct. The code
looks like this:

Set plsShare = cmd.CreateParameter("@shareIdeas", adVarChar,
adParamInput, 500, trim(Request("Please share your ideas")))

The crazy thing is that it only throws this error 1/20 times the
procedure is submitted. The last time it failed, the text string
passed in was:

always share a brief inspirational reading/statistic at beginning or
end of the class and establish a theme around which you teach your
material. Everything has a story.

Does anybody have insight into this error? A big thank you for anyone
who can solve this. It's been baffling me for a month!!

Some thoughts:
I'm always a little suspicious when someone sets a parameter value directly
from a Request collection variable (you really should specify which Request
collection contains that variable - someday failing to do so will bite you
in the you-know-where) without first validating that it contains what it
should contain.

Maybe you are concentrating on the wrong statement. A long time ago (<grin>)
I had a problem populating the Parameters collection that was driving me
nuts. I wound up checking for errors after every CreateParmameter statement
until I zeroed in on the one causing the issue. It was a totally different
staement than the one I originally thought it was.

Maybe using something like this will help:
http://support.microsoft.com/kb/299986/EN-US/


Bob Barrows

 

[Kevin Audleman]

Bob, you're a genius. That's exactly what it was. My stored procedure
has 14 parameters. The fourth one is state, which I had as a
varchar(10), but turns out sometimes people were putting in
"Pennsylvania". Apparently an error was being thrown, but I didn't
check until the end, so I just assumed it was the last CreateParameter
causing the problem.

It didn't help that the stored procedure was returning the error
"Missing parameter: <NAME OF THE LAST PARAMETER>". Apparently it
ignores the name you assign to the input parameter in the
CreateParameter line, and just runs through them sequentially. Thus the
last parameter would always be the missing one.

Anyhow, thanks to everyone who helped me brainstorm on this one.

Kevin

 

[Bob Barrows [MVP]]

My stored procedure
has 14 parameters. The fourth one is state, which I had as a
varchar(10), but turns out sometimes people were putting in
"Pennsylvania".

.... which gets us back to the point I was making about validating the
user-supplied data before attempting to use it. ;-)

 

[Aaron Bertrand [SQL Server MVP]]

Bob, you're a genius. That's exactly what it was. My stored procedure

has 14 parameters. The fourth one is state, which I had as a
varchar(10), but turns out sometimes people were putting in
"Pennsylvania".

Well, why isn't state a CHAR(2), and constrained in some way? I wonder how
much bad data has made it into your table?

Bob is completely right: VALIDATE USER INPUT, ALWAYS. And when you can,
take away the opportunity for free text when in reality there is a finite
number of options. Calendar controls and drop-down lists are very easy ways
to avoid the inevitable typo (or intentional abuse).