Specifying bound date parameters for Oracle SQL in VB.NET

Question

Hoping someone can help. I'm using Oracle.ManagedDataAccess.Client in my VB.NET project to pull information from an Oracle 11g database.

I have the project working with a hard-coded string, but I'm looking to change the datetime into bound variables as I've heard it's easier on the server load.

I can get the below to work with Using Ocommand As New OracleCommand( and a hard-coded datetime string, but cannot get the below to work replacing the Using with a straight connection and with the bound variables:

    ''Build SQL Query and set variables
    Dim d_start_date As DateTime = New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour - 1, 0, 0)
    Dim p_start_date As OracleParameter = New OracleParameter
    p_start_date.OracleDbType = OracleDbType.Date
    p_start_date.Value = d_start_date
    p_start_date.ParameterName = "fromDateParam"

    Dim d_end_date As DateTime = New DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, 0, 0)
    Dim p_end_date As OracleParameter = New OracleParameter
    p_end_date.OracleDbType = OracleDbType.Date
    p_end_date.Value = d_end_date
    p_end_date.ParameterName = "toDateParam"


    ''Setup Datatable, connections and pull data
    Dim myDT As New DataTable
    Dim Oconn As New OracleConnection(my_CREDS & my_COMMS)

    Dim Ods As New DataSet
    Dim Ocommand As OracleCommand = New OracleCommand("select " & _
        "username as Recipient, " & _
        "to_char(CreatedDate,'YYYY-mm-dd HH24:MI:SS') as CREATEDDATE " & _
        "from " & _
        "tbl.one " & _
        "where " & _
        "CreatedDate >= :fromDateParam " & _
        " and CreatedDate < :toDateParam;", Oconn)
    Ocommand.Parameters.Add(p_start_date)
    Ocommand.Parameters.Add(p_end_date)
    Using Oda As New OracleDataAdapter(Ocommand)
        Oda.Fill(myDT)
    End Using
    Ocommand.Dispose()

Doing this it errors trying to fill the datatable: Oda.Fill(myDT). Can anyone explain why? I'm still quite new to Oracle db querying.

Answer 1

I found, after a messing around a bit, that I had added an extra ; at the end of the SQL statement, taken from the original statement in Oracle SQL Developer. After removing this, the code performs like a dream!