setting up a sub in vbscript

rivkamak
rivkamak used Ask the Experts™
on
I am trying to set up sending a stored procedure as a sub so I can reuse it just by passing in the proc name.
when I run the code straight it works. when I turn it into a sub with the variable as the procedure name and try to run it, it doesn't work.
Did I code something wrong?
sub getData(procedure)
	set viewDates = Server.CreateObject("ADODB.Command")
	viewDates.ActiveConnection = MM_TowerConn2_STRING
	viewDates.CommandText = procedure
	viewDates.Parameters.Append viewDates.CreateParameter("@ShadchinId", 200, 1,50,Session("MM_Username"))
	set viewDatesList = viewDates.Execute
	
	
end sub

 getData "Csm56ShadchinViewDates"

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Big MontyWeb Ninja at large

Commented:
"it doesn't work" is pretty vague, can you be more specific?

do you get an error or does nothing happen?
Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
The variables inside the sub are not going to used outside.

You have MM_TowerConn2_STRING and that may not be what you think it is.

You can try sub getData(procedure,MM_TowerConn2_STRING)

And to call
 getData "Csm56ShadchinViewDates",MM_TowerConn2_STRING

Open in new window

Big MontyWeb Ninja at large

Commented:
you can still access variables defined outside of your sub, these are called global variables.

http://msdn.microsoft.com/en-us/library/ms524870(v=vs.90).aspx

i don't think scope is an issue here. Did you ensure your session variable is populated correctly?
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Scott FellDeveloper & EE Moderator
Fellow 2018
Most Valuable Expert 2013

Commented:
Also, if you are trying to view results, try using a function instead.

function getData(procedure,connectionString)
	set viewDates = Server.CreateObject("ADODB.connectionString")
	viewDates.ActiveConnection = MM_TowerConn2_STRING
	viewDates.CommandText = procedure
	viewDates.Parameters.Append viewDates.CreateParameter("@ShadchinId", 200, 1,50,Session("MM_Username"))
	set viewDatesList = viewDates.Execute
	
       getData=xyz ' the output you expect
	
end function

response.write getData ("Csm56ShadchinViewDates", MM_TowerConn2_STRING) 

Open in new window

Author

Commented:
Thank you for your advice. So I tried putting a response.write for a returned field and it is pulling the proc.
      response.write("<br>" & (viewDatesList.Fields.Item("KVSDT_Date").Value))

If I put in after I pull the sub
      response.write("<br>" & (viewDatesList.Fields.Item("KVSDT_Date").Value))

i get an error .

I am trying to find the best way to display the returned information out of the proc.
It's returning multiple rows, so I'm not sure how I could do that in a function.
I was hoping to use a sub where it would execute.

then outside the subI would continue the process, displaying it differently each time

 
While ((RepeatDates__numRows <> 0) AND (NOT viewDatesList.EOF)) 
=(viewDatesList.Fields.Item("KVSDT_Date").Value)
  RepeatDates__index=RepeatDates__index+1
  RepeatDates__numRows=RepeatDates__numRows-1
    viewDatesList.MoveNext()
Wend

Open in new window

Big MontyWeb Ninja at large

Commented:
what's the error you're getting?

Author

Commented:
Object required: ''
Big MontyWeb Ninja at large

Commented:
I would recommend looping directly in the sub, much easier that way:

sub getData(procedure)
	set viewDates = Server.CreateObject("ADODB.Command")
	viewDates.ActiveConnection = MM_TowerConn2_STRING
	viewDates.CommandText = procedure
	viewDates.Parameters.Append viewDates.CreateParameter("@ShadchinId", 200, 1,50,Session("MM_Username"))
	set viewDatesList = viewDates.Execute

        if not viewDatesList.EOF then
             do while not viewDatesList 
                  Response.Write "value1 = " & viewDatesList( 0 ) & "<br/>"
                  Response.Write "value2 = " & viewDatesList( 1 ) & "<br/>"
                  Response.Write "value3 = " & viewDatesList( 2 ) & "<br/>"
                  Response.Write "value4 = " & viewDatesList( 3 ) & "<br/><br/>"
                  'etc etc
                  viewDatesList.MoveNext
             loop
        end if
	
	
end sub

 getData "Csm56ShadchinViewDates"

Open in new window

Big MontyWeb Ninja at large

Commented:
if that doesn't work, then please post the error along with the line number it is referencing

Author

Commented:
Problem is , i'm trying to find a way to reuse the script and each proc has different columns.
some return 1 and some return up to 5.
Big MontyWeb Ninja at large

Commented:
I think you should use the GetRows method. Returns a two-dimensional array. You don't need column names through GetRows. Consider this:

sub getData(procedure)
	set viewDates = Server.CreateObject("ADODB.Command")
	viewDates.ActiveConnection = MM_TowerConn2_STRING
	viewDates.CommandText = procedure
	viewDates.Parameters.Append viewDates.CreateParameter("@ShadchinId", 200, 1,50,Session("MM_Username"))
	set viewDatesList = viewDates.Execute

        if not viewDatesList.EOF then
             arrViewDatesList  = rs.GetRows
             For row = 0 To UBound(arrViewDatesList, 2) 'Rows
                 For col = 0 To UBound(arrViewDatesList, 1) 'Columns
                    Response.Write viewDatesList .Fields(col).Name & " = " & arrViewDatesList(col, row) & " "
                 Next
                 Response.Write "<br />"
            Next
        end if
end sub

Open in new window

Developer & EE Moderator
Fellow 2018
Most Valuable Expert 2013
Commented:
And each proc will may have different parameters

If you reuse the same code over you can put the code on a separate .asp page and include it

<!--#include virtual="/procedures/Csm56ShadchinViewDates.asp" --> 

Open in new window

That page can contain just
set viewDates = Server.CreateObject("ADODB.Command")
	viewDates.ActiveConnection = MM_TowerConn2_STRING
	viewDates.CommandText = procedure
	viewDates.Parameters.Append viewDates.CreateParameter("@ShadchinId", 200, 1,50,Session("MM_Username"))
	set viewDatesList = viewDates.Execute
	

Open in new window

On any page you need to use that same code, just insert the include statement.  It will render just as if it was there.

Author

Commented:
that's an interesting idea. let me try it

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial