0
String x=jTextField1.getText();

After connecting to the database the query is:

String query="INSERT INTO student(A) VALUES('"+a+"') where date=' " +x+ " ';";

stmt.executeUpdate(query);

*a is a string which has a letter P assigned to it.

The error i am getting is "....check your mysql syntax....corresponding to the date='"+x'"; "

I want to compare the date entered in the textfield to the date in the mysql 'date' column and if it is correct,the 'a' value (which is P) should be written in column A in the same row of the date entered...

Please help...

Thank you...

seagull
  • 1
  • 1

1 Answers1

1

I see a space after/before the single quote.

Furthermore date is also an SQL keyword, so better not use that as field name. You could write

`date`

Addition

Sorry, I realized that I erred (date cannot be a field queried as we are inserting a new record). Either you mean:

String query = "INSERT INTO student(A) VALUES('P') WHERE CURRENT_DATE() = '2012-05-09'";

Or date is a field, and you just want to set another field:

String query = "UPDATE student SET A = 'P' WHERE `date` = '2012-05-09'";

Inserting new records into same table

This is not allowed to do immediately, so one has to use a temporary table.

CREATE TEMPORARY TABLE tmp (A VARCHAR(1));

INSERT INTO tmp (A) 
SELECT 'P' FROM student WHERE dt = '...';

INSERT INTO student(A) 
SELECT A FROM tmp;

DROP TABLE tmp;
Joop Eggen
  • 107,315
  • 7
  • 83
  • 138
  • Try enter a valid date: `2012-05-08`. – Joop Eggen May 08 '12 at 08:32
  • Your first comment(in addition) is right.... But i did not get why we cannot query date?? * try{Class.forName("java.sql.DriverManager"); Connection con = (Connection) DriverManager.getConnection ("jdbc:mysql://localhost:3306/attendance","root", "******"); Statement stmt = (Statement) con.createStatement(); String query="INSERT INTO student(A) VALUES('"+a+"') where dt='"+x+"';";stmt.executeUpdate(query); } * This is the code.There are 2 columns called 'dt' and 'A'in table 'student'.I want to insert("p") in A where a date in dt resembles x(date from textfield) – seagull May 09 '12 at 14:52
  • INSERT creates a new record, so I thought you might want to change an existing record. But I'll edit the answer another time. – Joop Eggen May 09 '12 at 19:36