Last post Jun 06, 2012 11:17 PM by srelliott
May 09, 2012 06:47 PM|srelliott|LINK
I'm trying to connect to a progress database with asp.net (c#), but I'm getting an error. It looks like it's related to the way I'm using "hour" in the SELECT string, so I'm not sure if it's just not recognized by the Progress db or what?
Here's my DataSource code:
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
SelectCommand="SELECT SUM(Qty) as Total From Productionhistory WHERE Operation='Mold 1' AND (Shift = case when DATEPART(hour, GetDate()) < 7 then 3 when DATEPART(hour, GetDate()) < 15 then 1 when DATEPART(hour, GetDate()) < 23 then 2 else 3 end) AND (Date = case when DATEPART(hour, GetDate()) = 23 then convert(varchar(10),DATEADD(day,1,GetDate()),101) else convert(varchar(10),getdate(),101) end)"
ConnectionString="<%$ ConnectionStrings:blablabla %>"
ProviderName="<%$ ConnectionStrings:blablabla.ProviderName %>" >
And here's the error I'm getting:
ERROR[42S22][DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OpenEdge]Column "HOUR" cannot be found or is not specified for query. (13865)
May 10, 2012 07:43 PM|dahla|LINK
You are most likely getting the error because hour is a
reserved keyword, so try writing [hour] instead when you need to reference the column
May 11, 2012 06:36 PM|srelliott|LINK
Thanks, I'll try this! I was also having trouble with "Date" in a very simple test string, but it worked fine when I used *, so I'm guessing this may also be the problem with that too. I'll try it out when I get back into their system next week and post
back here with an update.
May 16, 2012 01:23 AM|srelliott|LINK
Actually, it looks like GetDate(), DATEPART, CONVERT, DATEADD (and maybe a few others) must not work with a Progress database. I think it's just not recognizing them. If anyone is familiar with connecting to a Progress database from a SELECT string within
an asp.net page, please let me know if this SELECT string is correct...or if you see anything wrong with it.
SELECT SUM(Qty) as Total From Productionhistory WHERE Operation='MOLD 1' AND (Shift = case when TO_CHAR(CURTIME(), 'HH24:MM') < '06:30' then 3 when TO_CHAR(CURTIME(), 'HH24:MM') < '15:00' then 1 when TO_CHAR(CURTIME(), 'HH24:MM') < '23:30' then 2 else 3 end)
I still need to end it with adding a day if the time is between 11:30pm and 12:00am (since 3rd shift starts at 11:30pm and uses the next day for that 30 minutes), by converting the end of my original code (first post) so a Progess database can understand
it, but I want to make sure I'm on the right track first and the rest of this select string is formatted correctly.
Jun 06, 2012 11:17 PM|srelliott|LINK
Anyone in here familiar with Progress databases enough to be able to tell me how to properly convert this SQL string to work with a progress database?