0

im trying to make an update query on my java, i keep getting error in my sql syntax but i already tried executing the query in my sqlyog and it worked, but it doesnt work in my program, here is my query 

String query = "UPDATE t_surat_masuk SET kode_klasifikasi = '"+kode_klasifikasi+"',"

                        + " pengirim = '"+pengirim+"',"
                        + " no_surat = '"+no_surat+"', "
                        + " tgl_surat = '"+tanggalsurat+"',"
                        + " perihal = '"+perihal+"',"
                        + " tgl_surat_masuk = '"+tanggalmasuk+"', "
                        + " penerima = '"+penerima+"', "
                        + " WHERE id_surat='"+id_surat+"'";

sorry for my bad english, thank you

Improve this question
6
  • Please also add your schema definition to your answer. Commented May 12, 2018 at 5:54
  • How are you executing the SQL string? And...what is the Error you are getting? Commented May 12, 2018 at 5:57
  • i used the '?' but it gave me the same error Commented May 12, 2018 at 10:07
  • It's ? with NO quotes around it. The whole point of statements is that they handle the quoting for you. Commented May 12, 2018 at 10:08
  • right syntax to use near '?, pengirim = ?, no_surat = ?, tgl_surat = ?, perihal = ?, tgl_surat_masuk = ?, ' at line 1 this is the error if i used the question mark Commented May 12, 2018 at 10:12

1 Answer 1

Reset to default
1

Your exact syntax error is that you have a stray comma after the SET clause, before the WHERE clause. But the best answer here is for you to use a prepared statement:

String sql = "UPDATE t_surat_masuk SET kode_klasifikasi = ?, pengirim = ?, ";
sql += "no_surat = ?, tgl_surat = ?, perihal = ?, tgl_surat_masuk = ?, penerima = ? ";
sql += "WHERE id_surat = ?";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, kode_klasifikasi);
ps.setString(2, pengirim);
ps.setString(3, no_surat);
ps.setString(4, tanggalsurat);
ps.setString(5, perihal);
ps.setString(6, tanggalmasuk);
ps.setString(7, penerima);
ps.setInt(8, id_surat);
ps.executeUpdate();

Note that I assumed all columns are strings, except for the id_surat column, which sounds like an integer column. You may have to change the types of some of the above setters to get it to work.

In general, you can see that with a prepared statement, you may write out what is essentially the actual raw query. This makes it much harder to have syntax errors of the sort you currently have.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

why im getting a parameter index out of range error?
@LuthfiMusafa You shouldn't be getting that error from my code.