Run stored procedure when button is clicked on

Is there a way to run a Stored procedure when a button is clicked ?

Using classic ASP/VB script

Here is the code for my button:

<a href="SPDeleteQnr.asp?QnrId=<%=(rs_outboxall.Fields.Item("Id").Value)%>" class="btn btn-white btn-sm" onClick="GP_popupConfirmMsg('Please confirm you wish to delete the Questionnaire.');return document.MM_returnValue"> <i class="fa fa-trash-o"></i> Delete </a>

As you can see right now I have the SP on a separate page. I want to see if I can include it in this page and run it when user clicks on the 'delete' button. On the page it runs on pageload then redirects back to this page.

Here is my SP:

<%

if(Request("QnrId") <> "") then Updatestatus__MMColParam = Request("QnrId")

%>
<%

set Updatestatus = Server.CreateObject("ADODB.Command")
Updatestatus.ActiveConnection = MM_bluedot_STRING
Updatestatus.CommandText = "Delete from Qnrsmailed where id = " + Replace(Updatestatus__MMColParam, "'", "''") + ""
Updatestatus.CommandType = 1
Updatestatus.CommandTimeout = 0
Updatestatus.Prepared = true
Updatestatus.Execute()

%>
LVL 1
AleksAsked:
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.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
first of all, which is your landing page that showing info with the delete button?

once the delete button is clicked, it redirected to SPDeleteQnr.asp with parameter: QnrId=<%=(rs_outboxall.Fields.Item("Id").Value)%>

Hence in SPDeleteQnr.asp, you can do a response.redirect to redirect back to this landing page.

example:

<%

if(Request("QnrId") <> "") then Updatestatus__MMColParam = Request("QnrId")

set Updatestatus = Server.CreateObject("ADODB.Command")
Updatestatus.ActiveConnection = MM_bluedot_STRING
Updatestatus.CommandText = "Delete from Qnrsmailed where id = " + Replace(Updatestatus__MMColParam, "'", "''") + ""
Updatestatus.CommandType = 1
Updatestatus.CommandTimeout = 0
Updatestatus.Prepared = true
Updatestatus.Execute()

Response.Redirect yourPage.asp
%>

Open in new window

AleksAuthor Commented:
Yes. I was just trying to figure out to see if its possible to have the SP on the SAME page without it running, then when a button is clicked call it and pass the necessary parameters to it and refresh the page after it runs.

You can tell I want to delete a record using a SP but right now it is on a separate page then I have to redirect page to the initial one.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>I was just trying to figure out to see if its possible to have the SP on the SAME page without it running
from my opinion, it's always dangerous to run a SP in the SAME page is not a good idea, just imagine if the user is trying to refresh the page for many times, then your page will then send multiple requests to call the SP.

>>You can tell I want to delete a record using a SP but right now it is on a separate page then I have to redirect page to the initial one.
Yup, that's the idea... to separate the presentation page from the processing page....

Workflow
if you don't want to "redirect" to another page, you probably can consider to use AJAX instead, which can send a request without refreshing/redirecting the page itself.

AJAX - Send a Request To a Server
http://www.w3schools.com/ajax/ajax_xmlhttprequest_send.asp

jQuery.ajax()
http://api.jquery.com/jquery.ajax/

I think these 2 suggested ways could be better approaches for a better solution.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AleksAuthor Commented:
Thanks
Big MontyWeb Ninja at largeCommented:
from my opinion, it's always dangerous to run a SP in the SAME page is not a good idea, just imagine if the user is trying to refresh the page for many times, then your page will then send multiple requests to call the SP.

if this is done via AJAX (which I agree would be the best method), then you can easily avoid this issue by disabling the button afterwards, or if you want to keep the button active, disable it, then re-activate it when an action has occurred that warrants use of that piece of functionality.

to implement this via AJAX using jQuery, simply create a function and attach it to your button like so:

<a href="#" class="btn btn-white btn-sm" onClick="return deleteMe(<%=(rs_outboxall.Fields.Item("Id").Value)%>)"> <i class="fa fa-trash-o"></i> Delete </a>

Open in new window


and you javascript function would be:

function deleteMe( id ) {
   if ( GP_popupConfirmMsg('Please confirm you wish to delete the Questionnaire.') ) {
      $.ajax({
          method: "POST",
          url: "SPDeleteQnr.asp",
         data: { QnrId: id }
    })
    .done(function( msg ) {
         alert( "Data Saved: " + msg );    //-- if you don't have any response from the server, you can remove the msg variable from the alert box
     }); 
     return true;
   }
   else {
      return false;
    }
}

Open in new window


this code should get you most of the way, obviously I have no way to test it :) Also keep in mind you will need to make a reference to a jquery library in order for this to work.

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
AleksAuthor Commented:
Thank you !
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
ASP

From novice to tech pro — start learning today.