Web Analytics Made Easy -
StatCounter Error with execute method - CodingForum

Announcement

Collapse
No announcement yet.

Error with execute method

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new 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,

  • #2
    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"

    Comment


    • #3
      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; Feb 9, 2004, 09:07 PM.
      Former ASP Forum Moderator - I'm back!

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

      Comment


      • #4
        (post removed)
        Last edited by [email protected]; Feb 12, 2004, 04:17 AM.
        Marcus Tucker / www / blog
        Web Analyst Programmer / Voted SPF "ASP Guru"

        Comment


        • #5
          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.
          Glenn
          vBulletin Mods That Rock!

          Comment


          • #6
            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

            Comment


            • #7
              [email protected], 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; Feb 11, 2004, 09:09 PM.
              Former ASP Forum Moderator - I'm back!

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

              Comment


              • #8
                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 [email protected]; Feb 12, 2004, 10:31 AM.
                Marcus Tucker / www / blog
                Web Analyst Programmer / Voted SPF "ASP Guru"

                Comment


                • #9
                  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. ;)

                  Comment

                  Working...
                  X