Error with execute method

[ScottInTexas]

I am trying to update a record in the DB with the connection.execute method and I get this error everytime.

Microsoft VBScript runtime error '800a01a8'

Object required: '[string: "Provider=Microsoft.J"]'

/GetData.asp, line 78

I have a recordset to which I have made changes. The connection is opened at the top of the asp with a call to GetConn, then the record is displayed and changes can be made on the form. When the user clicks save this same asp is called and it falls through to the this code.

	Case "Save"
	SQL="UPDATE " & session("curtbl") & " SET "
	for each fld in RS.Fields
                    If right(fld.Name, 2)<>"ID" Then
	           SQL=SQL & fld.name & "='" 
	            If Request.Form(fld.name)="" Then
		    SQL=SQL & fld.value & "',"
	            Else
		    SQL=SQL & Request.Form(fld.name) & "',"
	            End If			
	         End If
	next
	 SQL=LEFT(SQL, len(SQL)-1) & " WHERE " & session("Key")
	 Response.Write(SQL)
	 Set cnxn=GetConn
	 cnxn.Execute SQL

This call to GetConn seen here has just been added because I thought I was loosing the connection somehere, but I still get the same error. This is GetConn
[code=vb]
Function GetConn()
Dim cnxtion
set cnxtion=Server.CreateObject("ADODB.Connection")
cnxtion.Provider="Microsoft.Jet.OLEDB.4.0"
cnxtion.Open(Server.MapPath("/database/website.mdb"))
GetConn=cnxtion
Set cnxtion=nothing
End Function
[/code]

Why is the error occuring?

Thanks for you help,

[M@rco]

GetConn=cnxtion

should be

Set GetConn=cnxtion

because it's an object, not a value.

Also, when posting a problem there's not point telling us that the error is on line 78 without making it clear what line 78 actually is!!!

[whammy]

Er... ok I do it a bit more simply... like so:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "WHATEVER your dsn is"

Set rs = Conn.Execute("SELECT * FROM tablename where something = whatever")

Do While NOT rs.EOF Then
	' Do stuff here
rs.MoveNext
Loop

Set rs = Nothing
Conn.Close
Set Conn = Nothing

Note that this syntax will work with SQL Server, Access, and most likely other databases as long as you have the right DSN...

Anyway to sum it up it seems like you're using confusing names for connections vs. recordsets to begin with. I'd have a look at http://www.w3schools.com/asp and see how they are doing it, they have pretty good examples, anyway.

P.S. I'm not sure if they have actual connection examples, but they have pretty clear naming conventions.

I can guarantee, however, that the above syntax works as long as you have the right DSN.

[M@rco]

(post removed)

[glenngv]

Originally posted by ScottInTexas
This call to GetConn seen here has just been added because I thought I was loosing the connection somehere, but I still get the same error. This is GetConn...

Make sure that you only have ONE connection object in a page unless you are accessing multiple databases.

[miranda]

Function GetConn()
Dim cnxtion
set cnxtion=Server.CreateObject("ADODB.Connection")
cnxtion.Provider="Microsoft.Jet.OLEDB.4.0"
cnxtion.Open(Server.MapPath("/database/website.mdb"))
GetConn=cnxtion
Set cnxtion=nothing
End Function

Set cnxn=GetConn
cnxn.Execute SQL


isn't this releasing the connection before it is even being used?
take this part out and see if it works Set cnxtion=nothing then do your cleanup later on

[whammy]

m@rco, you may be right, but I think perhaps Miranda may have hit the nail on the head.

Go ahead and code a better answer... the ball is in your court.

[M@rco]

Actually, I believe my first post in this thread has already solved the problem! The connection object is an object, and must therefore be assigned to as the function's return by using the SET keyword! I don't know how to make it any plainer!


However, there's a little more to it than that... Miranda is correct in pointing out that the object is being set to nothing before the close of the function and is therefore being killed as the function currently stands, but since in VBScript you cannot truly dispose an object until *all* the variables which reference it have been set to nothing (or go out of scope), as soon as the code is corrected to use the SET keyword (as already stated above), the function will *still* return a valid connection object as expected.

Of course, it goes without saying that the line setting the object to equal nothing is *completely* superfluous and should be removed, but I just wanted to point out that it doesn't actually mess anything up (once the real error is corrected) because of the way that object references work. (So it's a red herring.)

Since dereferencing objects is a topic which is often misunderstood, I would like to take this opportunity to clarify it, and so I have written the following demonstration code to make it crystal clear - I hope it's enlightening (and perhaps even surprising!):

'create a Connection object
Set A = Server.CreateObject("ADODB.Connection")

'set B to reference the same object as A
Set B = A

'print the object type of both variables
Response.Write "Start: A=" & TypeName(A) & "<br />"
Response.Write "Start: B=" & TypeName(B) & "<br />"

'set A to nothing
Set A = Nothing
Response.Write "Middle: A=" & TypeName(A) & "<br />"
Response.Write "Middle: B=" & TypeName(B) & "<br />"

'and finally set B to nothing
Set B = Nothing
Response.Write "End: A=" & TypeName(A) & "<br />"
Response.Write "End: B=" & TypeName(B) & "<br />"

Now are the results what you were expecting?!

And Scott, once you add the "Set" keyword as I indicated, does the function function?!

And you STILL haven't said what line 78 is!!!

[whammy]

Surprisingly, that's exactly what I expected!

However, I must admit that is a great example... good going.