CODEWITHSUNDEEP
javasql-serverselectinsert
Chris
Chris

Reputation: 45

Java MS SQL Select inside Insert

I want to insert 5 datas into one SQL table, the last four of which are already working and fine, but the first one should be from another table, a String, giving an Int to the table in which im inserting it. And this is my solution so far. However i'm still getting an error: "The index 5 is out of range."

PreparedStatement stmt = connection.prepareStatement("INSERT INTO RECORDS (LocationId, RecId, RecValues, YearTime, HourTime) VALUES " +
                "((SELECT LocationId from Locations where Location_name = 'Mic HR1'), ?, ?, ?, ?)");
        stmt.setInt(1,1);
        stmt.setInt(2, recid);
        stmt.setInt(3, inputData);
        stmt.setDate(4, sqlDate);
        stmt.setTime(5, Time.valueOf(dtf.format(now)));
        stmt.executeUpdate();

Upvotes: 0

Views: 107

Answers (1)

ArturEld
ArturEld

Reputation: 78

Actually you have only four parameters defined on your query, because LocationId is being calculated by the inner select you provided. That's why you are getting the error.

Try this:

PreparedStatement stmt = connection.prepareStatement("INSERT INTO RECORDS (LocationId, RecId, RecValues, YearTime, HourTime) VALUES " +
                "((SELECT LocationId from Locations where Location_name = 'Mic HR1'), ?, ?, ?, ?)");
        stmt.setInt(1, recid);
        stmt.setInt(2, inputData);
        stmt.setDate(3, sqlDate);
        stmt.setTime(4, Time.valueOf(dtf.format(now)));
        stmt.executeUpdate();

Upvotes: 1

Related Questions