Hello
I need small help.
i need to upload the excel data to access through JAVA code.
please give me the pseudo code.
thanks
ravikumar
Printable View
Hello
I need small help.
i need to upload the excel data to access through JAVA code.
please give me the pseudo code.
thanks
ravikumar
Hey did u get any answer for this prob....??? Rite now i need the answer for this... :) If u have found d solution means pls help m out.. :)
Connect to an access database:
Connect to Access Database using JDBC-ODBC bridge : JDBC ODBC*«*Database*«*Java Tutorial
Read an excel file with java:
Apache POI - the Java API for Microsoft Documents
Now, combine the two.
Thank for the reply.. :) It helped m.. :)Quote:
Originally Posted by mervinshe
No problem! :(clap):
Hey hi... :) I jst need one more help ya... :)I just dunno how to upload files into ms access using jsp in net beans 7.0.1... :( I know that we have to create an OLE object as the design type in the table... :) But i dunno how to insert files into ms access using a jsp.. :( I need a step by step explanation... :( Its jst buggin m coz m not able to find a proper solution for this problem... :( Pls help m out.. :) Thanks in advance.. :)
Insert files? You mean data into the access database?Quote:
But i dunno how to insert files into ms access using a jsp
Insert files means i meant lk files with the extension .pdf,.doc,.docx, etc.... :):)Quote:
Originally Posted by quad64bit
You generally don't want to store files like that in a database. If you must, then you can read the file as binary data (byte array) and store it as a binary type in the database. You will need to make sure you use a sufficiently large data type, as the files you mentioned can be anywhere from a few kilobytes to hundreds of megabytes!
1. Use a PreparedStatement.
2. Use setBinaryStream() to stream the data to the relevant OLE field.
3. execute.
Hi der.. :) I need a small help.. :) In one of my jsp pages i keep on getting the "GENERAL ERROR"... ;( I just dounno wat to do... ;( Can u pls help m wid it.. ;( My coding is... :) M doin this in net beans and it gets connected to MS ACCESS... :)
<!DOCTYPE HTML>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import= "java.sql.*" %>
<%@ page import= "java.io.*" %>
<%@ page import= "java.lang.*" %>
<%@ page import= "java.util.*" %>
<%
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver " +"(*.mdb, *.accdb)};DBQ=C:\\Users\\DELL\\Documents\\more.acc db";
Connection con = DriverManager.getConnection(url);
System.out.println("Connected!");
Statement stmt=null,stmt1=null,stmt2=null;
stmt=con.createStatement();
stmt1=con.createStatement();
stmt2=con.createStatement();
String StudentName=request.getParameter("firstname");
String lastName=request.getParameter("lastname");
String passWord=request.getParameter("password");
String dateOfbirth=request.getParameter("dob");
String Email=request.getParameter("email");
String mobileNumber=request.getParameter("mobilenumber");
double mnum=Double.parseDouble(mobileNumber);
String Address=request.getParameter("add");
String fatherName=request.getParameter("fn");
String motherName=request.getParameter("mn");
String fatherOccupation=request.getParameter("fo");
String motherOccupation=request.getParameter("mo");
String Email1=request.getParameter("email1");
String mobileNumber1=request.getParameter("mobilenumber1" );
double mnum1=Double.parseDouble(mobileNumber1);
int sid=1000;
String cname=request.getParameter("course");
Random rand = new Random();
int randnum = rand.nextInt(sid+1);
String aw=request.getParameter("aware");
String s1="Artificial Intelligence";
String s2="Java";
String s3="Data Structures";
String s4="Software Testing";
String s5="Mobile Communications";
stmt.executeUpdate("INSERT INTO parentInfo VALUES("+randnum+",'"+fatherName+"','"+motherName+ "','"+fatherOccupation+"','"+motherOccupation+"',' "+Email1+"',"+mnum1+")");
stmt1.executeUpdate("INSERT INTO studentInfo(StudentID,StudentName,lastName,passWor d,dateOfBirth,Email,mobileNumber,Address,Awareness ) VALUES ("+randnum+",'"+StudentName+"','"+lastName+"','"+p assWord+"','"+dateOfbirth+"','"+Email+"',"+mnum+", '"+Address+"','"+aw+"')");
if(cname.equals(s1))
{
int cid=1;
stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s1+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");
}
else if(cname.equals(s2))
{
int cid=2;
stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s2+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");
}
else if(cname.equals(s3))
{
int cid=3;
stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s3+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");
}
else if(cname.equals(s4))
{
int cid=4;
stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s4+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");
}
else if(cname.equals(s5))
{
int cid=5;
stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s5+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");
}
else
{
out.println("Please contact your Administrator");
}
con.close();
}
catch (SQLException e)
{
System.out.println("SQL Exception: "+ e.toString());
}
catch (ClassNotFoundException cE)
{
System.out.println("Class Not Found Exception: "+cE.toString());
}
%>
1. Use code tags when posting code.Quote:
Originally Posted by mervinshe
2. Don't do database stuff in a JSP page.
3. AT least do printStackTrace() for exceptions, so you know where your errors are coming from.
4. Use a PreparedStatement instead of concatenating your SQL, then bind (setXXX()) the variables into the PreparedStatement and then execute it.
I think that should give you enough to be going on with.
ETA:
5. Close your resources (ie your connection) in a finally block.
Thanku for the help... :):)
Can you please provid m with a sample code coz m new to all these suffs... :(:(Quote:
Originally Posted by Tolls
Here's an example that can help you
Insert File
Not a bad example (and a lot better than most on the net), though there is a bug in the reading of the Blob's binary stream.
That should be '> -1' not '> 0', since it is quite possible for the read() call to return nothing into the byte[], but for the stream to have not reached the end.Code:while ((byteLeidos = salida.read(arreglo)) > 0) {
archivoSalida.write(arreglo, 0, byteLeidos);
}
'-1' means "I have reached the end of the stream".
Tolls thank you very much, I'll take into account to correct and to my knowledge:)
Hi der Mr.Tolls and Mr.Arielb :)Quote:
Originally Posted by arielb
Thank you for helping m... :) I have got an idea and i have done this... :) I'll explain it in a step by step process... :):)
Step 1: I am using MS-ACCESS 2010 as my database and these are the details regarding it... :)
Database name=upl
Table name=up
No. of fields=1
Field name=fil
Data type for that field name=attachment
I am not sure of the data type to be used :( There is no predefined BLOB or CLOB in MS-ACCESS :(
Step 2: I am using netbeans 7.0.1 and i have created two jsp pages... :)
-->The first jsp page is "index.jsp" which is a form, from where i browse the file and click on the upload button.. :)
The coding of "index.jsp" is:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head><title>UPLOAD</title></head>
<body>
<form method="post" action="plsworkout.jsp" name="upform" enctype="multipart/form-data">
<table width="60%" border="0" cellspacing="1" cellpadding="1" align="center" class="style1">
<tr>
<td align="left"><b>Select a file to upload :</b></td>
</tr>
<tr>
<td align="left">
<input type="file" name="uploadfile" size="50">
</td>
</tr>
<tr>
<td align="left">
<input type="hidden" name="todo" value="upload">
<input type="submit" name="Submit" value="Upload">
<input type="reset" name="Reset" value="Cancel">
</td>
</tr>
</table>
</form>
</body>
</html>
--> My second jsp page is "plsworkout.jsp":):) In this page i give the connection and i upload the files into the database :)
I have the "System.out.println("Connected!")" statements just to know till where the code gets executed coz i am a newbie :( I get the
Connected statements upto "Connected3" but after that i get an error lk "File not found" :( I found out that the file name given dynamically is not being considered as the filename :( I dunno how to insert file name dynamically :(
Pls help m out with all these problems and if i have done anything wrong means pls do correct m :(:(
The coding for "plsworkout.jsp" is,
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<%@ page import= "java.sql.*"%>
<%@ page import= "java.io.*" %>
<%@ page import= "java.lang.*" %>
<%@ page import= "java.util.*" %>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver " +"(*.mdb, *.accdb)};DBQ=C:\\Users\\DELL\\Documents\\upl.accd b";
Connection con = DriverManager.getConnection(url);
System.out.println("Connected!");
InputStream input = null;
int entered=0;
PreparedStatement pst = null;
try
{
File file;
String insert;
String name=request.getParameter("uploadfile");
con.setAutoCommit(false);
insert = "INSERT INTO up VALUES(?)";
System.out.println("Connected1!");
pst = con.prepareStatement(insert);
System.out.println("Connected2!");
file = new File("name");
System.out.println(file);
System.out.println("Connected3!");
input = new FileInputStream("file");
System.out.println(input);
System.out.println("Connected4!");
pst.setBinaryStream(1, input, (int) file.length());
System.out.println("Connected5!");
entered = pst.executeUpdate();
con.commit();
}
catch (SQLException e)
{
System.out.println("SQL Exception: "+ e.toString());
}
finally
{
con.close();
}
%>
See point 2 in my post above. That second JSP should be a servlet.
Also that first JSP should simply be an html file, as there is no Java in there at all. By making it a JSP you are forcing the container to parse this and turn it into a servlet, which is a lot more overhead than simply having it serve up a plain html page.
And your upload code in the JSP is wrong.
You need to handle this as a Multi-part request.
Apache has a FileUpload package that is quite useful for this sort of thing.
Quote:
Originally Posted by Tolls
What FileUpload package? Can you please elaborate it :)