Solved

Querying a Paradox Database with ADO

Posted on 2003-11-03
39
1,720 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:Jagata
  • 19
  • 18
39 Comments
 
LVL 8

Expert Comment

by:MYLim
ID: 9675551
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9675571
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9675609
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
 
LVL 1

Author Comment

by:Jagata
ID: 9675620
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9675664
Pls read this page:
http://www.elists.org/pipermail/delphi-db/2003-January/006570.html

Are you sure Paradox date quote by # ?
0
 
LVL 1

Author Comment

by:Jagata
ID: 9675729
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9675784
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
 
LVL 1

Author Comment

by:Jagata
ID: 9675865
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
 
LVL 1

Author Comment

by:Jagata
ID: 9675868
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
 
LVL 1

Author Comment

by:Jagata
ID: 9675883
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9675913
Pls post your Date setting in Regional Setting.
0
 
LVL 1

Author Comment

by:Jagata
ID: 9675926
Short date sample: 4/11/2003
Short date format: d/MM/yyyy
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9676055
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9676076
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
 
LVL 1

Author Comment

by:Jagata
ID: 9676232
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
 
LVL 1

Author Comment

by:Jagata
ID: 9676266
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9676325
wat is this "Call ConnectActivity" ?
a function ?
0
 
LVL 1

Author Comment

by:Jagata
ID: 9676361
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
 
LVL 1

Author Comment

by:Jagata
ID: 9676366
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 8

Expert Comment

by:MYLim
ID: 9676390
how about your field type ? Date or string ?
0
 
LVL 1

Author Comment

by:Jagata
ID: 9676396
It is a Date field type.
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9676451
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
 
LVL 1

Author Comment

by:Jagata
ID: 9676470
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
 
LVL 1

Author Comment

by:Jagata
ID: 9676485
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9676507
Did you try change this :
  SDate = "1-9-2003"
  EDate = "10-9-2003"
0
 
LVL 1

Author Comment

by:Jagata
ID: 9676550
With the above changes, the query returns 26-Aug to Sep-10.
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9676649
Getting Give Up :)
0
 
LVL 1

Author Comment

by:Jagata
ID: 9676651
Yeah I know, this is such a crazy problem. Thanks for your help MYLim. :)
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9677073
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9677418
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9677432
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9677443
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
 
LVL 1

Author Comment

by:Jagata
ID: 9678546
Hey MYLim, thanks for not giving up. I will check over all your comments when I get to work in the morning. :)
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9685604
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
 
LVL 1

Author Comment

by:Jagata
ID: 9698462
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
 
LVL 8

Expert Comment

by:MYLim
ID: 9698717
totally give up :)
0
 
LVL 1

Author Comment

by:Jagata
ID: 9711375
I am thinking I will need to use DTS in SQL Server to import the data and then work with it.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 9754150
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now