Gar04
asked on
Access Parameter querys in DMX with JSP
hey i have an access data base
with many parameter queries
i was wondering if it is possible to have the same queries incorporated into dreamweaver
if so how exactly
i am using Tomcat and JSP
i'm sure it is possible but have no idea where to begin
any pointers would be great
bit like
select stuff
from tables
where value= item on dropdown list selected by user
Gar
with many parameter queries
i was wondering if it is possible to have the same queries incorporated into dreamweaver
if so how exactly
i am using Tomcat and JSP
i'm sure it is possible but have no idea where to begin
any pointers would be great
bit like
select stuff
from tables
where value= item on dropdown list selected by user
Gar
<%@ page import="java.util.*, java.sql.*" %>
<%
Class.forName("sun.jdbc.od bc.JdbcOdb cDriver");
Connection con = null;
PreparedStatement ps = null;
try
{
con = DriverManager.getConnectio n("jdbc:od bc:mydb");
ps = con.prepareStatement("sele ct a, b from table where c=?");
ps.setString(1, request.getParameter("c")) ;
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Object a = rs.getObject("a");
Object b = rs.getObject("b");
// do what you want with results
}
}
%>
<%
Class.forName("sun.jdbc.od
Connection con = null;
PreparedStatement ps = null;
try
{
con = DriverManager.getConnectio
ps = con.prepareStatement("sele
ps.setString(1, request.getParameter("c"))
ResultSet rs = ps.executeQuery();
while (rs.next())
{
Object a = rs.getObject("a");
Object b = rs.getObject("b");
// do what you want with results
}
}
%>
ASKER
hey objects thanks for the code
would it be possible for you
to explain to me briefly how it works
as i am a bit unsure??
gaz
would it be possible for you
to explain to me briefly how it works
as i am a bit unsure??
gaz
>> with many parameter queries
Haven't use MSAcces for a long while, but if my memery serves me, you'll need to use callable statement to call MS-Access parameter queries as well as update/delete/instead queries.
I'll try to see if I can find more information for you.
Haven't use MSAcces for a long while, but if my memery serves me, you'll need to use callable statement to call MS-Access parameter queries as well as update/delete/instead queries.
I'll try to see if I can find more information for you.
ASKER
thanks
I could find the webpage that I came acrossed about that topic, but I found something in my old code, there is an example of how to call into MS Access Parameter Query Object, you can use similar interface to call other type of MS Access Query, like append, update, insert and delete query.
CallableStatement cs = null;
cs = conn.prepareCall("{ call query1( ? ) }");
cs.setInt(1, 12 ); //depend on parameter type, you'll use setString, setDouble and etc.
// more set statement if you have more parameters.
ResultSet rs = cs.executeQuery();
while (rs.next()) {
......
CallableStatement cs = null;
cs = conn.prepareCall("{ call query1( ? ) }");
cs.setInt(1, 12 ); //depend on parameter type, you'll use setString, setDouble and etc.
// more set statement if you have more parameters.
ResultSet rs = cs.executeQuery();
while (rs.next()) {
......
I meant I COULDN'T find the webpage. :)
> to explain to me briefly how it works
It makes a queuy on the database using a request parameter.
It makes a queuy on the database using a request parameter.
// get connection to database
con = DriverManager.getConnectio n("jdbc:od bc:mydb");
// create query statement
ps = con.prepareStatement("sele ct a, b from table where c=?");
// bind request parameter 'c' to first query statement
// eg. if queury contains ?c=3 the query would become:
// select a, b from table where c=3
ps.setString(1, request.getParameter("c")) ;
// execute the queury
ResultSet rs = ps.executeQuery();
// loop thru the results one row at a time
while (rs.next())
{
// get value of column 'a'
Object a = rs.getObject("a");
// get value of column 'b'
Object b = rs.getObject("b");
// do what you want with results
}
con = DriverManager.getConnectio
// create query statement
ps = con.prepareStatement("sele
// bind request parameter 'c' to first query statement
// eg. if queury contains ?c=3 the query would become:
// select a, b from table where c=3
ps.setString(1, request.getParameter("c"))
// execute the queury
ResultSet rs = ps.executeQuery();
// loop thru the results one row at a time
while (rs.next())
{
// get value of column 'a'
Object a = rs.getObject("a");
// get value of column 'b'
Object b = rs.getObject("b");
// do what you want with results
}
ASKER
Hey I eventually used something like this:
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.od bc.JdbcOdb cDriver");
Connection conn = null;
CallableStatement cs = null;
try
{
conn = DriverManager.getConnectio n("jdbc:od bc:BillBoa rdCompany" );
cs = conn.prepareCall("{Call SiteSearchQ(?, ?, ?)}"); >>>>>>>>>>>>>>>>>>>>this calls the MS Access Query and then sends the
>>>>>>>>>>>>>>>>>>>> necessary parameters to the query
String siteName = request.getParameter("Site Name");
if( siteName == null ) siteName = "";
int selCyc=Integer.parseInt( request.getParameter("Sele ctCyc"));
int year=Integer.parseInt( request.getParameter("Sele ctYear"));
cs.setString(1, siteName );
cs.setInt(2, selCyc );
cs.setInt(3, year);
ResultSet rs = cs.executeQuery();
%>
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" %>
<% Class.forName("sun.jdbc.od
Connection conn = null;
CallableStatement cs = null;
try
{
conn = DriverManager.getConnectio
cs = conn.prepareCall("{Call SiteSearchQ(?, ?, ?)}"); >>>>>>>>>>>>>>>>>>>>this calls the MS Access Query and then sends the
>>>>>>>>>>>>>>>>>>>> necessary parameters to the query
String siteName = request.getParameter("Site
if( siteName == null ) siteName = "";
int selCyc=Integer.parseInt( request.getParameter("Sele
int year=Integer.parseInt( request.getParameter("Sele
cs.setString(1, siteName );
cs.setInt(2, selCyc );
cs.setInt(3, year);
ResultSet rs = cs.executeQuery();
%>
is it working ok?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ghost