Below is my very simple code to delete the information of the employee having certain emp-id...But when m tryin to execute the page, its giving me the error -ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.


Can any one help?? M goin nuts now!!!




<%@Language=VBScript%>
<% Option Explicit %>
<%

        Dim objConn,Rs,found,str,sql,str1
        set objConn=Server.createObject("ADODB.Connection")
        objConn.ConnectionString="Provider=SQLOLEDB; Data Source =(local); Initial Catalog = company_information;"&_
                                "User Id = sa; Password=sa"


        objConn.open



        str=request.form("cid")
        str1=request.form("eid")

        If ((str="") Or (str1="")) Then
                objConn.close
                Set objconn=nothing
                response.write "<center>"
                response.write "<a href='deletion.asp'>"
                response.write "You Can Not Leave Any Field Empty."
                response.write "</a>"
                response.write "</center>"
                response.End
        End If


        sql="DELETE FROM EMPLOYEE WHERE CompanyId='"& str &"' and EmpId='"& str1 & "'"
        Set Rs=objConn.execute(sql)



        ' -- CHECK IF RECORDSETS HAVE DATA
            If (rs.eof And rs.bof) then
                response.write "<center>"
                response.write "<A HREF='deletion.asp'>"
                response.write "Invalid Employee-Id.Please Enter Again.<P>"
                response.write "</A>"
                response.write "</center>"
                response.End

            Else
                response.write "<center>"
                response.write "Employee Information Deleted from Database."
                response.write "</center>"

            End if


        rs.close
        Set rs=nothing
        objConn.close
        Set objConn=Nothing %>

change your code

<%@Language=VBScript%>
<% Option Explicit %>
<%

        Dim objConn,Rs,found,str,sql,str1
        set objConn=Server.createObject("ADODB.Connection")
        objConn.ConnectionString="Provider=SQLOLEDB; Data Source =(local); Initial Catalog = company_information;"&_
                                "User Id = sa; Password=sa"


        objConn.open



        str=request.form("cid")
        str1=request.form("eid")

        If ((str="") Or (str1="")) Then

                                 TO

        If ((LEN(TRIM(str))=0) Or (LEN(TRIM(str1))=0)) Then

                objConn.close
                Set objconn=nothing
                response.write "<center>"
                response.write "<a href='deletion.asp'>"
                response.write "You Can Not Leave Any Field Empty."
                response.write "</a>"
                response.write "</center>"
                response.End
        End If


        sql="DELETE FROM EMPLOYEE WHERE CompanyId='"& str &"' and EmpId='"& str1 & "'"
        Set Rs=objConn.execute(sql)



        ' -- CHECK IF RECORDSETS HAVE DATA
            If (rs.eof And rs.bof) then
                response.write "<center>"
                response.write "<A HREF='deletion.asp'>"
                response.write "Invalid Employee-Id.Please Enter Again.<P>"
                response.write "</A>"
                response.write "</center>"
                response.End

            Else
                response.write "<center>"
                response.write "Employee Information Deleted from Database."
                response.write "</center>"

            End if


        rs.close
        Set rs=nothing
        objConn.close
        Set objConn=Nothing %>

__________________
Vikash Kumar Singh

I tried changing my code the way you told me...but it is giving me the same error "Operation is not allowed when the object is closed." What should I do now..if I leave this condition then it wud give wrong information to the user...that is, even when the id doesnt exists in the database, it gives a msg "Employee-id deleted from the database" .What is the solution to check that emp-id does not exisst and then how to tell the user that the Id entered is invalid?

Its seems like you have closed your connection, then are trying to execute an sql statement.

You cant do this:

If ((str="") Or (str1="")) Then
                objConn.close
                Set objconn=nothing
                response.write "<center>"
                response.write "<a href='deletion.asp'>"
                response.write "You Can Not Leave Any Field Empty."
                response.write "</a>"
                response.write "</center>"
                response.End
        End If

Then this:

        sql="DELETE FROM EMPLOYEE WHERE CompanyId='"& str &"' and EmpId='"& str1 & "'"
        Set Rs=objConn.execute(sql)

get rid of the following code:

objConn.close
Set objconn=nothing

You should close your connection when you no longer need it on a page.

FYI You shold post your error line number in the future, help others help you!


Wind is your friend
Matt

Additionaly IMO this:

If ((str="") Or (str1="")) Then

is the same as:

If ((LEN(TRIM(str))=0) Or (LEN(TRIM(str1))=0)) Then

This is not where your page errors out, is it?

Wind is your friend
Matt

Thanx Mat...but I tried removing those two lines from my code but it was of no use...n also the two lines will come into effect only if user does not enter company-id or employee-id...otherwise the lines will not get executed...But still neways..i removed these lines n asusual it game me the same error - Operation is not allowed when the object is closed. The line in which the error is -


            If (rs.eof And rs.bof) then


wat shud I do??

;;;the two lines will come into effect only if user does not enter company-id or employee-id
Yes but if it does go into the condition you are closing your connection when you need it for the DELETE statement below. dont ever close a connection if you are going to execute any more sql beneath it


For a start Change this:

sql="DELETE FROM EMPLOYEE WHERE CompanyId='"& str &"' and EmpId='"& str1 & "'"
Set Rs=objConn.execute(sql)

to:

sql="DELETE FROM EMPLOYEE WHERE CompanyId='"& str &"' and EmpId='"& str1 & "'"
objConn.execute(sql)

You dont use 'Set' when updating or deleting - the query does not return anything so no need to. Which means tou dont need this:

' -- CHECK IF RECORDSETS HAVE DATA
            If (rs.eof And rs.bof) then
                response.write "<center>"
                response.write "<A HREF='deletion.asp'>"
                response.write "Invalid Employee-Id.Please Enter Again.<P>"
                response.write "</A>"
                response.write "</center>"
                response.End

            Else
                response.write "<center>"
                response.write "Employee Information Deleted from Database."
                response.write "</center>"

            End if

When you say:
;;;CHECK IF RECORDSETS HAVE DATA
what data set are you checking? I can not see any select query on your page, mmmmm confused...

Wind is your friend
Matt

yes I did tried this way too!! I mean I replaced
set rs=objConn.execute(sql) with
objConn.execute(sql)

and then I get it inform the user that the data is being deleted but the problem is if I dont put the condition to check if the company-id or emp-id exist at all....then even if user supplies wrong company-id or emp-id...the message is getting displayed "Information deleted" and thats wat i dont want...though on supplying wrong company-id or emp-id, theres no harm to my data...but I dont want to print this ambiguous statement "Information deleted" . If the Id does not exist then how to inform the user that the id supplied is wrong?

;;;the problem is if I dont put the condition to check if the company-id or emp-id exist at all....
if you run the delete it will be deleted provided there is a rcord for that id, you can be sure of this

;;;though on supplying wrong company-id or emp-id
I thought you said you had a login. Shouldnt the username AND password prevent this? If you are just asking for a company ID, well, of course your users are going to see other companies data. If this is the case placing random conpany ID's will let them see data thsy shouldnt - not ideal!

;;If the Id does not exist then how to inform the user that the id supplied is wrong?
fairly easy one this one:

sql = "SELECT FROM EMPLOYEE WHERE CompanyId='"& str &"' and EmpId='"& str1 & "'"
Set Rs=objConn.execute(sql)

if Rs.Eof then
   'data does not exist...
else
   'data exists...
end if

Wind is your friend
Matt

As soon as I write this line-
if rs.eof
it gives me error -"operation is not allowed when the object is closed."