Updating database records in classic ASP using javascript

Hello,

How do I update database record (MS Access) in classic ASP when javascript onclick event is triggered on the image button?
Here's the detailed description of what I'm trying to achieve.

I have created a recordset "rsMembers" in my (classic) ASP page for displaying a record stored in the MS Access database table and bound the table field data on the ASP page for the initial display of the personal details record data to the user when the page is loaded on the browser. The ASP page looks like the image shown below.

The ASP page on the browser
What I want to achieve is when the user clicks the blue circular button on the page, I want the javascript onclick event script to trigger/execute vbscript ASP update codes shown below.

<%
   Set conn = Server.CreateObject("ADODB.Connection")
   conn.Open Session("DSN=MembersSampleDSN")
   Set rsMembers = Server.CreateObject("ADODB.Recordset")
   rsMembers.Open "MembersTable", conn, 1, 3, 2

   ' Update the value of 'DataPrinted' field for the currently displayed record
   rsMembers ("DataPrinted") = "Yes"
   rsMembers .Update

   rsMembers .Close
%>

Open in new window


I know how to update MS Access database records using ASP when the ASP page is loaded or when the submit button inside a form is pressed but I don't know how to achieve this using javascript -- onclick event of the image button. That is, I want to know the way of changing the database record without submitting/opening the ASP page.
Is this possible? Please show me the correct javascript codes for executing particular block of vb codes shown above if possible.


<img id="BlueButton" onclick="DataPrinted" />

    function DataPrinted() { 
        ?
    }

Open in new window

ZKM128Asked:
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:
>>That is, I want to know the way of changing the database record without submitting/opening the ASP page.
You can try build an AJAX method to send the request to your ASP page in your click event.

Examples:

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

jQuery.ajax()
http://api.jquery.com/jquery.ajax/
Big MontyWeb Ninja at largeCommented:
I know how to update MS Access database records using ASP when the ASP page is loaded or when the submit button inside a form is pressed but I don't know how to achieve this using javascript -- onclick event of the image button.

if you know how to do this, then you're most of the way there. simply call a javascript function that'll manually submit your page, if you have data to submit, or call a url manually:

<img id="BlueButton" onclick="DataPrinted" />

    function DataPrinted() { 
           //-- if you want to manually submit the form
           document.forms[0].action = 'somePage.asp';
           document.forms[0].submit();

           //-- or if you don't have any data to submit, and just want to call another url
           window.location = 'somePage.asp';
    }

Open in new window


if you opt for the 2nd method (only choose one of them!), I would add some kind of querystring variable to distinguish this particular action, unless "somePage.asp" only contains the code to save that particular piece of data.

ajax is another possibility, the only difference between ajax and the method above is that the page is not refreshed, thus giving a better user experience. if you rather go that route, look into using jquery, a javascript framework, as it'll make the call for ajax much easier to work with. I can provide code examples if you need assistance with that as well.
ZKM128Author Commented:
Thank you experts,
Big Monty, if I use the code shown below,
window.location = 'somePage.asp';

Open in new window

Will it redirect me to the 'somePage.asp' or will it just run the vb codes stored in the 'somePage.asp' quietly in the background and update the "DataPrinted" field of the currently displayed record?
If window.location = 'somePage.asp'; has to send/redirect the user to the somePage.asp then I think I have to use the ajax method. I'll try to learn how to use jquery and ajax to accomplish this.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ZKM128Author Commented:
I followed the instruction given on: http://www.w3schools.com/ajax/ajax_xmlhttprequest_send.asp
and It worked :-)

For the image button:
<img src="Button.jpg" onclick="DataPrinted(<%=(rsMembers.Fields.Item("IDNumber").Value)%>)"/>

Open in new window


"DataPrinted" javascript function:
<script type="text/javascript">
	
	function DataPrinted(IDNumber) {
	
		xmlhttp= new XMLHttpRequest();
		xmlhttp.open("POST","UpdateDataPrintStatus.asp",true);
 		xmlhttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
 		xmlhttp.send("ID="+IDNumber);
	
        }
	
</script>

Open in new window


And for the UpdateDataPrintStatus.asp which actually updates the "DataPrinted" field value:
<%
   Set conn = Server.CreateObject("ADODB.Connection")
   conn.Open Session("DSN=MembersSampleDSN")   
   Set rsMembers = Server.CreateObject("ADODB.Recordset")
   Dim rsMembersSQL, IDNumber
   IDNumber = Request.Form("ID")
   rsMembersSQL = "MembersTable WHERE IDNumber = " & IDNumber
   rsMembers.Open rsMembersSQL, conn, 1, 3, 2

   ' Update the value of 'DataPrinted' field for the currently displayed record
   rsMembers("DataPrinted") = "Yes"
   rsMembers.Update

   rsMembers.Close
%>

Open in new window


In fact, I have encountered an error when I have omitted "xmlhttp= new XMLHttpRequest();" in the DataPrinted(IDNumber) javascript function. I have simply put this code on the top of the javascript function but, is this the correct place to put this code? Did I do everything correctly?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
>>I have encountered an error when I have omitted "xmlhttp= new XMLHttpRequest();" in the DataPrinted(IDNumber) javascript function
it is a necessity to define the XMLHTTPRequest object so that you can send the request and then get the response accordingly. Hence, the script: xmlhttp= new XMLHttpRequest(); need to be there in your DataPrinted(IDNumber) javascript function

>>I have simply put this code on the top of the javascript function but, is this the correct place to put this code?
it doesn't matter where you put if your page is fully loaded (without any javascript errors).

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
ZKM128Author 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
JavaScript

From novice to tech pro — start learning today.