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 3 of 3
  1. #1
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts

    VBScript classes - calling sibling functions

    I'm trying - more as an excercise than anything else - to write a "database interface" class in VBScript:

    Code:
    <%
    class databaseInterface
    
    public oCn
    
    private sub Class_Initialize()
    '------------------------------------------------------------------------------
    '	init connection object
    '------------------------------------------------------------------------------
    	set oCn = Server.CreateObject("ADODB.Connection")
    	oCn.open application("sCn")
    end sub
    
    
    private sub Class_Terminate()
    '------------------------------------------------------------------------------
    '	clean up ADO objects
    '------------------------------------------------------------------------------
    	if isObject(oCn) then
      		oCn.close
    		set oCn = nothing
    	end if
    end sub
    
    
    public function QuerySQL(ByVal sSQL)
    '------------------------------------------------------------------------------
    '	return query resultset as array, or return sql string if no results
    '------------------------------------------------------------------------------
    	dim oRs
    	set oRs = oCn.execute(sSQL)
    	if not oRs.EOF then
    		QuerySQL = oRs.getRows()
    	else
    		QuerySQL = sSQL
    	end if
    end function
    
    
    public function InsertSQL(ByVal sTableName, ByVal aData)
    '------------------------------------------------------------------------------
    '	insert aData as row into sTableName. Return new row ID in recordset array
    '------------------------------------------------------------------------------
    	dim aTableFields : aTableFields = DataFields.Item(sTableName)
    	dim i, sSQL, aRV
    	sSQL = "INSERT INTO " & sTableName & " "
    	sSQL = sSQL & FieldsList(aTableFields)
    	sSQL = sSQL & " VALUES "
    	sSQL = sSQL & DataList(aTableFields, aData)
    	sSQL = sSQL & " ;SELECT MAX([ID]) FROM " & sTableName
    	aRV = QuerySQL(sSQL)
    	if isArray(aRV) then rv = aRV(0,0)
    	InsertSQL = rv
    end function
    
    end class
    Obviously there are a few functions in the class that I haven't pasted above, simply because they don't appear to be relevant to the problem I'm having.

    The problem is with the function InsertSQL. Having generated a SQL statement, it then attempts to call QuerySQL to execute it.

    I get an error: "Operation is not allowed when the object is closed". Now, it's telling me that the error is on a line in function QuerySQL; specifically the one that reads "if not oRs.EOF then". Which is weird.

    The only reason I can think of is that I can't call QuerySQL the way I am, from within InsertSQL. I know I'm creating and opening a connection object when I create an instance of the class. I think that I've correctly made the connection object available to the class functions. I know that I'm creating and opening a recordset object from within QuerySQL. So... I'm a bit stuck. Do I just need to do something like "parentClass.QuerySQL()", or something?

  • #2
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Actually... has this got anything do do with the fact that I'm bolting a SELECT onto the end of an INSERT? I ran it through Query Analyser and it seemed to work, so I just assumed that I could run it through ADODB.

  • #3
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    Aha

    It's got bugger all to do with classes

    It was the running together of SQL statements.

    After extensive and exhaustive Googling I can proudly present the following discovery: you CAN insert a new row and get the new row ID in one statement, using the magic of SET NOCOUNT ON

    Code:
    public function InsertSQL(ByVal sTableName, ByVal aData)
    '------------------------------------------------------------------------------
    '	insert aData as row into sTableName. Return new row ID
    '------------------------------------------------------------------------------
    	dim aTableFields : aTableFields = DataFields.Item(sTableName)
    	dim sSQL, aNewRowId, rv
    	rv = 0
    	'	Set NOCOUNT ON to suppress return messages from INSERT / UPDATE statements.
    	'	The query will then return an open recordset containing the new row ID, as selected in the final SQL statement.
    	'	http://www.kamath.com/tutorials/tut007_identity.asp
    	sSQL = "SET NOCOUNT ON; "
    	sSQL = sSQL & "INSERT INTO " & sTableName & " "
    	sSQL = sSQL & FieldsList(aTableFields)
    	sSQL = sSQL & " VALUES "
    	sSQL = sSQL & DataList(aTableFields, aData)
    	sSQL = sSQL & "; SELECT @@IDENTITY AS newRowID"
    	aNewRowId = QuerySQL(sSQL)
    	if isArray(aNewRowId) then
    		rv = aNewRowId(0,0)
    	end if
    	InsertSQL = rv
    end function


  •  

    Posting Permissions

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