[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 674
  • Last Modified:

JSP & Access

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
Doneen
Asked:
Doneen
  • 5
  • 4
  • 3
  • +3
4 Solutions
 
NetGrooveCommented:
0
 
NetGrooveCommented:
To move your question to the correct topic area ask for support in this topic area:
http://www.experts-exchange.com/Community_Support/

0
 
DoneenAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
NetGrooveCommented:
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
 
DoneenAuthor Commented:
Can it be moved to
http://www.experts-exchange.com/Web/Web_Languages/JSP/
here so,
Thanks
0
 
TimYatesCommented:
0
 
TimYatesCommented:
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
 
anthony_castillonCommented:
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
 
applekannaCommented:
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
 
jarasaCommented:
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
 
jarasaCommented:
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
 
jarasaCommented:
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
 
DoneenAuthor Commented:
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
 
jarasaCommented:

The jdbcodbc driver comes with the JDK you should have it

Javier

0
 
DoneenAuthor Commented:
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
 
jarasaCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now