CODEWITHSUNDEEP
javasqloracle-databasejdbcsql-update
shubhs9
shubhs9

Reputation: 23

Trying to update SQL tables using Java

I am building a database of 8 tables and I am using Java Frames to build a UI that would perform following functions:

Insert, Delete, Update, Search and Select.

All the functionalities work well except for update. Following is the code for update:

   stmt_update = conn_update.createStatement();
   stmt_update.executeUpdate("UPDATE Conference SET C_NAME = '" + confname + "', C_YEAR = " + yr
                + ", START_DATE = to_timestamp('" +stdate +"','yyyy-mm-dd HH24:MI:ss.FF1'), END_DATE = to_timestamp('" +enddate +"','yyyy-mm-dd HH24:MI:ss.FF1'),"
                + "COUNTRY = '"+country_update+"', CITY = '"+city_update+"', VENUE = '"+venue_update+"',"
                + ",  CONTACT_EMAIL = '"+c_email_update+"' where conference_id = '" + confid + "'");

Table structure is:

(
Conference_ID varchar(10) NOT NULL,
C_Name varchar(20),
C_Year numeric(4,0),
Start_Date date,
End_Date date,
Country varchar(10),
City varchar(10),
Venue varchar(10),
Contact_Email varchar(10),
PRIMARY KEY(Conference_ID)
);

Error is:

ORA-01747: invalid user.table.column, table.column, or column specification

I thought there was something to do with the date inputs.

Any suggestions would help greatly.

Thanks.

-----Update-------------- Tried using prepare Statement

stmt_update = conn_update.prepareStatement("UPDATE Conference SET C_NAME = '" + confname + "', C_YEAR = " + yr
                    + ", START_DATE = to_timestamp('" +stdate +"','yyyy-mm-dd HH24:MI:ss.FF1'), END_DATE = to_timestamp('" +enddate +"','yyyy-mm-dd HH24:MI:ss.FF1'),"
                    + "COUNTRY = '"+country_update+"', CITY = '"+city_update+"', VENUE = '"+venue_update+"',"
                    + ",  CONTACT_EMAIL = '"+c_email_update+"' where conference_id = '" + confid + "'");
            stmt_update.executeUpdate("UPDATE Conference SET C_NAME = '" + confname + "', C_YEAR = " + yr
                    + ", START_DATE = to_timestamp('" +stdate +"','yyyy-mm-dd HH24:MI:ss.FF1'), END_DATE = to_timestamp('" +enddate +"','yyyy-mm-dd HH24:MI:ss.FF1'),"
                    + "COUNTRY = '"+country_update+"', CITY = '"+city_update+"', VENUE = '"+venue_update+"',"
                    + ",  CONTACT_EMAIL = '"+c_email_update+"' where conference_id = '" + confid + "'");

Still getting the same error.

Error:

ORA-01747: invalid user.table.column, table.column, or column specification

Upvotes: 2

Views: 95

Answers (2)

Charif DZ
Charif DZ

Reputation: 14751

this to much use this :

java.sql.PreparedStatement stm=your_conn_variable.prepareStatement("UPDATE table_name set field_name=?,seconde_field=? ");
//
stm.setString(1,your_value);
stm.setString(2,your_seconde_value);
stm.executeUpdate();
//will not have to write all this lines and forget all ' , ;

here is an example like your case :

stmt_update = conn_update..prepareStatement( "UPDATE Conference SET C_NAME = ?")
stmt_update.setString(1,confname);
stmt_update.executeUpdate();

Upvotes: 0

Arulkumar
Arulkumar

Reputation: 13247

   stmt_update = conn_update.createStatement();
   stmt_update.executeUpdate("UPDATE Conference SET C_NAME = '" + confname + "', C_YEAR = " + yr
                + ", START_DATE = to_timestamp('" +stdate +"','yyyy-mm-dd HH24:MI:ss.FF1'), END_DATE = to_timestamp('" +enddate +"','yyyy-mm-dd HH24:MI:ss.FF1'),"
                + "COUNTRY = '"+country_update+"', CITY = '"+city_update+"', VENUE = '"+venue_update+"',"
                + ",  CONTACT_EMAIL = '"+c_email_update+"' where conference_id = '" + confid + "'");
                   __ Here one extra comma is exist.

Those two commas causing the error.

So your code should be:

   stmt_update = conn_update.createStatement();
   stmt_update.executeUpdate("UPDATE Conference SET C_NAME = '" + confname + "', C_YEAR = " + yr + ", " 
                + "START_DATE = to_timestamp('" + stdate + "','yyyy-mm-dd HH24:MI:ss.FF1'), END_DATE = to_timestamp('" + enddate + "','yyyy-mm-dd HH24:MI:ss.FF1'), "
                + "COUNTRY = '" + country_update + "', CITY = '"+city_update+"', VENUE = '"+ venue_update + "', "
                + "CONTACT_EMAIL = '" + c_email_update + "' WHERE conference_id = '" + confid + "'");

Upvotes: 1

Related Questions