Problem Updating with Two Record Sets 1

[AllenRitch]

I'm trying to update two distinct tables in the same database using two seperate record sets. While the 1st record set (RDSet) is updated, the 2nd one (RDSet2) isn't. Below is the code I'm using along with the ASP error I recieve. Line 55 in the error message is the very 1st RDSet.Close statement. Thanks!

----------------------------- Code --------------------------------
<% LANGUAGE=&quot;VBSCRIPT&quot; %>
<!--#include file=includes\DataConn.inc -->
<!--#include file=includes\UserClass.inc -->

<%
' Declare User Variable
Dim UserAlias

' Open UserClass
Dim User
Set User = New UserClass

' Get User's Alias
UserAlias = User.Alias

' Declare Form Variables
Dim asp_qust1, asp_qust2, asp_qust3, asp_qust4, asp_qust5, asp_qust6, asp_qust7, asp_qust8, asp_qust9, asp_qust10
Dim asp_qust11, asp_qust12, asp_qust13, asp_qust14, asp_qust15, asp_qust16, asp_qust17, asp_qust18, asp_commnts

' Assign Values
asp_qust1 = request.form(&quot;frm_qust_1&quot
asp_qust2 = request.form(&quot;frm_qust_2&quot
asp_qust3 = request.form(&quot;frm_qust_3&quot
asp_qust4 = request.form(&quot;frm_qust_4&quot
asp_qust5 = request.form(&quot;frm_qust_5&quot
asp_qust6 = request.form(&quot;frm_qust_6&quot
asp_qust7 = request.form(&quot;frm_qust_7&quot
asp_qust8 = request.form(&quot;frm_qust_8&quot
asp_qust9 = request.form(&quot;frm_qust_9&quot
asp_qust10 = request.form(&quot;frm_qust_10&quot
asp_qust11 = request.form(&quot;frm_qust_11&quot
asp_qust12 = request.form(&quot;frm_qust_12&quot
asp_qust13 = request.form(&quot;frm_qust_13&quot
asp_qust14 = request.form(&quot;frm_qust_14&quot
asp_qust15 = request.form(&quot;frm_qust_15&quot
asp_qust16 = request.form(&quot;frm_qust_16&quot
asp_qust17 = request.form(&quot;frm_qust_17&quot
asp_qust18 = request.form(&quot;frm_qust_18&quot
asp_commnts = replace(request.form(&quot;frm_commnts&quot,&quot;'&quot;,&quot;''&quot

' SQL Update Statement for tblFPA_Survey
SQLString = &quot;INSERT INTO tblFPA_Survey (db_qust1, db_qust2, db_qust3, db_qust4, db_qust5, db_qust6, db_qust7, &quot;
SQLString = SQLString + &quot;db_qust8, db_qust9, db_qust10, db_qust11, db_qust12, db_qust13, db_qust14, db_qust15, db_qust16, &quot;
SQLString = SQLString + &quot;db_qust17, db_qust18, db_commnts) VALUES ('&quot; & asp_qust1 & &quot;', '&quot; & asp_qust2 & &quot;', '&quot; & asp_qust3 & &quot;', &quot;
SQLString = SQLString + &quot;'&quot; & asp_qust4 & &quot;', '&quot; & asp_qust5 & &quot;', '&quot; & asp_qust6 & &quot;', '&quot; & asp_qust7 & &quot;', '&quot; & asp_qust8 & &quot;', &quot;
SQLString = SQLString + &quot;'&quot; & asp_qust9 & &quot;', '&quot; & asp_qust10 & &quot;', '&quot; & asp_qust11 & &quot;', '&quot; & asp_qust12 & &quot;', '&quot; & asp_qust13 & &quot;', &quot;
SQLString = SQLString + &quot;'&quot; & asp_qust14 & &quot;', '&quot; & asp_qust15 & &quot;', '&quot; & asp_qust16 & &quot;', '&quot; & asp_qust17 & &quot;', '&quot; & asp_qust18 & &quot;', &quot;
SQLString = SQLString + &quot;'&quot; & asp_commnts & &quot;')&quot;

' Create & Execute Record Set Update for tblFPA_Survey
Set RDSet=server.createobject(&quot;adodb.recordset&quot
Set RDSet = DBConn.execute(SQLString)

' Close First Record Set
RDSet.Close
Set RDSet = Nothing

' SQL Update Statement for tblAlias
SQLString = &quot;INSERT INTO tblAlias (user_alias) VALUES ('&quot; & UserAlias & &quot;')&quot;

' Create & Execute Record Set Update for tblAlias
Set RDSet2 = server.createobject(&quot;adodb.recordset&quot
Set RDSet2 = DBConn.execute(SQLString)

' Close Second Record Set
RDSet2.Close
Set RDSet2 = Nothing

' Checks for VBScript Errors
If err.number>0 Then
response.write &quot;VBScript Errors Occured:&quot; & &quot;<p>&quot;
response.write &quot;Error Number=&quot; & err.number & &quot;<p>&quot;
response.write &quot;Error Descr.=&quot; & err.description & &quot;<p>&quot;
response.write &quot;Help Context=&quot; & err.helpcontext & &quot;<p>&quot;
response.write &quot;Help File=&quot; & err.helpfile & &quot;<p>&quot;
response.write &quot;Native Error=&quot; & err.nativeerror & &quot;<p>&quot;
response.write &quot;Source=&quot; & err.source & &quot;<p>&quot;
response.write &quot;SQLState=&quot; & err.sqlstate & &quot;<p>&quot;
End If

' Checks for Database Errors
If DBConn.Errors.Count>0 Then
response.write &quot;Database Errors Occured&quot; & &quot;<p>&quot;
response.write SQL & &quot;<p>&quot;
For each error in DBConn.Errors
response.write(Error.Number & &quot; : &quot; & Error.Description) & &quot;<p>&quot;
Next
Else
response.write &quot;<b>Everything Went Fine! Record is now UPDATED!</b>&quot;
response.write &quot;<br>&quot; & SQL
End If

' Close Database
DBConn.Close
Set DBConn = Nothing
%>
<!-- Used to redirect the user to a thank you page that doesn't appear in frames -->
<script type=&quot;text/javascript&quot; language=&quot;JavaScript&quot;>
parent.location.href='

http://intranet_dev/floracct/thank_you.htm';

</script>

--------------------------------- Error Message -------------------
ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/~floridasurveys/fpa_survy.asp, line 55

 

[Chopstik]

Unless I am mistaken, it appears that you are simply executing an insert statement and not actually creating the recordset. Therefore, if you are not creating a recordset per se, then there is no need to close something that does not actually exist in the first place.

In short, take out the statements that close the non-existant recordsets. Hope this helps.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[AllenRitch]

Chopstik... I removed both the RDSet & RDSet2 Close statements, and it worked perfectly! Everything updated, and I recieved no errors. However, are you sure I don't need to close any of the record sets? Note that I did leave the Set RDSet/RDSet2 = Nothing statements.

Thanks a million!

 

[Chopstik]

Technically, you do not even need to the RDSet or RDSet2 recordsets as they are never used. You could REM those lines out and things would work. When you need to execute your SQL strings, you could simply write:

DBConn.execute(SQLString)

and all should work just fine. I know there is something/someone that could explain this better, but essentially, there is no recordset, therefore, nothing to close or set to nothing. Right now in your code, they are simply taking up unnecessary processing time. Hope this clarifies things.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[AllenRitch]

Chopstik... You were correct. I removed all the statements pertaining to RDSet/RDSet2 and went with two DBConn.execute statements and it worked. I guess by using the .execute statment instead of the .open statement, I'm not creating anything that should be closed (other than the original DBConn connection).

Thanks.