ADODB.Recordset error '800a0e7b'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ADODB.Recordset error '800a0e7b'

Author

Topic

sageer_kazi
Starting Member

2 Posts

Posted - 2004-11-24 : 12:13:55

Please Help. Urgent

Error message displayed on the ASP Page:
"ADODB.Recordset error '800a0e7b'
Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."

SQL Stored Proc:
ALTER procedure procGetHotDealsRotation_HomePage
as
INSERT INTO tbl_Product_Details_TEMP
SELECT DISTINCT TOP 2 VW.PROD_CODE, VW.PRODUCT_NAME,
FROM vwAllProducts VW INNER JOIN ct_td_deals DL ON VW.PROD_CODE = DL.PROD_CODE
ORDER BY DL.SORT_ORDER

ASP Code:
Set connDB = oGetOpenDBConnection()
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = connDB
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "procGetHotDealsRotation_HomePage"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
Set rs.Source = cmd
rs.Open

But When I remove the "INSERT INTO tbl_Product_Details_TEMP " from the stored procedure it works fine.

Any suggestion/guidlines/sample code/anything is highly appreciated.

Thanks in advance.
-Sageer

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-24 : 12:24:31

maybe it's because insert doesn't return a recordset.
try it like this:
connDB.Execute "procGetHotDealsRotation_HomePage"

Go with the flow & have fun! Else fight the flow

sageer_kazi
Starting Member

2 Posts

Posted - 2004-11-24 : 12:46:04

But i need to get the resultset and other related values from the inserted values in the table
Plz Have a look at the part of SQL code.

--------------[code starts here]------------------
--Display all the Items which are not shown
SET @sQuery = 'INSERT INTO tbl_Product_Details_TEMP SELECT DISTINCT TOP ' + convert(varchar(10),@Item_Display_Count) + ' VW.PROD_CODE, VW.PRODUCT_NAME, DL.DEPT, DL.SECT_ID, DL.SORT_ORDER, 0 AS ITEM_DISPLAY_SEQUENCE, VW.THUMB_IMAGE_FILE, VW.THUMB_IMAGE_WIDTH, VW.THUMB_IMAGE_HEIGHT, DL.CAPTION, VW.LIST_PRICE, VW.RETAIL_PRICE, VW.AS_LOW_AS, VW.EQUIP, VW.NEW_TAG, VW.SHIP_TAG, '''+ @Session_ID +'''
FROM vwAllProducts VW INNER JOIN ct_td_deals DL ON VW.PROD_CODE = DL.PROD_CODE
WHERE (VW.STATUS = 1 OR VW.STATUS = 2 OR VW.STATUS = 5) AND VW.PRODUCT_HIDDEN = 0 AND VW.MANUF_HIDDEN = 0 AND DL.PAGE_CODE = ''' + @Page_Code + ''' AND DL.SORT_ORDER > ' + convert(varchar(10),@Last_Sort_Order) + '
ORDER BY DL.SORT_ORDER '
exec(@sQuery)

select * from tbl_Product_Details_TEMP where SESSION_ID = @Session_ID

--Get the last sort order (Would be the max of all in the current result as it is sorted on sort_order)

select @New_Last_Sort_Order = max(sort_order)
from tbl_Product_Details_TEMP
where SESSION_ID = @Session_ID
---------[code ends here]--------------

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-24 : 12:48:13

well command.execute return a recordset
Set rs = Server.CreateObject("ADODB.Recordset")
rs = connDB.Execute "procGetHotDealsRotation_HomePage"

Go with the flow & have fun! Else fight the flow

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-25 : 01:45:59

quote:
Originally posted by sageer_kazi

Please Help. Urgent

Error message displayed on the ASP Page:
"ADODB.Recordset error '800a0e7b'
Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."

SQL Stored Proc:
ALTER procedure procGetHotDealsRotation_HomePage
as
INSERT INTO tbl_Product_Details_TEMP
SELECT DISTINCT TOP 2 VW.PROD_CODE, VW.PRODUCT_NAME,
FROM vwAllProducts VW INNER JOIN ct_td_deals DL ON VW.PROD_CODE = DL.PROD_CODE
ORDER BY DL.SORT_ORDER

ASP Code:
Set connDB = oGetOpenDBConnection()
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = connDB
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "procGetHotDealsRotation_HomePage"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic
Set rs.Source = cmd
rs.Open

But When I remove the "INSERT INTO tbl_Product_Details_TEMP " from the stored procedure it works fine.

Any suggestion/guidlines/sample code/anything is highly appreciated.

Thanks in advance.
-Sageer

you can use the cmd directly, no need to reassign the source to a new recordset.

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources