using a wsc from asp to query a database

Hey Guys,

I want to make use of WSC's in my asp-based website. One of the things I want to put in a component is all the stuff for accessing a database.
I would like to pass the DSN to the WSC, then execute an SQL statement through the component and have the component return a recordset.
The problem is the returning of the recordset. Here is my wsc-code:


<?xml version="1.0"?>
<component>
<?component error="true" debug="true"?>
<registration
      description="dbaction"
      progid="dbaction.WSC"
      version="1.00"
      classid="{f1700385-f8c0-4f5e-a001-89539e1a5224}"
>
</registration>

<public>
      <property name="dsn">
            <get/>
            <put/>
      </property>
      <property name="queryResult">
            <get/>
      </property>
      <method name="executeSQL">
            <PARAMETER name="SQLstatement"/>
      </method>
      <method name="clearConnection">
      </method>      
      <method name="generateUUID">
      </method>
</public>



<script language="VBScript">
<![CDATA[

Dim dsn,queryResult,Connection

function get_dsn()
      get_dsn = dsn
end function

function put_dsn(newValue)
      dsn = newValue
end function

function get_queryResult()
      get_queryResult = queryResult
end function

function executeSQL(SQLstatement)
      Dim tmpQuery
      Set Connection = CreateObject("ADODB.Connection")
      Connection.ConnectionString = dsn
      Connection.open
      Set tmpQuery = Connection.Execute(SQLstatement)
      executeSQL = tmpQuery
      'Set tmpQuery = Nothing
end function

function clearConnection()
      Connection.close()
      Set Connection = Nothing      
end function

function generateUUID()
'Generates a unique ID
      Dim TypeLib
      Set TypeLib = CreateObject("Scriptlet.TypeLib")
      generateUUID = TypeLib.Guid
      Set TypeLib = Nothing
end function

]]>
</script>
</component>

In the asp page I do the following:

<%
Dim objWSC, Qresult
      ' instantiate object
      Set objWSC = CreateObject("dbaction.wsc")
      
      ' test generateUUID()
      response.write("generateUUID:" & objWSC.generateUUID())
      response.write("<br/>")
      
      ' pass DSN to component
      response.write("Setting DSN...")
      response.write("<br/>")
      objWSC.dsn = "Provider=Microsoft.Jet.OLEDB.4.0; data source="& server.mappath("\data\test.mdb")
      
      ' Query the set DSN
      response.write("get DSN:" & objWSC.dsn)
      response.write("<br/>")
      
      ' Pass a query and display result
      SET Qresult = objWSC.executeSQL("SELECT COUNT(*) FROM Afkortingen")
      response.write("QueryResult:" & Qresult(0))
      SET Qresult = Nothing
      
      ' Second query
      SET Qresult = objWSC.executeSQL("SELECT * FROM Afkortingen")
      do until Qresult.EOF
            response.write(Qresult("Afkorting") & "<br/>")
      Qresult.MoveNext
        loop
      SET Qresult = Nothing
      
      ' clear the connection after all database action is finished
      objWSC.clearConnection()
      
      ' Finished with the object
      Set objWSC = Nothing
%>

Now the query for geting the COUNT works, the problem occurs after I want to return more than one record.
In the component I assign the resulting recordset and in my asp code I assign this result to "Qresult". I assumed I could then use Qresult as if it were a recordset. The problem is it isn't recognised as such. When I try to do recordset specific things like "movenext" or ".EOF" this doesn't work.
It seems asp doesn't recognise my object as a recordset.
If anyone can give me an example of a working code where a recordset is passed from the WSC I'm a happy man ;)

Thanks in advance,
Erik
LVL 2
ricosuaveAsked:
Who is Participating?
 
GranModCommented:
PAQed with points refunded (200)

GranMod
Community Support Moderator
0
 
kevp75Commented:
what's the error?
0
 
ricosuaveAuthor Commented:
Hey kevp75,

The error is in Dutch, because my developmentmachine is in dutch, but it is:

Runtimefout Microsoft VBScript (0x800A01B6)
Deze eigenschap of methode wordt niet ondersteund door dit object: 'EOF'
/index.asp, line 41

Which trandslates roughly into:

Runtime error Microsoft VBScript (0x800A01B6)
This property or method is not supported by this object: 'EOF'
/index.asp, line 41

Erik
0
 
kevp75Commented:
ok.
can you temporarily comment these lines out of your wsc file:
function executeSQL(SQLstatement)
     Dim tmpQuery
     Set Connection = CreateObject("ADODB.Connection")
     Connection.ConnectionString = dsn
     Connection.open
     'Set tmpQuery = Connection.Execute(SQLstatement)     <-this one
     'executeSQL = tmpQuery               <-this one 2
     'Set tmpQuery = Nothing
end function


and try it again
0
 
ricosuaveAuthor Commented:
Sorry for my non-reply. I only remembered the question after I got the abandonement mail.

The comments I got here were not a solution.
I figured it out in the end myself.
The solution is to replicate the methods you need in the WSC you write.

So, if you write a "replacement" recordset-object in WSC, and you need a "movenext" you need to write a "movenext" in your WSC that passes this through to the recordset object.

This solved it for me, none of the expert advices were any help I'm afraid.

Erik
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.