• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

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

0
rivkamak
Asked:
rivkamak
  • 6
  • 4
  • 3
1 Solution
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
"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 MVEDeveloperCommented:
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 MontySenior Web Developer / CEO of ExchangeTree.org 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?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Scott Fell, EE MVEDeveloperCommented:
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 MontySenior Web Developer / CEO of ExchangeTree.org Commented:
what's the error you're getting?
0
 
rivkamakAuthor Commented:
Object required: ''
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org 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

0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
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 MontySenior Web Developer / CEO of ExchangeTree.org 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

0
 
Scott Fell, EE MVEDeveloperCommented:
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
 
rivkamakAuthor Commented:
that's an interesting idea. let me try it
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now