An exception occurred: 'RS.open'

can you post more code

In my experience it means that the recordset is empty and therefore cannot be operated on. to avoid this I usually chekc if RS.EOF is true before doing anything

----product.asp----
<%@ LANGUAGE = "VBScript" %>
<!-- #include file="db.asp" -->

prdId = trim(request.querystring("prdId"))

sql = "select * from product where prdId = " & prdId

RS.Open sql, db
     
echo sql 'for debugging purpose

Do while not RS.eof                    

  'prdId = RS("prdId")
  prdA = RS("prdA")
  prdB = RS("prdB")
  prdC = RS("prdC")
  prdD = RS("prdD")
  prdE = RS("prdE")

  RS.MoveNext
loop
               
RS.Close
----end---

the RS is created in file: db.asp

----db.asp---
set RS = server.createobject("ADODB.Recordset")
---end---

error occurred on this line:

RS.Open sql, db

This might not help but it won't hurt to try. Destroy the recordset after you've used and closed it with

Set RS = Nothing

Another thing you could try is to use an if-case to test the prdId:

If prdId <> "" Then
   sql = "SELECT"
   RS.Open ...
   and so on
End If

Defensive programming...

-Marko

khongkham,


<%@ LANGUAGE = "VBScript" %>
<!-- #include file="db.asp" -->
<%
set rs = Server.CreateObject("ADODB.Recordset")
prdId = trim(request.querystring("prdId"))
sql = "select * from product where prdId = " & prdId
RS.Open sql, db
.
.
.
%>

Regrads,
Wee Siong
   

khongkham,

Sorry, try this:

<%
set conn=server.CreateObject("adodb.connection")
conn.Open "Provider=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&Server.MapPath("yourdatbase.mdb")
set rs = Server.CreateObject("ADODB.Recordset")
prdId = trim(request.querystring("prdId"))
sql = "select * from product where prdId = " & prdId
RS.Open sql, conn
.
.
.
%>

Regards,
Wee Siong

hai, marko
i had added in set RS = nothing          
but, this part i need a lot of testing which i may not be able to know is this error has been fixed!


but, later on if i m sure with i'll accept ur answer and give u the point!


thanks a lot!

khongkham,

You are no define what database you want to open, see the last code i posted in here. please define db as a Connection String.....

Regards,
Wee Siong

prdId might be empty.. try to response.write the SQL variable..

hai, the db connection is setted in db.asp

---db.asp---
<%
dim db
dim sconn
dim RS

Set db = Server.CreateObject("ADODB.Connection")

strConn "Provider=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE="&Server.MapPath("yourdatbase.mdb")

set RS = server.createobject("ADODB.Recordset")

'to open database connection
db.open strConn
%>

---end---

then, the product.asp included this db.asp

----product.asp----
<%@ LANGUAGE = "VBScript" %>
<!-- #include file="db.asp" -->

prdId = trim(request.querystring("prdId"))

sql = "select * from product where prdId = " & prdId

RS.Open sql, db
   
echo sql 'for debugging purpose

Do while not RS.eof                    

 'prdId = RS("prdId")
 prdA = RS("prdA")
 prdB = RS("prdB")
 prdC = RS("prdC")
 prdD = RS("prdD")
 prdE = RS("prdE")

 RS.MoveNext
loop
             
RS.Close
set RS = nothing          'added this

----end---


BUT, same error still ocurring!!!

hhmm..., the prdId shouldn't be empty... coz it's working perfectly sometimes!!!
will check it later!

sorry, the dim sconn should be strConn!


cheers,
khongkham

try using:
Set [variable] = [Connection Variable].Execute(RS)

you are not closing the database connection.. add this at the end:

db.close : set db=nothing

The thing that might be happening with you is that the maximum amount of connection is reached and the error fires until a connection times out then you don't get the error again..

Try printing out the SQL string that you are attempting to run and running it in your database first-that error is sometimes typical of having an invalid SQL string or an error in the statement.

Silver5,

I've been told or read from somewhere that VB will do the gargage collection for you if it is local variable.

You don't have to specific to set object to nothing. When the routine is out of scope, the vb internal will free up memory.

Is that true?

in web developement you do it to free up resources immediately since a session times out after a while (sometimes it never times out) and system resources are crytical on web servers..

You could try adding this just before the Rs.Open Statement

     If Rs.State = 1 then Rs.Close

the error could be cause by the record set already being opened.

hai, once i had done the testing will get back to u guys ASAP

hai, i just wondering that is it all the RS and db conn should be closed and setted to nothing? can't i just leave it open? if i din close the RS n db conn, will it slow down the system performance? or will it be faster if i closed it and the open it again?

but, as for the db conn i need to use it all the pages... is there a need to close the db conn and set it to nothing and then open it again??

sorry for my late reply, thanks a lot!