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!

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

MySQL/ASP/VBScript SELECT and loop not working.

Status
Not open for further replies.
peeechy3

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>
 
Sort by date Sort by votes
Add a line to print out the SQL statement you are using, your date parameter might not be what you think it is. If the select statement looks like you think it should, copy it and paste it into a query tool to see if it returns and results when you execute it.

A line like:

Response.Write(SQL)

Should print out what your statement looks like.
 
Upvote 0 Downvote
Hi there!

I already have a bit that prints out my SQL query to screen:

<%
If DebugMode = "True" Then
%>
SQL Code = <%=SQL%><BR>
<%=TodaysDate%>
<%
End If
%>

On screen it is as it should and I have already tested it in the tool which returns the two rows.

I'm confused.
 
Upvote 0 Downvote
Try to comment out all the ERROR RESUME NEXT statements to get more info on what happens.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
I already have a bit that prints out my SQL query to screen
Click to expand...

*smacks forehead*

Right, well then, have you tried just doing a "SELECT * FROM News"? Maybe your db connection is wonky.
 
Upvote 0 Downvote
Hi,

Thanks both of you. I reckon you are both right.
I just took off the ON ERROR RESUME NEXT and got this:

ADODB.Recordset.1 error '80004005'

SQLState: S1000
Native Error Code: 1129
[TCX][MyODBC]Host 'localhost.localdomain' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'


/NewSite/news.asp, line 46 (This is my connection string)

Not quite sure what I need to do now. I use PHPAdmin to administer the MySQL database.
Do i need to contact my host support for this unlock thingy?

Cheers!
 
Upvote 0 Downvote
I've got a feeling I will have to ask my host support to do this for me... which will take days evertime something like this happens! Blimey!

I just went into MySQL MyAdmin and saw the command Flush Table so I clicked it and got this back:

Error

SQL-query :

FLUSH TABLE `NEWS`

MySQL said:

Access denied for user: = 'blah'
(Using password: YES)

What does this mean?
 
Upvote 0 Downvote
Just found this in the documentation, i think it will be a support job:

If you get the following error, it means that mysqld has received many connect requests from the host 'host_name' that have been interrupted in the middle:

Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'

The number of interrupted connect requests allowed is determined by the value of the max_connect_errors system variable. After max_connect_errors failed requests, mysqld assumes that something is wrong (for example, that someone is trying to break in), and blocks the host from further connections until you execute a mysqladmin flush-hosts command or issue a FLUSH HOSTS statement. See section 5.2.3 Server System Variables.

By default, mysqld blocks a host after 10 connection errors. You can adjust the value by starting the server like this:

shell> mysqld_safe --max_connect_errors=10000 &

If you get this error message for a given host, you should first verify that there isn't anything wrong with TCP/IP connections from that host. If you are having network problems, it will do you no good to increase the value of the max_connect_errors variable.
 
Upvote 0 Downvote
I'm afraid you've to post in a more suited forum, like Forum436 as this is not a VBScript issue anymore.
Anyway, seems you've got some permissions issue.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
Yes, will do.

Thanks for your help anyhow and I've been able to dedue the host lock at least.

Thanks again!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Alexvb6
  • Locked
  • Question
Deprecation of VBScript + Classic ASP: How to Ask Microsoft to cancel their planned craziness
Replies
0
Views
343
Alexvb6
Alexvb6
K
  • Locked
  • Question
VBSCRIPT dont work windows 11 23h2 ???
Replies
2
Views
497
SoftwareRT
SoftwareRT
bdwilcox
  • Locked
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
993
SoftwareRT
SoftwareRT
bdwilcox
  • Locked
  • Question
HTA fails with Syntax Error when working VBScript inserted
Replies
2
Views
345
bdwilcox
bdwilcox
paullem
  • Locked
  • Question
vbscript to read properties of JPEG file
Replies
4
Views
433
paullem
paullem

Part and Inventory Search

Sponsor

Back
Top