Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ASP/MySQL SELECT and INSERTs do ot work.

Status
Not open for further replies.

peeechy3

Programmer
May 18, 2004
10
GB
I am completely new to using a MySQLDB but I'm surprised that this doesn't work. Basically, I'm selecting all records from my NEWS database table.

There are definately two rows of test data in the NEWS table so they should be fetched row by row, but without the loop on, nothing is returned. With the loop on, nothing is returned again, but it takes longer. This suggests to me that there is a looping issue but I have used this same syntax on many loops in VBScript and without the loop, nothing is returned.

Any ideas?

The Code:

<PRE>
<HTML>
<head>
<title></title>
</head>
<%
ON ERROR RESUME NEXT
DebugMode = "True"

TodaysDate=Date()
TodaysDate_D=Day(TodaysDate)
TodaysDate_M=Month(TodaysDate)
TodaysDate_Y=Year(TodaysDate)

SQL=" SELECT TITLE, PUB_DATE, PARA1, PARA2, PARA3, PARA4, PARA5, PARA6, PARA7, PARA8 "
SQL=SQL &" FROM NEWS "
SQL=SQL &" WHERE PUB_DATE >= '" & TodaysDate & "' "

Set rs = Server.CreateObject("ADODB.RecordSet")
rs.ActiveConnection = "Driver={MySQL}; DATABASE=Blah; UID=Blah; PASSWORD=Blah; Server=Blah;"
rs.Open SQL, strConnect,3,3
%>
<body>
<table>
<tr>
<td>TITLE</td>
<td>PUBLICATION DATE</td>
<td>PARAGRAPH 1</td>
<td>PARAGRAPH 2</td>
<td>PARAGRAPH 3</td>
<td>PARAGRAPH 4</td>
<td>PARAGRAPH 5</td>
<td>PARAGRAPH 6</td>
<td>PARAGRAPH 7</td>
<td>PARAGRAPH 8</td>
</tr>
<% If Not rs.eof Then
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<tr>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("TITLE").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PUB_DATE").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PARA1").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PARA2").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PARA3").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PARA4").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PARA5").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PARA6").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PARA7").Value)%></td>
<td>&nbsp;<%=Server.HTMLEncode(rs.Fields("PARA8").Value)%></td>
</tr>
<% rs.MoveNext
loop
Else %>
<tr>
<td colspan="4">No Cases found matching the criteria!</td>
</tr>
<% End If %>
</table>
<%
If DebugMode = "True" Then
%>
SQL Code = <%=SQL%><BR>
<%=TodaysDate%>
<%
End If
%>
</body>
</html>
</PRE>

P.S. I checked that all PUB_DATEs are more than todays date and all spelling and database connection strings seem to be correct.
 
Hey Peeechy,

I think your problems lies in the following line:

SQL=SQL &" WHERE PUB_DATE >= '" & TodaysDate & "' "

In this query, you are asking it to return all news entries that are equal to or GREATER than todays date. Is this what you want to do?

This is just a shot in the dark, but I am assuming you do not posses ESP and have news that hasn't happened yet, so with that I am assuming you would want all news items that are equal to or LESS THAN todays date?

hehe.. sorry for the sarcasm.. We've all made mistakes like this a few times.. I've racked my brain over problems like this too in the past.

Cheers,

G.



GT Interactive
-----------------------------
Components/Tools/Forums/Software/Web Services
 
Hi there,

Yes, I see why you would say that. Actually, it is doing what I want.
I have an admin only area for my client to update the news page and he can specify when he wants the news to appear on the website, and since he will not put an old date in (otherwise it wouldn't be news) then it is =>. Bear in mind I will have an archive for old news items that will use < than to eliminate new articles.

Thanks for the thought anyhow.

I have since discovered that the MySQL host neeeds flushing anyway so I'm waiting for my clients' host support to do that too.

Cheers!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top