2 Attachment(s)
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
Re: Querying in Oracle databse from date time picker using parameters
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.
Re: Querying in Oracle databse from date time picker using parameters
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
Re: Querying in Oracle databse from date time picker using parameters
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.
Re: Querying in Oracle databse from date time picker using parameters
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.