Bizarre error in cmd.CreateParameter

Discussion in 'ASP General' started by Kevin Audleman, Apr 18, 2006.

  1. 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
     
    Kevin Audleman, Apr 18, 2006
    #1
    1. Advertising

  2. Kevin Audleman

    Guffa Guest

    Does the maximum size that you specified in the parameter (500) correspond to
    the maximum size of the data field?
     
    Guffa, Apr 19, 2006
    #2
    1. Advertising

  3. Kevin Audleman

    Evertjan. Guest

    =?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/>

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Apr 19, 2006
    #3
  4. Evertjan. wrote:
    > =?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?
    > >


    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


    >
    > 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/>
    >
    > --
    > Evertjan.
    > The Netherlands.
    > (Please change the x'es to dots in my emailaddress)
     
    Kevin Audleman, Apr 19, 2006
    #4
  5. Kevin Audleman wrote:
    > Evertjan. wrote:
    >> =?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?
    >>>

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

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 19, 2006
    #5
  6. 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." <> wrote in message
    news:Xns97AAAEA717BF2eejj99@194.109.133.242...
    > =?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/>
     
    Aaron Bertrand [SQL Server MVP], Apr 21, 2006
    #6
  7. Kevin Audleman

    Evertjan. Guest

    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.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
     
    Evertjan., Apr 21, 2006
    #7
  8. >
    > 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
     
    Kevin Audleman, Apr 25, 2006
    #8
  9. Kevin Audleman wrote:
    > 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
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 25, 2006
    #9
  10. 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
     
    Kevin Audleman, May 1, 2006
    #10
  11. Kevin Audleman wrote:
    > 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. ;-)

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 1, 2006
    #11
  12. > 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).
     
    Aaron Bertrand [SQL Server MVP], May 1, 2006
    #12
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    399
  2. Achim Domma (Procoders)

    read input for cmd.Cmd from file

    Achim Domma (Procoders), Jun 3, 2005, in forum: Python
    Replies:
    2
    Views:
    8,102
    Peter Otten
    Jun 3, 2005
  3. Sarir Khamsi

    Interpreter-like help in cmd.Cmd

    Sarir Khamsi, Jun 9, 2005, in forum: Python
    Replies:
    4
    Views:
    389
    Bengt Richter
    Jun 26, 2005
  4. =?ISO-8859-1?Q?Sch=FCle_Daniel?=

    [exec cmd for cmd in cmds]

    =?ISO-8859-1?Q?Sch=FCle_Daniel?=, Mar 8, 2006, in forum: Python
    Replies:
    3
    Views:
    404
    Scott David Daniels
    Mar 8, 2006
  5. news
    Replies:
    3
    Views:
    607
    Bob Barrows
    Jul 13, 2009
Loading...

Share This Page