Prepared Statement problem in my servlet's data manager

[John Turner]

Hi all,

I am having trouble with a prepared statement in my Java Servlet's data manager where it inserts a member into the database. I believe the syntax is correct, but I always seem to get the error:

SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)' at line 1

Here is my code

// data manager
public class DataManager
{
	private DBCon dbc;  
	private Connection con; 
	private Statement stmt;  
	private PreparedStatement ps = null; 
	
	public DataManager()
	{
		dbc = new DBCon(); 
		con = dbc.getConnection();
		stmt = dbc.getStatement();
	}

        public void insertMember( String first, String last, String birth, String line1, String line2, String town, String province, String zip, String eadd, 
	                           String contactno, String user, String pass, String quest, String ans, String agree ) 
    {
        try
        {
            
	    
			String strUpdate = "INSERT INTO mdb VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(strUpdate);
			ps.setString(1, first);
			ps.setString(2, last);
			ps.setString(3, birth);
			ps.setString(4, line1);
			ps.setString(5, line2);
			ps.setString(6, town);
			ps.setString(7, province);
			ps.setString(8, zip);
			ps.setString(9, eadd);
			ps.setString(10, contactno);
			ps.setString(11, user);
			ps.setString(12, pass);
			ps.setString(13, quest);
			ps.setString(14, ans);
			ps.setString(15, agree);
         
			ps.executeUpdate( strUpdate ) ;
        } 
        catch (SQLException ex) 
        {        
            System.out.println("SQLException: " + ex.getMessage());
        }

// connector
public class DBCon
{
	private Connection con;  
	private Statement stmt;   
	
	public DBCon()
	{
		try
		{
			Class.forName( "com.mysql.jdbc.Driver" );
		}
		catch ( Exception ex )
		{
			System.out.println( "Exception is: " + ex.toString( ) ) ;
		}
		
		try
		{
			conn = DriverManager.getConnection( "jdbc:mysql://localhost/theauctiondb?user=root&password=mypass" );
			
			stmt = conn.createStatement( );
			
			//ps = conn.prepareStatement();
		}
		catch ( SQLException ex )
		{
			System.out.println( "SQLException: " + ex.getMessage( ) );
		}
	}
	
	public Connection getConnection( )
	{
		return conn ;
	}
	
	public Statement getStatement( )
	{
		return stmt ;
	}
	
	public void closeConnection()
	{
		try
		{
			if ( stmt != null )
			{
				stmt.close( ) ;
			}
			
			if ( conn != null )
			{
				conn.close( ) ;
			}
		}
		catch ( SQLException ex )
		{
			System.out.println( "SQLException: " + ex.getMessage( ) );
		}
	}
}

Hope you can help me solve this problem.

[Tim McGuire]

First step to solve this. is to back up and run that SQL in a SQL editor like dbvisualizer or Toad for MYSQL and see if it works there.

[laila NiHai]

I think the column names are left out...

String strUpdate = "INSERT INTO mdb (column_names) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

[Paul Clapham]

The error is in line 41 of the code you posted. Review the API documentation for PreparedStatement. In particular the documentation for the executeUpdate method.

[Deepakkumar Devarajan]

Yes,Paul Clapham is right. The method prepareStatement holds parameterized SQL statements that needs to be sent to the database. So while calling executeUpdate, no parameter is required.