asked on

Object required: 'ActiveConnection' ASP Classic MS SQL

this was working, i change the call to the database to use the Parameters element of ADO as just passing the values failed when passing HTML to database..

i have obviously done something wrong but been looking at this now for 4 days and not got any closer to a solution..

your help would be a god send
Thank you

DIM cmd, DbStr
DbStr = "User ID=xxxx;Password=xxxx;Initial Catalog=CMMS;Data Source=MYCMMS;Provider=SQLOLEDB"

Set cmd = Server.CreateObject("ADODB.Command")
set cmd.ActiveConnection=DbStr
cmd.ActiveConnection.open


'Prepare the stored procedure
        cmd.CommandText = "ConfUpdate"
        cmd.CommandType = adCmdStoredProc
	cmd.Parameters("@Company") = MyCompany
	cmd.Parameters("@address1") = Myaddress1
	cmd.Parameters("@address2") = Myaddress2
	cmd.Parameters("@city") = Mycity
	cmd.Parameters("@county") = Mycounty
	cmd.Parameters("@postcode") = Mypostcode
	cmd.Parameters("@phone") = Myphone
	cmd.Parameters("@WorkorderEmailTPL") = MyWorkorderEmailTPL
	cmd.Parameters("@VendorEmailtpl") = MyVendorEmailtpl
	cmd.Parameters("@InstanceID") = MyInstanceID
	cmd.Parameters("@VersionNo") = MyVersionNo
	cmd.Parameters("@updatedBy") = MyupdatedBy
	cmd.Parameters("@IssueDate") = MyIssueDate
	cmd.Parameters("@IssuedBy") = MyIssuedBy
	cmd.Parameters("@LiveDate") = MyLiveDate
	cmd.Parameters("@emailfooter") = Myemailfooter
	cmd.Parameters("@emailDisclaimer") = MyemailDisclaimer
	cmd.Parameters("@action") = Myaction
	cmd.Parameters("@confID") = MyconfID


       cmd.Execute true

cmd.ActiveConnection.Close
SET cmd.ActiveConnection = Nothing

Open in new window

Microsoft VBScript runtime error '800a01a8'

Object required: 'ActiveConnection'

/cmms/common/configupdate.asp, line 69

Partha Mandayam

You need a connection object also
See https://docs.microsoft.com/en-us/sql/ado/guide/data/creating-and-executing-a-simple-command?view=sql-server-ver15

Tony Owers

ASKER

Sorry not getting what you mean, those examples on that page do not use Parameters, my code works without parameters if i just pass the values, my problem is some of those values contain special chars that make the procedure fail.. so i need to use parameters..

also i can not use " As New" this is unrecognized in ASP Classic

Partha Mandayam

You need an adodb.connection object also

Tony Owers

ASKER

how does that fit into the code?

slightwv (䄆 Netminder)

My classic ASP and VBScript is really rusty but I'm not sure you can just use cmd.parameters that way.

I remember having to create the parameter and add it to the collection.

The docs have an example:
https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/parameters-collection-ado?view=sql-server-ver15

Partha Mandayam

It's basic fundamentals. You have to have a connection object first before you create a command object

ASKER CERTIFIED SOLUTION

Partha Mandayam

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Big Monty

you havent passed any type of connection object to your command object, but rather a string, which contains your connection info, which IS valid. Try removing the line below:

cmd.ActiveConnection.open

Tony Owers

ASKER

Thank you got it working, code as follows..

DIM cmd, DbStr, conn
DbStr = "User ID=xxxx;Password=xxxx;Initial Catalog=CMMS;Data Source=MYCMMS;Provider=SQLOLEDB"

Set cmd = Server.CreateObject("ADODB.Command")
set conn = Server.CreateObject("ADODB.Connection")
conn.open DbStr
cmd.ActiveConnection = conn


cmd.CommandText = "ConfUpdate"
cmd.CommandType = 4

	cmd.Parameters("@Company") = MyCompany
	cmd.Parameters("@address1") = Myaddress1
	cmd.Parameters("@address2") = Myaddress2
	cmd.Parameters("@city") = Mycity
	cmd.Parameters("@county") = Mycounty
	cmd.Parameters("@postcode") = Mypostcode
	cmd.Parameters("@phone") = Myphone
	cmd.Parameters("@WorkorderEmailTPL") = MyWorkorderEmailTPL
	cmd.Parameters("@VendorEmailtpl") = MyVendorEmailtpl
	cmd.Parameters("@InstanceID") = MyInstanceID
	cmd.Parameters("@VersionNo") = MyVersionNo
	cmd.Parameters("@updatedBy") = LastupdatedBy

	cmd.Parameters("@IssueDate") = MyIssueDate
	cmd.Parameters("@IssuedBy") = MyIssuedBy
	cmd.Parameters("@LiveDate") = MyLiveDate


	cmd.Parameters("@emailfooter") = Myemailfooter
	cmd.Parameters("@emailDisclaimer") = MyemailDisclaimer
	cmd.Parameters("@action") = Myaction
	cmd.Parameters("@confID") = MyconfID


cmd.Execute true

Open in new window