• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

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
0
ricosuave
Asked:
ricosuave
  • 2
  • 2
1 Solution
 
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
 
GranModCommented:
PAQed with points refunded (200)

GranMod
Community Support Moderator
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now