The column either is not in any table in the search list in the FROM database.

Question

I am working on a database program in Java and I had a problem with it, telling me that what I am looking for is not in the table.

public static void displayRecord(ResultSet rs) throws SQLException
{
    String assignment = rs.getString("assignment");
    int grade = rs.getInt("grade");
    System.out.println(assignment + " | " + grade + "\n");
}

public void findGrade(String assignment){

    String assignString;
    int gradeInt;

    try{
        Class.forName(driver).newInstance();
    }
    catch (Exception err){
        System.err.println("Unable to load the embedded driver.");
        err.printStackTrace(System.err);
        System.exit(0);
    }
    Connection connected = null;

    try{

        int ble = 25;
        connected = DriverManager.getConnection(protocol + databaseName);

        Statement statement = connected.createStatement();

        ResultSet rs = null;

        rs = statement.executeQuery("SELECT assignment, grade FROM student WHERE assignment = " + assignment);

        if(rs == null ) {
            System.out.println("Assignment not found!");
        }
        else {
            displayRecord(rs);
        }

        rs.close();

        connected.close();


    }

    catch (SQLException err){
        System.err.println("SQL error.");
        err.printStackTrace(System.err);
        System.exit(0);
    }
}

This results in an error:

SQL. java.sql.SQLSyntaxErrorException: "ASSIGNMENT1" - FROM, , HAVING GROUP BY. CREATE ALTER TABLE, "ASSIGNMENT1" .     at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException( )     at org.apache.derby.impl.jdbc.Util.generateCsSQLException( )     at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException( )     at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException( )     at org.apache.derby.impl.jdbc.EmbedConnection.handleException( )     at org.apache.derby.impl.jdbc.ConnectionChild.handleException( )     at org.apache.derby.impl.jdbc.EmbedStatement.execute( )     at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery( )      Student.findGrade(Student.java:153)      GradeBookTester.main(GradeBookTester.java:17)

, , .

, , , .

DataBase:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateGradeBook{

private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
private static final String protocol = "jdbc:derby:";

public static void main(String[] args)
{
    try{
        Class.forName(driver).newInstance();
        System.out.println("Loaded the embedded driver.");
    }
    catch (Exception err){
        System.err.println("Unable to load the embedded driver.");
        err.printStackTrace(System.err);
        System.exit(0);
    }

    String dbName = "GradeBook";
    Connection connected = null;

    try{
        System.out.println("connecting to and creating the database...");
        connected = DriverManager.getConnection(protocol + dbName + ";create=true");
        System.out.println("database created.");

        Statement statement = connected.createStatement();

        statement.execute("CREATE TABLE student" + "(student_name varchar(50), student_id int, assignment varchar(80), grade int)");
        System.out.println("created Student table.");


        connected.close();
    }

    catch (SQLException err){
        System.err.println("SQL error.");
        err.printStackTrace(System.err);
        System.exit(0);
    }
}

}

public class GradeBookTester {

public static void main(String[] args) {
    Student bob = new Student();

    bob.setStudentName("Bob");
    bob.setStudnetID(12345);

    bob.addGrade("ASSIGNMENT1", 100);
    bob.addGrade("assignment2", 50);
    bob.addGrade("assignment3", 25);
    bob.addGrade("assignment4", 100);

    bob.displayGrades();

    bob.findGrade("ASSIGNMENT1");
}

}

Answer 1

, ,

rs = statement.executeQuery("SELECT assignment, grade FROM student WHERE assignment = '" + assignment + "'");

, , ,

SELECT, FROM student WHERE = = ASSIGNMENT1

SELECT, FROM student WHERE = 'ASSIGNMENT1'

,