...

View Full Version : VBScript classes - calling sibling functions



Spudhead
05-03-2006, 03:48 PM
I'm trying - more as an excercise than anything else - to write a "database interface" class in VBScript:



<%
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?

Spudhead
05-03-2006, 03:54 PM
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.

Spudhead
05-03-2006, 04:51 PM
Aha :)

It's got bugger all to do with classes :o

It was the running together of SQL statements.

After extensive and exhaustive Googling :rolleyes: 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 :thumbsup:



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



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum