locked
Unable to Add Parameters to an Oracle Query in a Typed-Dataset Class RRS feed

  • Question

  • User-1847796215 posted

    I am using ODP.Net to connect to an Oracle database.  I am using a dataset class to generate a typed-dataset.  I am able to create a tableAdapter and add a query that will return data.  However, I cannot figure out how to add a parameter to the query. 

    Sample query:

    SELECT PERSON_CODE, LAST_NAME
    FROM PERSONTABLE

    The same query with a parameter:

    SELECT PERSON_CODE, LAST_NAME
    FROM PERSONTABLE
    WHERE PERSON_CODE = :Parameter1

    Whenever the parameter is added, the TableAdapter Configuration Wizard gives the error "Specified argument was out of the range of valid values."

    I have tried substituting ? for the parameter name, but that gives the same error.  @Parameter1 is not recognized, an Oracle syntax error.

    I would greatly appreciate your help.

    Friday, February 22, 2019 9:42 PM

All replies

  • User269602965 posted

    Use Oracle parameter collection and always clear your parameter collection (best practice).

    Oracle Parameters must be in the same order as they appear as BIND variables in the SQL Statement.

    Example using an UPDATE statement with BIND VARIABLES derived from ODP.NET Oracle Parameters collection.

    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
    

    You can also pass parameters INTO an Oracle PL/SQL procedure and receive parameters back (refcursor, et al).

    Sunday, February 24, 2019 1:27 AM
  • User-1847796215 posted

    Sorry, I wasn't clear.  I am using a data set class to create a strongly-typed dataset so I can reference the table adapter objects without embedding SQL in code.  I have used this successfully with SQL Server, but Oracle is not allowing me to add parameters to the query in the data set's table adapter.

    The data set has the following table adapter:

    Table adapter

    I have added the parameter via the query properties:

    Parameter properties

    I then edit the query to add the parameter reference (see last line):

    Add parameter to query

    When completing the edit query wizard, the parameter is not recognized:

    This is where I have tried numerous ways to add the parameter, but I have had no success.  I am hoping someone has experience with this method.

    Monday, February 25, 2019 9:51 PM
  • User269602965 posted

    I have had but trouble with those "TOOLS" for data object management.  I just code directly, either using SQL statement in the .NET side or calling an Oracle stored procedure to return data.  You can return to a data adapter and then fill a data table.

    In this case I pass Oracle parameter as a BIND VARIABLE in an SQL SELECT statement to filter an Oracle Table, put it in to an Oracle Data Adapter to fill a .NET dataset for use in whatever, like a data display datagrid. 

    ' Get Oracle dataset into .NET dataset for use in DataGrid
    Imports System.Xml.Linq.XElement
    
    Dim decContractSeq as Decimal = 45
    Try
    	Dim connectionString As String = ConfigurationManager.ConnectionStrings("AuthenticatedOracleConnectionString").ConnectionString
    	Dim SQL = _
    		<SQL>
    		 SELECT PRODUCT_CLASS, AMOUNT_ANNUAL_CONTRACT, COUNT, TOTAL_VALUE
    		 FROM AER2.VW_COUNT_CONTRACTS WHERE CONTRACT_SEQ = :CONTRACT_SEQ
    	</SQL>
    	Using conn As New OracleConnection(connectionString)
    		Using cmd As New OracleCommand(SQL.Value, conn)
          cmd.Parameters.Clear()
          cmd.Parameters.Add("CONTRACT_SEQ", OracleDbType.Decimal, decContractSeq, ParameterDirection.Input)
    			conn.Open()
    			Using oda As New OracleDataAdapter(cmd)
    				Dim ds As New DataSet()
    				oda.Fill(ds)
    				Me.RadGrid1.DataSource = ds
    				Me.RadGrid1.MasterTableView.DataSource = ds
    			End Using
    		End Using
    	End Using
    Catch ex As Exception
    	AppCalls.WriteToEventLog(ex, "Selecting VW_COUNT_CONTRACTS", "ContractAdjust.aspx.vb")
    End Try
        
    

    Tuesday, February 26, 2019 12:34 AM