VentureFree
Well-known member
- Joined
- Jan 9, 2008
- Messages
- 54
- Programming Experience
- 5-10
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...):
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:
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:
Any idea what I'm missing and why this isn't working? Thanks.
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...):
VB.NET:
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
VB.NET:
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
VB.NET:
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"