locked
Oracle Parametrized Query From Ado.Net RRS feed

  • Question

  • User1436283608 posted

    So this is the error I have been getting: ORA-01036: illegal variable name/number

    All I am doing is trying to update the table with these values. The values are not null and each of the variables in the parameters have values in them.

    The Query executes well in Oracle (without Parameters), but it gives ORA-01036: illegal variable name/number, when I add parameters.

    I have tried almost everything, but with no luck !!

    using (OracleConnection con = new OracleConnection(cs))
    {
    const string query = @"UPDATE ASSOCIATED_CENTERS_INFO
    SET DIS_ASSOCIATED_BY = (SELECT ID FROM VC_ADMINISTRATORS WHERE USERNAME = ':USER'),
    DIS_ASSOCIATION_DATE = SYSDATE,
    ACTIVE_YN = 'N',
    NOTES = CONCAT(NOTES, '<br>' || SYSDATE || ' ' || (SELECT FIRSTNAME || ',' || LASTNAME FROM VC_ADMINISTRATORS WHERE USERNAME = ':USER') || ': ' || ':COMMENT')
    WHERE OSC_ID = :OSCID AND ASSOCIATED_OSC_ID = :ASSOCIATEDOSCID;";
    OracleCommand cmd = new OracleCommand(query, con);
    cmd.CommandType=CommandType.Text;
    cmd.Parameters.AddWithValue(":USER", userName);
    cmd.Parameters.AddWithValue(":COMMENT", comment);
    cmd.Parameters.AddWithValue(":OSCID", selectedIdOnMap);
    cmd.Parameters.AddWithValue(":ASSOCIATEDOSCID", selectedIdOnListBox);
    con.Open();
    cmd.ExecuteNonQuery();
    }

    Friday, April 26, 2013 9:46 PM

Answers

  • User269602965 posted

    cmd.Parameters.AddWithValue(":USER", userName);

    remove the colon in the Parameter statement

    cmd.Parameters.AddWithValue("USER", userName);

    colon only used in the WHERE statement WHERE USERNAME = :USER

    no need to put single quotes around the Parameter name in the WHERE statement

    EXAMPLE:

    Imports System.Xml.Linq.XElement
    
    Dim DateTimeStamp As Date = DateTime.Now
    Dim TicketSeq As Decimal = 1
    
    Dim connectionString As String = ConfigurationManager.ConnectionStrings("{YourOraConnStringName").ConnectionString
    ' Insert help ticket into database '
    Try
      Dim SQL = <SQL>
                  UPDATE {YourSchemaName}.HELP_TICKET
                  SET TICKET_TIMESTAMP = :TICKET_TIMESTAMP
                  WHERE TICKET_SEQ = :TICKET_SEQ
                </SQL>
      Using conn As New OracleConnection(connectionString)
        Using cmd As New OracleCommand(SQL.Value, conn)
          cmd.Parameters.Clear()
          cmd.Parameters.Add("TICKET_TIMESTAMP", OracleDbType.Date, DateTimeStamp, ParameterDirection.Input)
          cmd.Parameters.Add("TICKET_SEQ", OracleDbType.Decimal, decTicketSeq, ParameterDirection.Input)
          conn.Open()
          cmd.ExecuteNonQuery()
        End Using
      End Using
    Catch ex As Exception
    End Try

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 29, 2013 8:03 PM