Hello, I'm doing a school project in .JSP and I came across this syntax error while inserting data in my database.

DB Query: INSERT INTO MainApplicant (MainApplicantId,FullName, MaritalStatus, Height, Weight, Gender, AgeAsOfLastBDay,
DateOfBirth, PlaceOfBirth, IDNo, CountryOfIssue, Nationality, Race, HomeNo, OfficeNo, MobileNo, Address, PostalCode, CorrAddress, CorrPostalCode, EmailAddress) VALUES ('P-8915710B,'Jaslyn ', 'Single', '160', '45', 'Female', '20', '12/4/1989', 'Singapore', 'S8915710B', 'SG', 'Singaporean', 'Chinese', '-', '-', '-', 'Bishan St 13', '650190', '', '', 'Jaslyn@hotmail.com')

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect
syntax near 'Jaslyn'.

If you realise, there's no closing quote at the end of "P-S8915710B"
I suspect the error is from my JSP page.

<%
String id = request.getParameter("idNo");
String fullName = request.getParameter("fullName");
String maritalStatus = request.getParameter("maritalStatus");
String height = request.getParameter("height");
String weight = request.getParameter("weight");
String gender = request.getParameter("gender");
String age = request.getParameter("age");
String dateOfBirth = request.getParameter("dateOfBirth");
String placeOfBirth = request.getParameter("placeOfBirth");
String idNo = request.getParameter("idNo");
String countryOfIssue = request.getParameter("countryOfIssue");
String nationality = request.getParameter("nationality");
String race = request.getParameter("race");
String homeNo = request.getParameter("homeNo");
String officeNo = request.getParameter("officeNo");
String mobileNo = request.getParameter("mobileNo");
String address = request.getParameter("address");
String postalCode = request.getParameter("postalCode");
String corrAddress = request.getParameter("corrAddress");
String corrPostalCode = request.getParameter("corrPostalCode");
String emailAddress = request.getParameter("emailAddress");
%>
<%
String command1 = request.getParameter("command");

if ((command1 != null) && (command1.equals("insert")))
{
MainApplicant lifeinsured = new MainApplicant();

lifeinsured.setId("P-"+idNo);
lifeinsured.setFullName(fullName);
lifeinsured.setMaritalStatus(maritalStatus);
lifeinsured.setHeight(height);
lifeinsured.setWeight(weight);
lifeinsured.setGender(gender);
lifeinsured.setAge(age);
lifeinsured.setDateOfBirth(dateOfBirth);
lifeinsured.setPlaceOfBirth(placeOfBirth);
lifeinsured.setIdNo(idNo);
lifeinsured.setCountryOfIssue(countryOfIssue);
lifeinsured.setNationality(nationality);
lifeinsured.setRace(race);
lifeinsured.setHomeNo(homeNo);
lifeinsured.setOfficeNo(officeNo);
lifeinsured.setMobileNo(mobileNo);
lifeinsured.setAddress(address);
lifeinsured.setPostalCode(postalCode);
lifeinsured.setCorrAddress(corrAddress);
lifeinsured.setCorrPostalCode(corrPostalCode);
lifeinsured.setEmailAddress(emailAddress);

lifeinsured.createMainApplicant();

The codes in red.
How do I set

lifeinsured.setId("P-"+idNo);

so that it my ID will be closed with a quote without any syntax near my fullname.

sorry if it's kind of confusing. but I really need some help here.
Thanks a lot!

The problem is where you create your query not where you read the value and call the set method:
Assuming this:

public void setId(String id) {
   this.id = id;
}

>>> query = . . . . + "'" + id +"', "

I think you forgot to add the single quote at the query.
Post the relevant code where you generate the query

The problem is occurring where you are constructing your SQL query you need to show us that code, for us to trace the error.

Also Just for your Information, in case one of the values you wish to insert inside your database contains problem characters like the single quote ('), I advise you use the "PreparedStatement" as shown below:-

String name = "abc'jhd";
.
.
Connection con = DriverManager.getConnection(.....);
PreparedStatement ps = con.prepareStatement("INSERT INTO student(name) values (?)");
ps.setString(1,name);
ps.executeUpdate();
.
.
.

Here's my insert statement in my MainApplicant.java

public boolean createMainApplicant() 
	{
		
	boolean success = false;
	ResultSet rs = null;
	DBController db = new DBController();

		
String dbQuery = "INSERT INTO MainApplicant (MainApplicantId, FullName, MaritalStatus, Height, Weight, Gender, AgeAsOfLastBDay, DateOfBirth, PlaceOfBirth, IDNo, CountryOfIssue, Nationality, Race, HomeNo, OfficeNo, MobileNo, Address, PostalCode, CorrAddress, CorrPostalCode, EmailAddress) VALUES 
('" + id + "','" + fullName + "', '" + maritalStatus + "','" + height + "', '" + weight + "', '" + gender + "', '" + age + "', '" + dateOfBirth + "', '" + placeOfBirth + "', '" + idNo + "', '" + countryOfIssue + "', '" + nationality +"', '" + race + "', '" + homeNo + "', '" + officeNo + "', '" + mobileNo + "', '" + address + "', '" + postalCode + "', '" + corrAddress + "', '" + corrPostalCode + "', '" + emailAddress + "')";

	rs = db.updateRequestKey(dbQuery);
	try {
		if (rs.next()) {
				
			id = rs.getString(1);
			success = true;

		}
	} catch (Exception e) {
		e.printStackTrace();
	}


	db.terminate();
	return success;

}
VALUES ('" + id + "','" + fullName

I don't see a missing quote. Are you sure this is the code you tried to run?

yup, very sure. I've double checked it a lot of time but the syntax error still exist.

can you do a System.out.println of the values:
id and fullName before you set them at the lifeinsured object?

The syntax error is solved!!! Thanks for all your advices!! =)

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.