Solved

Pervasive error when Using date add function and using result in query

Posted on 2008-06-25
12
1,515 Views
Last Modified: 2012-05-05
I have written a program that writes data to text files.  For one of the textfiles I need to check the date 30 days in the past and write the data results from that date forward.  The code I'm using is below and the error I receive is attached.  Any help is greatly appreciated!!  Thanks!!!

If System.IO.File.Exists(FILE_NAME) = True Then

            Dim LastMonth As Date = DateAdd(DateInterval.Day, -30, Date.Today)
            Convert.ToString(LastMonth)


            mySelectQuery6 = " "
            mySelectQuery6 = mySelectQuery6 & "SELECT DISTINCT APVendor.ID, CMCheck.CheckNo, CMCheck.CheckDate, CMCheck.Amount "
            mySelectQuery6 = mySelectQuery6 & "FROM APVendor INNER JOIN CMCheck ON APVendor.SKAPVendor = CMCheck.FKAPVendor "
            mySelectQuery6 = mySelectQuery6 & "WHERE(((CMCheck.Status) <> 2 And (CMCheck.Status) <> 34) AND (CMCheck.CheckDate > " & LastMonth & ")) "
            mySelectQuery6 = mySelectQuery6 & "ORDER BY CMCheck.CheckNo; "


        Else

            mySelectQuery6 = " "
            mySelectQuery6 = mySelectQuery6 & "SELECT DISTINCT APVendor.ID, CMCheck.CheckNo, CMCheck.CheckDate, CMCheck.Amount "
            mySelectQuery6 = mySelectQuery6 & "FROM APVendor INNER JOIN CMCheck ON APVendor.SKAPVendor = CMCheck.FKAPVendor "
            mySelectQuery6 = mySelectQuery6 & "WHERE(((CMCheck.Status) <> 2 And (CMCheck.Status) <> 34)) "
            mySelectQuery6 = mySelectQuery6 & "ORDER BY CMCheck.CheckNo; "

        End If
        'Pass the file path and the file name to the StreamWriter constructor.
        myCommand = New OdbcCommand(mySelectQuery6, myConnection)
        Dim objStreamWriter6 As StreamWriter
        objStreamWriter6 = New StreamWriter("C:\Rolligon\Data\POPayHist.txt")

        myConnection.Open()


        header(0) = "VendorID"
        header(1) = "ERP"
        header(2) = "CheckNo"
        header(3) = "CheckDate"
        header(4) = "CheckAmt"
        header(5) = " "
        header(6) = " "
        header(7) = " "
        header(8) = " "
        header(9) = " "
        header(10) = " "
        header(11) = " "
        header(12) = " "
        header(13) = " "
        header(14) = " "
        header(15) = " "
        header(16) = " "
        header(17) = " "
        header(18) = " "
        header(19) = " "
        header(20) = " "
        header(21) = " "
        header(22) = " "
        header(23) = " "
        header(24) = " "
        header(25) = " "
        header(26) = " "
        header(27) = " "
        header(28) = " "
        header(29) = " "
        header(30) = " "
        header(31) = " "
        header(32) = " "
        header(33) = " "
        header(34) = " "
        header(35) = " "
        header(36) = " "

        objStreamWriter6.WriteLine(String.Join(ControlChars.Tab, header))
        Try
            myReader = myCommand.ExecuteReader()
        Catch ex As OdbcException
            MsgBox(ex.Message)
        End Try
Pervasive-error.doc
0
Comment
Question by:stephaniem712
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 21868344
WHat is that actual query being sent to Pervasive when it fails?  Also, what version of PSQL are you using?  It could be a formatting issue of the date being passed in or the value of the date.  
0
 

Author Comment

by:stephaniem712
ID: 21868433
The first select query where I use the LastMonth variable.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21868619
It looks like the DATE variable LastMonth is not being formatted correctly, resulting  in the incompatible type error.  To troubleshoot this, please add a line to print out the entire SQL statement (mySelectQuery6) and see what it looks like.  If you are comparing the date against a date field, then you may need to force-format it as a date, i.e. " { d '2008-05-25'} ".

You'll see what's wrong when you print the SQL query...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:stephaniem712
ID: 21868829
Result:
" SELECT DISTINCT APVendor.ID, CMCheck.CheckNo, CMCheck.CheckDate, CMCheck.Amount FROM APVendor INNER JOIN CMCheck ON APVendor.SKAPVendor = CMCheck.FKAPVendor WHERE(((CMCheck.Status) <> 2 And (CMCheck.Status) <> 34) AND (CMCheck.CheckDate > 5/26/2008)) ORDER BY CMCheck.CheckNo; "

Is this what you're looking for?
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 21868954
Yep, that's the problem.  The PSQL date format needs to be either 'yyyy-mm-dd' (with the single quotes) like '2008-05-26' or the forced format date like " {d '2008-05-25'} "
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 500 total points
ID: 21869025
Exactly.  The text "5/26/2008" is being interpretted by the SQL parser as the numeric expression 5 / 26 / 2008, which is approximately 0.00009577.  It is then comparing a FLOAT with a DATE, resulting in your error.

One solution is to use the explicit DATE format, such as:  {d '2008-05-26'} instead.  You could also try to use the CONVERT function, but this will still require that you format the date string correctly.  Be sure to include the single quotes, too!
0
 

Author Comment

by:stephaniem712
ID: 21869314
How can I convert to the explicit date format?   I'll need to do it after this..

Dim LastMonth As Date = DateAdd(DateInterval.Day, -30, Date.Today)
          The below doesn't work....
            Convert.ToDateTime(LastMonth)
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21869392
Remember that with SQL statements, you are building strings to create the statement.

"(CMCheck.CheckDate > " & LastMonth & ")) "
will become something more like:

(CMCheck.CheckDate > {d '" & YEAR(LastMonth) & "-" & MONTH(LastMonth) & "-" & DAY(LastMonth) & "'} )) "

My VB is a bit rusty, so you may wish to make sure that the functions are correct.  As before, print the SQL statement to verify that it has proper syntax.
0
 

Author Comment

by:stephaniem712
ID: 21869706
The functions are correct but for some reason on the day function, it says that it's a "type'" and can't be used as an expression..
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21869763
VB functions should work similarly to VBScript, right?  I found those here:
    http://www.w3schools.com/VBscript/vbscript_ref_functions.asp
Maybe there's another alternative?
0
 

Author Comment

by:stephaniem712
ID: 21869891
You're right.  It should work.  But, I ended up having to do this to get it to work...

 '" & Year(LastMonth) & "-" & Month(LastMonth) & "-" & Microsoft.VisualBasic.DateAndTime.Day(LastMonth) & "' )

Thanks!
0
 

Author Closing Comment

by:stephaniem712
ID: 31470661
Great help always!
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

631 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