Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    20

    Unanswered: Apostrophes problem using ASP and Oracle

    Hi everyone,

    I'm having extreme difficulties in getting a stored procedure to work. I am passing string values from a web page to the following procedure which updates a table in an Oracle db.


    var cmd = Server.CreateObject("ADODB.Command");
    cmd.ActiveConnection = Application("bmcmaindb");

    var param1 = cmd.CreateParameter("text1", adChar, adParamInput, 4000);

    var param2 = cmd.CreateParameter("text2", adChar, adParamInput, 4000);

    cmd.Parameters.Append(param1);
    cmd.Parameters.Append(param2);

    cmd.Parameters("text1") = param1;
    cmd.Parameters("text2") = param2;

    cmd.CommandText = "{CALL my_pkg.my_proc(?,?) }";

    cmd.CommandType = adCmdText;
    cmd.Execute();

    cmd = null;


    The trouble here is that it is knocking off text after any apostrophe within both of the parameters. Does anyone know why?


    Regards,

    John

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Apostrophes problem using ASP and Oracle

    Originally posted by johnstv3
    Hi everyone,

    I'm having extreme difficulties in getting a stored procedure to work. I am passing string values from a web page to the following procedure which updates a table in an Oracle db.


    var cmd = Server.CreateObject("ADODB.Command");
    cmd.ActiveConnection = Application("bmcmaindb");

    var param1 = cmd.CreateParameter("text1", adChar, adParamInput, 4000);

    var param2 = cmd.CreateParameter("text2", adChar, adParamInput, 4000);

    cmd.Parameters.Append(param1);
    cmd.Parameters.Append(param2);

    cmd.Parameters("text1") = param1;
    cmd.Parameters("text2") = param2;

    cmd.CommandText = "{CALL my_pkg.my_proc(?,?) }";

    cmd.CommandType = adCmdText;
    cmd.Execute();

    cmd = null;


    The trouble here is that it is knocking off text after any apostrophe within both of the parameters. Does anyone know why?


    Regards,

    John
    Sounds like you need to double up the apostrophes within the string, like this:

    string = 'John''s string'

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Here you go:

    Add this to your ASP page:

    Code:
    Function InsertAP(t)
        If Not IsNull(t) Then
            InsertAP = Replace(t, "'", "''")
        Else
            InsertAP = ""
        End If
    End Function
    And use it like this:

    Code:
    InsertAP(request("Name"))
    Where this would possibly happen:

    Code:
    "INSERT INTO IM (NAME) VALUES ('" & InsertAP(request("Name")) & "')"
    That will take care of it. Later, Jeremy
    Nothing better than a good ride.

Posting Permissions

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