Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    Join Date
    Nov 2002
    Posts
    567
    Thanks
    2
    Thanked 4 Times in 4 Posts

    Error with execute method

    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.

    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,
    Scott Stewart
    Always happy to learn from pros.

  • #2
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Code:
    GetConn=cnxtion
    should be
    Code:
    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!!!

    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Er... ok I do it a bit more simply... like so:

    Code:
    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.
    Last edited by whammy; 02-10-2004 at 02:07 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #4
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    (post removed)
    Last edited by M@rco; 02-12-2004 at 09:17 AM.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #5
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    10,966
    Thanks
    0
    Thanked 236 Times in 233 Posts

    Re: Error with execute method

    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.

  • #6
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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

  • #7
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Last edited by whammy; 02-12-2004 at 02:09 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #8
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    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!):
    Code:
    '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!!!
    Last edited by M@rco; 02-12-2004 at 03:31 PM.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #9
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Surprisingly, that's exactly what I expected!

    However, I must admit that is a great example... good going.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •