I've created database in Mysql and would like to access db tables from localhost jsp. This is what I've created for far:

<%@ page import="java.sql.*" %> 
<%@ page import="java.io.*" %> 
<HTML>
<HEAD>
    <TITLE>insert data using JSP </TITLE>
</HEAD>
<BODY bgcolor="#ffffcc">
  <font size="+3" color="green"><br>Welcome to my world !</font>
  <FORM action="statement.jsp" method="get">
    <TABLE style="background-color: #ECE5B6;" WIDTH="30%" >
    
      <TR>
	      <TH width="50%">Name</TH>
		  <TD width="50%"><INPUT TYPE="text" NAME="name"></TD>
	  </tr>
      <TR>
	     <TH width="50%">City</TH>
		 <TD width="50%"><INPUT TYPE="text" NAME="description"></TD>
	  </tr>

	  </TABLE>
<%
   String name = request.getParameter("name");
   String description = request.getParameter("description");
   
	String url = "jdbc:mysql://localhost/random";

    Connection con = null;

     MyStatement mstatement = null;

     Class.forName("com.mysql.jdbc.Driver").newInstance();
     int updateQuery = 0;
     
	
	 if(name!=null && description!=null){
		
	     if(name!="" && description!="") {
            try {

              con = DriverManager.getConnection
              (url, "trainer", "eagle");

              String queryString = "INSERT INTO lesson1(Name,
              Description)" + "VALUES (?, ?)";

              mstatement = con.myStatement(queryString);
              mstatement.setString(1, name);
			  mstatement.setString(2, description);
			  updateQuery = mstatement.executeUpdate();
              if (updateQuery != 0) { %>
	           <br>
	           <TABLE style="background-color: #E3E4FA;" 
                   WIDTH="30%" border="1">
		      <tr><th>Data is inserted successfully 
                    in database.</th></tr>
		   </table>
              <%
              }
            } 
            catch (Exception ex) {
            out.println("Unable to connect to batabase.");
   
            }
            finally {

                mstatement.close();
                con.close();
            }
	  }
	}
%>
  </FORM>
 </body> 
</html>

Although, it's throwing the following error:

An error occurred at line: 44 in the jsp file: /statement.jsp
String literal is not properly closed by a double-quote
41:               con = DriverManager.getConnection
42:               (url, "trainer", "eagle");
43: 
44:               String queryString = "INSERT INTO lesson1(Name,
45:               Description)" + "VALUES ("?", "?")";
46: 
47:               mstatement = con.myStatement(queryString);

What exactly I'm missing here?

First of all in the code you have posted you write this:

String queryString = "INSERT INTO lesson1(Name,Description)" + "VALUES (?, ?)";

And at the error:

String queryString = "INSERT INTO lesson1(Name,Description)" + "VALUES ("?", "?")";

So which one is deployed? Because the first is correct. Also I believe you are missing a submit button or I didn't look closely

Also I trust that the MyStatement extends the PreparedStatement.


And one last thing when you solve your problem, delete the entire file and start all over. Because you do not write java inside jsp apart for the minimum needed to display the data (a for loop) and do minor calculations. Database access is not minor calculations.

Have the form submit to a servlet, do all the calculations there: Open connection, insert values and then according to the results you will direct to the appropriate page.
Your jsp should have only the form and an if statement that will determine whether to display any messages or not depending on the results. But the actions will take place in the servlet where you will get the result and send it to the jsp.

In order to accomplish the above I strongly suggest to read the post at the top of this forum. It has everything you will need.

http://www.daniweb.com/forums/thread141776.html

This article has been dead for over six months. Start a new discussion instead.