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

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

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.