SQL working in SQLPlus but not returning rows in VB.net using Oracle.OLEDB provider
-
I have SQL that is returning 24 rows in SQLPlus however when I run the same query in VS2010 using Provider=OraOLEDB.Oracle its doesn't return any rows. I am using a LIKE operator with to_date. Any suggestions? Is it the provider I am using the problem? See example
select level from testTable where dTime LIKE to_date('3/27/2013','MM/DD/YYYY') and station = '019654' order by dTime
I did try it with '%3/27/2013%'
-
I have SQL that is returning 24 rows in SQLPlus however when I run the same query in VS2010 using Provider=OraOLEDB.Oracle its doesn't return any rows. I am using a LIKE operator with to_date. Any suggestions? Is it the provider I am using the problem? See example
select level from testTable where dTime LIKE to_date('3/27/2013','MM/DD/YYYY') and station = '019654' order by dTime
I did try it with '%3/27/2013%'
Have you tried running the query in Oracle directly?
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
Have you tried running the query in Oracle directly?
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
I have SQL that is returning 24 rows in SQLPlus however when I run the same query in VS2010 using Provider=OraOLEDB.Oracle its doesn't return any rows. I am using a LIKE operator with to_date. Any suggestions? Is it the provider I am using the problem? See example
select level from testTable where dTime LIKE to_date('3/27/2013','MM/DD/YYYY') and station = '019654' order by dTime
I did try it with '%3/27/2013%'
What's the code you're using to execute the query?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
What's the code you're using to execute the query?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Dim dsFred = New DataSet
conn = New OleDbConnection("Provider=OraOLEDB.Oracle;User ID=test;Password=testPass;Data Source=ARC")
sql = "select level from testTable where dTime LIKE to_date('3/27/2013','MM/DD/YYYY') and station = '019654' order by dTime"
myCommand = New OleDbDataAdapter(sql, conn)
myCommand.Fill(dsFred, "fred")
Dim fred As Integer = dsFred.Tables("fred").Rows.Count -
Dim dsFred = New DataSet
conn = New OleDbConnection("Provider=OraOLEDB.Oracle;User ID=test;Password=testPass;Data Source=ARC")
sql = "select level from testTable where dTime LIKE to_date('3/27/2013','MM/DD/YYYY') and station = '019654' order by dTime"
myCommand = New OleDbDataAdapter(sql, conn)
myCommand.Fill(dsFred, "fred")
Dim fred As Integer = dsFred.Tables("fred").Rows.CountI notice that you are using the OleDb namespace data objects and not the OracleClient objects. Try adding a reference to System.Data.OracleClient.dll and use the OracleClient.OracleConnection, OracleClient.OracleCommand, and OracleClient.OracleDataAdapter objects.
-
I have SQL that is returning 24 rows in SQLPlus however when I run the same query in VS2010 using Provider=OraOLEDB.Oracle its doesn't return any rows. I am using a LIKE operator with to_date. Any suggestions? Is it the provider I am using the problem? See example
select level from testTable where dTime LIKE to_date('3/27/2013','MM/DD/YYYY') and station = '019654' order by dTime
I did try it with '%3/27/2013%'
That's a funny Oracle feature. LIKE with a date/datetime field does not make sense, I think. But I learned that "The date column will be implicitly converted to a character string (according to nls_date_format) before the comparison" (https://forums.oracle.com/forums/thread.jspa?threadID=2132385[^]) with Oracle. Never did I expect such a nonsense. What's your requirement behind that LIKE? And I guess that the cited text shows the reason for the failure in one situation and the success in another: the nls_date_format differs.
-
That's a funny Oracle feature. LIKE with a date/datetime field does not make sense, I think. But I learned that "The date column will be implicitly converted to a character string (according to nls_date_format) before the comparison" (https://forums.oracle.com/forums/thread.jspa?threadID=2132385[^]) with Oracle. Never did I expect such a nonsense. What's your requirement behind that LIKE? And I guess that the cited text shows the reason for the failure in one situation and the success in another: the nls_date_format differs.
What I am trying to do is capture any records that happened that day. Where it is a datetime field there are 24 records during that time period. I can always put in qualifers for midnight to midnight however I thought the LIKE would work. I will have to try the other way I guess...
