Deleting a Data Base Entry

[EmbeddedHelp]

Evening all,

I have created a program that reads a Data Base contents, displays the information, prompts the user to press enter, then in theory they should enter an account number and that specific accounts details be deleted from the data base. Finally the contents of the data base should be displayed once more.

However, it does not work...

Here is my code:

//Model solution to Task 2 of Practical 15.

import java.sql.*;
import java.util.*;

public class Q3
{
	private static Statement statement;
	private static Connection connection;

	public static void main(String[] args)
	{
		Scanner keyboard = new Scanner(System.in);

		try
		{
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			connection = DriverManager.getConnection(
								"jdbc:odbc:Finances","","");
		}
		catch(ClassNotFoundException cnfEx)
		{
			System.out.println("* Unable to load driver! *");
			System.exit(1);
		}
		catch(SQLException sqlEx)
		{
			System.out.println(
						"* Cannot connect to database! *");
			System.exit(1);
		}

		System.out.println("\nInitial table contents:\n");
		displayTableContents();
		System.out.print("\nPress <Enter> to continue...");
		keyboard.nextLine();

		try
		{
			System.out.println("Enter Account Number to be deleted: ");
			int accountNum = keyboard.nextInt();
			String remove = "SELECT acctNum, balance FROM Accounts "
				+ "WHERE acctNum = "+ accountNum;
				int results = statement.executeUpdate(remove);
		} 
		catch(SQLException sqlEx)
		{
			System.out.println("* Cannot execute query! *");
			closeDown();
			sqlEx.printStackTrace();
			System.exit(1);
		}

		System.out.println(
						"\nTable contents after deletion:\n");
		displayTableContents();
		
		// try
		// {
			// String change = "UPDATE Accounts"
				// + " SET surname = 'BLOGGS',"
				// + "firstNames='Fred Joseph'"
				// + " WHERE acctNum = 112233";
		// int result = statement.executeUpdate(change);
		// }
		// catch(SQLException sqlEx)
		// {
			// System.out.println("* Cannot execute query! *");
			// closeDown();
			// sqlEx.printStackTrace();
			// System.exit(1);
		// }
		
		// System.out.println(
		// "\nTable contents after Change:\n");
		// displayTableContents();

		closeDown();
	}

	static void displayTableContents ()
	{
		ResultSet results = null;

		try
		{
			statement = connection.createStatement();
			results = statement.executeQuery(
							"SELECT * FROM Accounts");
			while (results.next())
			{
				System.out.println("Account no. "
									+ results.getInt(1));
				System.out.println("Account holder:  "
									+ results.getString(3) + " "
									+ results.getString(2));
				System.out.printf("Balance: %.2f %n",
										results.getFloat(4));
				System.out.println();
			}
		}
		catch(SQLException sqlEx)
		{
			System.out.println("* Error retrieving data! *");
			closeDown();
			sqlEx.printStackTrace();
			System.exit(1);
		}
	}

	static void closeDown()
	{
		try
		{
			connection.close();
		}
		catch(SQLException sqlEx)
		{
			System.out.println("* Unable to disconnect! *");
		}
	}
}

Output is as follows:

"Initial table contents:

Account no. 333333
Account holder: Sally Jones
Balance: 5000.00

Account no. 112233
Account holder: Fred Joseph BLOGGS
Balance: 752.85

Press <Enter> to continue...
Enter Account Number to be deleted:
112233
* Cannot execute query! *
java.sql.SQLException: No row count was produced
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:296)
at Q3.main(Q3.java:44)"

If anyone could advise on how to tackle this error it would be much appreciated!

(Please ignore commented out information).

Cheers.

[Ezzaral]

The query in your "remove" string is a select query - not a delete query.

Select queries return result sets and are called with executeQuery().

Delete/update queries return the number of rows affected and are executed with executeUpdate().

Do you know how to delete rows from a table? You need to re-write that query to be a delete statement instead of a select.

[EmbeddedHelp]

I have tried every method in my notes...still no working solution.

Can you suggest how to write the delete query??

Thanks for the help.

[Ezzaral]

The general form would be

DELETE FROM [tableName] WHERE [expression]

You need to fill in your own table name and WHERE expression to specify the rows to be deleted.