Querying a Paradox Database with ADO

Hello,

I have connected sucessfully to a Paradox Database using both a System DSN and a Microsoft Access linked table setup.

I am trying to query table ACTIVITY, with a criteria for a specific date. The query is quite simple;

   SELECT *
   FROM ACTIVITY
   WHERE ACTV_BEGIN_DATE = #27-Aug-2003#

The table has records dating from the 26/08/2003 and onwards. The query returns rows where ACTV_BEGIN_DATE is equal to the 26th or the 27th. If I then change the query to;

   WHERE ACTV_BEGIN_DATE = #28-Aug-2003#

It then returns rows for the 26th, 27th, and 28th. This occurs regardless of the access method.

I have simplified the query slightly to try and illustrate my problem. The final query will be specific regarding selection of fields, and will use a similar WHERE clause to the one below;

   WHERE ACTV_BEGIN_DATE BETWEEN #1-Aug-2003# AND #31-Aug-2003#

Thanks,

Jason.
LVL 1
JagataAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MYLimCommented:
make sure your connection string have no problem and
Paradox 7 doesn't include ODBC drivers, upgrade to version 8 or 9. Or by the ODBC driver from http://www.intersolv.com 

after you have try above and still have problem,then pls try to format your date.Also make sure you understand your Regional setting format.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

JagataAuthor Commented:
MYLim,

Thankyou for your reply. However the problem has nothing to do with the connection to the Paradox database, I have quite successfully connected to the database using both a System DSN, and a Access Linked Table setup. I am querying all tables within the database with no issues.

I do not believe the date formating is incorrect, however to be sure I have also tried the following with the same results;

   dd/mm/yy
   dd/mm/yyyy
   mm/dd/yy
   mm/dd/yyyy
   dd-mmm-yyyy

The last format should never pose any issues as the system cannot confuse the order of the day and month.

Jason.
0
MYLimCommented:
Pls read this page:
http://www.elists.org/pipermail/delphi-db/2003-January/006570.html

Are you sure Paradox date quote by # ?
0
JagataAuthor Commented:
I have tried the following based on the Paradox date formating (error follows WHERE clause);

   WHERE ACTV_BEGIN_DATE = "27.08.2003"
   Coded as; "WHERE ACTV_BEGIN_DATE = " & Chr(34) & "27.08.2003" & Chr(34)

      [Microsoft][ODBC Paradox Driver] " is not a valid name. Make sure it does not include invalid characters or punctuation and that it is
      not too long.

   WHERE ACTV_BEGIN_DATE = "8/27/2003"
   Coded as; "WHERE ACTV_BEGIN_DATE = " & Chr(34) & "8/27/2003" & Chr(34)

      [Microsoft][ODBC Paradox Driver] Too few parameters. Expected 1.

   WHERE ACTV_BEGIN_DATE = #27.08.2003#

      [Microsoft][ODBC Paradox Driver] Syntax error in date in query expression 'ACTV_BEGIN_DATE = #27.08.2003#'.


Other than the above I have tried all other formating.

I know it has to be some kind of issue between Paradox & ADO, but I am at a loss as to what else to try. I do appreciate your help MYLim. Thanks.
0
MYLimCommented:
Pls try the combination below...
1.
WHERE ACTV_BEGIN_DATE >= #1-Aug-2003#
AND ACTV_BEGIN_DATE <= #31-Aug-2003#

2.
WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'

3.
WHERE ACTV_BEGIN_DATE BETWEEN '1-Aug-2003' AND '31-Aug-2003'

4.WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
AND ACTV_BEGIN_DATE <= '31-Aug-2003'
0
JagataAuthor Commented:
1.

   WHERE ACTV_BEGIN_DATE >= #1-Aug-2003#
   AND ACTV_BEGIN_DATE <= #31-Aug-2003#

      Query returned correct results with rows dating (ACTV_BEGIN_DATE) from 26/08/2003 to 31/08/2003. Please note; there are no
      records before 26/08/2003.

   WHERE ACTV_BEGIN_DATE >= #1-Sep-2003#
   AND ACTV_BEGIN_DATE <= #30-Sep-2003#

      Query returned incorrect results with rows dating from 26/08/2003 to 21/08/2003.


2.

   WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
   AND ACTV_BEGIN_DATE <= '31-Aug-2003'

      Run-time error '-2147217913 (80040e07)':
      [Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.


3.

   WHERE ACTV_BEGIN_DATE BETWEEN '1-Aug-2003' AND '31-Aug-2003'

      Run-time error '-2147217913 (80040e07)':
      [Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.


4.

   WHERE ACTV_BEGIN_DATE >= '1-Aug-2003'
   AND ACTV_BEGIN_DATE <= '31-Aug-2003'

      Run-time error '-2147217913 (80040e07)':
      [Microsoft][ODBC Paradox Driver] Data type mismatch in criteria expression.


It basically seems to understand the upperbound of any criteria, i.e. = or < or BETWEEN, but any criteria if the syntax is correct returns rows below the lower bound.
0
JagataAuthor Commented:
In above message;
   Query returned incorrect results with rows dating from 26/08/2003 to 21/08/2003

Should be;
   Query returned incorrect results with rows dating from 26/08/2003 to 30/09/2003
0
JagataAuthor Commented:
I just tried;

   WHERE (ACTV_BEGIN_DATE >= #1-Sep-2003#
   AND ACTV_BEGIN_DATE <= #10-Sep-2003#)

   The query returned records before 1/09/2003.
0
MYLimCommented:
Pls post your Date setting in Regional Setting.
0
JagataAuthor Commented:
Short date sample: 4/11/2003
Short date format: d/MM/yyyy
0
MYLimCommented:
Hi ! try the code below :
Dim MySDate,MyEDate as date
Dim SDate,EDate as string

SDate='1-Sep-2003'
EDate='10-Sep-2003'

if isdate(sdate)=true then
   mysdate=format(sdate,"mm/dd,yyyy")
else
  msgbox "Invalid Start date"
end if

if isdate(edate)=true then
   myedate=format(edate,"mm/dd/yyyy")
else
  msgbox "Invalid Ending date"
end if



"select * from activity WHERE (ACTV_BEGIN_DATE >= #" & mysdate & "# " _
&  AND ACTV_BEGIN_DATE <= #" & myedate & "#) "

0
MYLimCommented:
Still Cannot ? Pls try the following:
change your regional setting to mm/dd/yyyy
and try again...

before you change regional setting,Pls read follow:
http://www.4guysfromrolla.com/webtech/041001-1.2.shtml

Go for Launch :
q(^ _ ^)p
0
JagataAuthor Commented:
Sorry was in a meeting. This is the code I used, system still returned 26-Aug to 10-Sep.

    Dim MySDate, MyEDate As Date
    Dim SDate, EDate As String
   
    SDate = "1-Sep-2003"
    EDate = "10-Sep-2003"
   
    If IsDate(SDate) = True Then
        MySDate = Format(SDate, "mm/dd,yyyy")
    Else
        MsgBox "Invalid Start date"
    End If
   
    If IsDate(EDate) = True Then
        MyEDate = Format(EDate, "mm/dd,yyyy")
    Else
        MsgBox "Invalid Start date"
    End If

    Call ConnectActivity("SELECT * " & _
                         "FROM ACTIVITY " & _
                         "WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
                         "AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
    MsgBox rstActivity.RecordCount
    Set dgActivity.DataSource = rstActivity
0
JagataAuthor Commented:
I changed the date format in regional settings to MM/dd/yyyy and ran the above code with the same result (returned 26-Aug to 10-Sep). I do not think that the regional settings will effect the query criteria as we are passing the date format in dd-MMM-yyyy which cannot be confused by the query engine.
0
MYLimCommented:
wat is this "Call ConnectActivity" ?
a function ?
0
JagataAuthor Commented:
Yes, they call some procedures in my module modDatabase. These are the two that are applicable;

Public Sub ConnectDatabase()

    Set adoConnection = New ADODB.Connection
    With adoConnection
        .ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=dsnTripmaster"
        .Open
    End With

End Sub

Public Sub ConnectActivity(ByVal pstrQuery As String)

    Set rstActivity = New ADODB.Recordset
    With rstActivity
        .ActiveConnection = adoConnection
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = pstrQuery
        .Open
    End With

End Sub
0
JagataAuthor Commented:
In the ConnectDatabase() Procedure; the following ConnectionString also works;
   "Driver={Microsoft Paradox Driver (*.db )};DriverID=538;Fil=Paradox 5.X;DefaultDir=C:\INFOTRAX\Db\;Dbq=C:\INFOTRAX\Db\;CollatingSequence=ASCII"
0
MYLimCommented:
how about your field type ? Date or string ?
0
JagataAuthor Commented:
It is a Date field type.
0
MYLimCommented:
Change below connection:
Public Sub ConnectActivity(ByVal pstrQuery As String)
    Set rstActivity = New ADODB.Recordset
    With rstActivity
        .ActiveConnection = adoConnection
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic 'change
        .LockType = adLockReadOnly 'change
        .Source = pstrQuery
        .Open
    End With
End Sub

And  execute this command:

Set rstActivity=adoConnection.Execute("SELECT * " & _
                         "FROM ACTIVITY " & _
                         "WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
                         "AND ACTV_BEGIN_DATE <= #" & MyEDate & "#)  order by actv_begin_date")

Set dgActivity.DataSource = rstActivity
0
JagataAuthor Commented:
I have executed this (returned no records);

    Dim prstActivity As New ADODB.Recordset

    Dim MySDate, MyEDate As Date
    Dim SDate, EDate As String
   
    SDate = "1-Sep-2003"
    EDate = "10-Sep-2003"
   
    If IsDate(SDate) = True Then
        MySDate = Format(SDate, "mm/dd,yyyy")
    Else
        MsgBox "Invalid Start date"
    End If
   
    If IsDate(EDate) = True Then
        MyEDate = Format(EDate, "mm/dd,yyyy")
    Else
        MsgBox "Invalid Start date"
    End If

    Set prstActivity = adoConnection.Execute("SELECT * " & _
                                             "FROM ACTIVITY " & _
                                             "WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
                                             "AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
    Set dgActivity.DataSource = prstActivity
0
JagataAuthor Commented:
I have also executed this, with requested changes to ConnectActivity() (which return records from 26-Aug to 10-Sep);

    Dim MySDate, MyEDate As Date
    Dim SDate, EDate As String
   
    SDate = "1-Sep-2003"
    EDate = "10-Sep-2003"
   
    If IsDate(SDate) = True Then
        MySDate = Format(SDate, "mm/dd,yyyy")
    Else
        MsgBox "Invalid Start date"
    End If
   
    If IsDate(EDate) = True Then
        MyEDate = Format(EDate, "mm/dd,yyyy")
    Else
        MsgBox "Invalid Start date"
    End If

    Call ConnectActivity("SELECT * " & _
                         "FROM ACTIVITY " & _
                         "WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
                         "AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")
    MsgBox rstActivity.RecordCount
    Set dgActivity.DataSource = rstActivity
0
MYLimCommented:
Did you try change this :
  SDate = "1-9-2003"
  EDate = "10-9-2003"
0
JagataAuthor Commented:
With the above changes, the query returns 26-Aug to Sep-10.
0
MYLimCommented:
Getting Give Up :)
0
JagataAuthor Commented:
Yeah I know, this is such a crazy problem. Thanks for your help MYLim. :)
0
MYLimCommented:
1.Reserved work
("SELECT * " & _
                         "FROM [ACTIVITY] " & _
                         "WHERE (ACTV_BEGIN_DATE >= #" & MySDate & "# " & _
                         "AND ACTV_BEGIN_DATE <= #" & MyEDate & "#) ")


2.y2k
http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_20308204.html?query=paradox+date+problem&searchType=all

3.reporting tools
Try to Use reporting tool such as Crystal report to select your data.Is output correct ?
0
MYLimCommented:
Accepted Answer from supunr
Date: 06/29/2003 07:47PM PDT  Accepted Answer  

Private Sub cmdSearch_Click()
    If rs.State = adStateOpen Then rs.Close
   
    If From.Value = "" Or dtto.Value = "" Then
        MsgBox "Please Enter valid dates ", vbCritical, "mbc"
        Exit Sub
    Else
    If Not IsDate(From.Value) Then
        MsgBox "Please enter a valid date in it", vbCritical, "mbc"
        Exit Sub
    ElseIf Not IsDate(dtto.Value) Then
        MsgBox "Please enter a valid date in it", vbCritical, "mbc"
        Exit Sub
    End If
   
    rs.Open "select *, (Format([date], ""dd/mmm/yyyy"")) as FormattedDate from movies where Val(Format([date],""YYYYMMDD""))>=" & Val(Format(CDate(From.Value), "YYYYMMDD") & " And Val(Format([date],""YYYYMMDD""))<=" & Val(Format(CDate(dtto.Value), "YYYYMMDD"))), CN
    'rs.Open "select *, (Format([date], ""dd/mmm/yyyy"")) as FormattedDate from movies"
    ' if first does not work try commenting it out and try the second
    If rs.RecordCount > 0 Then rs.MoveFirst
    ' press Ctrl+G before you run the code (or goto View->Immediate window) and this is the
    ' debug window. when you type debug.print, it prints to this immediate window
    ' similar to print, but print will print to the form itself.
    Do While Not rs.EOF
      Debug.Print rs.Fields("FormattedDate").Value
      rs.MoveNext
    Loop
   
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Set DataGrid1.DataSource = rs
    Else
        MsgBox "Could not found Attendance between" & "  " & From.Value & " and " & dtto.Value, vbCritical, "mbc"
    End If
End Sub
 
0
MYLimCommented:
do you have the latest MDAC install in your computer (ADO 2.7+) aslo make sure you have the latest jet servicepack installed as well.  (Jet SP5)
0
JagataAuthor Commented:
Hey MYLim, thanks for not giving up. I will check over all your comments when I get to work in the morning. :)
0
MYLimCommented:
Take a look :)
http://oldlook.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20413021.html?query=%22date+problem%22&searchType=all

http://www.experts-exchange.com/Databases/MS_Access/Q_20686807.html

http://www.experts-exchange.com/Databases/MS_Access/Q_10191629.html?query=%22date+problem%22&searchType=all

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20100058.html?query=%22date+problem%22&searchType=all


After that Pls check this, and configure it all as the format you want, be sure the first two of them to be the same:
1.  Configure Date format in Access
2.  Configure Date format on Control Panel, (the icon: PLANET EARTH, in Spanish        Configuracion Regional) to be the same as you want
3.  In your SQL query when you work with dates put this function DATE VALUE( date field) (remember it is part of the SQL query, not of VB code).

How about the look of date field inside your database.Pls post the format...
0
JagataAuthor Commented:
MyLim, thanks again for your help. However, the problem I am facing here has nothing to do with the date format. I am using the format dd-MMM-yyyy, cannot be misinterpreted by SQL. The regional settings will not affect it.

Jason.
0
MYLimCommented:
totally give up :)
0
JagataAuthor Commented:
I am thinking I will need to use DTS in SQL Server to import the data and then work with it.
0
Computer101Commented:
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.