janetopps
11-22-2008, 09:22 PM
The code i'm including below is the code that worked in Access, but i've now moved to MySQL
This page 24hours.asp pulls out a record of articles posted in the last 24 hours. It updates automatically at midnight. The date the article is added to the database, is fldCREATED, the fldPOSTED can differ because you can add it now, and set it to post at a later date.
This page also contains a drop down menu, which contains the previous days. If you choose a previous day, the page changes to that date, and shows the 24 hour total articles for that day.
The code below shows this error :
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.45]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
24hours.asp, line 77
line 77 is highlighted in the code below, in Green colour and bold.
here is the code for the 24hours.asp, it includes the drop down menu too
<%
title = "title"
meta_description = "description"
%>
<!--#include file="inc_header.asp"-->
<!--#include file="inc_track_pages.asp"-->
<%PAGE_LOG("last24hours")%>
<% Dim SetDate, EndDate, QueryDate, CATEGORIES, POP_DROP
QueryDate = Request.QueryString("D")
If IsDate(QueryDate) THEN
QueryDate = CDate(QueryDate)
ELSe
QueryDate = Date()
END IF
SetDate = QueryDate
EndDate = QueryDate + 1
Call OPEN_DB()
SQL = "SELECT fldCREATED FROM nm_tbl_news GROUP BY fldCREATED ORDER BY fldCREATED DESC"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.LockType = 1
RS.CursorType = 0
RS.Open SQL, MyConn
WHILE NOT RS.EOF
blSEL = ""
If NOT Instr(POP_DROP, FormatDateTime(RS("fldCREATED"),2)) > 0 THEN
'IF Trim(QueryDate) = Trim(FormatDateTime(RS("fldCREATED"),2)) THEN blSEL = " selected"
POP_DROP = POP_DROP & "<option " & blSEL & " value='24hours.asp?D=" & FormatDateTime(RS("fldCREATED"),2) & "'>" & FormatDateTime(RS("fldCREATED"),2) & vbcrlf
END IF
RS.MoveNext
WEND
RS.Close
Set RS = Nothing
Dim DATE_F
DATE_F = GET_SETTINGS(False, "fldDATE_F")
%>
<script language="JavaScript" type="text/javascript">
function Jump(fe){
var opt_key = fe.selectedIndex;
var uri_val = fe.options[opt_key].value;
window.open(uri_val,'_top');
return true;
}
</script>
<table width="752" align="center" cellpadding="2" cellspacing="0" border="0">
<tr>
<td> <span style="font-size: 9px; font-style: italic">24 hour weblog for :<strong style="font-size: 12px;"> <%=QueryDate%></strong></span></th>
<tr> <td width="628" align="left" valign="top"><table width="592">
<tr>
<th width="237" align="left" scope="col"><img src="../images1/navig/todaysweblog.JPG" alt="todays weblog" width="150" height="21" align="top"></th>
</tr>
</table>
<span class="tdSUMMARY"> <table width="595" height="199" align="left" cellpadding="2" cellspacing="2">
<% Dim I
I = 1
SQL = "SELECT nm_tbl_news.ID AS `NID`, nm_tbl_news.fldSUMMARY AS `SUMMARY`, nm_tbl_news.fldIMAGE AS `NIMAGE`, nm_tbl_news.fldTITLE AS `TITLE`, nm_tbl_agent.fldNAME AS `ANAME`, nm_tbl_news.fldPOSTED AS `POSTED`, fldCREATED FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (fldCREATED > #" & SetDate & "# AND fldPOSTED < #" & EndDate & "# ) AND CURDATE() BETWEEN fldCREATED AND fldEXPIRES) ORDER BY nm_tbl_news.ID DESC"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.LockType = 1
RS.CursorType = 0
RS.Open SQL, MyConn
If RS.EOF Then
Response.Write "<br><br><br><br>nothing added today.<br><br>you can use the drop down menu to read previous days.<br><br> Thank you."
End If
WHILE NOT RS.EOF
NID = trim(RS("NID"))
SUMMARY = trim(RS("SUMMARY"))
IMAGE = trim(RS("NIMAGE"))
TITLE = trim(RS("TITLE"))
AUTHOR = trim(RS("ANAME"))
POSTED = trim(RS("POSTED"))
CREATED = trim(RS("fldCREATED"))
CATEGORIES = GET_CATES(NID)
%></span>
<%'IF Trim(FormatDateTime(POSTED,2)) = Trim(SetDate) THEN%>
<%IF I = 1 THEN%>
<tr>
<td width="585" height="98" align="left" valign="top" style="padding: 5px;"><a href="../page.asp?ID=<%=NID%>" class="NavigLNK"><%=TITLE%></a><br />
<span class="divPOSTEDON">Category: <%=CATEGORIES%></span><%END IF%> <br />
<%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%>
<img src="<%=IMAGE%>" width="70" height="80" vspace="5" border="0" align="left" />
<% END IF %> <%= FormatDateTime(POSTED,DATE_F) %> <%IF SHOW_AUTHOR = True THEN%>
[By: <%=AUTHOR%>]<br />
<span class="tdSUMMARY"><%= SUMMARY %> <br />
</span><br /><hr width="100%" size="1" style="color: gray ;height: 1px;" /> </td>
</tr>
<%ELSE%>
<tr>
<td width="585" align="left" valign="top" style="padding: 5px;; font-size: 12px"><div align="justify">
<%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%>
<img src="<%=IMAGE%>" width="50" height="50" border="1" />
<%ELSE%>
<%END IF%>
</div></td>
</tr>
<%END IF%>
<%'END IF%>
<%
I = I + 1
IF I > 1 THEN I = 1
RS.MoveNext
WEND
RS.Close
Set RS = Nothing
MyConn.Close
Set MyConn = Nothing
%>
</td> </tr></table>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<td width="150" valign="top">
<p><img src="../images1/navig/prevdays.JPG" alt="previousdays" width="150" height="21" vspace="4">
<select class="textbox" name="D" onChange="return Jump(this);" >
<option value=''>Select One ...<%=POP_DROP%>
</select>
</td>
</table>
the dates don't look as if they make sense , but they worked fine in Access, which means it's just the code which is at fault. Not the date formats, unless they're written wrong.
The code above was for access, and i need to change it to work in MySQL
I'm sure it's just some simple tweaking here and there......any tips appreciated.
This page 24hours.asp pulls out a record of articles posted in the last 24 hours. It updates automatically at midnight. The date the article is added to the database, is fldCREATED, the fldPOSTED can differ because you can add it now, and set it to post at a later date.
This page also contains a drop down menu, which contains the previous days. If you choose a previous day, the page changes to that date, and shows the 24 hour total articles for that day.
The code below shows this error :
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[MySQL][ODBC 3.51 Driver][mysqld-5.0.45]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
24hours.asp, line 77
line 77 is highlighted in the code below, in Green colour and bold.
here is the code for the 24hours.asp, it includes the drop down menu too
<%
title = "title"
meta_description = "description"
%>
<!--#include file="inc_header.asp"-->
<!--#include file="inc_track_pages.asp"-->
<%PAGE_LOG("last24hours")%>
<% Dim SetDate, EndDate, QueryDate, CATEGORIES, POP_DROP
QueryDate = Request.QueryString("D")
If IsDate(QueryDate) THEN
QueryDate = CDate(QueryDate)
ELSe
QueryDate = Date()
END IF
SetDate = QueryDate
EndDate = QueryDate + 1
Call OPEN_DB()
SQL = "SELECT fldCREATED FROM nm_tbl_news GROUP BY fldCREATED ORDER BY fldCREATED DESC"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.LockType = 1
RS.CursorType = 0
RS.Open SQL, MyConn
WHILE NOT RS.EOF
blSEL = ""
If NOT Instr(POP_DROP, FormatDateTime(RS("fldCREATED"),2)) > 0 THEN
'IF Trim(QueryDate) = Trim(FormatDateTime(RS("fldCREATED"),2)) THEN blSEL = " selected"
POP_DROP = POP_DROP & "<option " & blSEL & " value='24hours.asp?D=" & FormatDateTime(RS("fldCREATED"),2) & "'>" & FormatDateTime(RS("fldCREATED"),2) & vbcrlf
END IF
RS.MoveNext
WEND
RS.Close
Set RS = Nothing
Dim DATE_F
DATE_F = GET_SETTINGS(False, "fldDATE_F")
%>
<script language="JavaScript" type="text/javascript">
function Jump(fe){
var opt_key = fe.selectedIndex;
var uri_val = fe.options[opt_key].value;
window.open(uri_val,'_top');
return true;
}
</script>
<table width="752" align="center" cellpadding="2" cellspacing="0" border="0">
<tr>
<td> <span style="font-size: 9px; font-style: italic">24 hour weblog for :<strong style="font-size: 12px;"> <%=QueryDate%></strong></span></th>
<tr> <td width="628" align="left" valign="top"><table width="592">
<tr>
<th width="237" align="left" scope="col"><img src="../images1/navig/todaysweblog.JPG" alt="todays weblog" width="150" height="21" align="top"></th>
</tr>
</table>
<span class="tdSUMMARY"> <table width="595" height="199" align="left" cellpadding="2" cellspacing="2">
<% Dim I
I = 1
SQL = "SELECT nm_tbl_news.ID AS `NID`, nm_tbl_news.fldSUMMARY AS `SUMMARY`, nm_tbl_news.fldIMAGE AS `NIMAGE`, nm_tbl_news.fldTITLE AS `TITLE`, nm_tbl_agent.fldNAME AS `ANAME`, nm_tbl_news.fldPOSTED AS `POSTED`, fldCREATED FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (fldCREATED > #" & SetDate & "# AND fldPOSTED < #" & EndDate & "# ) AND CURDATE() BETWEEN fldCREATED AND fldEXPIRES) ORDER BY nm_tbl_news.ID DESC"
Set RS = Server.CreateObject("ADODB.Recordset")
RS.LockType = 1
RS.CursorType = 0
RS.Open SQL, MyConn
If RS.EOF Then
Response.Write "<br><br><br><br>nothing added today.<br><br>you can use the drop down menu to read previous days.<br><br> Thank you."
End If
WHILE NOT RS.EOF
NID = trim(RS("NID"))
SUMMARY = trim(RS("SUMMARY"))
IMAGE = trim(RS("NIMAGE"))
TITLE = trim(RS("TITLE"))
AUTHOR = trim(RS("ANAME"))
POSTED = trim(RS("POSTED"))
CREATED = trim(RS("fldCREATED"))
CATEGORIES = GET_CATES(NID)
%></span>
<%'IF Trim(FormatDateTime(POSTED,2)) = Trim(SetDate) THEN%>
<%IF I = 1 THEN%>
<tr>
<td width="585" height="98" align="left" valign="top" style="padding: 5px;"><a href="../page.asp?ID=<%=NID%>" class="NavigLNK"><%=TITLE%></a><br />
<span class="divPOSTEDON">Category: <%=CATEGORIES%></span><%END IF%> <br />
<%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%>
<img src="<%=IMAGE%>" width="70" height="80" vspace="5" border="0" align="left" />
<% END IF %> <%= FormatDateTime(POSTED,DATE_F) %> <%IF SHOW_AUTHOR = True THEN%>
[By: <%=AUTHOR%>]<br />
<span class="tdSUMMARY"><%= SUMMARY %> <br />
</span><br /><hr width="100%" size="1" style="color: gray ;height: 1px;" /> </td>
</tr>
<%ELSE%>
<tr>
<td width="585" align="left" valign="top" style="padding: 5px;; font-size: 12px"><div align="justify">
<%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%>
<img src="<%=IMAGE%>" width="50" height="50" border="1" />
<%ELSE%>
<%END IF%>
</div></td>
</tr>
<%END IF%>
<%'END IF%>
<%
I = I + 1
IF I > 1 THEN I = 1
RS.MoveNext
WEND
RS.Close
Set RS = Nothing
MyConn.Close
Set MyConn = Nothing
%>
</td> </tr></table>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<td width="150" valign="top">
<p><img src="../images1/navig/prevdays.JPG" alt="previousdays" width="150" height="21" vspace="4">
<select class="textbox" name="D" onChange="return Jump(this);" >
<option value=''>Select One ...<%=POP_DROP%>
</select>
</td>
</table>
the dates don't look as if they make sense , but they worked fine in Access, which means it's just the code which is at fault. Not the date formats, unless they're written wrong.
The code above was for access, and i need to change it to work in MySQL
I'm sure it's just some simple tweaking here and there......any tips appreciated.