|
 |
asp_databases thread: Opening a recordset within a loop created from a different recordset
Message #1 by dfalconer@g... on Wed, 10 Oct 2001 11:47:15
|
|
Hi,
i want to write to a table based on the results of another table. So i
intended to open a select recordset from Table A, and loop through that
recordset, within the loop i wanted to write to table B. However i keep
getting
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/admin/subscriptions/subscriptions_add_commit.asp, line 42
I dont see how this can be because i am opening and closing the second
recordset with every loop. The first recordset returns values.
My code is:
set rs = Server.CreateObject("ADODB.recordset")
rs.open "SELECT unit_id FROM gp_unit WHERE unit_type_id = 1", Application
("admin_con")
Do While NOT rs.eof
unit_id = rs("unit_id")
set rs2 = Server.CreateObject("ADODB.recordset")
rs2.open "INSERT INTO gp_subscriptions (account_id, unit_id,
start_date, expiry_date, subscription_type_id, comments) VALUES (" &
account_id & "," & unit_id & ",'" & start_date & "','" & expiry_date
& "'," & subscription_type_id & ",'" & formatquery(comments) & "')",
Application("admin_con"), adOpenKeyset, adLockPessimistic
rs2.close
set rs2 = nothing
rs.MoveNext
Loop
rs.close
set rs = nothing
Message #2 by "Tomm Matthis" <matthis@b...> on Wed, 10 Oct 2001 09:06:41 -0400
|
|
For an insert statement you don't need a recordset (since one is not
returned). Try executing the SQL insert statement against the conn.execute
method.
-- Tomm
> -----Original Message-----
> From: dfalconer@g... [mailto:dfalconer@g...]
> Sent: Wednesday, October 10, 2001 11:47 AM
> To: ASP Databases
> Subject: [asp_databases] Opening a recordset within a loop created from
> a different recordset
>
>
> Hi,
> i want to write to a table based on the results of another table. So i
> intended to open a select recordset from Table A, and loop through that
> recordset, within the loop i wanted to write to table B. However i keep
> getting
>
> ADODB.Recordset error '800a0e78'
>
> Operation is not allowed when the object is closed.
>
> /admin/subscriptions/subscriptions_add_commit.asp, line 42
>
> I dont see how this can be because i am opening and closing the second
> recordset with every loop. The first recordset returns values.
> My code is:
>
> set rs = Server.CreateObject("ADODB.recordset")
> rs.open "SELECT unit_id FROM gp_unit WHERE unit_type_id = 1", Application
> ("admin_con")
> Do While NOT rs.eof
> unit_id = rs("unit_id")
> set rs2 = Server.CreateObject("ADODB.recordset")
> rs2.open "INSERT INTO gp_subscriptions (account_id, unit_id,
> start_date, expiry_date, subscription_type_id, comments) VALUES (" &
> account_id & "," & unit_id & ",'" & start_date & "','" & expiry_date
> & "'," & subscription_type_id & ",'" & formatquery(comments) & "')",
> Application("admin_con"), adOpenKeyset, adLockPessimistic
> rs2.close
> set rs2 = nothing
> rs.MoveNext
> Loop
> rs.close
> set rs = nothing
Message #3 by "Robert Segarra" <robert_segarra@h...> on Wed, 10 Oct 2001 14:52:39 -0500
|
|
Try the following,
Create a connection and then do the selects and updates.
Connection String to the datasource.
MyDSN = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=;Initial
Catalog=;Data Source="
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open MyDSN
Set rs = conn.Execute("SELECT unit_id FROM gp_unit WHERE unit_type_id = 1")
Do While ....
For your update you can do..
conn.Execute "INSERT INTO ....." _
Good Luck and hope it works.
----- Original Message -----
From: <dfalconer@g...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, October 10, 2001 11:47 AM
Subject: [asp_databases] Opening a recordset within a loop created from a
different recordset
> Hi,
> i want to write to a table based on the results of another table. So i
> intended to open a select recordset from Table A, and loop through that
> recordset, within the loop i wanted to write to table B. However i keep
> getting
>
> ADODB.Recordset error '800a0e78'
>
> Operation is not allowed when the object is closed.
>
> /admin/subscriptions/subscriptions_add_commit.asp, line 42
>
> I dont see how this can be because i am opening and closing the second
> recordset with every loop. The first recordset returns values.
> My code is:
>
> set rs = Server.CreateObject("ADODB.recordset")
> rs.open "SELECT unit_id FROM gp_unit WHERE unit_type_id = 1", Application
> ("admin_con")
> Do While NOT rs.eof
> unit_id = rs("unit_id")
> set rs2 = Server.CreateObject("ADODB.recordset")
> rs2.open "INSERT INTO gp_subscriptions (account_id, unit_id,
> start_date, expiry_date, subscription_type_id, comments) VALUES (" &
> account_id & "," & unit_id & ",'" & start_date & "','" & expiry_date
> & "'," & subscription_type_id & ",'" & formatquery(comments) & "')",
> Application("admin_con"), adOpenKeyset, adLockPessimistic
> rs2.close
> set rs2 = nothing
> rs.MoveNext
> Loop
> rs.close
> set rs = nothing
>
robert_segarra@h...
$subst('Email.Unsub')
>
>
|
|
|
View Cart
