Solved

Querying a Paradox Database with ADO

Posted on 2003-11-03
39
1,722 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Determine Range to Select 5 42
Run code from text file in vb 1 56
VB6 - Compare and highlight cell not the same 3 42
VBA Shell can't Find Word document 11 74
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

914 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