SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Oracle Stored Procedure APPEND problem

    I'm trying to run a stored procedure on an Oracle database. I currently get this error message:

    Error Type:
    ADODB.Command (0x800A0BB9)
    Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
    /live/verify_3.asp, line 24

    My code is as follows:

    <%
    Dim objConn
    Dim objCmd
    Dim var1
    Dim var2
    Dim var3
    Dim return_var

    var1 = Request.Form("frmVar1")
    var2 = Request.Form("frmVar2")
    var3 = Request.Form("frmVar3")

    'Instantiate objects
    Set objConn = Server.CreateObject("ADODB.Connection")
    set objCmd = Server.CreateObject("ADODB.Command")
    objConn.Open "DSN=testdb;UID=dbtest;PWD=testdb"

    With objCmd
    .ActiveConnection = objConn
    .CommandText = "sm_verify"
    .CommandType = 4 'Requires the adovbs.inc file or typelib meta tag

    'Add Input Parameters
    'LINE 24 below - This is problem line -> .Parameters.Append .CreateParameter("@var1", adVarChar , adParamInput, , var1)
    .Parameters.Append .CreateParameter("@var1", adVarChar , adParamInput, , var1)
    .Parameters.Append .CreateParameter("@var2", adInteger, adParamInput, , var2)
    .Parameters.Append .CreateParameter("@var3", adInteger, adParamInput, , var3)

    'Add Output Parameters
    .Parameters.Append .CreateParameter("@return", adInteger, adParamOutput, , 0)

    'Execute the function
    'If not returning a recordset, use the adExecuteNoRecords parameter option
    .Execute, objConn
    return_var = .Parameters("@return")
    response.write("return_var")
    End With
    %>

    It doesn't like the first .Parameters.Append line and for the live of me, I don't know why. I'm sure that the next 3 .Parameters.Append will cause the same problem.

    Any assistance would be greatly appreciated. Thank you in advance.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Is var1 a varchar field on the database. The error message is you are trying to send a varchar where it expects and integer. The other thing to look at is to make sure a value exists after doing the request.form
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    oracle stored procedure

    I received this from the DBA. I'm awaiting more specific descriptions of the variable types.

    var2 and var3 are number
    var1 is alphanumeric

    I've commented out the DB portion of the code on a different page to prove that the frmvar's are being passed properly and they have been passed proper like.


    BTW, thank you Dave. Your post is the first I've had in response to this question on about 4 different asp boards I shadow stalk on.

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think you'll need to specify the max length of the @var1 param, as it's a variable length parameter.

    eg
    Code:
    ...
    objCmd.Parameters.Append .CreateParameter("@var1", adVarChar , adParamInput, 20, var1)
    ...

    Just one other thing toetag, will you actually be returning a recordset? The code doesn't at the moment but you don't use the adExecuteNoRecords option. You've also specified the connection object in the Execute method call which isn't necessary as you've already set the ActiveConnection earlier.

    They're just minor things which won't cause a problem but could do with tidying up.

    Code:
    .ActiveConnection = objConn 
    ...
    ...
    'Execute the function 
    'If not returning a recordset, use the adExecuteNoRecords parameter option 
    .Execute, objConn 
    return_var = .Parameters("@return") 
    response.write("return_var")

  5. #5
    SitePoint Member
    Join Date
    Mar 2002
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    According to the DBA it is supposed to return a value.

    ie: 1 not verified, updated verify
    2 previous verified
    -1 not in database.

    I'll give the max length a try as well.

    When you say "the codes doesn't at the moment", do you mean I've left something out or that returning from an oracle stored procedure is not supported?

  6. #6
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're returning an output parameter but you're not returning a recordset. I thought that the code may be modified to return a recordset as well. (I was trying to guess what you'll be doing with you code. Not the best of traits.)

    If a recordset was being returned from the Execute method then it'd be assigned to a recordset object. As your code doesn't return a recordset you can use the adExecuteNoRecords, to save some unnecessary processing. The adExecuteNoRecords option only applies to returned recordsets and doesn't affect output parameters.

    Code:
    'Getting a recordset
    Set objRS = objCmd.Execute(strSomeSQL)
    
    'Not getting a recordset
    objCmd.Execute strSomeSQL, , adExecuteNoRecords

  7. #7
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK. I use Oracle stored procedures in ASP. The one I am looking at has 3 inputs and 1 output. I see a few differences, and I learned something.

    First, what I learned. Iím assuming that this:

    With objCmd
    .ActiveConnection = objConn
    .CommandText = "sm_verify"

    Is the same as:

    objCmd.ActiveConnection = objConn
    objCmd.CommandText = "sm_verify"

    and, objCmd is used in front of any Ď.í , and that takes place until it hits END WITH.

    If so, I just learned something. All my code uses the later style.


    Here are a couple of points:
    1) As far as I know, the inputs must have a size equal to that in the database table.
    2) The order of the input/output must match the stored procedure EXACTLY.
    3) The name in the input (e.g. FNAME) must match the table in the DB. (Iíve used other names, and it worked. However, I was told that I was asking for trouble in certain situations - I don't know what they are.)
    4) The data type must match the input type. TEST in ASP to make sure!
    5) Must use the 'cmd.execute, ,adExecuteNoRecords' to get output. I think it sets the recordset pointer to the beginning? I forget. Mine won't output without it.


    Here is the important parts of my code:

    cmd.commandtype = adcmdstoredproc ' Use name if have adovbs.inc
    cmd.commandtext = "mmp_addcustomer" ' Name of Oracle stored procedure.

    set prm = cmd.createparameter("FNAME", adchar, adparaminput,21, fname)
    cmd.parameters.append prm

    set prm = cmd.createparameter("LNAME", adchar, adparaminput,30, lname)
    cmd.parameters.append prm

    set prm = cmd.createparameter("GENDER", adchar, adparaminput,1 , gender)
    cmd.parameters.append prm

    set prm = cmd.CreateParameter("NUM", adinteger, adParamOutput, , 0)
    cmd.parameters.append prm

    cmd.execute, ,adExecuteNoRecords ' Required by MS to get output
    response.write("<p>The customer number is = " & cmd.Parameters("NUM"))


    More if I think of anything.

    -Mike-
    [COLOR=red]

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, doesn't form data always come in as a sting?

    -Mike-

  9. #9
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, thatís it. You need to convert your FORM string data to an integer. Also, should have data validation on the client web page (javascript) and/or in the ASP. You can see what happens when things donít match up. Maybe some error capture code too.

    I think this is the way to convert the string (that is a number) to an integer:

    if isnumeric(var1) then
    var1=Cint(var1)
    else
    Ďerror. User did not enter a number.
    end if

    -Mike-

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oops,

    I used var1 just as an example, but I used the wrong one, so it may have confused. In the code var1 as a string is ok as the input to the stored procedure is varchar. However, var2 and var3 need to be converted from a string to an integer for their input into the stored procedure.

    -Mike-


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •