CODEWITHSUNDEEP
javamysqlsql
Mr Asker
Mr Asker

Reputation: 2380

Unknown column '1' in 'field list'

I am trying to insert data into my stop table the stops table as well as the route table are being created, but the program fails to insert the data into the stop table, and I am getting this error:

Unknown column '1' in 'field list'

I appreciate any help.

    stt.execute("CREATE TABLE IF NOT EXISTS routes"
             + "(route_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, "
             + "route INT(11) NOT NULL)" );
    stt.execute("INSERT INTO routes(route) VALUES"
            + "(1),"
            + "(9)");

    stt.execute("CREATE TABLE IF NOT EXISTS stops"
            + "(stop_id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, "
            + " name varchar(30) NOT NULL, "
            + " lat double(10,6) NOT NULL, "
            + " longi double(10,6)NOT NULL,"
            + "route_id INT, FOREIGN KEY fk_route_id(route_id) REFERENCES routes(route_id) )" );

    stt.execute("INSERT INTO stops(route_id, name,  lat, longi) VALUES"
            + "(`1`, 'Alex allee', '83.868937', '18.665545' ),"                     
            + "(`1`,'city street', '83.840642', '18.701246' )," 
            + "(`2`,'apple street', '83.740642', '18.761246' ),"
            + "(`2`,'wall street', '83.868256', '18.669520' )");

Upvotes: 1

Views: 854

Answers (1)

Jens
Jens

Reputation: 69440

For escaping strings you hae to use single quotes. backtick are needed for column or table names. For numerical there is no escape charater nesassary.

because your route_id values are escape by backticks mysql interpreting it as a column name. remove them and your query will work.

stt.execute("INSERT INTO stops(route_id, name,  lat, longi) VALUES"
            + "(1, 'Alex allee', 83.868937, 18.665545 ),"                     
            + "(1,'city street', 83.840642, 18.701246 )," 
            + "(9,'apple street', 83.740642, 18.761246 ),"
            + "(9,'wall street', 83.868256, 18.669520 )");

Upvotes: 5

Related Questions