Solved

setting up a sub in vbscript

Posted on 2014-03-03
13
329 Views
Last Modified: 2014-03-03
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

0
Comment
Question by:rivkamak
  • 6
  • 4
  • 3
13 Comments
 
LVL 32

Expert Comment

by:Big Monty
ID: 39900857
"it doesn't work" is pretty vague, can you be more specific?

do you get an error or does nothing happen?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39900888
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39900903
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
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39900911
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
 

Author Comment

by:rivkamak
ID: 39900974
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39900980
what's the error you're getting?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rivkamak
ID: 39900982
Object required: ''
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39900994
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39900995
if that doesn't work, then please post the error along with the line number it is referencing
0
 

Author Comment

by:rivkamak
ID: 39901004
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
 
LVL 32

Expert Comment

by:Big Monty
ID: 39901024
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
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 39901040
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
 

Author Comment

by:rivkamak
ID: 39901043
that's an interesting idea. let me try it
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now