Solved

setting up a sub in vbscript

Posted on 2014-03-03
13
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
13 Comments
 
LVL 33

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 53

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 33

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 53

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 33

Expert Comment

by:Big Monty
ID: 39900980
what's the error you're getting?
0
 

Author Comment

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

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 33

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 33

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 53

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well hello again!  Glad to see you've made it this far without giving up.  In this, the fourth installment of my popular series, I'm going to cover functions and subroutines, what they are, and why they are useful.  Just in case you stumbled onto th…
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 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