I get operation is not allowed when the object is open error

[BionicHartman]

When I execute the script it tells me:
"Operation is not allowed when the object is open.
Line 32 "myMainConnection.open"

I tried looking out in the FAQs and couldn't find anything that addressed something similar. I tried adding a .Close at the end but then it gives me the same error but "Operation is not allowed when the object is closed". So open or closed it is not working. I noticed that the object is open in the beginning of the script and tried closing it there but that did not work either.

[Dim myMainConnection
Set myMainConnection = CreateSqlConnection()
myMainConnection.Open
Dim ObjRecordset
Set ObjRecordset = CreateObject("ADODB.Recordset")
objRecordset.Source ="DECLARE @CURRENT_DATE DATETIME" &_
"SET @CURRENT_DATE = GETDATE()" &_
"SELECT DISTINCT I.ItemShortDescription, I.ItemLongDescription, I.ItemFullDescription,I.ItemSID, I.StandardItemNumber, I.VendorCatalogNumber, Mfr.VendorName,VendorNumber, I.VendorSID, I.VendorCatalogNumber,UOM.UOM_Code,P.UOM_Factor,P.PriceEffectiveDate,C.ContractNumber,PT.PricingTierDescription,P.PriceAmount,OFT.OrderFromTypeDescription,UNSPSC.UNSPSC_CODE,UNSPSC.UNSPSC_Description,ContainsHazMat = CASE WHEN IMT_ContainsHazMat.ItemSID IS NOT NULL THEN 'Y' ELSE 'N' END,ContainsLatex = CASE WHEN IMT_ContainsLatex.ItemSID IS NOT NULL THEN 'Y' ELSE 'N' END, PVCDEHPFree = CASE WHEN IMT_PVCDEHPFree.ItemSID IS NOT NULL THEN 'Y' ELSE 'N' END,MercuryFree = CASE WHEN IMT_MercuryFree.ItemSID IS NOT NULL THEN 'Y' ELSE 'N' END,ThimerosalFree = CASE WHEN IMT_ThimerosalFree.ItemSID IS NOT NULL THEN 'Y' ELSE 'N' END,LatexFree = CASE WHEN IMT_LatexFree.ItemSID IS NOT NULL THEN 'Y' ELSE 'N' END" &_
"FROM Price P" & _
"JOIN AvailableItem AI ON AI.AvailableItemSID = P.AvailableItemSID,JOIN Item I ON I.ItemSID = AI.ItemSID,JOIN Vendor Mfr ON Mfr.VendorSID = I.VendorSID,JOIN VendorContract VC ON VC.VendorContractSID = AI.VendorContractSID,JOIN Contract C ON C.ContractSID = VC.ContractSID,JOIN Ref_PricingTier PT ON PT.PricingTierSID = P.PricingTierSID,JOIN ContractGroup CG ON CG.VendorContractSID = VC.VendorContractSID --AND CG.PricingTierSID = P.PricingTierSID,JOIN Junc_GroupParticipant GP ON GP.GroupSID = CG.GroupSID,JOIN Ref_Participant PAR ON PAR.ParticipantSID = GP.ParticipantSID,JOIN Ref_UnitOfMeasure UOM ON UOM.UOM_SID = P.UOM_SID,JOIN Ref_OrderFromType OFT ON OFT.OrderFromTypeID = C.OrderFromTypeID,JOIN Ref_UNSPSC_Taxonomy UNSPSC ON UNSPSC.UNSPSC_Code = I.UNSPSC_Code" &_
"LEFT JOIN Junc_ItemMaterialType IMT_ContainsLatex" &_
"ON IMT_ContainsLatex.ItemSID = I.ItemSID" &_
"AND IMT_ContainsLatex.MaterialTypeSID = 1 -- Contains Latex" &_
"LEFT JOIN Junc_ItemMaterialType IMT_ContainsHazMat" &_
"ON IMT_ContainsLatex.ItemSID = I.ItemSID" &_
" AND IMT_ContainsHazMat.MaterialTypeSID = 2 -- Contains Hazardous Material" &_
"LEFT JOIN Junc_ItemMaterialType IMT_PVCDEHPFree" &_
"ON IMT_PVCDEHPFree.ItemSID = I.ItemSID" &_
" AND IMT_PVCDEHPFree.MaterialTypeSID = 3 -- PVC/DEHP Free" &_
"LEFT JOIN Junc_ItemMaterialType IMT_MercuryFree" &_
"ON IMT_MercuryFree.ItemSID = I.ItemSID" &_
"AND IMT_MercuryFree.MaterialTypeSID = 4 -- Mercury Free" &_
"LEFT JOIN Junc_ItemMaterialType IMT_ThimerosalFree" &_
"ON IMT_ThimerosalFree.ItemSID = I.ItemSID" &_
"AND IMT_ThimerosalFree.MaterialTypeSID = 5 -- Thimerosal Free" &_
"LEFT JOIN Junc_ItemMaterialType IMT_LatexFree" &_
"ON IMT_LatexFree.ItemSID = I.ItemSID" &_
"AND IMT_LatexFree.MaterialTypeSID = 6 -- Latex Free" &_
"WHERE" &_
"C.ContractNumber = 82,AND PAR.ParticipantSID = 180 -- Centennial Medical Center,AND P.UOM_Type_SID = 1, AND @CURRENT_DATE BETWEEN CG.CG_EffectiveDate AND CG.CG_ExpireDate,AND @CURRENT_DATE BETWEEN VC.VC_EffectiveDate AND VC.VC_ExpireDate,AND @CURRENT_DATE BETWEEN P.PriceEffectiveDate AND P.PriceExpireDate,AND P.PriceAmount > 0"
ObjRecordset.ActiveConnection=myMainConnection
myMainConnection.open 'This will execute your Query
If ObjRecordset.recordcount>0 then
ItemShortDescription = ObjRecordset ("ItemShortDescription").Value
ItemLongDescription = ObjRecordset ("ItemLongDescription").Value
ItemFullDescription = ObjRecordset ("ItemFullDescription").Value
ItemSID = ObjRecordset ("ItemSID").Value
StandardItemNumber = ObjRecordset ("StandardItemNumber").Value
VendorCatalogNumber = ObjRecordset ("VendorCatalogNumber").Value
VendorName = ObjRecordset ("VendorName").Value
VendorNumber = ObjRecordset ("VendorNumber").Value
VendorSID = ObjRecordset ("VendorSID").Value
VendorCatalogNumber = ObjRecordset ("VendorCatalogNumber").Value
UOM_Code = ObjRecordset ("UOM_Code").Value
UOM_Factor = ObjRecordset ("UOM_Factor").Value
PriceEffectiveDate = ObjRecordset ("PriceEffectiveDate").Value
ContractNumber = ObjRecordset ("ContractNumber").Value
PricingTierDescription = ObjRecordset ("PricingTierDescription").Value
PriceAmount = ObjRecordset ("PriceAmount").Value
OrderFromTypeDescription = ObjRecordset ("OrderFromTypeDescription").Value
UNSPSC_Code = ObjRecordset ("UNSPSC_Code").Value
UNSPSC_Description = ObjRecordset ("UNSPSC_Description").Value
ContainsHazMat = ObjRecordset ("ContainsHazMat").Value
ContainsLatex = ObjRecordset ("ContainsLatex").Value
PVCDEHPFree = ObjRecordset ("PVCDEHPFree").Value
MercuryFree = ObjRecordset ("MercuryFree").Value
ThimerosalFree = ObjRecordset ("ThimerosalFree").Value
LatexFree = ObjRecordset ("LatexFree").Value
End if

Public Function ExportToExcel
Dim objexcel,excbook,iSheet,sFileName,objSheet
sFileName = "C:\CatScanQuery.xls" 'Filename of your workbook
iSheet = 1 'The sheet # in the workbook that contains the data
Set objexcel= createobject("excel.application")
objexcel.Visible = True
set excbook=objexcel.workbooks.open(sFileName)
set objSheet=excbook.worksheet(iSheet)
objexcel.Quit
End Function

Public Function CreateSqlConnection()
Dim myConnection
Set myConnection=CreateObject("ADODB.Connection")
myConnection.ConnectionString = "Driver=SQL Server;Trusted_Connection=Yes;DATABASE=IMSWeb;SERVER=ENTRDBS2KQA03;"
myConnection.Open
myConnection.Close
Set CreateSqlConnection = myConnection
End Function ]

Any guidance anyone could provide would be greatly appreciated. Also, if there is a FAQ out here already please direct me to that also. Like I stated earlier I saw some database threads but nothing that addressed this particular scenario.

Thanks,
Micah

 

[PHV]

Replace this:
myMainConnection.open 'This will execute your Query

with this:
ObjRecordset.Open 'This will execute your Query


But I'd fix the numerous errors in the SQL code ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BionicHartman]

Thanks PH. I made the change as described but I am still getting the same error. This is only my second VBScript and I the query portion I wrote in Query Analyzer and pasted it into here. So I will be more than happy to fix the errors, is it just in the query portion that you see issues or the whole script? If you would let me know and I will go from there.

Thanks again, I appreciate your help.
Micah

 

[guitarzan]

Are you sure you're still getting the same error? I believe that you're getting an error, but if you made the change that PHV suggested, it's probably a different one.