I can't pass dates to Oracle

[Elysium]

I have searched this site for an answer to my question, so if I overlooked something or didn't use the right key words, forgive me.

Here's my problem: I am using VB6 and I am trying to connect to an Oracle database. My SQL is restricted with date ranges, the standard Start and End, but I am getting an error.

The WHERE clause looks like this:
&quot;WHERE ((Account_Detail.Account_Start_Date) >= to_date('11/03/2002','mm-dd-yyyy') AND (Account_Detail.Account_Start_Date) <= to_date('11/09/2002','mm-dd-yyyy')) AND ((Organization.Region_Num)='04') AND ((Organization.District_Num)='74')&quot;

The error that comes back reads:
&quot;Unsupported query syntax&quot;

My connection object looks like this:
oConn.ConnectionString = &quot;Provider=MSDASQL.1;Persist Security Info=False;User ID=; Data Source=MyDSN ;Extended Properties='DSN=MyDSN'&quot;

For the life of me, I just can't see it. I need someone from the outside to clue me in. Where am I going wrong? Thank you.

 

[Topic973]

the date formats do not match! 12-12-2002 <> 12/12/2002!!!
HTH ;-)

 

[Elysium]

I am still getting the unsupported query syntax error even after changing the date formats. It really seems to be the date criteria that is causing all of the problems.

 

[CasperTFG]

[tt]&quot;WHERE ((Account_Detail.Account_Start_Date) >= to_date('11/03/2002','mm-dd-yyyy') AND (Account_Detail.Account_Start_Date) <= to_date('11/09/2002','mm-dd-yyyy')) AND ((Organization.Region_Num)='04') AND ((Organization.District_Num)='74')&quot;
[/tt]
Are you sure it's the date field... Look at all your fields.
[tt]((Organization.Region_Num)='04') [/tt]
[tt]((Organization.District_Num)='74') [/tt]
Are the above field set as Numeric In Oracle? if so take out the single quotes. '
Craig, mailto:sander@cogeco.ca

Si hoc legere scis, nimis eruditionis habes

 

[Elysium]

The Region and District fields are defined as text. That's why I think that it's the date criteria.

 

[CasperTFG]

try one by one, i.e. take apart your select query and one by one add in the where criteria to see which one it is that fails.

Try it like this

1) SELECT * FROM tablename;

2) SELECT field1 FROM tablename;

3) SELECT field2 FROM tablename; etc.

then

4) SELECT field1... FROM tablename WHERE Criteria1;

5) SELECT field1... FROM tablename WHERE Criteria2; etc.

Craig, mailto:sander@cogeco.ca

Si hoc legere scis, nimis eruditionis habes

 

[Elysium]

Well, it's not that easy because the warehouse serves 9 regions and 66 districts. Because of that, the rules on the server state that I have to at least provide a Region and District criteria. So, when I took out the date criteria, guess what, I got another error that is sooooooo descriptive. It said &quot;unspecified error&quot;.

 

[maxpower1]

try this:-
1. changed mm-dd--yy to mm/dd/yy. Look at your date input. They use forward slash, i.e. 11/03/2002, hence you cannot use the hyphen in the second part of the argument.


The WHERE clause looks like this:
&quot;WHERE ((Account_Detail.Account_Start_Date) >= to_date('11/03/2002','mm/dd/yyyy') AND (Account_Detail.Account_Start_Date) <= to_date('11/09/2002','mm/dd/yyyy')) AND ((Organization.Region_Num)='04') AND ((Organization.District_Num)='74')&quot;


2. TO find out the fields types of your table, get into sql plus or any other programs and type in:-
sqlplus> desc sample

where sample a tablename. This should list all the fieldnames with the datatypes.

let me know if this help. Else, we can try some other way. Post the entire query. What oracle error did you get, i.e. ORA-1521 etc.

 

[engineer2100]

WHY NOT TRY CDATE FUNCTION AND TEST THE SQL?

 

[Elysium]

The error that I get is exactly as follows:

[PLATINUM][ODBC][Server]Error preparing query for execution on <<Server>>. IW00003 501 Unsupported query syntax. . (IWODBC1008)

The code that I am running is exactly as follows:


Option Explicit

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sSQL As String
----------------------------------------
Private Sub Class_Initialize()

oConn.ConnectionString = &quot;Provider=MSDASQL.1;Persist Security Info=False;User ID=myID;Password=myPWD; Data Source=myDSN;Extended Properties='DSN=myDSN'&quot;

End Sub
----------------------------------------
Public Sub Download_NewStarts()

On Error GoTo ErrHandler

oConn.Open
oConn.CursorLocation = adUseClient

sSQL = &quot;SELECT Account_Detail.Account_Num &quot; & _
&quot;FROM Account_Detail, Organization &quot; & _
&quot;WHERE (Account_Detail.Account_Start_Date >= to_date('11/03/2002','mm/dd/yyyy') AND Account_Detail.Account_Start_Date <= to_date('11/09/2002','mm/dd/yyyy')) AND (Organization.Region_Num='04') AND (Organization.District_Num='74');&quot;
oRS.Open sSQL, oConn, adOpenKeyset, adLockOptimistic

Do Until oRS.EOF
MsgBox oRS(0)
oRS.MoveNext
Loop

oConn.Close
Exit Sub

ErrHandler:
MsgBox Err.Description

End Sub
--------------------------------------


The loop showing the msgbox is just a test for me to see if the code works. Nothing more.

Elysium

 

[johnwm]

Try putting a Debug.Print sSQL immediately before you open the recordset. Read through the constucted SQl carefully, looking for missing spaces, spare 's etc.
If the answer doesn't appear obvious, post the result of that debug.print so qwe can look at it
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[maxpower1]

1. Are you using Oracle ODBC driver for the DSN or MS ODBC driver for Oracle?

2. DO this:-

Set a break point on line oRs.Open and run the program. I am interested in seeing the value in sSQL. OPen up an intermediate window(from the View dropdown menu) and type this in there (don't forget the question mark):-

?sSQl


hit return and you should see the actual query formed by the sSQl. Cut the query and paste it in SQL*Plus or what have you. Run it and see if you get any errors. If you do, paste the result from sSQL on this site and let me see it.

 

[GFrantsen]

Hmmm...seems to me we had to put # around our dates to get them to work i.e. #11/18/2002# or something like that.
It's been a while.
later.
greg.

 

[Elysium]

Here's the SQL from the debug window ... didn't run:

SELECT Account_Detail.Account_Num FROM Account_Detail, Organization WHERE (Account_Detail.Account_Start_Date >= to_date('11/03/2002','mm/dd/yyyy') AND Account_Detail.Account_Start_Date <= to_date('11/09/2002','mm/dd/yyyy')) AND (Organization.Region_Num='04') AND (Organization.District_Num='74');

 

[mattKnight]

Pls for give my ignorance... I am very to oracle

Is teh format field for the to-date function correct?

I think the format field might be 'mm/dd/rr'

Give it a try, but let me know if I am wrong (and preferably why)

Matt

 

[maxpower1]

Matt,
1. TO_DATE function varies in its usage. if you use:-
Start_Date = TO_DATE('01/01/01', 'MM/DD/YY'). If you
2001 in the clause, then it will error out since the
format is expecting 2 chars(YY). So it is doable.

2. Back to the problem, I mean opportunity.
Did you cut and paste that query(from the debug window) and run it in sql plus or sql worksheet? what error did you get? My point is if you can run it in Oracle SQL*Plus without any problems, then there is something in VB that is being overlooked.

3. Again, are you using Oracle ODBC Driver (go to ODBC setup under COntrol Panel) or some other ODBC Driver Like Intersolv etc.

4. If you still get an error, while you're in Oracle Sql*Plus or Sql Worksheet, do this:-

sql>Desc Organization

this will give all the datatypes for the fields.

then, same thing, do this:

sql) Desc Account_Detail

 

[johnwm]

I'm not up on Oracle, but do you need some JOIN statement to link the two tables?
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[maxpower1]

I think you're right! Part of the problem is there is no join statement in the sql! Elysium, you need to join those 2 tables with a common field. Alos, don't forget to run the sql that you cut in vb and run it in sql plus directly.

 

[Elysium]

I can't make an explicit join due to the rules in the data warehouse. All of the relationships are defined on the server, so I wouldn't even know what fields to join on.

 

[maxpower1]

it doesn't make sense to have all tables that 'stand alone' in the database by themselves; one or two maybe. You may want to get a schema from your dba to see the table layout in the database. Even if these two tables cannot be joined, there has to be a third table that links them together. Without the join, there is no way you can get any output.

good luck