Solved

JSP & Access

Posted on 2003-11-25
19
645 Views
Last Modified: 2010-04-01
Can i use access with JSP??
If so has anyone got code i can use to connect to a Access database,
read from it,display this on the web,
Also has anyone code on how to insert with JSP,
I know how to create SQL statements I basically need the the JSP code to open the link to the database,display the info etc, and to assign variables that can be used to pass to the database fields
0
Comment
Question by:Doneen
  • 5
  • 4
  • 3
  • +3
19 Comments
 
LVL 10

Expert Comment

by:NetGroove
Comment Utility
0
 
LVL 10

Expert Comment

by:NetGroove
Comment Utility
To move your question to the correct topic area ask for support in this topic area:
http://www.experts-exchange.com/Community_Support/

0
 

Author Comment

by:Doneen
Comment Utility
all JSP code in general for form a web page would be appreciated,
an overall example of a page accessing a database,display & inserting into the database would be I feel the best possible solution for me.
I could then edit it to connect to my own database and read from it,
Drop down menus being created from field in the database would be very useful,
Thanks,
Shane.
PS-I could do with an answer to this fairly soon,so any help would be appreciated.
0
 
LVL 10

Expert Comment

by:NetGroove
Comment Utility
But you are again in wrong topic area.
This is JavaScript topic area.

To end your Odyssey look here:
http://cse-ferg41.unl.edu/docs/DevelopmentDocs/jdbcpage/jdbc.htm

0
 

Author Comment

by:Doneen
Comment Utility
Can it be moved to
http://www.experts-exchange.com/Web/Web_Languages/JSP/
here so,
Thanks
0
 
LVL 35

Expert Comment

by:TimYates
Comment Utility
0
 
LVL 35

Accepted Solution

by:
TimYates earned 114 total points
Comment Utility
Also have a look at this:

http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_20109419.html
http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20505968.html
http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20516530.html
http://www.experts-exchange.com/Web/Web_Languages/JSP/Q_20393727.html

I hope these links help!

Come back with any questions they may bring up...  I have never used Access with Java, but it is possible...  I always used MySQL or PostgreSQL or Oracle :-/

Good luck!

Tim
0
 
LVL 2

Assisted Solution

by:anthony_castillon
anthony_castillon earned 112 total points
Comment Utility
You can create a DatabaseConnector class, with code like the following:

public class DatabaseConnector{

     Connection conn;

     public DatabaseConnector(String driver, String dsn){
            connect(driver, dsn);
     }

     private connect (String driver, String dsn){
          try{
                 Class.forName(driver);
                 conn = DriverManager.createConnection (dsn);
          } catch (Exception e){}
     }

     // Other methods omitted
}

In your JSP, you can have a scriptlet that instantiates the DatabaseConnector so you can do something in the database.
  e.g.
            DatabaseConnector dbconn = new DatabaseConnector ("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:MyData");

MyData represents the alias you set for your Access database.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Assisted Solution

by:applekanna
applekanna earned 112 total points
Comment Utility
here is the database connection part
just add this to your JSP page and make atable called country

and a dsn named "test"


                       <!--connecting to database -->

                        <%
                             try{
                                 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                                 }
                            catch(Exception e){
                                                 }


                                   connect=DriverManager.getConnection("jdbc:odbc:test");
                                   stmt=connect.createStatement();
                                   result=stmt.executeQuery("select * from Country"); %>


                          while(result.next())  {
                           System.out.println(result.getString(1));
                         }
                        result.close();
                        connect.close(); %>
                       

Hope this helps
Cheers!
0
 
LVL 6

Assisted Solution

by:jarasa
jarasa earned 112 total points
Comment Utility
Hi.
This is a Chunk of Old code I made for a web site, it shows how to create a connection, make a Select and display a country drop down list on the JSP hope it helps.
regads
Javier

P.S.: One thing it has no comments I had to decompile and have no time to comment it, sorry, but if you know Java won't have much trouble, At least I did translated it from Spanish, :c)) I'll Attach the JSP part in a diferent comment, ok?

package vw02;

import java.io.IOException;
import java.io.PrintStream;
import java.sql.*;
import java.util.Date;
import java.util.Vector;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.http.HttpServletRequest;
import vw02.com.Vw02Constants;
import vw02.com.Vw02Countries;
import vw02.com.Vw02Session;

public class Vw02Login extends Vw02Constants
{

    private HttpServletRequest request;
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    public Vw02Login(HttpServletRequest httpservletrequest)
   
    {
        request = httpservletrequest;
    }

    public void openDB()
    {
        try
        {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            connection = DriverManager.getConnection("jdbc:odbc:VW02", "", "");
            statement = connection.createStatement();
        }
        catch(ClassNotFoundException classnotfoundexception)
        {
            System.err.println("Cannot find the database driver classes.");
            System.err.println(classnotfoundexception);
        }
        catch(SQLException sqlexception)
        {
            System.err.println("Cannot connect to this database.");
            System.err.println(sqlexception);
        }
    }

    public Vector loadCountries()
    {
        Vw02Countries vw02countries = new Vw02Countries();
        Vector vector = new Vector();
        int i = 0;
        try
        {
            for(resultSet = statement.executeQuery("SELECT * FROM VW02_COUNTRIES"); resultSet.next();)
            {
                Vw02Countries vw02countries1 = new Vw02Countries();
                vw02countries1.setCountryCode(resultSet.getString("COUNTRY_CODE"));
                vw02countries1.setName(resultSet.getString("COUNTRY_NAME"));
                vector.add(i, vw02countries1);
                i++;
            }

            resultSet.close();
        }
        catch(SQLException sqlexception)
        {
            System.out.println("Cannot connect to this database.");
            System.out.println(sqlexception);
        }
        return vector;
    }


    public void closeDB()
    {
        try
        {
            statement.close();
            connection.close();
        }
        catch(SQLException sqlexception)
        {
            System.err.println("Cannot connect to this database.");
            System.err.println(sqlexception);
        }
    }

    public boolean enter()
        throws IOException, ServletException
    {
        boolean flag = false;
        Vector vector = null;
        String s = request.getParameter("vw02LOGIN");
        String s1 = request.getParameter("vw02PW");
        if(s == null)
        {
            s = (String)request.getAttribute("vw02LOGIN");
            s1 = (String)request.getAttribute("vw02PW");
        }
        if(s == null)
            s = "";
        else
            s.trim();
        openDB();
        vector = loadCountries();
        if(flag)
        {
            javax.servlet.http.HttpSession httpsession = request.getSession(true);
            if(httpsession != null)
            {
                Vw02Session vw02session = new Vw02Session(httpsession, false);
                vw02session.setCountries(vector);
            }
            else
            {
                flag = false;
            }
        }
        else
        {
            flag = false;
        }
        closeDB();
        return flag;
    }

    public Vw02Login()
    {
    }


}


package vw02.com;

import java.util.Vector;
import javax.servlet.http.HttpSession;

public class Vw02Session extends Vw02Constants
{

    public Vw02Session(HttpSession httpsession, boolean flag)
    {
        session = httpsession;
    }

    public void setLogin(String s)
    {
        session.setAttribute("vw02LOGIN", s);
    }

    public void setClave(String s)
    {
        session.setAttribute("vw02PW", s);
    }

    public String getLogin()
    {
        return (String)session.getAttribute("vw02LOGIN");
    }

    public String getPW()
    {
        return (String)session.getAttribute("vw02PW");
    }

    public void removeSession()
    {
        session.removeAttribute("vw02LOGIN");
        session.invalidate();
    }

    public Vector getCountries()
    {
        return (Vector)session.getAttribute("vw02COUNTRIES");
    }

    public boolean haySession()
    {
        if(session != null)
            return session.getAttribute("vw02COUNTRIES") != null;
        else
            return false;
    }

    public void setCountries(Vector vector)
    {
        session.setAttribute("vw02COUNTRIES", vector);
    }

    private HttpSession session;
}



package vw02.com;


public class Vw02Countries
{
    private String code;
    private String name;

    public Vw02Countries()
    {
    }

    public String getCountryCode()
    {
        return code;
    }

    public String getName()
    {
        return name;
    }

    public void setCode(String s)
    {
        code = s;
    }

    public void setName(String s)
    {
        name = s;
    }

}

package vw02.com;


public class Vw02Constants
{

    public Vw02Constants()
    {
    }

    public static final String COUNTRIES = "vw02COUNTRIES";
    public static final String LOGIN = "vw02LOGIN";
    public static final String PW = "vw02PW";
    public static final int TABLE_COUNTRIES = 6;
    public static final int SQL_UPDATE = 0;
    public static final int SQL_INSERT = 1;
    public static final int SQL_DELETE = 2;
    public static final int SQL_SELECT = 3;
    public static final int UPDATE_CAMPO = 0;
    public static final int UPDATE_VALOR = 1;
    public static final int UPDATE_CLAVE = 2;
}
0
 
LVL 6

Expert Comment

by:jarasa
Comment Utility
Here is the Login.jsp
<%@ include file="vw02Constantes.jsp"%>
<%@ page import="vw02.com.*"%>
<%@ page import="vw02.Vw02Login"%>
<%

      boolean paginaLogin = true;
      String error = "";
      String usuario = "";
      
      Vw02Sesion sess = null;
      
      // Check it there is a session already
      sess = new Vw02Sesion(session, true);
      
      if (sess.haySesion()) {
      %>
            <SCRIPT>
            alert("There is a session already");
            parent.location.href="start.jsp";
            </SCRIPT>
      <%
      }
      // if not Check if the user has logged in
      if (request.getParameter(ENTRAR) != null) {
            if (OK.equals(request.getParameter(ENTRAR))) {
                  paginaLogin=false;
                  usuario = request.getParameter(Vw02Constantes.LOGIN);
                  Vw02Login login = new Vw02Login(request);
                  
                  try {
                        if (login.enter()) {;
                        
                        %>
                        <SCRIPT>
                              parent.location.href="start.jsp";
                        </SCRIPT>

                        <%
                        
                        } else {
                              error = "User o Password Failed, try again";
                              paginaLogin=true;
                        }
                        
                  } catch(Exception e){
                        e.printStackTrace();
                        paginaLogin = true;
                        error = "Unexpected Error. Try Later";
                  }
            }
      }

      if (paginaLogin) {      
%>

<html>
<head>
<title>Login</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<SCRIPT language="JavaScript" src="/VW02/Motivacion/Programa/js/scripts.js"></SCRIPT>
<SCRIPT language="JavaScript">

function send() {
      
      frm = document.forms[0];


if (frm.<%=Vw02Constantes.LOGIN%>.value=="") {
      
            alert("Please, Enter User Name.");
            frm.<%=Vw02Constantes.LOGIN%>.focus();
            return false;

} else if(frm.<%=Vw02Constantes.CLAVE%>.value=="") {
      
            alert("Please Enter Password.");
            frm.<%=Vw02Constantes.CLAVE%>.focus();
            return false;
}

return true;

}
</SCRIPT>

</head>

<body bgcolor="#000066" text="#0066FF" leftmargin="0" topmargin="0">
<table width="100%" border="0">
  <tr>
    <td width="9%"><img src="lv55.gif" width="82" height="81"></td>
    <td width="86%">
      <h1 align="center"><font face="Arial, Helvetica, sans-serif"><font size="5">LOGIN PAGE</font></font></h1>
    </td>
    <td width="5%"><img src="VW.gif" width="82" height="81"></td>
  </tr>
</table>
<form name="frm" method="post" action="<%=LOGIN_JSP%>" onsubmit="return send()">
  <input type="hidden" name="<%=ENTRAR%>" value="<%=OK%>">
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <p>&nbsp;</p>
  <div align="center">
    <table width="50%" border="1">
      <tr>
        <td>
          <table width="100%" border="0">
            <tr>
              <td width="17%" height="25">&nbsp;</td>
              <td width="13%" height="25">&nbsp;</td>
              <td width="28%" height="25">&nbsp;</td>
              <td width="42%" height="25">&nbsp;</td>
            </tr>
            <tr>
              <td width="17%">&nbsp;</td>
              <td width="13%">&nbsp;</td>
              <td width="28%">&nbsp;</td>
              <td width="42%">&nbsp;</td>
            </tr>
            <tr>
              <td width="17%">&nbsp;</td>
              <td width="13%">Usuario:</td>
              <td width="28%">
                <input type="text" name="<%=Vw02Constantes.LOGIN%>" value="<%=usuario%>">
              </td>
              <td width="42%">&nbsp;</td>
            </tr>
            <tr>
              <td width="17%" height="32">&nbsp;</td>
              <td width="13%" height="32">Clave:</td>
              <td width="28%" height="32">
                <input type="password" name="<%=Vw02Constantes.CLAVE%>" value="">
              </td>
              <td width="42%" height="32">
                <div align="center">
                  <input type="submit" name="bLogin1" value="Conectar">
                </div>
              </td>
            </tr>
            <tr>
              <td width="17%" height="36">&nbsp;</td>
              <td width="13%" height="36">&nbsp;</td>
              <td width="28%" height="36">&nbsp;</td>
              <td width="42%" height="36">&nbsp;</td>
            </tr>
          </table>
        </td>
      </tr>
    </table>
  </div>
</form>

<% if(!"".equals(error)) { %>
  <SCRIPT>
     alert("<%=error%>")
  </SCRIPT>
<% } %>
</body>
</html>
<% } %>
0
 
LVL 6

Expert Comment

by:jarasa
Comment Utility
Here is the start.jsp wich only populates the Countries drop down list.
<%

Vector vPaises = null;
Vw02Paises oPais = new Vw02Paises();
vPaises = sess.getPaises();

%>


<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head>

<body bgcolor="#000066" text="#FFFFFF" leftmargin="0" topmargin="0" onLoad="init();">
<div align="center"> <font face="Arial, Helvetica, sans-serif">
<select name="paises" onchange="javascript:cargalistas(this);">
<option selected>Seleccione Pais</option>
<%
if (vPaises!=null) {
      for (x=0; x<vPaises.size() ; x++) {
            oPais = (Vw02Paises)vPaises.get(x);
            %>
            <option value="<%=oPais.getCodigoPais()%>"> <%=oPais.getNombre()%> </option>
            <%
            oPais = null;            
      }
}
%>
</select>
</font>
</div>
</body>
</html>

Have Fun
Javier
0
 

Author Comment

by:Doneen
Comment Utility
applekanna
Can anyone suggest where I could get a suitablle driver,for the piece of code you posted,
I have been away for awhile hence i was not working on this ,
thanks for all your help,
I am just looking to connect to the database first and will go into more detail once i get this done,
Thanks again,
any help would be greatly appreciated,
Doneen.
0
 
LVL 6

Expert Comment

by:jarasa
Comment Utility

The jdbcodbc driver comes with the JDK you should have it

Javier

0
 

Author Comment

by:Doneen
Comment Utility
I don't think I have a JDK,
I am using iplanet a netscape webserver,
And am coding it with Dreamweaver,
Any suggestion,
or does this make sense the way I am tryin to do it??
0
 
LVL 6

Expert Comment

by:jarasa
Comment Utility
Well if you're using iPlanet then you sure have a jdk, iPlanet is java based and can not work without a jdk, anyway have you tryied downloading one from the sun page http:\\www.sun.com
besides that you should need an ODBC DNS entry to pion your access database.
Any further questions do not doubt to ask
:c)
Javier
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
A procedure for exporting installed hotfix details of remote computers using powershell
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now