locked
"SELECT" SQL query only returning first record RRS feed

  • Question

  • I've created an ASP VBScript page to display all the records in an SQL table with the basic "SELECT" query, but it only returns the first record! Can anyone see where i'm going wrong?

    <%
    Dim adoCon
    Dim strInsertSQL
    Dim rtn
    Dim x
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=TheDB;UID=MyUID;PWD=MyPWD;OPTION=3;"
    strInsertSQL = "SELECT num,ip,time FROM theTable;"
    Set rtn = adoCon.Execute(strInsertSQL,3,3)
    If (rtn.EOF=False And rtn.BOF=False) Then
    For Each x in rtn
    Response.Write(x.name & "=" & x.value)
    Next
    Else
    Response.Write("Nothing Here!")
    End If
    Set adoCon = Nothing
    %>

    In theory, that should return the three specified fields for each of the 9 records in the database, but it doesn't. The resulting response of this is:

    num=2ip=*********time=6/15/2010 3:05:18 PM

    but the response should be at least 8 times that length. Any advice?

    Wednesday, June 16, 2010 7:17 PM

Answers

  • Because you are **NOT iterating ** through your recordset....

    while not rtn.eof 
     
     rs.movenext()	
    wend 

    You need to use Recordset.MoveNext to jump to the next row else you are stuck at the first row. By the way you are using MySQL and this is a Microsoft SQL server forum.

    • Marked as answer by alex.arnold52 Wednesday, June 16, 2010 8:30 PM
    Wednesday, June 16, 2010 7:57 PM

All replies

  • Because you are **NOT iterating ** through your recordset....

    while not rtn.eof 
     
     rs.movenext()	
    wend 

    You need to use Recordset.MoveNext to jump to the next row else you are stuck at the first row. By the way you are using MySQL and this is a Microsoft SQL server forum.

    • Marked as answer by alex.arnold52 Wednesday, June 16, 2010 8:30 PM
    Wednesday, June 16, 2010 7:57 PM
  • Thanks :) I know now that it's the wrong type of SQL, but the focus was on the VBScript and ASP side of it, and as there is not forum dedicated to VBScript i attached it to the closest possible match.
    Wednesday, June 16, 2010 8:36 PM