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 ASC

Here'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 IF

and finally; here is the code that executes it:

<% 

                                               Set RS = Conn.Execute(SQL)

                                               RS.MoveFirst()

                                               Do While Not RS.EOF 

                               %>

wow... that was such a simple answer that actually worked :)
The ironic part is, that access doesn't accept it, so i thought it had to be stars.

Either way, this worked, thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.