OracleTransation strange problem with parameters RSS

• • Reply
  

  kszymaniak

  Member

  205 Points

  112 Posts

  OracleTransation strange problem with parameters

  Jan 20, 2013 06:52 PM|LINK

  Dear All

  I have very strange problem with oracletransation and applying parameters, below is part of code

  transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
  OracleCommand cmd = conn.CreateCommand();
  cmd.Transaction = transaction;
   //first command
  cmd.CommandText = "updated sometable1 ..... set values "
  cmd.Parameters.Add(":somevariable1", textBox1.Text);
  cmd.Parameters.Add(":somevariable2", textBox2.Text);
  
  cmd.ExecuteNonQuery();
   //second command
  cmd.CommandText = "insert into sometable1 ..... values (somevariable3, somevariable4);"
  cmd.Parameters.Add(":somevariable3", textBox3.Text);
  cmd.Parameters.Add(":somevariable4", textBox4.Text);
  
  cmd.ExecuteNonQuery();
  
  transaction.Commit();

  I got error Oracle.DataAccess.Client.OracleException ORA-01036: illegal variable name/number in ......,

  but when I'm using second command as

  cmd.CommandText = "insert into sometable1 ..... values ('" + textBox3.Text + "', '" + textBox4.Text + "');"
  cmd.ExecuteNonQuery(); 

  and keep first command with parameters everything works fine.  Is someone who knows what is wrong and what should be code with parameters?

  Thank you in avance.

  Regards

  Krzysztof

  • Edited by kszymaniak on Jan 20, 2013 06:53 PM
  • Edited by kszymaniak on Jan 20, 2013 06:56 PM
• • Reply
  

  Lannie

  Contributor

  3724 Points

  726 Posts

  Re: OracleTransation strange problem with parameters

  Jan 21, 2013 02:32 AM|LINK

   

  Got COLON symbols in the wrong location

  transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);
  OracleCommand cmd = conn.CreateCommand();
  cmd.Transaction = transaction;
   //first command
  cmd.CommandText = "update sometable1 set val1 = :somewariable1, val2 = :somevariable2 "
  cmd.Parameters.Add("somevariable1", textBox1.Text);
  cmd.Parameters.Add("somevariable2", textBox2.Text);
   
  cmd.ExecuteNonQuery();
   //second command
  cmd.CommandText = "insert into sometable1 (val3, val4) values (:somevariable3, :somevariable4);"
  cmd.Parameters.Add("somevariable3", textBox3.Text);
  cmd.Parameters.Add("somevariable4", textBox4.Text);
   
  cmd.ExecuteNonQuery();
   
  transaction.Commit();

• • Reply
  

  kszymaniak

  Member

  205 Points

  112 Posts

  Re: OracleTransation strange problem with parameters

  Jan 21, 2013 05:21 AM|LINK

  Sir, you're right I forgot about colon symbols, but it isn't still working.

  rgds

• • Reply
  

  Lannie

  Contributor

  3724 Points

  726 Posts

  Re: OracleTransation strange problem with parameters

  Jan 21, 2013 05:14 PM|LINK

  I have had the most success doing it this way, where I pass variable to a procedure, define the SQL statement as XML like statement, pass that value to the Oracle command, and in the parameters, specify not only bind variable name, but also the data type and direction.

  Imports System.Xml.Linq.XElement
  
    Public Shared Sub updateUnitsActiveFlag(ByVal decQuantity As Decimal, ByVal strDescription As String, ByVal dateDateClosed As DateTime)
      ' Insert Quantity into new row Units table'
      Dim OraConnStr As String = ConfigurationManager.ConnectionStrings("OraConnStr").ConnectionString
      Try
        Dim SQL =
        <SQL>
        INSERT INTO {YOURSCHEMANAME}.UNITS
          (UNITS_SEQ, QUANTITY, DESCRIPTION, DATE_CLOSED, DATE_TODAY)
        VALUES
          (UNIT_SEQ.NextVal, :BindVarQuantity, :BindVarDescription, :BindVarDateClosed, SYSDATE)
        </SQL>
        Using conn As New OracleConnection(OraConnStr)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("BindVarQuantity", OracleDbType.Decimal, decQuantity, ParameterDirection.Input)
            cmd.Parameters.Add("BindVarDescription", OracleDbType.CLOB, strDescription, ParameterDirection.Input)
            cmd.Parameters.Add("BindVarDateClosed", OracleDbType.Date, dateDateClosed, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
        AppCalls.WriteToEventLog(ex, "Inserting UNITS.QUANTITY failed", "AppCalls.updateUnitsActiveFlag.vb")
      End Try
  End Sub