Link to home
Start Free TrialLog in
Avatar of stephaniem712
stephaniem712

asked on

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

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
Avatar of Mirtheil
Mirtheil
Flag of United States of America image

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.  
Avatar of stephaniem712
stephaniem712

ASKER

The first select query where I use the LastMonth variable.
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...
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?
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'} "
ASKER CERTIFIED SOLUTION
Avatar of Bill Bach
Bill Bach
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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)
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.
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..
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?
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!
Great help always!