• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1459
  • 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
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

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