Hi

I have created a database in mysql.

How do I know what the auto incremented value is when I need to delete a specific record, i.e. if the user scrolls through the records and selects a specific field?

My code:

deleteEntry = connection.prepareStatement("DELETE FROM People WHERE xxxxx = xxxxx");

What should the values of the xxxx be?

Being auto incremented only affects adding rows. Once they're there, the auto inc field is just like any other, except that it's normally the primary key, since it's bound to be unique. (Call it ID, for example)

So when you display the list of records for the user to scroll through, you'd better put the ID somewhere: e.g.

foreach ($rows as $row)
{ <<display $row>>
<<show Delete checkbox: give it name=del$ID>>
}

Then when you get the POST args, anything starting del means delete the record with the ID given.

HTH

I have managed to figure out some code but there seems to be an error.

My code:
public int deleteRecord(String mName, String mSurname)
{
int result = 0;
try
{
Statement statement = connection.createStatement();
result = statement.executeUpdate("DELETE FROM People WHERE PeopleID = ?");

if(result == 1)
{
System.out.println("Row is deleted.");
}
else
{
System.out.println("Row is not deleted.");
}
}
catch (SQLException s)
{
System.out.println("SQL statement is not executed!");
}

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


My error:
Exception in thread "AWT-EventQueue-0" java.lang.NumberFormatException: For input string: ""


This is my first time working with a database, so I really do not know where to fix the error.
Please help

I'm not familiar with the system you are using, but you seem to have written a Java function. How is this function going to be called from a web page?

The function has two arguments, String mName, String mSurname, but your DB call uses
"WHERE PeopleID = ?" How is this function going to know the value of ID? (Answer: It isn't.)

If you want to, you can do the DB delete operation with a clause that matches the name and surname, but I suggested to you this is not the best way. Because what will happen is that somehow there will be a bunch of entries in the table with the same values (e.g. John and Smith), and while the user will be able to see which one they want to delete based on some other information, your program will delete them all. That's why you should use a unique key to identify the record. And I suggested how to do it.

I expect your error above is because the DB has a PeopleID (this is ungrammatical English, by the way: it should be PersonID) which has a numeric type, and you have supplied it with "?" a string, there's a type mismatch.

When it comes to deleting entries in a database.... I don't..... I normally have a field called 'delete' and when I do the insert into database that field is given a 'N' value... and of course when i delete the entry it is updated to 'Y'.... of course the queries to the table must have a 'WHERE 'delete' = 'N'' to display the data.



I assume that there fields corresponding to the above variables in the database....

I also notice that there is no connection to the database....or is the host, password and username entered elsewhere

Before you can query the db.... you need to connect to the db

With the assumptions I made above, the code to delete would be.... also when doing a query I like to put as many criteria as i can in to narrow the search, and by putting LIMIT 1.... will only change 1 row.

I don't understand what your answer is supposed to tell us...

I suggested to the OP that the way to delete a particular record is to have the ID (unique key) of the record you want to delete. You have reiterated the wrong way to do it -- by matching just two fields which may not be unique -- and added "LIMIT 1". This means that instead of deleting all of the wrong records, you will only delete one wrong record. Is this supposed to be a userful improvement?

Brian,


Thank you for your response, you are quite right, guess i was trying to work with the information i had in the code and not advising as I would have done... if it was my database I would have applied a search to find the correct entry and then once i had the entry i would then delete the entry using it's ID (primary key) as per the following,


by doing the above there is the ability to undelete should there be a reason.

usage of deletion flags also makes it easier to work with foreign keys. This is especially tough in complecated database schemas when you try to delete for instance customer - the major entity of database where almost all other tables are linked to.