Query not returning data - Date conversion issue?

[hersheysc]

I am attempting to populate a datatable from a select query that runs against an oracle database (currently using odp in Vs 2005). The strange thing is, when I run this same query in SQLPlus or Toad, it returns rows...but not from .NET. It looks like it runs, but just doesn't return any data. I've worked on this for two days, and I know I am missing something. Can someone help? Here's the code:

----------------------------------

Shared Function GetReport(ByVal emplid As String, ByVal toDate As Date, ByVal fromDate As Date) As DataTable

Dim conn As OracleConnection = New OracleConnection()
conn.ConnectionString = effortConStr
Dim cmdGetDesc As OracleCommand = Nothing
Dim Sql As String
Dim da As OracleDataAdapter = Nothing
Dim dt As New DataTable
Dim descr As String = Nothing


Dim td As String = toDate.ToString("dd-MMM-yy").ToUpper()
Dim fd As String = fromDate.ToString("dd-MMM-yy").ToUpper()


Sql = "Select * from sysadm.ps_CU_EFFORT where emplid = :emplid " & _
"and to_date = TO_DATEtdate, 'dd-MON-yyyy') " & _
"and from_date = TO_DATEfdate, 'dd-MON-yyyy')"


Try
cmdGetDesc = New OracleCommand(Sql, conn)
cmdGetDesc.BindByName = True
cmdGetDesc.Parameters.Add("emplid", OracleDbType.Varchar2).Value = Trim(emplid)
cmdGetDesc.Parameters.Add("tdate", OracleDbType.Varchar2, ParameterDirection.Input).Value = td
cmdGetDesc.Parameters.Add("fdate", OracleDbType.Varchar2, ParameterDirection.Input).Value = fd
da = New OracleDataAdapter(cmdGetDesc)
da.Fill(dt)
If dt.Rows.Count.Equals(0) Then
Return Nothing
Else
Return dt
End If
If cmdGetDesc Is Nothing Then cmdGetDesc.Dispose()
If da Is Nothing Then da.Dispose()


Catch ex As Exception
Return Nothing
Finally
If conn Is Nothing Then
conn.Close()
conn.Dispose()
End If

End Try

End Function

-----------------
What am I missing here?

 

[cjard]

Sql = "Select * from sysadm.ps_CU_EFFORT where emplid = :emplid trunc(to_date) = trunc(:tdate) and trunc(from_date) = trunc(:fdate)"


Try
cmdGetDesc = New OracleCommand(Sql, conn)
cmdGetDesc.BindByName = True
cmdGetDesc.Parameters.Add("emplid", OracleDbType.Varchar2).Value = Trim(emplid)
cmdGetDesc.Parameters.Add("tdate", OracleDbType.Date, ParameterDirection.Input).Value = toDate
cmdGetDesc.Parameters.Add("fdate", OracleDbType.Date, ParameterDirection.Input).Value = fromDate

One of the major ideas of using parameters is data types.. You have dates, so don't convert to string and back to date..

Remember that dates are stored as decimal number (the hours part is the decimal) so TRUNC strips the hours off and just does the date