Getting "Object Required" when running VBScript 1

[BionicHartman]

I have a script that is VBScript and not ASP, that when I kick it off I keep getting the error message "Line (2): "Set myMainConnection = CreateSqlConnection()". I am not sure what it is wanting here, below is the code:

Dim myMainConnection
Set myMainConnection = CreateSqlConnection()
myMainConnection.Close
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=ObjConnection
objRecordset.Open 'This will execute your Query
If ObjRecordset.recordcount>0 then
Field 1 = objRecordSet ("ItemShortDescription").Value
Field 2 = objRecordSet ("ItemLongDescription").Value
Field 3 = objRecordSet ("ItemFullDescription").Value
Field 4 = objRecordSet ("ItemSID").Value
Field 5 = objRecordSet ("StandardItemNumber").Value
Field 6 = objRecordSet ("VendorCatalogNumber").Value
Field 7 = objRecordSet ("VendorName").Value
Field 8 = objRecordSet ("VendorNumber").Value
Field 9 = objRecordSet ("VendorSID").Value
Field10 = objRecordSet ("VendorCatalogNumber").Value
Field 11 = objRecordSet ("UOM_Code").Value
Field 12 = objRecordSet ("UOM_Factor").Value
Field 13 = objRecordSet ("PriceEffectiveDate").Value
Field 14 = objRecordSet ("ContractNumber").Value
Field 15 = objRecordSet ("PricingTierDescription").Value
Field 16 = objRecordSet ("PriceAmount").Value
Field 17 = objRecordSet ("OrderFromTypeDescription").Value
Field 18 = objRecordSet ("UNSPSC_Code").Value
Field 19 = objRecordSet ("UNSPSC_Description").Value
Field 20 = objRecordSet ("ContainsHazMat").Value
Field 21 = objRecordSet ("ContainsLatex").Value
Field 22 = objRecordSet ("PVCDEHPFree").Value
Field 23 = objRecordSet ("MercuryFree").Value
Field 24 = objRecordSet ("ThimerosalFree").Value
Field 25 = 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
CreateSqlConnection = myConnection
End Function

The main thing I am trying to do right now is just get connected to the database, I am not too concerned with executing the query at this time.

Any guidance anyone could provide, would be greatly appreciated.

Thank You,
Micah

 

[PHV]

Replace this:
CreateSqlConnection = myConnection
with this:
Set CreateSqlConnection = myConnection

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

 

[BionicHartman]

Thanks, that helped a lot and took care of that error message. Now I am getting an error "Object Variable not set Line (6): "objRecordSet.Source = "DECLARE @CURRENT_DATE DATETIME" &_".

I appreciate the assistance,
Micah