Search code examples
asp-classic

asp classic retrieve and display single recordset

i hv a code that need me to find only a single line of recordset from the database into the variable.

dim Connect,conn_,data,sql

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "toServer"
sql = "SELECT * from sppro where proj_name='pname'"
set Data = Connect.Execute(sql)

response.write data("proj_id")

i just cant find to correct way to retrieve and view single record set... i found something about cursor, but i dont understand it at all... can anyone pls explain to me?

edit:

error tht i got with this code is as below.

ADODB.Field error '80020009'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/bkpi-msn/Include/ServerSideAjax.asp, line 0

Solution

  • If I understand correctly you already know how to display one record from a database, but now you want to display more than one record. Is that right?

    With some changes to your code, you can loop though all records:

    dim Connect,conn_,data,sql

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "toServer"
sql = "SELECT * from sppro where proj_name='pname'"
set Data = Connect.Execute(sql)

Do Until Data.Eof

    response.write data("proj_id")

    Data.MoveNext
Loop

    Edit: to retrieve just one row, you can use the following. It's basically the same, except there is a check to ensure there is at least one record, and no need to loop through the recordset.

    dim Connect,conn_,data,sql

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "toServer"
sql = "SELECT * from sppro where proj_name='pname'"
set Data = Connect.Execute(sql)

If Not Data.Eof Then

    response.write data("proj_id")

End If