Updating mysql table with JSP and JavaScript

Question

I am a newbie in JSP. I am creating a table that shows the notification approval. The function of it is to show whether the admin approves the record or not. However, when I click on the Accept button, nothing changed (both the page and the database remain 0). Is there any error in my code?? Please help. Thank you in advance.

The Javascript in my JSP file:

<script language="JavaScript">
function submitaccept(){
    String connectionURL = "jdbc:mysql://localhost/ams";
    Connection connection =null;
    PreparedStatement pstatement = null;
    var sqlstr = "UPDATE notification SET ack_flag = 1 WHERE notification_id = 001;";
    connection = DriverManager.getConnection(connectionURL, "root", "");
    pstatement =connection.prepareStatement(sqlstr);
    pstatement.executeUpdate();
    location.reload(true);
}

The call function of my JSP file:

    <c:choose>
        <c:when test="${row.ack_flag=='0'}">

        <a href="javascript:submitaccept()" >
            Approve</a> | 

        <a href="notification.jsp" > 
            Reject </a>
        </c:when>

        <c:when test="${row.ack_flag=='1'}">
            Approved
        </c:when>
        <c:otherwise>
            Rejected
        </c:otherwise>
    </c:choose>

score 0 · Answer 1 · answered Sep 04 '13 at 07:38

0

You do not need to provider ; in your update statement it will consider value as 001; not 001 remove it

var sqlstr = "UPDATE notification SET ack_flag = 1 WHERE notification_id = 001";

answered Sep 04 '13 at 07:38

commit

4,777
15
43
70

Changed but not the solution. However, still thank you for helping – Vincent Cruz Sep 04 '13 at 07:45

score 0 · Accepted Answer · edited May 23 '17 at 11:56

0

There is problem with your connection. you are using javascript to connect to database and you have written connection code of JDBC.

you can find how to connect to database server using javascript here

edited May 23 '17 at 11:56

Community

1
1

answered Sep 04 '13 at 07:39

Abhijit

673
2
17
35

what is meant by Initial Catalog= in the answer – Vincent Cruz Sep 04 '13 at 07:48
Initial Catalog means your database name. this example is of SQL Server. – Abhijit Sep 04 '13 at 07:52
so for me, it should be var connectionstring="Data Source=jdbc:mysql://localhost;Initial Catalog=ams;User ID=root;Password=;Provider=SQLOLEDB"; is it? – Vincent Cruz Sep 04 '13 at 07:54
you can not connect to mysql db using javascript, only mssql. you have to use either jsp or servlet to connect mysql. – Abhijit Sep 04 '13 at 07:56
OH, is it, sorry for the stupidity. However, do you have any solution do create a hyperlink in JSP to fulfill both updating a mysql database and refreshing the page? – Vincent Cruz Sep 04 '13 at 08:01
yes you send request to self page using parameter. like 'index.jsp?update=1' then capture the response using request.getParameter("update") compare the value if its one then call the method in jsp which updates the database – Abhijit Sep 04 '13 at 08:08

JBA · Answer 3 · 2013-09-04T07:46:26.253

0

While you already recived a answer to your question id like to point out that you do your DB- connection from within java script code. This means the code including your connection parameters will be sent to the client which is probably not what you want to archive.

Rather make sure to have a Servlet that gets called and do your database stuff back there. Your JSP generally should just take data, hand it to the servlet and recive data back to display it but not contain busines logic code like data base connections.

While i havent verified this example: http://www.java-samples.com/showtutorial.php?tutorialid=619 I assume it will give you the possibility to archive what you want to do as fast as possible and dont have your connection parameters transfered to the client.

edited Sep 04 '13 at 07:46

answered Sep 04 '13 at 07:41

JBA

2,769
5
24
40

I did the retrieve records stuff in the JSP already, but i do not know how to update a record and refresh the page by only simple clicking a button. – Vincent Cruz Sep 04 '13 at 07:58
Your JSP will be rendered with any request that points to it. So i dont know whats your current status but if you click a button that will trigger the rendering of your JSP the according response of the server (that would contain Java Script code but no JSP Code) should contain the updated status. Unfortunately i never had the case where i just had a JSP without a servlet. So in general you would have a request that you first work with in a Servlet class, this is where you update your DB, then forward any new data or message or whatever to the JSP. – JBA Sep 04 '13 at 08:16
You may quickly work throught this tutorial (i unfortunately dont have time to verify it works). It probably makes sence to create a new project to do this. I also recomend you do both parts so you have part 1 that pretty much is where you are right now and part 2 that will give you an idear on how to work with the database and have a new status displayed to the browser afterwards (in this tutorial) deleting or adding a row. Then adapt the learned to your currend project, i bet you get this together in less than 2 hours :) http://zetcode.com/tutorials/jeetutorials/mysqldatabase/ – JBA Sep 04 '13 at 08:26

score 0 · Answer 4 · answered Sep 04 '13 at 07:42

i never did something like this, but i think whenever i try to use javascript and server script like jsp or php

i always use ajax

so my concept is like this

whenever you press the button, it called javascript function as in your case "submitaccept()"

if i were you, i will try to use ajax like this

function submitaccept(){
    //code for getting the notificationid you would like to update
    notifId = document.getElementById('..').value;

    if (window.XMLHttpRequest)
    {// code for IE7+, Firefox, Chrome, Opera, Safari
        xmlhttp=new XMLHttpRequest();
    }
    else
    {// code for IE6, IE5
        xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
    }

    xmlhttp.onreadystatechange=function()
    {
        if (xmlhttp.readyState==4 && xmlhttp.status==200)
        {
            var x = xmlhttp.responseText;

            //whatever you want to do after your update is done , where x is the response from updateNotif.jsp

        }
    }
    xmlhttp.open("GET","updateNotif.jsp?notif="+notifId,false);
    xmlhttp.send();
}

then , i will make updateNotif.jsp

request.getResponse("notif")

and update it using your code

hope it helps you :)

This code is a bit difficult for me, do you mind to change it according to my case. All I want is to change the ack_flag into 1 and refresh the page. — Vincent Cruz, Sep 04 '13 at 07:53

score 0 · Answer 5 · answered Sep 05 '13 at 09:19

i don't know about your JSP , it's different with my style , so you will need to change it from mine

here let me make the new one for you with my style

try to copy that into your new project, learn the concept, so you can solve your problem

i'll make 2 pages for you

index.jsp

<html>
<head>
<script> function change(ackFlag){
    var notifId = document.getElementById('myNotifId').value;

    if (window.XMLHttpRequest)
    {// code for IE7+, Firefox, Chrome, Opera, Safari
        xmlhttp=new XMLHttpRequest();
    }
    else
    {// code for IE6, IE5
        xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
    }

    xmlhttp.onreadystatechange=function()
    {
        if (xmlhttp.readyState==4 && xmlhttp.status==200)
        {
            var x = xmlhttp.responseText;
            document.getElementById('myNotification').value = x;
        }
    }
    xmlhttp.open("GET","updateNotif.jsp?af="+ackFlag+"&notif="+notifId,false);
    xmlhttp.send();  } </script>
</head>
<body>
    <% String notifID = "001"; String ackFlag = "1"; %>

    <input type='hidden' value='<% out.print(notifID); %>' id='myNotifId' />
    <input id='myNotification' value='<% if(ackFlag.equals("1"))out.print("Accepted");else out.print("Rejected") %>' />
    <input type='button' value='Accept' onclick='change(1)' />
    <input type='button' value='Reject' onclick='change(0)' />
</body>
</html>

updateNotif.jsp

<%
    String notifID = request.getParameter("notif");
    String af = request.getParameter("af");

    //this is the start of your code, i just copied from your code 

    String connectionURL = "jdbc:mysql://localhost/ams";
    Connection connection =null;
    PreparedStatement pstatement = null;
    var sqlstr = "UPDATE notification SET ack_flag = "+af+" WHERE notification_id = " + notifID;
    connection = DriverManager.getConnection(connectionURL, "root", "");
    pstatement =connection.prepareStatement(sqlstr);
    pstatement.executeUpdate();

    //this is the end of your code, once again, this is from your code

    if(af.equals("1"))
        out.print("Accepted");
    else
        out.print("Rejected");
%>

oh by the way, i haven't even compiled it yet, so if you have any problem about my code, just post it here again , but i'm pretty sure it will work , so have fun :)

Updating mysql table with JSP and JavaScript

5 Answers5