Member Avatar for nadius

Hello everyone,

I realise this may seem like a log winded way of doing it but I need to insert data from a JSP into a database, here is the set up.

I have an HTML form, that passes it's data to a JSP. The JSP acts as a bridge between the form and a JAvaBean that contains my SQL. The JSP puts the form data into relevant variables.

The problem I have is that line of code that should send all information to the JavaBean appears to be fine, yet when I run the code (I am running tomcat in Eclipse), tomcat comes back with an SQLException error.

The error is state to come from this line in my JSP:

String results = matchResults.insertMatchResult(opponent, homeAway, clubScore, opponentScore);

Below you can see my code for both the JSP and the JavaBeean, note: I have only showed the relevant module of the JavaBean.

###################################################
JSP
###################################################

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<jsp:useBean id="matchResults" class="matchBeans.MatchResults" />
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
Hello, you FINALLY managed to get to "insertMatchResult.jsp THEN!!"

<%
	String opponent = request.getParameter("opponent");
	String homeAway = request.getParameter("homeaway");
	String clubScore = request.getParameter("clubscore");
	String opponentScore = request.getParameter("opponentscore");

	//String results;
	
	String results = matchResults.insertMatchResult(opponent, homeAway, clubScore, opponentScore);
%>
	<TABLE>
		<TR>
			<TD><%out.println(opponent);%></TD>
		</TR>
		<TR>
			<TD><%out.println(homeAway);%></TD>
		</TR>
		<TR>
			<TD><%out.println(clubScore);%></TD>
		</TR>
		<TR>
			<TD><%out.println(opponentScore);%></TD>
		</TR>
	</TABLE>
<BR>
</body>
</html>

##############################################
JavaBean
##############################################

public String insertMatchResult(String opponentName, String homeAway, 
			String clubScore, String opponentScore) throws SQLException
	{	
		
		connectAndCreateStatement();
		
		statement.executeUpdate("INSERT INTO Results"
			+ " (opponents, homeOrAway, thisClubScore, opponentsScore)"
			+ " VALUES ('"+opponentName+"', '"+homeAway+"'," +
						" '"+clubScore+"', '"+opponentScore+"')");
		
		disconnectFromDb();
		return;
	}

#############################################

Many thanks in advance for any advice you might be able to offer.

Daniel

  • 3 Contributors
  • 2 Replies
  • 119 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by ~s.o.s~

Dani AI

Generated

— the SQLException thrown at the line that calls your bean usually points to one of a few concrete issues: the actual SQL sent to Access is malformed or has wrong data types, the JDBC driver/connection string is wrong (or Access file locked), or the bean/JSP are not handling checked exceptions correctly. ’s backslash suggestion isn’t needed when Java strings are delimited with double quotes; that change alone won’t address runtime SQL errors. was right to push away from raw concatenation — parameter binding fixes many of the common problems here.

  • Reproduce the failing SQL and error: log SQLException.getMessage(), getSQLState() and getErrorCode(), and log the exact SQL text you’re executing. Paste that SQL into Access (or a DB tool) to see the exact Access error.
  • Confirm column types in Access. If a column is numeric, bind an int (or omit quotes) rather than inserting a quoted string.
  • Stop building SQL by concatenation. Use a PreparedStatement and bind parameters so you avoid quoting/escaping mistakes and SQL injection.
  • Check your bean method signature: if it declares String it must return a String (or change it to void/boolean). Also catch or translate SQLExceptions inside the bean or handle them in the JSP — JSP methods cannot throw checked exceptions that aren’t declared by _jspService.
  • For Access on modern JVMs, prefer a maintained JDBC driver such as UCanAccess rather than the legacy JDBC‑ODBC bridge.

A minimal pattern to follow is: open the Connection, create a PreparedStatement with placeholders, bind values using the right setter methods, call executeUpdate(), and always close resources (try-with-resources is easiest). See Oracle’s JDBC prepared-statement guidance for details and examples: PreparedStatement tutorial. If using MS Access, consider UCanAccess as the current driver option: UCanAccess driver.

Recommended Answers

All 2 Replies

Member Avatar for ze0001ng

Try changing the following code:

+ " VALUES ('"+opponentName+"', '"+homeAway+"'," +						" '"+clubScore+"', '"+opponentScore+"')");

To:

+ " VALUES (\'"+opponentName+"\', \'"+homeAway+"\'," +						" \'"+clubScore+"\', \'"+opponentScore+"\')");

That is:
Add the escape \ for the single quote mark

Member Avatar for ~s.o.s~

Don't use scriptlets, use something along the lines of JSTL or JSF. Don't use normal statements, use prepared / callable statements. Read the official Sun J2EE developer guide for J2EE best practices.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.