setting up a sub in vbscript

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

rivkamakAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
"it doesn't work" is pretty vague, can you be more specific?

do you get an error or does nothing happen?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
Big MontyWeb Ninja at largeCommented:
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?
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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

0
rivkamakAuthor 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

0
Big MontyWeb Ninja at largeCommented:
what's the error you're getting?
0
rivkamakAuthor Commented:
Object required: ''
0
Big MontyWeb Ninja at largeCommented:
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

0
Big MontyWeb Ninja at largeCommented:
if that doesn't work, then please post the error along with the line number it is referencing
0
rivkamakAuthor 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.
0
Big MontyWeb Ninja at largeCommented:
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

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rivkamakAuthor Commented:
that's an interesting idea. let me try it
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.