Toublesome Date!

[RFH Reseller: s80wkr]

<%
Session.LCID=2057
%>
<!--#include file="Connections/connUs.asp" -->
<%
Dim rsOnline__MMColParam
rsOnline__MMColParam = "W"
If (Request("MM_EmptyValue") <> "") Then
rsOnline__MMColParam = Request("MM_EmptyValue")
End If
%>
<%
Dim rsOnline__MMColParam2
rsOnline__MMColParam2 = "3"
If (Request.QueryString("status") <> "") Then
rsOnline__MMColParam2 = Request.QueryString("status")
End If
%>
<%
Dim rsOnline__MMColParam3
rsOnline__MMColParam3 = "0"
If (Request("MM_EmptyValue") <> "") Then
rsOnline__MMColParam3 = Request("MM_EmptyValue")
End If
%>
<%
Dim rsOnline__var
rsOnline__var = "01/11/2005"
If (Request.QueryString("txtDate") <> "") Then
rsOnline__var = Request.QueryString("txtDate")
End If
%>
<%
Dim rsOnline
Dim rsOnline_numRows
Set rsOnline = Server.CreateObject("ADODB.Recordset")
rsOnline.ActiveConnection = MM_connUs_STRING
rsOnline.Source = "SELECT IONO, DESPNO, SALESVALUE, DELYADDR, UNISTRIP.ORH.CUSTCODE, STATUS, ""DATE"", DATEINPUT, UNISTRIP.CUS.CUSTCODE, NAME, UNISTRIP.CUS.LEDGER FROM UNISTRIP.ORH, UNISTRIP.CUS WHERE UNISTRIP.CUS.CUSTCODE = UNISTRIP.ORH.CUSTCODE AND UNISTRIP.ORH.LEDGER = '" + Replace(rsOnline__MMColParam, "'", "''") + "' AND UNISTRIP.CUS.LEDGER = '" + Replace(rsOnline__MMColParam, "'", "''") + "' AND STATUS = " + Replace(rsOnline__MMColParam2, "'", "''") + " AND SALESVALUE > '" + Replace(rsOnline__MMColParam3, "'", "''") + "' AND ""DATE"" < #" + Replace(rsOnline__var, "'", "''") + "# ORDER BY ""DATE"", UNISTRIP.ORH.CUSTCODE, IONO"
rsOnline.CursorType = 0
rsOnline.CursorLocation = 2
rsOnline.LockType = 1
rsOnline.Open()
rsOnline_numRows = 0
%>

The above works except for the DATE < var

When the code above is run it generates a: -

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Easysoft ODBC]Invalid datetime format
/unistrip/online.asp, line 52

I have tried entering the date forwards, backwards and inside out!

Dates make me :crying:

[Staff: Warren Ashcroft]

Type specifying the date in a YYYY-MM-DD format.

[RFH Reseller: s80wkr]

I left the code as is except for: -

<%
Dim rsOnline__var
rsOnline__var = "2005-11-01"
If (Request.QueryString("txtDate") <> "") Then
rsOnline__var = Request.QueryString("txtDate")
End If
%>

And got this: -

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Easysoft ODBC]Syntax error or access violation "parse error near '#'"
/unistrip/online.asp, line 52

[Staff: Warren Ashcroft]

Is there anything in the URL for txtDate?

[RFH Reseller: s80wkr]

It doesn't matter as it should default to 2005-11-01 but I have tried loading the page with and without it and get the same error

[Staff: Warren Ashcroft]

It doesn't matter as it should default to 2005-11-01 but I have tried loading the page with and without it and get the same error

I know - but what I was saying is that if there WAS something in the URL, it would override the value you are playing with.

[RFH Reseller: s80wkr]

Sorry,

No I have been leaving txtDate out of the URL

[RFH Reseller: Sol]

You have this in your code currently:

""DATE"" < #" + Replace(rsOnline__var, "'", "''") + "#

Try using this instead:

""DATE"" < '" + rsOnline__var + "'

Also rsOnline_var should be a date in the format Warren suggested ('YYYY-MM-DD')


I would also, highly suggest you look at using Parameterized queries.

[RFH Customer: JohnHorne]

I use JScript rather than VBScript, so if my comments miss the mark then I apologise. However, this looks similar to a JScript ptoblem I had, so here goes.

I suspect the your rsOnline_var is undefined. Can you check this ? You said that the URL does not set txtDate. I had a piece of JScript where I expected Request.Querystring to return nothing: instead, it returned undefined, which is not equal to null or an empty string. So the code tests the querystring, finds that is not equal to "" and, in your case, assigns an undefined value to rsOnline_var.

The solution is to code it as:
inputDate = IsBlank(Request.Querystring("txtDate"), "")
if (inputDate <> "") then
rsOnline_var = inputDate

At any rate, IsBlank is the function you would use in JScript - don't know if it is the same in VBScript.

Hope that helps
John

[RFH Reseller: s80wkr]

Thanks Sol that worked great!

Must look into Parameterized queries

Thanks everyone for your input