CODEWITHSUNDEEP
sqlasp-classic
Ryan
Ryan

Reputation: 257

Missing Results from Recordset

I'm having an issue when I create a recordset object in classic asp where my recordset object does not recognize the correct number of results.

My code:

subProdQuery = "SELECT idProduct, idSupplier FROM products WHERE pcprod_ParentPrd="&prodID&";"
subRes = conn.execute(subProdQuery)
if subRes.BOF and subRes.EOF then
    response.write subProdQuery&"<br>"&subRes("idProduct")&"LAST ENTRY<br>"
else
    do until subRes.EOF
        response.write subRes("idProduct")&"<br>"
        subRes.MoveNext
    loop
end if
set subRes = nothing

The results I'm getting are very strange. If I manually type the generated queries into SQL management studio, I get at least 5 results for each. When generating the results via asp, subRes is always recognized as being both BOF and EOF. However, the result of printing subRes("idProduct") in each case is the first result that I expect for each query. Any suggestions? Thanks in advance!

Upvotes: 3

Views: 150

Answers (2)

Flakes
Flakes

Reputation: 2442

you have to use set .

set subRes = conn.execute(subProdQuery)

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

Firstly, protect against SQL injection and use an ADODB.Command with parameters. At least, if ProdID is an int, validate it first.

You sometimes need a call to MoveFirst to set the BOF/EOF flags. It is sufficient to check for EOF only if you are moving forwards only.

on error resume next
Dim inttest
inttest = CLng(prodID)
if err.number > 0 then ... <<< do something about invalid inputs
on error goto 0

subProdQuery = "SELECT idProduct, idSupplier FROM products WHERE pcprod_ParentPrd=" & prodID
subRes = conn.execute(subProdQuery)

subRes.MoveFirst ' << add this
if subRes.EOF then
    response.write subProdQuery&"<br>"&subRes("idProduct")&"LAST ENTRY<br>"
else
    do until subRes.EOF
        response.write subRes("idProduct")&"<br>"
        subRes.MoveNext
    loop
end if
set subRes = nothing

Instead of ADOConnection.Execute, I normally use ADOConnection.Open which allows more options.

Const adUseClient = 3
Const adCmdText = 1
Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Dim subRes = Server.CreateObject("ADODB.Recordset")
subRes.Open subProdQuery, conn, adOpenForwardOnly, adLockReadOnly, adUseClient

Upvotes: 2

Related Questions