Update Records Into M.S Access/SQL Table Using Java Servlet Programming

Home	»	Java	»	Advance Java	»	Java Servlet	»	Update Reocords

The following are two Java Servlet Server side programs and one HTML Client Side. HTML Program will send client side information for find out a specific or selected data from M.S.Access/SQL table and Java Servlet (Server Side) program will display specific records from database. and second java servlet program will update records into M.S.Access Table. For this first of all you need to create a M.S.Access database or SQL... but in this example I'm using M.S.Access. Create one table in M.S Access as shown below:

Table Definitions:

Table Name	: [ICTCashBook.mdb]-Download
Primary Key	: VoucherNo
Foreign Key	: Nil

Column Definition:

Column Name	Data Type
ID	AutoNumber
Session	Text
Month	Text
DD	Number
MM	Number
YY	Number
VoucherNo	Number
Articles	Memo
FromWhichPurchsed	Memo
Amount	Number
Remarks	Memo

Table Description:

Column Name	Description
ID	Create AutoNumber for image id
Session	insert Session for ex. 2011-12
Month	insert month in text...
DD	insert date in numeric
MM	insert month in numeric
YY	insert year in numeric
VoucherNo	insert unique numeric no
Articles	insert text
FromWhichPurchsed	insert text
Amount	insert numeric value
Remarks	insert text/numeric value

After create table in M.S. Access database now you will create DSN.

<!--
===========================================================
File Name : EditIncomeExp.html
WebSite : http://javaproglang.blogspot.in/
Facebook : https://www.facebook.com/AdvanceJavaProgramming
Twitter ID : https://twitter.com/AdvanceJava
Created By : Bintu Chaudhary
===========================================================
-->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<Title>Update Income/Grant & Expenditure- School Management System</Title>
<link rel="stylesheet" type="text/css" href="style/style.css" />
<link rel="stylesheet" type="text/css" href="style/w3.css" /> 

</Head>

<Body background='background.bmp'>
<Table width=100% align=center>
<Tr><Th align=left><img src='schoollogo2.JPG' alt=schoollogo.jpg border=0 width=550 height=65></Th>
</Table><HR><BR>
<center>
<Table align=center width=60% cellspacing=0 cellpadding=0 bordercolor=gray border=1 class="w3-ul w3-margin-top">  
<Tr><Th>
<BR><Center><Font size=4>Update Income/Grant & Expenditure</Font></Center>
<BR><BR>
<Table align=center cellspacing=5 cellpadding=10 bordercolor=gray border=0 class="w3-ul w3-margin-top">  
<Tr>
<Form method=Post action='http://localhost:8080/examples/servlet/UpdateIncomeGrant'>
<Th align=left>Income/Grant</Th>
<Th align=right>
 <Select name=month class="w3-input w3-border">
 <Option>Month</Option>
 <Option>April</Option>
 <Option>May</Option>
 <Option>June</Option>
 <Option>July</Option>
 <Option>August</Option>
 <Option>September</Option>
 <Option>October</Option>
 <Option>November</Option>
 <Option>December</Option>
 <Option>January</Option>
 <Option>February</Option>
 <Option>March</Option>

</Select>
</Th>
<Th align=right>
<select name=year class="w3-input w3-border">
 <Option>Year</Option>
 <Option>2005</Option>
 <Option>2006</Option>
 <Option>2017</Option>
 <Option>2008</Option>
 <Option>2009</Option>
 <Option>2010</Option>
 <Option>2011</Option>
 <Option>2012</Option>
 <Option>2013</Option>
 <Option>2014</Option>
 <Option>2015</Option>
 <Option>2016</Option>
</Select>

<Th align=center><Input Type=Submit class="w3-btn w3-teal" Value='Search' ></Th>
</Form>
</Tr>

<Tr>
<Form method=Post action='http://localhost:8080/examples/servlet/UpdateIncomeExpenditure'>
<Th align=left>Expenditure</Th>
<Th align=right>
 <Select name=month class="w3-input w3-border">
 <Option>Month</Option>
 <Option>April</Option>
 <Option>May</Option>
 <Option>June</Option>
 <Option>July</Option>
 <Option>August</Option>
 <Option>September</Option>
 <Option>October</Option>
 <Option>November</Option>
 <Option>December</Option>
 <Option>January</Option>
 <Option>February</Option>
 <Option>March</Option>

</Select>
</Th>

<Th align=right>
<select name=year class="w3-input w3-border">
 <Option>Year</Option>
 <Option>2005</Option>
 <Option>2006</Option>
 <Option>2007</Option>
 <Option>2008</Option>
 <Option>2009</Option>
 <Option>2010</Option>
 <Option>2011</Option>
 <Option>2012</Option>
 <Option>2013</Option>
 <Option>2014</Option>
 <Option>2015</Option>
</Select>

<Th align=center><Input Type=Submit class="w3-btn w3-teal" Value='Search' ></Th>
</Form>
</Tr>

</Table>



<BR><BR>
<Table align=center cellspacing=2 width=35% Border=0>
<Tr>
<Th align=center><Input Type=Button OnClick='history.back();' class="w3-btn w3-teal" Value='Back' ></Th>

</Tr>
</Table>
</Th>
</Tr></Table>

<BR><BR><BR><BR>
<table width=26% align=center>
<tr><th><a href="http://localhost:8080/school/2011-12/index2011_12.html"><b>Home</b></a>     | </th>
<th><a href="http://localhost:8080/school/2011-12/contactus.html"><b>Contact Me</b></a>     |    </th>
<th><a href="http://localhost:8080/school/2011-12/README.html"><b>Help</b></a></th></tr></Table>


<Center><BR>
<Font size=2 color=gray>
Copyright © 2011-12, School-Management System, Developed by <A Href="MailTo:bintu.chaudhary.mks@gmail.com"> <Font color=gray><B>Bintu Chaudhary </b></Font></A>  | Disclaimer
<BR>Best viewed at 1024 x 768 resolution with Google Chrome or Mozila Firefox 3.6 and higher not Internet Explorer
</Body>
</html>

Following is the Java Servlet source code of the UpdateIncomeExpenditure.java

/*
===========================================================
File Name : UpdateIncomeExpenditure.java 
WebSite : http://javaproglang.blogspot.in/
Facebook : https://www.facebook.com/AdvanceJavaProgramming
Twitter ID : https://twitter.com/AdvanceJava
Created By : Bintu Chaudhary
===========================================================
*/
import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.Calendar;

public class UpdateIncomeExpenditure extends HttpServlet
{
        Connection conn;
        Statement stmt;
        ResultSet rs;
        PrintWriter pw;
        String sql,sql_insert;
        int int_pre_amt,int_total;
        public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException
        {
                res.setContentType("text/html");
                pw=res.getWriter();
                String url="jdbc:odbc:khokhar";

                String month  =req.getParameter("month");                
                String year   =req.getParameter("year");

                String str_mm="Month";
                String str_yy="Year";
                
                pw.println("<SCRIPT LANGUAGE=JAVASCRIPT>");
                pw.println("function PrintIt(){");
                pw.println("document.all['Print'].style.visibility = 'hidden';");
                pw.println("window.print();");
                pw.println("document.all['Print'].style.visibility = 'visible';");
                pw.println("}</script>");

                String  STRSESS,STRDD,STRMM,STRYY,STRCS,STRAMT,STRTOT,STRREMARKS;

                pw.println("<html><Head><meta http-equiv='Content-Type' content='text/html; charset=utf-8' /><Title>Update Income/Grants</Title>");
    pw.println("<link rel='stylesheet' type='text/css' href='style/style.css' />");
    pw.println("<link rel='stylesheet' type='text/css' href='style/w3.css' />");
    pw.println("</Head>");
                pw.println("<Body background='http://localhost:8080/school/school_images/background.bmp'>");                        
                pw.println("<Table width=100% align=center>");
                pw.println("<Tr><Th align=left><img src='http://localhost:8080/school/schoollogo2.JPG' alt=schoollogo.jpg border=0 width=550 height=65></Th></Tr>");
                pw.println("</Table><hr>");

                if(month.equalsIgnoreCase(str_mm) || year.equalsIgnoreCase(str_yy))
                {
                        pw.println("<BR><BR><BR><BR><center><H1><Font Color=red>Please Select Month and Year !!!");
                        pw.println("<BR><BR><BR><BR><Center><Input Type=Button OnClick='history.back();' Value='   Try Again   '></Center><BR><BR>");
                }
                else
                {        
                        try
                        {
                                int int_yy=Integer.parseInt(year);
        
                                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                                url="jdbc:odbc:khokhar";
                                conn=DriverManager.getConnection(url);
                                stmt=conn.createStatement();                                
                                pw.println("<Form method=post action='http://localhost:8080/examples/servlet/UpdateIncomeExpenditures'>");
        
                                String sql="Select * from ComputerCashBookExpenditure Where Month='"+month+"' and YY="+year;
                                rs=stmt.executeQuery(sql);                        
                
                                if(rs.next()==false)
                                {
                                        pw.println("<BR><BR><BR><BR>");
                                        pw.println("<Font size=5 color=red><Center><B>Sorry!!! There is No Entry about - "+month+" - "+int_yy+"</Center></Font>");
                                        pw.println("<BR><BR><BR><BR><Center><Input Type=Button OnClick='history.back();' Value='   Try Again   '></Center><BR><BR>");                                
                                        pw.println("<Table width=25% align=center>");                        
                                        pw.println("<Th align=right><a href='http://localhost:8080/school/2011-12/index2011_12.html'><b>Home</b></a>     |     ");
                                        pw.println("<a href='http://localhost:8080/school/2011-12/contactus.html'><b>Contact Us</b></a>     |     ");
                                        pw.println("<a href='http://localhost:8080/school/2011-12/README.html'><b>Help</b></a></th></tr></table>");        
                                }                        
                                else
                                {
                                        pw.println("<Table width=80% align=center Border=0>");
                                        pw.println("<Tr><Th align=left><Font size=5>Income - "+month+" - "+int_yy+"</Font></Th><Th align=right><Font size=5><B>Fund Account</B></Font></Th></Tr>");
                                        pw.println("</Table><BR>");
                
                                        pw.println("<Table align=center Border=1>");
                                        pw.println("<Tr bgcolor=gray>");
                                        pw.println("<Th>Session</Th>");
                                        pw.println("<Th>Date</Th><Th>Month</Th><Th>Voucher<BR>No</Th><Th>Full Particulars of <BR>Articles Purchased</Th>");
                                        pw.println("<Th>From Whom Purchased</Th><Th>Amount</Th><Th>Remarks</Th>");
                                        pw.println("</Tr>");
        
                                        sql="Select * from ComputerCashBookExpenditure Where Month='"+month+"' and YY="+year;
                                        rs=stmt.executeQuery(sql);                        
        
                                        while(rs.next())
                                        {
                                                String sess     =rs.getString("Session");
                                                String mnth     =rs.getString("Month");
                                                int vno         =rs.getInt("VoucherNo");
                                                String dd       =rs.getString("DD");
                                                String mm       =rs.getString("MM");
                                                String yy       =rs.getString("YY");
                                                String articles =rs.getString("Articles");
                                                String pur      =rs.getString("FromWhomPurchsed");
                                                String amount   =rs.getString("Amount");
                                                String remarks  =rs.getString("Remarks");                                                                                                                                                
        
                                                pw.println("<Tr>");
                                                pw.println("<Td><Select name=sess><Option>"+sess+"</Option>");
                                                pw.println("<Option>2008-09<Option>2009-10<Option>2010-11<Option>2011-12");
                                                pw.println("</Select></Td>");
        
                                                pw.println("<Td align=center><Select name=dd><Option>"+dd+"</Option>");
                                                pw.println("<Option>1<Option>2<Option>3<Option>4<Option>5<Option>6<Option>7<Option>8<Option>9<Option>10");
                                                pw.println("<Option>11<Option>12<Option>13<Option>14<Option>15<Option>16<Option>17<Option>18<Option>19<Option>20");
                                                pw.println("<Option>21<Option>22<Option>23<Option>24<Option>25<Option>26<Option>27<Option>28<Option>29<Option>30");
                                                pw.println("<Option>31</Select>");
        
                                                pw.print("<Select name=mm><Option>"+mm+"</Option>   ");
                                                pw.println("<Option>1<Option>2<Option>3<Option>4<Option>5<Option>6<Option>7<Option>8<Option>9<Option>10<Option>11<Option>12");
                                                pw.println("</Select>");
        
                                                pw.print("   <Select name=yy><Option>"+yy+"</Option>");
                                                pw.println("<Option>2008<Option>2009<Option>2010<Option>2011<Option>2012");
                                                pw.println("</Select></Td>");
        
                                                pw.println("<Td align=center><Select name=month><Option>"+month+"</Option>");
                                                pw.println("<Option>Jan<Option>Feb<Option>Mar<Option>Apr<Option>May<Option>Jue");
                                                pw.println("<Option>Jul<Option>Aug<Option>Sep<Option>Oct<Option>Nov<Option>Dec");
                                                pw.println("</Select></Td>");

                                                pw.println("<input Type=hidden name=vno value="+vno+">");
                                                pw.println("<Th align=center>"+vno+"</Th>");

                                                pw.println("<Td><input type=text name=articles value="+articles+" /></Td>");
                                                pw.println("<Td><input type=text name=pur value="+pur+"></Td>");
                                                pw.println("<Td align=right><input type=text name=amount value="+amount+"></Td>");                                        
                                                pw.println("<Td align=center><input type=text name=remarks value="+remarks+"></Td></Tr>");
                                        }
                                        pw.println("</Table>");
                                        pw.println("<BR><BR><BR><BR><BR>");
                                        pw.println("<Center><Input Type=Button OnClick='history.back();' class='w3-btn w3-teal' Value='   Back    '>");                                
                                        pw.println("<input type=submit class='w3-btn w3-teal' Value='    Update    '></Center>");                        
                
                                        stmt.close();
                                        conn.close();
                                }
                                pw.println("</Form>");
                        }
                        catch(Exception e)
                        {
                                pw.println(e.getMessage());
                        }
                }
        }
}

Now, second java servlet program...

/*
===========================================================
File Name : UpdateIncomeExpenditures.java 
WebSite : http://javaproglang.blogspot.in/
Facebook : https://www.facebook.com/AdvanceJavaProgramming
Twitter ID : https://twitter.com/AdvanceJava
Created By : Bintu Chaudhary
===========================================================
*/
import java.sql.*;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;

public class UpdateIncomeExpenditures extends HttpServlet
{
        boolean stat;
        Connection conn;
        Statement stmt;
        ResultSet rs;
        PrintWriter pw;
        PreparedStatement pre_sess,pre_res;

        String url;
        String sql_chk;
        String sql_six;
        public void doPost(HttpServletRequest req,HttpServletResponse res) throws ServletException,IOException
        {
                res.setContentType("text/html");
                pw=res.getWriter();
                try
                {

                        String  ses       =   req.getParameter("sess");
                        String  mnth      =   req.getParameter("month");

                        String  sess[]    =   req.getParameterValues("sess");
                        String  month[]   =   req.getParameterValues("month");                        
                        String  vno[]     =   req.getParameterValues("vno");
                        String  dd[]      =   req.getParameterValues("dd");
                        String  mm[]      =   req.getParameterValues("mm");
                        String  yy[]      =   req.getParameterValues("yy");
                        String  articles[]=   req.getParameterValues("articles");
                        String  pur[]     =   req.getParameterValues("pur");
                        String  amount[]  =   req.getParameterValues("amount");
                        String  remarks[] =   req.getParameterValues("remarks");

                        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                        url="jdbc:odbc:khokhar";
                        conn=DriverManager.getConnection(url);
                        stmt=conn.createStatement();                                                                       
                        
                        for(int i=0;i<vno.length;i++)
                        {                                                                                                                                                                        
                                pre_sess=conn.prepareStatement("UPDATE ComputerCashBookExpenditure SET Session='"+sess[i]+"',Month='"+month[i]+"',DD='"+dd[i]+"',MM='"+mm[i]+"',YY='"+yy[i]+"',Articles='"+articles[i]+"',FromWhomPurchsed='"+pur[i]+"',Amount='"+amount[i]+"',Remarks='"+remarks[i]+"' WHERE VoucherNo="+vno[i]);
                                pre_sess.executeUpdate();
                        }                        

                        pw.println("<html><head><Title>Update Income Grants</Title></Head>");
                        pw.println("<Body Background='http://localhost:8080/school/2011-12/background.bmp'>");
                        pw.println("<Table width=100% align=center>");
                        pw.println("<Tr><Th align=left><img src='http://localhost:8080/school/2011-12/schoollogo2.JPG' alt=schoollogo.jpg border=0 width=550 height=65></Th></Tr>");
                        pw.println("</Table><hr><BR>");

                        pw.println("<Table width=50% align=center border=0>");
                        pw.println("<Tr><Th><Font size=5>Successfully Update Income Expenditure<BR><BR><BR>");
                        pw.println("for the month - "+mnth+" -- "+ses+"<BR><BR><BR><BR>");
                        pw.println("</Th></Tr>");
                        pw.println("<Tr><Th><Font size=5><a href='http://localhost:8080/school/2011-12/EditIncomeExp.html'><Input Type=Button Value='Wants More Update???'></a><BR><BR><BR>");
                        pw.println("</Th></Tr>");
                        pw.println("</Table>");
                        stmt.close();
                        conn.close();

                        pw.println("<br><br><br><br><br>");
                        pw.println("<table width=23% align=center>");
                        pw.println("<tr><th><a href='http://localhost:8080/school/2011-12/index2011_12.html'><b>Home</b></a>     | </th>");
                        pw.println("<th><a href='http://localhost:8080/school/2011-12/contactus.html'><b>Contact Us</b></a>     |    </th>");
                        pw.println("<th><a href='http://localhost:8080/school/2011-12/README.html'><b>Help</b></a></th></tr></Table>");
                        pw.println("</Body>");
                        pw.println("</html>");                        
                        
                }
                catch(SQLException se)
                {
                        System.out.println(se.getMessage());
                        pw.println("<html><head><Title>Update Expenditure</Title></Head>");
                        pw.println("<Body>");
                        pw.println("<BR><BR><BR><Center><h1>Currently There is a Problem with the Database");
                        pw.println("<BR><BR>Please Try Again</h1></Center>");
                        pw.println("</Body>");
                        pw.println("</html>");                                
                }
                catch(ClassNotFoundException cnfc)
                {
                        System.out.println(cnfc.getMessage());                        
                }
        }
}

"Start -> All Programs -> Apache Tomcat 4.0 -> Start Tomcat." and minimize window

How to Install Apache Tomcat 4.0 then Click Here

and for Download Apache Tomcat 4.0 then Click Here

Now compile the above both code using javac compiler...

C:\jdk1.4\bin>javac UpdateIncomeExpenditure.java -classpath "C:/Program Files/Apache Tomcat 4.0/common/lib/servlet.jar"

C:\jdk1.4\bin>javac UpdateIncomeExpenditures.java -classpath "C:/Program Files/Apache Tomcat 4.0/common/lib/servlet.jar"

Now, copy "UpdateIncomeExpenditure.class" and "UpdateIncomeExpenditures.class" Files from c:/jdk1.4/bin/ and then paste it under below dir...

"C:/Program Files/Apache Tomcat 4.0/webapps/examples/WEB-INF/classes/"

or use below command

C:\jdk1.4\bin>copy UpdateIncomeExpenditure.class "C:/Program Files/Apache Tomcat 4.0/webapps/examples/WEB-INF/classes/"

C:\jdk1.4\bin>copy UpdateIncomeExpenditures.class "C:/Program Files/Apache Tomcat 4.0/webapps/examples/WEB-INF/classes/"

or you can use "*.class" for copy all classes...

C:\jdk1.4\bin>copy *.class "C:/Program Files/Apache Tomcat 4.0/webapps/examples/WEB-INF/classes/"

Now Open any web browser and type client side URL into address bar as below...

http://localhost:8080/EditIncomeExp.html

HTML Form for retrieve selecting information

Open Records for Updating

Final Update Reocrd using Java Servlet Programming

5 comments:

RasiSeptember 9, 2019 at 11:17 PM
Nice Article.
AWS Training in Hyderabad
Best AWS Training in Hyderabad
AWS Online Training
AWS Training Online
AWS Training In Bangalore
Institute in GurgaonNovember 8, 2019 at 3:34 AM
Our training will be focused on aiding placements too. We have separate HR team professionals who will take care of all your interview needs. Our Core Java Training in Gurgaon Course Fees is very moderate compared to others. We are the main Core Java training institute who can share video reviews of every one of our students.
For More Info: Core Java Training in Gurgaon
snehaJune 1, 2020 at 8:46 PM
Your blog is in a convincing manner, thanks for sharing such an information with lots of your effort and time
sql server dba online training
sql dba course
sql dba training
Ria Training InstituteJanuary 28, 2021 at 5:48 AM
Wow what a great blog, i really enjoyed reading this, good luck in your work. Sap Mm Training Institute In Bangalore | Dot Net Training Institute In Bangalore | Selenium Training Institute in Bangalore
Jeron MobikJanuary 20, 2022 at 7:11 AM
Thankyou for sharing such as nice post. It is really helpful for developer, Keep it sharing
Visit our website accessdatabasedevelopment.co.uk/ for develop custom databases tailored to your business needs and providing your business with a competitive advantage by enabling you to increase efficiency, enhance quality of service and make the work lives of staff simpler. We microsoft access database development London specialise in building bespoke business database solutions and form creation in ms access using industry standard tools such as Microsoft Access.
microsoft access developer London

	Section 1
	Home
	Introduction To Java
	Java Evolution
	Overview of Java
	Constants, Variables & Data Types
	Java Operators
	Branching Statements
	Looping Statements
	Section 2
	Classes, Objects & Methods
	Inheritance
	Array, String & Vectors
	Java Interfaces
	Java Packages
	Java Applet Programming
	Java Graphics Programming
Advance Java Programming
	Section 1
	Java AWT
	Java Event Handling
	Java File Handling
	Java MultiThreading
	Java Swing-I
	Java Swing-II
	Java Exception Handling
	Section 2
	Java Socket Programming
	Java Database Connectivity
	Java RMI
	Java Servlets
	Java Beans

Update Records Into M.S Access/SQL Table Using Java Servlet Programming

5 comments:

Useful Links

Visitors

Java Tutorials

Follow by Email

Follow Advanced Java Programming on Facebook

Popular Programs

Follow Advanced Java Programming on Twitter