problem passing function arguments vbscript

Question

I have a little problem with vbscript I have declared some function with passing the result set from a sql query like an argument and the problem is that the function showData accept the argument like an object not like a resultset

function get_count(conn)
    dim query, size
    Set query = Server.CreateObject("ADODB.Recordset")
    set query = conn.Execute("select count(*) as size from doctor")
    size = query.Fields("size")
    get_count = size
end function
function get_rows_from_to(from,size,conn)
    dim result
    Set result = Server.CreateObject("ADODB.Recordset")
    set result = conn.Execute("SELECT name, surname,family,egn,citizenship FROM doctor limit "&from&","&size&"")
    get_rows_from_to = result
end function
Sub showData(objRS)
    dim isEven
    isEven = false
    Response.Write "<table>"
    Response.Write "<tr>"
    Response.Write "<th >Име</th><th >Презиме</th><th >Фамилия</th><th >ЕГН</th><th >Гражданство</td>"
    Response.Write "</tr>"
    While Not objRS.EOF
        Response.Write "<tr>"
        if isEven then
            Response.Write "<td style = 'background-color :#e9ebf2'>"&objRS.Fields("name")&"</td>"&_
                "<td style = 'background-color :#e9ebf2'>"&objRS.Fields("surname")&"</td>"&_
                "<td style = 'background-color :#e9ebf2'>"&objRS.Fields("family")&"</td>"&_
                "<td style = 'background-color :#e9ebf2'>"&objRS.Fields("egn")&"</td>"&_
                "<td style = 'background-color :#e9ebf2'>"&objRS.Fields("citizenship")&"</td>"
            isEven = false
        else
            Response.Write "<td>"&objRS.Fields("name")&"</td><td>"&objRS.Fields("surname")&"</td><td>"&objRS.Fields("family")&"</td><td>"&objRS.Fields("egn")&"</td><td>"&objRS.Fields("citizenship")&"</td>"
            isEven = true
        end if
        Response.Write "</tr>"
        objRS.MoveNext
    Wend 
    Response.Write "</table>"
    objRS.Close
end sub
const ROWS_PER_PAGE = 10
Dim sConnection, conn , result,pages,selPage,from,lenght,pos,size

from = 0
lenght = 10
pos = 1
size = 0
sConnection = "DRIVER={MySQL ODBC 5.1 Driver}; SERVER=localhost; DATABASE=docunion; UID=root;PASSWORD=root; OPTION=3" 

Set conn = Server.CreateObject("ADODB.Connection") 
Set result = Server.CreateObject("ADODB.Recordset")
conn.Open sConnection

size = get_count (conn)
size = CInt(size)
if size  > 0 then
    pages = size / 10
    lenght = (ROWS_PER_PAGE*pos)
    set result = get_rows_from_to(from,lenght,conn)
    from = lenght + 1
    pos = pos + 1
    showData(result)
else
    Set result = Nothing
    conn.Close
    Set conn = Nothing
end if

Could anyone tell me where is the problem here. I'm new in vbscript. This is the error in the function showData with the object objRS

Microsoft VBScript runtime error '800a01b6'
Object doesn't support this property or method: 'EOF'
/index.asp, line 57

Tomalak · Accepted Answer · 2011-06-12 20:37:42Z

3

The main problem is that functions that return an object reference must use Set for their return value:

function get_rows_from_to(from,size,conn)
    ' ...
    set get_rows_from_to = result
end function

A few other hints:

There is no need to declare functions before you use them. You can put all your function declarations at the end of the script and keep the main script body at the top.

This is needlessly redundant:

function get_rows_from_to(from,size,conn)
    dim result
    Set result = Server.CreateObject("ADODB.Recordset")
    set result = conn.Execute("SELECT name, surname,family,egn,citizenship FROM doctor limit "&from&","&size&"")
    set get_rows_from_to = result
end function

and is equivalent to this

function get_rows_from_to(from,size,conn)
    set get_rows_from_to = conn.Execute("SELECT name, surname,family,egn,citizenship FROM doctor limit " & from & "," & size &"")
end function

conn.Execute() already returns an ADODB.RecordSet. It makes no sense to create a blank one via Server.CreateObject() before. In fact, all your Set xyz = Server.CreateObject("ADODB.Recordset") lines are superfluous.

There is no sense in creating a separate variable for the return value in Functions. The function is its return value:

function getFoo()
    getFoo = "foo"
end function

Two final tips:

Always use Option Explicit.
Always use Server.HtmlEncode() on any piece of data you output to the page. This is even more important than all the other tips.

edited Jun 12, 2011 at 20:37

answered Jun 12, 2011 at 20:28

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dabbler Over a year ago

The hint to use Set was a lifer-saver... +1

Collectives™ on Stack Overflow

problem passing function arguments vbscript

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related