|
|
Hi again,
I had been facing this problem since I started to develop my my database assignment. I used the following code to connect to my database;
Assume my DSN=asd; userId=asd; pwd=123;
<%
Dim oConn, Rs, Sql
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open ("DSN=asd; userId=asd; pwd=123")
Sql = "Select * From table1;"
Set Rs = Server.CreateObject("ADODB.recordset")
Rs.Open (Sql, oConn)
%>
This is the method how I connect to my database to grab, insert, edit or delete data. My problem is not on the connection part actually. The error occur whenever I try to close my connection as below;
<%
Rs.Close
Set Rs = Nothing
oConn.Close
oConn = Nothing
%>
The error will be on the Rs.Close statement. I always get an error message saying <B>"Operation is not allowed when the object is closed"</B>. So what I did all this while is to cancel off the Rs.Close and keep the remaining, and this work fine. But isn't this a "handicap" program? I can open a connection but I cannot close it?
Can anyone tell me why does this happen? How to overcome it and how to write a "beautiful" system out of it. This error really drives me nut!
Thanks all!
|
|
|
|
This may seem to obvious, but it sounds to me like there is another Rs.Close in your code. Just do a global, non case-sensitive search for "Rs.Close". My suspicion is that you will find another Rs.Close somewhere in the code before you are hitting your cleanup code.
Also, the line:
should be
But I suppose that was just a typo when you entered the code snippet in the forum. <g>
|
|
|
|
|
|
Sorry Troy Wolf, but I really don't get what you mean by global here? Mind to explain in advance? But I don't think there is another Rs in the code. Take this as example:
<%@ Language = JScript %>
<!-- include file = "adovbs.inc" -->
<%
var oConn, Rs, Sql = new String;
var Address, Notes;
var Duplicate;
String.prototype.encodeBreak = function()
{
return this.replace(/\n/g, "<BR>\n");
}
Address = Server.HTMLEncode(Request.Form("Address")).encodeBreak();
Notes = Server.HTMLEncode(Request.Form("Remark")).encodeBreak();
oConn = Server.CreateObject("ADODB.Connection");
oConn.Open ("DSN=tyeyo; userId=tyeyo; pwd=tye0407");
Sql = "Select * From Employee Where EmployeeID = '" + Request.Form("EmployeeID") + "';";
Rs = Server.CreateObject("ADODB.Recordset")
Rs.Open (Sql, oConn, 3, 3);
if (Rs.EOF) {
Duplicate = true
}
else {
Duplicate = false
}
Rs.Close
if (Duplicate == true) {
Sql = "Insert Into Employee (EmployeeID, EmployeeName, ShortForm, JobTitle, Address, PhoneNumber, FaxNumber, EmailAddress, Remark) Values ('" + Request.Form("EmployeeID") + "', '" + Request.Form("EmployeeName") + "', '" + Request.Form("ShortForm") + "', '" + Request.Form("JobTitle") + "', '" + Address + "', '" + Request.Form("Phone") + "', '" + Request.Form("Fax") + "', '" + Request.Form("Email") + "', '" + Notes + "' ;"
Rs.Open (Sql, oConn, 1, 3, 1);
// Rs.Close
Rs = null;
oConn.Close;
oConn = null;
Response.Redirect ("/forum/Main.html")
}
else {
Response.Write("The Employee ID " & Request.Form("EmployeeID") & " already been used!")
Response.Write("<BR><INPUT type=submit value='Click Here' onClick='window.history.back()'>to go back")
}
%>
You will notice that I have cancel off the Rs.Close in this example. Ya I know this is in JScript but the same thing goes with the VBScript. (Note that I have no choice but to develop it in JScript as one of my assignment).
Anyway I have post some question in the .Net newbie, hope you can glance through. Thanks!
<Added>
http://www.codetoad.com/forum/23_22583.asp
|
|
|
|
I think I know what the problem is.
Notice the bold, italic line from your code below:
Sql = "Insert Into Employee (EmployeeID, EmployeeName, ShortForm, JobTitle, Address, PhoneNumber, FaxNumber, EmailAddress, Remark) Values ('" + Request.Form("EmployeeID") + "', '" + Request.Form("EmployeeName") + "', '" + Request.Form("ShortForm") + "', '" + Request.Form("JobTitle") + "', '" + Address + "', '" + Request.Form("Phone") + "', '" + Request.Form("Fax") + "', '" + Request.Form("Email") + "', '" + Notes + "';"
Rs.Open (Sql, oConn, 1, 3, 1);
// Rs.Close
Rs = null;
|
|
I have a question for you. Does that line actually INSERT the record? I mean, does it actually work? Because an INSERT statement does not create a Recordset. The normal way to execute a non SELECT query is:
Whether the INSERT actually works using your code or not, since it does not return a Recordset object, Rs.Close fails. You should replace the line in your code with the line I show, then remove the line to Close the Rs object.
|
|
|
|
|
|
It actually work. I learn the statement from a site but I couldn't recall the url now. Anyway do you want my complete code (with the DB)? the Rs.Open (Sql, oConn, 1, 3, 1); actually work. I am able to insert the value into it.
|
|
|
|
No, I do not want the code. I completely believe you if you say the INSERT works. But the point I tried to make in my reply was that it is not appropriate to try to instantiate a Recordset object to execute an INSERT statement (or a DELETE or UPDATE). This is why your Rs.Close fails -- there is not a recordset object created from the INSERT statement.
Replace your "Set Rs = ..." line with oConn.Execute(Sql). There is not a recordset involved in this logic -- make sense?
|
|
|
|
|
|
Oh now I'm confused! To my understanding, Connection is where we establish a connection to the database and Recordset is where we can play around with the data like SEARCH, INSERT, EDIT, and DELETE. If I understood wrongly pls clarify with me what is Recordset and what it does and when to use recordset and when not to use.
|
|
|
|
A recordset is what you'd think it would be -- a set of records. When you SELECT records from a database, the database returns a set of records -- a Recordset. INSERT, UPDATE, and DELETE are statements that affect records in the database but do not return records.
It may be easier to see the logic of the code when you look at this example using another method to create a Recordset. (The method you are using is more powerful because you can pass parameters to control the recordset object, but this code will demonstrate the theory better.)
In this example, we are sending a SELECT query to the database and expect a Recordset to be returned.
sql = "select * from Employees"
set rs = cnn.Execute(sql)
|
|
In this example, we are deleting a records by sending a DELETE statement to the database. Notice we just execute the statement rather than set a recordset.
sql = "delete from Employee where EmpID = 5"
cnn.Execute(sql)
|
|
You could execute the DELETE using "set rs = cnn.Execute(sql)", but rs will not be a recordset.
I hope this helps clear some of this up.
|
|
|
|
|
|
Ok thanks troy, I get some picture now.
Have a nice day
|
|
|
|
|
|
|
|
swspm
Troy Wolf
