Greetings again!
I'm learning how to write to and read from databases using JDBC, (using Netbeans) and I've run into a snag. I'm trying to save data to a database, but when I try the following exception message is thrown:

Column 'COURSEID' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a 
CREATE or ALTER TABLE  statement then 'COURSEID' is not a column in the target table.

The screenshot shows the server and database set up:

JDBC.PNG

I've tried changing the case of the columns in my INSERT statement and checked to make sure the server was started (though that would have given a different error message) and that the spelling was correct, but I think I'm missing something. The following is the code where I make the query (or insert) to the database:

// Retrieve course data from form
        Course newCourse = 
                new Course(courseNameText.getText(),
                           courseDepartmentText.getText(),
                           courseInstructorText.getText(),
                           courseDescriptionText.getText());

        // Store the course data in the database
        //Retrieve form data
        int courseID = Course.getCourseID();
        String courseName = newCourse.getCourseName();
        String department = newCourse.getCourseDepartment();
        String instructor = newCourse.getInstructorName();
        String courseDescription = newCourse.getCourseDescription();

        String dataSource = "jdbc:derby://localhost:1527/StarfleetAcademyDB";
        try(Connection conn =
                DriverManager.getConnection(dataSource)){
            Statement st = conn.createStatement();
            Class.forName("org.apache.derby.jdbc.ClientDriver");
           PreparedStatement saveCourse = 
                   conn.prepareStatement("INSERT INTO "
                            + "AcademyCourses(CourseID, "
                            + "CourseName, "
                            + "CourseDepartment, "
                            + "CourseInstructor, "
                            + "CourseDescription) "
                            + "VALUES(courseID, courseName, department, instructor, courseDescription)");
            conn.close();
        }
        catch(SQLException sqe){
            JOptionPane.showMessageDialog(null, sqe.getMessage(), "ERROR", JOptionPane.ERROR_MESSAGE);
            System.out.println("Error: " + sqe.getMessage());
        }
        catch(ClassNotFoundException cnfe){
            JOptionPane.showMessageDialog(null, cnfe.getMessage(), "ERROR", JOptionPane.ERROR_MESSAGE);
            System.out.println("Error: " + cnfe.getMessage());
        }

I could use another pair of eyes on this. I appreciate any help you can offer.

NOTE: I've attempted to upload the full .java file, but it seems the system won't allow it even if I change the extension to .txt. If you need to see the full file, please let me know and I'll send it via e-mail.

Tekkno

Edited 1 Year Ago by TekknoDraykko

 + "VALUES(courseID, courseName, department, instructor, courseDescription)"

That string just contains the literal variable names ("courseID" etc). Didn't you intend to concatenate the values of those variables into the statement?

Yes, that was my intent. My algorithm is set up to create a Course object which will be used to retrieve the values from the form. Those values will then be passed to the database.

The values are retrieved from the form in these lines of code:

//Retrieve form data
        int courseID = Course.getCourseID();
        String courseName = newCourse.getCourseName();
        String department = newCourse.getCourseDepartment();
        String instructor = newCourse.getInstructorName();
        String courseDescription = newCourse.getCourseDescription();

Edited 1 Year Ago by TekknoDraykko

This article has been dead for over six months. Start a new discussion instead.