using a variable to indicate the stored procedure to execute

[crystalized]

Hi<br><br>I am not sure if I can do this but here goes.&nbsp;&nbsp;I have a function which performs a series of actions.&nbsp;&nbsp;One of the actions will be to execute a stored procedure.&nbsp;&nbsp;Now all the other actions for the function are the same for more than one form (so I would like to place it in a common functions file).&nbsp;&nbsp;The difference is in the stored procedure that I want to execute (by the way the stored procedures both have 2 parameters).<br><br>I was thinking that I could pass a parameter that would indicate which form I am calling the function from, and that would determine which of the stored procedures I execute.&nbsp;&nbsp;<br><br>I have to admit I am a little spoiled, I am using Visual Interdev for most of the recordsets I need so I am not overly familiar with the syntax for the recordsets etc.&nbsp;&nbsp;I have seen many examples but since I have not had to really use any of them I have a way to go until I am very comfortable with writing my own code for them.<br><br>Does anyone have any advice on how best to execute the stored procedure.&nbsp;&nbsp;I have seen a couple of different methods for doing this and am unsure of which way would be most suitable for my purposes.<br><br>Thanks in advance for any advice<br> <p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>

 

[jfriestman]

Put a hidden form element in your form which indicates the name of the form and use Select...Case to determine which stored procedure to run based on that form element.<br><br>Dim Rs&nbsp;&nbsp;&nbsp;'recordset<br>Dim dsn&nbsp;&nbsp;'datasource<br>Dim sp&nbsp;&nbsp;&nbsp;'stored procedure<br>Dim frm&nbsp;&nbsp;'form name<br><br>frm = Request.Querystring(&quot;formname&quot<br><br>Select Case frm<br>&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;form1&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp = &quot;StoredProcedure1&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;form2&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;sp = &quot;AnotherStoredProcedure&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Case &quot;form3&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;... 'you get the picture<br>&nbsp;&nbsp;&nbsp;&nbsp;Case Else&nbsp;&nbsp;'oops forgot the form element, raise an error<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Err.Raise 50001, &quot;Missing form element: formname&quot;<br>End Select<br><br>'now open the recordset<br>Rs.Open sp, dsn <p> Jeff Friestman<br><a href=mailto: > </a><br><a href=

http://www.brainbench.com/transcript.jsp?pid=61192>

View my Brainbench transcript</a><br>Brainbench 'Most Valuable Professional' for ASP<br>
Brainbench 'Most Valuable Professional' for JavaScript<br>

http://www.brainbench.com

 

[crystalized]

Thanks,<br><br>I played around with this for a little while yesterday and low and behold I have it working really nicely, in a manner very similar to the one you describe.<br><br> <p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>

 

[palbano]

Dear Crystal,<br><br>It would be nice if you posted the relevant code that works for others to use in the future (shared knowledge). You also should consider a 'tipmaster vote' if jfiestman's post helped you to discover your solution.<br><br>&quot;But, that's just my opinion... I could be wrong&quot;.<br>-pete

 

[crystalized]

Well here is the code, I do not know if this is the best way to handle this but nevertheless it works, and is fairly simple<br><br>'************************************************************<br>'name: removeSelected <br>'parameters:&nbsp;&nbsp;&nbsp;&nbsp;originator&nbsp;&nbsp;&nbsp;&nbsp;originating form constant<br>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;lstBoxIn&nbsp;&nbsp;&nbsp;&nbsp;listbox to select from <br>'purpose: To remove the selected employee or project from<br>'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;the database<br>'************************************************************<br> <br>sub removeSelected(originator,lstBoxIn)<br> <br>dim lstBox<br>dim connection<br>dim myDSN<br>dim firm<br>dim indivID<br>dim strMsg<br>dim strExec<br> <br> <br>'prepare the variables<br>set lstBox=lstBoxIn<br>myDSN=Application.Contents(&quot;NetworkDB_ConnectionString&quot<br>firm=Session.Contents(&quot;firmID&quot<br>Session.Contents(&quot;empID&quot=lstBox.getValue()<br>indivID=Session.Contents(&quot;empID&quot<br>strMsg=&quot;&quot;<br>strExec=&quot;&quot;<br> <br>'check if something is selected<br>if (indivID=&quot;&quotthen<br>&nbsp;&nbsp;&nbsp;&nbsp;'nothing selected send message<br>&nbsp;&nbsp;&nbsp;&nbsp;strMsg=&quot;You must select the entry you wish to delete&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;notifyError(strMsg)<br>else<br>&nbsp;&nbsp;&nbsp;&nbsp;'select the correct originator<br>&nbsp;&nbsp;&nbsp;&nbsp;if originator=frmEmployeeSelect then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strExec=&quot;stpRemoveEmployee &quot; & firm & &quot;, &quot; & indivID<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;set connection = server.createobject(&quot;adodb.connection&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection.open myDSN<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Connection.Execute strExec<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Close all objects and set to nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection.close<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;set connection = nothing<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'cause the page to refresh<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;changeForm(frmEmployeeSelect)<br> <br>&nbsp;&nbsp;&nbsp;&nbsp;else 'originator is project<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;set connection = server.createobject(&quot;adodb.connection&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connection.open myDSN<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Connection.Execute &quot;procname varvalue1, varvalue2&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;end if<br> <br>end if<br> <br>end sub<br><br>I know even now looking at it there are ways I can improve it, and likely I will do so shortly, for now though it will suffice as a first run through. <p>Crystal<br><a href=mailto:crystals@genesis.sk.ca>crystals@genesis.sk.ca</a><br><a href= > </a><br>--------------------------------------------------<br>
Experience is one thing you can't get for nothing.<br>
-Oscar Wilde<br>