System.out.println("Enter new name: ");
String stdName = input.readLine();
st.executeUpdate("UPDATE Customers SET stud_name='"+stdName+"' WHERE stud_ID='45698'");Ok, this is my problem, this command should update\replace the existing column stud_name with a new name. But it wasn't able to replace it. There were no sql errors.
Did I miss anything? Or are there any suggestions?
You might not committing the update to database.
remove the double quotes
UPDATE Customers SET stud_name='+stdName+' WHERE stud_ID='45698'
remove the double quotes
UPDATE Customers SET stud_name='+stdName+' WHERE stud_ID='45698'
You can do this only when you wish replace current value of stud_name with new value which will be stdName. However in this case stdName represent variable and not value so ivatanako approach is correct.
ivatanako can please post rest of your code in order to find the problem?
Sorry for the late response.
Can't post the whole code though, its too long.
case 3:
{
System.out.println("1 Name");
System.out.println("2 ID");
System.out.println("Enter choice: ");
int choice = Integer.parseInt(input.readLine());
if (choice == 1){
// enter name
System.out.println("Enter name: ");
stdName = input.readLine();
resultSet = command.executeQuery("SELECT * from Customers where stud_name='" + stdName + "'");
while (resultSet.next( )) {
// lets get his NAME
String name = resultSet.getString(2);
// and his ID
String idnum = resultSet.getString(3);
System.out.println("Name " + name + " with ID" + idnum);
}
// ask them which one should you choose
System.out.println("********************************");
System.out.println("[1] Change name");
// haven't done all the coding but this is how it should see for the first option
// get input
int editchoice = Integer.parseInt(input.readLine());
// conditional statement
if (editchoice == 1){
System.out.println("Enter new name: ");
stdName = input.readLine();
/*** this is where is should be updating the database ***/
st.executeUpdate("UPDATE Customers SET stud_name='"+stdName+"' WHERE stud_ID='45698'");
}
// end if
}
// break should be in here
break;
}Thanks everyone for the reply but I still can't have it to work... Been spending 4 hours for this.. :(
Oh and by the way, I miss out on telling that I used a fixed value for stud_ID but it is just to test if it actually edit that certain row, but it didnt.
Not sure if you are storing the ID as a number or a string in the db, but if it is a number then drop the single quotes in the where clause. Other than that the SQL look just fine.
When I removed the single quote a error occurs, unclosed character literal.
Is there any other way where I can edit a table?
>Is there any other way where I can edit a table?
I don't think so...
You probably need to pipe out all the information from the table to an array or text file then delete that table then add it again, but this time define id to be an integer.
A quick way to check if id is an integer in your database is to use the describe command.
http://dev.mysql.com/doc/refman/5.0/en/describe.html
1. try firing query like select * from Customers where stud_ID='45698' . If it returns some rows means that there is record with stud_ID='45698'
If no row is selected then the datatype of field need to be checked.
If it is NUMBER then last query itself should give error,if is CHAR with length more than 5 then the '45698' is stored with trailing spaces so it need to be trimmed while using in WHERE condition.
2. If above all is perfect then the AutoCommit mode of connection object in java need to be checked. By default autocommit is true, but when it is false then commit() method need to be called explicitly to commit changes in database. You should print autocommit using getAutoCommit() . If it is false, u should commit using commit() method of connection object.
Far better to use a PreparedStatement, no trouble figuring out what quotes (single, double) a specific database engine requires.
And of course never eat any exceptions that may occur, as they're likely to tell you what went wrong.
Capturing the output of the executeUpdate statement can also tell you a lot. If it doesn't return the number of records you expect to be updated there's something amiss with your SQL (or rather the SQL is correct but doesn't do what you think it should).
And as said, do see what happens when you do an explicit commit on the transaction after a successful update. Possibly it doesn't get committed.
Thank you all for the reply. Got it to work, i didnt commit/close connection.
I dont if this is the right place to post a question, but can I refresh a database with java. What command should that be?
refresh? There's no such term in database land so you'll first have to define it.
My question is wrong. What I really mean is that, CAN I updated two columns in one QUERY?
And can I perform addition inside a sql query like adding the value of 2 columns, is that possible and if it is, how?
Yes, it is possible, and is much more of an SQL question. Have you tried doing it? Post what you have tried.
I tried updating two columns with this sql command but it does not work, did I miss anything?
command.executeUpdate("UPDATE Students SET Quiz1='"+sq1+"',Raw_Score="+fD+" WHERE stud_name='"+name+"'");However, I haven't tried performing addition of two column values, I dont know how. Can you please post a snippet on how to perform this?
To avoid repeating previous sugestions I will ask. Did you sorted out problem with your original query that you posted in first place? Or that problem still remain?
Yes. The code now is working. My problem now is how to update 2 columns in a single query.
command.executeUpdate("UPDATE Students SET Quiz1='"+sq1+"',Raw_Score='"+fD+"' WHERE stud_name='"+name+"'");missing single quotes around fD
Got it to work. But how about my other question, how do I add a value of a column with another column value..Like:
quiz1 quiz2 final_grade
20 20 40
how is that performed? And what other mathematical operation can be done inside a query?
And pay attention to reply number 13. It will make your life soooooo much easier.
Try it. Think of how you might do it, and try it out, then post that if it doesn't work, and we'll nudge you again. Hint: Use Google to search for an SQL reference.
Maybe a quick introductory SQL course would help you...
You seem to be lacking the most fundamental understanding of the language, and without it you'll spend the rest of your time having to gasp for air and hope someone else writes your code for you whenever you have to achieve anything.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.