Seek Function in ASP

[gpiramide]

Is is Possible to use the Seek Method in ASP, I got this error message :
Current provider does not support the necessary interface for Index functionality. :

This is my code :
Set rs1=Server.CreateObject("ADODB.Recordset")
rs1.Open "Select id_no from student", MyConn, adOpenKeyset, adLockReadOnly, adCmdTableDirect

if rs1.supports(adSeek) = True then
rs1.Index = "id_no"
rs1.seek "=","921215"
else
response.redirect "error.asp"
end if

Thnks.

[Angelika]

To use this method, the provider must support this method and the use of indexes on a Recordset (the Index property). Use the Supports method to determine whether the provider supports seek and indexes.

Set the Index property to the desired index before executing this method.

This method can only be used with server-side cursors (not supported when the CursorLocation property value is adUseClient).

This method can only be used when the Recordset object was opened with the CommandTypeEnum value of adCmdTableDirect.

[gpiramide]

Thank you very much for your reply, I've tried that already, It is very hard for me to debug what is wrong with my code, can you give me an example of code that I can work on?

Thanks

[Angelika]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaexamples_vb02_18.asp

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q131/8/29.asp&NoWebContent=1

[gpiramide]

Thanks again, I've tried it already in Visual Basic, well it works just fine but I can't seen to run the code in a web environment using ASP, can Seek method be used in ASP?

I have a hard getting this one done. I can use the find method but the problem is it takes too long to search a record when you have at least 30K rows of data :(, Is there another way of searching data faster that the find method if seek record is not possible in ASP?

Thanks.

[gpiramide]

I always get this error message :

Error Type:
ADODB.Recordset (0x800A0CB3)
Current provider does not support the necessary interface for Index functionality.

[Whitesword]

Try this:


----------------------------[ Snip ]----------------
Dim strSQL

strSQL="SELECT student.id_no FROM student WHERE id_no=921215"

Set rs1=Server.CreateObject("ADODB.Recordset")
rs1.Open strSQL, MyConn, adOpenKeyset, adLockReadOnly, adCmdTableDirect

If rs1.EOF Then
Response.Redirect "error.asp"
End If
-------------------------[ Snip ]---------------------

This will make the SQL engine work more efficiently and faster especially if you have the id_no field indexed. Much better than a seek

[Whitesword]

Try this:


----------------------------[ Snip ]----------------
Dim strSQL

strSQL="SELECT student.id_no FROM student WHERE id_no=921215"

Set rs1=Server.CreateObject("ADODB.Recordset")
rs1.Open strSQL, MyConn, adOpenKeyset, adLockReadOnly, adCmdTableDirect

If rs1.EOF Then
Response.Redirect "error.asp"
End If
-------------------------[ Snip ]---------------------

This will make the SQL engine work more efficiently and faster especially if you have the id_no field indexed. Much better than a seek

[gpiramide]

Thanks for your help whitesword, but i've tried it already , the problem with the code is it will redirect to error page even if an existing id_no is found in the student table, coz it will only display a single row and at the same time last record or eof.

Thanks again

[gpiramide]

Thanks whitesword , I've got it already :)