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\POPay Hist.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.Jo in(Control Chars.Tab, header))
Try
myReader = myCommand.ExecuteReader()
Catch ex As OdbcException
MsgBox(ex.Message)
End Try
Pervasive-error.doc
If System.IO.File.Exists(FILE
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
Dim objStreamWriter6 As StreamWriter
objStreamWriter6 = New StreamWriter("C:\Rolligon\
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
Try
myReader = myCommand.ExecuteReader()
Catch ex As OdbcException
MsgBox(ex.Message)
End Try
Pervasive-error.doc
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.
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...
You'll see what's wrong when you print the SQL query...
ASKER
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?
" 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(LastMon th)
Dim LastMonth As Date = DateAdd(DateInterval.Day, -30, Date.Today)
The below doesn't work....
Convert.ToDateTime(LastMon
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.
"(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.
ASKER
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?
http://www.w3schools.com/VBscript/vbscript_ref_functions.asp
Maybe there's another alternative?
ASKER
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.Date AndTime.Da y(LastMont h) & "' )
Thanks!
'" & Year(LastMonth) & "-" & Month(LastMonth) & "-" & Microsoft.VisualBasic.Date
Thanks!
ASKER
Great help always!