Querying in Oracle databse from date time picker using parameters

hi Sir,

I try code below to query data from my oracle database base on the 2 date time picker .using parameters but i got an error message."missing expression"
please suggest where i made a mistakes
thanks .

Code:

---

  Public Sub loadQuery()
        Dim startDate = dtpFrom.Value.Date
        Dim endDate = dtpTo.Value.Date.AddDays(1)

        Try
            conn.Open()
            cmd.Connection = conn
            cmd.CommandType = CommandType.Text
            dataAdaptor.SelectCommand = cmd
            cmd.CommandText = ("select * from test_table where ERR_DTTM >= @startDate and ERR_DTTM < @endDAte")
            cmd.Parameters.AddWithValue("@startDate", dtpFrom.Value.Date)
            cmd.Parameters.AddWithValue("@endDate", dtpTo.Value.Date.AddDays(1))
            Dim dr As OleDbDataReader = cmd.ExecuteReader
            If dr.HasRows Then
                datatable.Load(dr)
                dgvWCQ.DataSource = datatable
            Else
                MsgBox("no data")

            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

---

my program interface
Attachment 160405

Oracle doesn't support @ as a parameter prefix. Do some reading on the database you're using. Also, it is generally preferred to use a provider specific to your data source if possible, only falling back to OleDb or Odbc if you have to. You can download an Oracle-specific ADO.NET provider from Oracle.

Quote:

---

Originally Posted by jmcilhinney

Oracle doesn't support @ as a parameter prefix. Do some reading on the database you're using. Also, it is generally preferred to use a provider specific to your data source if possible, only falling back to OleDb or Odbc if you have to. You can download an Oracle-specific ADO.NET provider from Oracle.

---

Indeed, you should be using Oracle Data Provider for .NET (ODP.NET) unless there's a specific reason you can't. I'd recommend the managed driver unless you need Core or added functionality of the unmanaged one.

I prefer using stored procedures, but you can extrapolate how to deal with dates and parameters from this example.

Code:

---

    Public Function getPumpGoldHistoryAnalysis(ByRef StartDate As Date, ByRef EndDate As Date, ByRef SN As String, ByRef errnum As Integer, ByRef ErrMsg As String) As Boolean

        getPumpGoldHistoryAnalysis = FAILURE

        Try

            Dim sql As String = "begin getPumpGoldHistoryAnalysis(:P1,:P2,:P3); end;"

            Using cmd As New OracleCommand(sql, conn)
                Dim P1 As OracleParameter = New OracleParameter()
                P1.ParameterName = "P1"
                P1.OracleDbTypeEx = OracleDbType.Date
                P1.Direction = ParameterDirection.Input
                P1.Value = StartDate
                cmd.Parameters.Add(P1)
                Dim P2 As OracleParameter = New OracleParameter()
                P2.ParameterName = "P2"
                P2.OracleDbTypeEx = OracleDbType.Date
                P2.Direction = ParameterDirection.Input
                P2.Value = EndDate
                cmd.Parameters.Add(P2)
                Dim P3 As OracleParameter = New OracleParameter()
                P3.ParameterName = "P3"
                P3.OracleDbTypeEx = OracleDbType.Varchar2
                P3.Direction = ParameterDirection.Input
                P3.Value = SN
                cmd.Parameters.Add(P3)
                cmd.ExecuteNonQuery()

            End Using

            getPumpGoldHistoryAnalysis = SUCCESS

        Catch exOra As OracleException
            ErrMsg = exOra.ToString

        Catch ex As SystemException
            ErrMsg = ex.ToString

        End Try

    End Function

---

while topshots code is ok and correct, there is a shorter Version which generally works well for queries:
cmd.Parameters.Add(":P1", StartDate)

this is similar to the .AddWithValue function for other Drivers.

Quote:

---

Originally Posted by digitalShaman

while topshots code is ok and correct, there is a shorter Version which generally works well for queries:
cmd.Parameters.Add(":P1", StartDate)

this is similar to the .AddWithValue function for other Drivers.

---

Other providers actually did have an Add method like that initially but it was deprecated, I think because it may have been a bit confusing when compared to other overloads in certain situations.

Also, using AddWithValue (and thus this Add overload) is discouraged by many because it will infer a default database type from a .NET type and that default type may not be what you want. Many times this doesn't matter but sometimes it can. If you don't have to want to think about when it matters and when it doesn't, don't use it at all. Do this sort of thing instead:

Code:

---

cmd.Parameters.Add("@MyColumn", SqlDbType.VarChar, 50).Value = myValue

---

If you specify the data type and, for variable-length data, the size then you know the parameter data type will match your database and there can't be any issues as a result of mismatched types. Note that that example is for SqlClient but the equivalent applies for other providers too.