• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1423
  • Last Modified:

Teradata date search fail

The following macro returns my page correctly:
exec fshe_dept_db.Meetings (date '2011-04-18');

When I modify it to search for other dates I get an error. This is my code:
exec fshe_dept_db.Meetings (date ''%" & Request.Form("MyReport_Year") & "%'-'%" & Request.Form("MyReport_Month") & "%'-'%" & Request.Form("MyReport_Day") & "%'');

The error message is the following:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Teradata][ODBC Teradata Driver][Teradata Database] '%' ('25'X) is not a valid Teradata SQL token.

I'd really appreciate your help. Thank you
0
baxtalo
Asked:
baxtalo
  • 5
  • 5
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you debug it by doing the following?

str = "(date ''%" & Request.Form("MyReport_Year") & "%'-'%" & Request.Form("MyReport_Month") & "%'-'%" & Request.Form("MyReport_Day") & "%'');
Response.Write str

then try to run it directly with the output?
0
 
baxtaloAuthor Commented:
I must be doing something wrong because I get this error message:
Unterminated string constant

Where exactly do I have to put this on my page?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try this right before the exec line of code:

str = "'%" & Request.Form("MyReport_Year") & "%'-'%" & Request.Form("MyReport_Month") & "%'-'%" & Request.Form("MyReport_Day") & "%''
Response.Write str
Response.End

then when you go to run it directly, do:

exec fshe_dept_db.Meetings (date " <output of str> ");
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
baxtaloAuthor Commented:
I was trying to put your code everywhere possible on my page, but unfortunately it always gives me an error message:
"Unterminated String Constant"

This is what I'm doing
<%
Dim rs4
Set rs4 = Server.CreateObject("ADODB.Recordset")
rs4.ActiveConnection = conn

str = "'%" & Request.Form("MyReport_Year") & "%'-'%" & Request.Form("MyReport_Month") & "%'-'%" & Request.Form("MyReport_Day") & "%''
Response.Write str
Response.End

rs4.Source = "exec fshe_dept_db.EWR_Enplanements (date '<output of str>');"
rs4.Open
%>

Open in new window

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try this:

<%
Dim rs4
Set rs4 = Server.CreateObject("ADODB.Recordset")
rs4.ActiveConnection = conn

str = "'%" & Request.Form("MyReport_Year") & "%'-'%" & Request.Form("MyReport_Month") & "%'-'%" & Request.Form("MyReport_Day") & "%'"
Response.Write str
Response.End

rs4.Source = "exec fshe_dept_db.EWR_Enplanements (date '<output of str>');"
rs4.Open
%>

this'll output something to the screen, can you report back what it is?
0
 
baxtaloAuthor Commented:
This is what it returns:

'%2010%'-'%12%'-'%29%'

0
 
baxtaloAuthor Commented:
I got rid of the % and of the '

The code looks like this now:
<%
Dim rs4
Set rs4 = Server.CreateObject("ADODB.Recordset")
rs4.ActiveConnection = conn

str = "" & Request.Form("MyReport_Year") & "-" & Request.Form("MyReport_Month") & "-" & Request.Form("MyReport_Day") & ""
Response.Write str
Response.End

rs4.Source = "exec fshe_dept_db.EWR_Enplanements (date '<output of str>');"
rs4.Open
%>

Open in new window

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
good, thats what we want, now change the original code to:

<%
Dim rs4
Set rs4 = Server.CreateObject("ADODB.Recordset")
rs4.ActiveConnection = conn

strDate = Request.Form("MyReport_Year") & "-" & Request.Form("MyReport_Month") & "-" & Request.Form("MyReport_Day") & ""

rs4.Source = "exec fshe_dept_db.EWR_Enplanements (date '" & strDate & "');"
rs4.Open
%>

I renamed str to strDate just to make it more obvious what it is
0
 
baxtaloAuthor Commented:
Thank you very much, now it's perfect. I've been struggling with this since yesterday. I learned a lot, thank you....
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
glad to help :) keep in mind that technique of the Response.Write function that will output the problem variable.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now