Heya people,
Here's my problem: i have a (for me quite lengthy) piece of SQL code which is automagically generated by my own application.
However, when i try to execute it, it gives me an recordSet error ("Either BOF of EOF is true, or the current record has been deleted").
However, when i response.write() the SQL line, and execute it in access, it runs fine.
this is the faulty line:
SELECT top 10 * FROM HSE_TRA_vergunning WHERE formuliertype LIKE '*heetwerk*' AND TraID > 0 ORDER BY 'traID' DESC;Heres a working, simpeler line:
SELECT top 50 * FROM HSE_TRA_vergunning WHERE TraID > 0 ORDER BY TraID ASCHere's the code that generates the SQL lines. it's taking values from a POST, uses it to make individual pieces of SQL, then melts it together
IF Request.Querystring("TraID") THEN
TraID = Request.Querystring("TraID")
ELSE
TraID = 0
END IF
SQL0 = "SELECT TraID FROM HSE_TRA_vergunning ORDER BY TraID DESC"
PS = Conn.Execute(SQL0)
maxTRA = PS("TraID")
if request.Querystring("modif") = "true" THEN
SELECT CASE request.Form("sortOn")
CASE "TraID"
sort = "traID"
CASE "Datumaanvraag"
sort ="Datumaanvraag"
CASE "geldigvan"
sort = "geldigvan"
CASE "geldigtot"
sort = "geldigtot"
CASE "vergunningshouder"
sort = "vergunningshouder"
END SELECT
IF request.Form("comboFilter") = "AND" THEN
combo = "AND"
ELSE
combo = "OR"
END IF
IF request.Form("heetFilter") = "heet" THEN
definition = "WHERE formuliertype LIKE '*heetwerk*' "
END IF
IF request.Form("koudFilter") = "koud" THEN
IF len(definition) > 1 THEN
definition = definition + combo +" formuliertype LIKE '*koudwerk*' "
ELSE
definition = "WHERE formuliertype LIKE '*koudwerk*' "
END IF
END IF
IF request.Form("graafFilter") = "graaf" THEN
IF len(definition) > 1 THEN
definition = definition + combo +" formuliertype LIKE '*graafwerk*' "
ELSE
definition = "WHERE formuliertype LIKE '* graafwerk*' "
END IF
END IF
IF request.Form("hijsFilter") = "hijs" THEN
IF len(definition) > 1 THEN
definition = definition + combo +" formuliertype LIKE '*hijswerk*' "
ELSE
definition = "WHERE formuliertype LIKE '*hijswerk*' "
END IF
END IF
IF request.Form("entreeFilter") = "entree" THEN
IF len(definition) > 1 THEN
definition = definition + combo +" formuliertype LIKE '*entree*' "
ELSE
definition = "WHERE formuliertype LIKE '*entree*' "
END IF
END IF
IF request.Form("actiefFilter") = "Actief" THEN
status = "AND status ='Actief' "
END IF
IF request.Form("afgerondFilter") = "Afgerond" THEN
IF len(status) > 1 THEN
status = status + "OR status='Afgerond' "
ELSE
status = "AND status ='Afgerond' "
END IF
END IF
IF request.Form("voorbereidingFilter") = "Voorbereiding" THEN
IF len(status) > 1 THEN
status = status + "OR status='Voorbereiding' "
ELSE
status = "AND status ='Voorbereiding' "
END IF
END IF
IF request.Form("555Filter") = "555" THEN
IF len(status) > 1 THEN
status = status + "OR status='555' "
ELSE
status = "AND status ='555' "
END IF
END IF
IF request.Form("ascdesc") = "ASC" THEN
ascdesc = "ASC"
ELSE
ascdesc = "DESC"
END IF
'Deze SQL wordt uitgevoerd als er een filter actie is geweest op de eerste pagina
SQL = "SELECT top 10 * FROM HSE_TRA_vergunning " & definition & " AND TraID > " & TraID & " " & status & " ORDER BY '" & sort & "' " & ascdesc & ";"
ELSE IF (request.Querystring("modif") = "post") THEN
sort = request.Querystring("sort")
combo = request.Querystring ("combo")
definition = request.Querystring ("definition")
status = request.Querystring("status")
ascdesc = request.Querystring ("ascdesc")
'deze actie wordt uitgevoerd als er een filter actie is uitgevoerd op een andere pagina
SQL = "SELECT top 10 * FROM HSE_TRA_vergunning " & definition & " AND TraID > " & TraID & " " & status& " ORDER BY '" & sort & "' " & ascdesc & ";"
ELSE
'en deze sql is voor filterloze pagina's
SQL = "SELECT top 50 * FROM HSE_TRA_vergunning WHERE TraID > " & TraID & " ORDER BY TraID ASC"
END IF
END IFand finally; here is the code that executes it:
<%
Set RS = Conn.Execute(SQL)
RS.MoveFirst()
Do While Not RS.EOF
%>