JDBC Exception

[Pat Watson]

Hello,

I have a treeSet that I iterate through and am trying to insert it into a table but I get an error that the syntax is incorrect near ONE value? What could be the problem?

Here is the code piece

		// Now read the new employee IDs
		for(String empid:newEmpSet) {
			// System.out.println(empid);
			sqlStmnt = "INSERT INTO TBLEmpID(Employee) VALUES(" + empid + ")";
		}
		
		stmnt.executeUpdate(sqlStmnt);
		

And this is the exception
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'MP903'.

Thank you for your help

[Carey Brown]

Your empId is a String which SQL requires to be enclosed in single quotes.

[Paul Clapham]

If you had used a PreparedStatement, though, you wouldn't have to concern yourself with that sort of details.

[Pat Watson]

Carey Brown wrote:Your empId is a String which SQL requires to be enclosed in single quotes.

Yes forgot about that part. Thank you

[Pat Watson]

Paul Clapham wrote:If you had used a PreparedStatement, though, you wouldn't have to concern yourself with that sort of details.

I haven't used PreparedStatements much but I will look into it

[Paul Clapham]

If your query is constant, e.g. SELECT that, that, theother from SOMETABLE, then a Statement is fine. But if your query requires parameters, like in your example, you should use PreparedStatement. That not only means you don't have to know how to format Strings and Dates and Times and so on for your database, it also protects you from SQL injection attacks.

Even formatting Strings isn't as simple as you might guess. Yes, you have to put quotes around them to make valid SQL, but if the String contains a quote (like a user named O'Brien) you have to escape that quote in the way your database wants it escaped. This can get to be a real pain, but PreparedStatement takes care of all that tedious stuff.