Question AddParameter Method results in "ORA-00936: missing expression"

[VentureFree]

As usual I'm copying other people's code and modifying it for my own purposes. Also as usual it's not going so smoothly.

Essentially someone wrote some helper methods for a DAL using Sql Server, and I'm trying to convert it so that it can access Oracle instead. Here are the methods as I've modified them (basically the original was using SqlCommand instead of OdbcCommand, with all of the differences that that entails, like SqlType instead of OdbcType, etc...):

Private Sub AddParamToOdbcCmd(ByVal sqlCmd As OdbcCommand, ByVal paramId As String, ByVal sqlType As OdbcType, ByVal paramSize As Integer, ByVal paramDirection As ParameterDirection, ByVal paramvalue As Object)
    ' Validate Parameter Properties
    If sqlCmd Is Nothing Then
        Throw New ArgumentNullException("sqlCmd")
    End If
    If paramId = String.Empty Then
        Throw New ArgumentOutOfRangeException("paramId")
    End If
    ' Add Parameter
    Dim newSqlParam As New OdbcParameter
    newSqlParam.ParameterName = paramId
    newSqlParam.OdbcType = sqlType
    newSqlParam.Direction = paramDirection

    If paramSize > 0 Then
        newSqlParam.Size = paramSize
    End If
    If Not (paramvalue Is Nothing) Then
        newSqlParam.Value = paramvalue
    End If
    sqlCmd.Parameters.Add(newSqlParam)
End Sub 'AddParamToSQLCmd

Private Function ExecuteOdbcReaderCmd(ByVal sqlCmd As OdbcCommand, ByVal gcfr As GenerateCollectionFromReader, ByVal ConnectionString As String) As CollectionBase
    If ConnectionString = String.Empty Then
        Throw New ArgumentOutOfRangeException("ConnectionString")
    End If
    If sqlCmd Is Nothing Then
        Throw New ArgumentNullException("sqlCmd")
    End If
    Dim cn As New OdbcConnection(ConnectionString)
    Try
        sqlCmd.Connection = cn
        cn.Open()
        ' gcfr stands for GetCollectionFromReader
        ' It is a delegated method that creates a collection from the reader
        Dim temp As CollectionBase = gcfr(sqlCmd.ExecuteReader())
        Return temp
    Finally
        cn.Dispose()
    End Try
End Function 'ExecuteReaderCmd

Private Sub SetOdbcCommandType(ByVal sqlCmd As OdbcCommand, ByVal cmdType As CommandType, ByVal cmdText As String)
    sqlCmd.CommandType = cmdType
    sqlCmd.CommandText = cmdText
End Sub 'SetCommandType

When I attempt to use these methods I keep getting the error: "ORA-00936: missing expression". I suspect that the AddParamToOdbcCmd is the culprit somehow because if I concatenate the values directly into the query string instead of using parameters the query works. Here's an example of how I am trying to use it:

Public Function GetEventsByIDOnDate(ByVal ID As Integer, ByVal EventDate As DateTime) As EventCollection
    ' Execute SQL Command
    Dim oracleCmd As New OdbcCommand()
    Dim Query As String = "SELECT * " & _
		"FROM MyTable " & _
		"WHERE MyTable.ID = @ID " & _
		"AND MyTable.EventDate = @EventDate " & _
		"ORDER BY MyTable.EventDate"

    AddParamToOdbcCmd(oracleCmd, "@ID", OdbcType.Int, 0, ParameterDirection.Input, ID)
    AddParamToOdbcCmd(oracleCmd, "@EventDate", OdbcType.DateTime, 0, ParameterDirection.Input, EventDate)

    SetOdbcCommandType(oracleCmd, CommandType.Text, Query)
    ' GenerateEventCollectionFromReader is the specific delegate used for event collections here
    Dim sqlData As New GenerateCollectionFromReader(AddressOf GenerateEventCollectionFromReader)
    Dim results As EventCollection = CType(ExecuteOdbcReaderCmd(oracleCmd, sqlData, MyOracleConnectionString), EventCollection)
    Return results
End Function

That's the one that results in the error. However, it works if I skip the whole parameter thing and do this for the query instead:

Dim Query As String = "SELECT * " & _
    "FROM MyTable " & _
    "WHERE MyTable.ID = " & ID.ToString() & " & _
    "AND MyTable.EventDate = TO_DATE('" & EventDate.ToString("MM/dd/yyyy") & "', 'MM/DD/YYYY') " & _
    "ORDER BY MyTable.EventDate"

Any idea what I'm missing and why this isn't working? Thanks.

 

[cjard]

Are you using a free version of visual studio? I'm just wondering why youre not using the Oracle daabase support that is built in (the the non-free versions)

 

[cjard]

ps; The tutorial youre copying is a little wacky. Try this slightly less messed up, less backwards ways of building an oracle command (if youre in the free version of VS):

    Dim sql as String = "SELECT * FROM MyTable WHERE id = :id AND eventdate = :ed ORDER BY eventdate"
    Using connection As New OracleConnection(connectionString)
        Dim command As New OracleCommand(sql, connection)
        command.Parameters.AddWithValue("id", id)
        command.Parameters.AddWithValue("ed", eventdate)
        Dim da as New OracleDataAdapter(cmd)

        Try
            da.Fill(yourDataTable)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
        ' The connection is automatically closed when the
        ' code exits the Using block.
    End Using

Note that the parameter names do not contain the colon. This is intentional

Note that MS have clearly grown tired of people not using their DB, and writing code in free visual studios; The system.data.oracleclient namespace has been deprecated and will disappear after .net4
They advocate using an external provider (such as the one from oracle) but free versions of VS do not support plugins of this nature. If you progress with Oracle and youre on a free version of VS, you'll probably have to buy it when .net5 comes out, if you switch